Manual Testing – SQL Queries Interview Questions and Answers –Part 2 :

Manual Testing – SQL Queries Interview Questions and Answers –Part 2 :

Views :It is a Virtual Table

If we do any modifications in view table,then those changes automatically effected in view table & parent table and vice-versa

create view myview as select * from emp where deptno=10

insert into myview values(12,'venky',1000,10)

The above statement inserts the values into base table emp AND view table myview

insert into v values(12,'venky',1000,20)

The above statement inserts the values into only the base table emp not into myview table

create view myview as select * from emp where deptno=20 with check option

The above statement can not inserts the values into the base table emp and myview table

SP_HELPTEXT  myview   // Displays the definition of that view

Indexes:Used to improve the performance of queries

Indexes are generally created on the following columns :

Primary Key Column

Foreign Key Column

Columns which are frequently used in where clause

Can not create on :

columnscontaning duplicate or null values

columns which are not used frequently

Types of indexes :

Clustered Index

Only one index allowed per table.The order of values in a table and the order of values in index is also same

Non Clustered Index

The order of values in a table and the order of values in index is different

create clustered index myindex on emp(empno)

If we want maintain unique values then we can mention unique keyword

create unique clustered index myindex1 on emp(empno)

createnonclusted index myindex2 on emp(ename,sal)

SP_HELPINDEX EMP   // Display the list of indexex  under that table

T-SQL : (Transact SQL)

DECLARE @A INT,@N INT

SET @A=4

IF(@A>4)

PRINT ' A VALUE IS MORE THAN 4'

ELSE

PRINT 'A VALUE IS LESS THAN 4'

SET @N=1

WHILE(@N<4)

BEGIN

PRINT @N

SET @N=@N+1

END

User Defined Functions :

If the functions are created by the user,then it is user defined function

udf are same as user defined stored procedure but with a return type

create function function_name(@parameter1 datatype,@parameter2 datatype,......)

returnsdatatype

as

begin

declare @vardatatype

------

------

return @var

end

Calling to that function

selectdbo.function_name(value1,value2,...)

Ex:

create function avg(@a int,@b int)

returnsint

as

begin

declare @c int

set @c=(@a+@b)/2

return @c

end

selectdbo.avg(34,67)

User Defined Stored Procedures:

createproc[ecure] procedure_name(@parameter1 datatype,@parameter2 datatype,......)

as

begin

---------

---------

end

To call that stored procedure :

execprocedure_name [value1,value2,.....]

Ex1:

create procedure emp_details @a int

as

begin

select * from emp where empno=@a

end

execemp_details 10

Ex2:

create procedure emp_insert @a int,@b float,@cvarchar(20)

as

begin

insert into emp values(@a,@b,@c)

end

execemp_insert 56,12.4,'venkat'

Cursors:

Cursor is a logical area which is used to retrieve a particular n th record.

Types of Cursors:

1.Static Cursor

2.Dynamic Cursor

3.Forward_ONLY_Cursor

syntax:

declarecursor_name cursor cursor_type for select * from table_name

Syntax to open a cursor:

opencursor_name

Syntax to fetech the records from cursor

fetch {first/next/prior/last/absolute n/relative n} from cursor_name

syntax to close a cursor:

closecursor_name

syntax to deallocate a cursor

deallocatecursor_name

Ex:EMP

EMPNO   ENAME  ESAL

234    VENKAT  30000

123    KRIS    40000

678    RAM     20000

130    KASI    20000

declaremycursor cursor static for select * from emp

openmycursor

fetch first from mycursor

234 VENKAT 30000

fetch NEXT from mycursor

123    KRIS    40000

fetch PRIOR from mycursor

234 VENKAT 30000

fetch LAST from mycursor

130    KASI    20000

fetch ABSOLUTE 2 from mycursor

123    KRIS    40000

fetch ABSOLUTE -2 from mycursor

678    RAM     20000

updateemp set empno=100 where empno=234  

// Updations will be reflected only after closing the cursor

fetch first from mycursor

234 VENKAT 30000

fetch RELATIVE 2 from mycursor

678    RAM     20000

fetch RELATIVE -2 from mycursor

234    VENKAT  30000

closemycursor

openmycursor

fetch first from mycursor

100 VENKAT 30000

Dynamic Cursor:

declaremycursor cursor dynamic for select * from emp

fetch first from mycursor

100 VENKAT 30000

updateemp set empno=234 where empno=100   // Updations will be reflected

fetch first from mycursor

234 VENKAT 30000