Saturday 11 June 2016

Question and Answers in Sql Server - Part 6


In This post Let we see some important question and answers in Sql Server, which is a continuation of previous post.


23. What is Joins?
       Join is nothing but Fetching records from two or more tables by combining the  two or more tables based on similar condition on a column.

Types of Join :
Inner Join         -  Join between two tables

Outer Join
    Left Outer     -  Left side table can have value but right side table specify null if condition                                fails
    Right Outer  -  Right side table have full value but sets null value in left side if condition                                  fails
    Full Outer     -  Specify both side null if condition fails
   
Cross Join       -   Each row of first table joins with the each row of second table.
   
Self Join          -   A table join with itself



CREATE TABLE EMPLOYEE
(
id INT IDENTITY(1,1),
NAME varchar(40),
addr varchar(50)
)

CREATE TABLE EMP_LEAVE
(
id INT ,
leaveDAYS INT
)


INSERT INTO employee(name,addr) VALUES('raj','chennai')
INSERT INTO employee(name,addr) VALUES('ram','banglore')
INSERT INTO employee(name,addr) VALUES('siva','US')

INSERT INTO emp_leave(id,leavedays) VALUES(1,4)
INSERT INTO emp_leave(id,leavedays) VALUES(5,1)


Inner Join  
SELECT e.id,e.name ,l.leavedays
FROM employee e
JOIN emp_leave l
ON e.id = l.id

  
Left Outer
SELECT e.id,e.name ,l.leavedays
FROM employee e
LEFT OUTER JOIN emp_leave l
ON e.id = l.id

Right Outer
SELECT e.id,e.name ,l.leavedays
FROM employee e
RIGHT OUTER JOIN emp_leave l
ON e.id = l.id


Cross Join
SELECT e.id,e.name ,l.leavedays
FROM employee e
CROSS JOIN emp_leave l



24. How to create a Dynamic Query and execute it ?
Query which is framed at runtime and execute is known as dynamic query.

DECLARE @dynamic NVARCHAR(100)
SELECT @dynamic = 'select * from products'
EXEC(@dynamic)



25. What is cursor and sample ?
      cursor is used to Row by row iteration of records from a table.

DECLARE @id INT
DECLARE @name VARCHAR(40)
DECLARE @category VARCHAR(50)

DECLARE cur CURSOR
FOR SELECT id,productname,category FROM dbo.products
OPEN cur
      FETCH NEXT FROM cur INTO @id, @name,@category
      WHILE @@FETCH_STATUS = 0
      BEGIN
     
      SELECT @id AS 'product id', @name AS 'Product name', @category AS 'Category'
      FETCH NEXT FROM cur INTO @id, @name,@category
     
      END
CLOSE cur
DEALLOCATE cur



26. While loop sample

DECLARE @i INT
SELECT @i = 1

WHILE @i < 8
BEGIN
PRINT @i
SELECT @i = @i +1
END


27. What is the best practices in SQL SERVER ?
       Click here to read




No comments:

Post a Comment