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