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
