19. What is View ?
View is the additional layer of table, which will protect the table by exposing the appropriate columns to the external one.example view is like a Window of a home, through window no one can't see the whole home. through window it can allow only the particular room thats it. Views is used to expose the required column to the others
SELECT * FROM pro_view
Here is used to encrypt the view code so no one can see the code, in the above code products table have four columns but in view we are exposing two columns alone.Using view we can insert , update and delete data in the table.
Following rules must be follow for insert , update and delete using view.
· If you are using a view to insert data, then your view should have a single select and also all the mandatory columns of the “being edited” table must be included in the view unless the table has a default values for all NOT NULL columns of the table.
· Secondly don’t forget, for views with “WITH CHECK” options enabled, it’s important to keep in mind that the data begin inserted qualifies in the WHERE clause of the view and is certain to be selected by the view. Simply put the data you insert is picked up while you select from your view.
· If the view is having joins with more than one table, then most cases chances of modifying capabilities are negligible unless INSTEAD OF Triggers are in place to handle the request.
20 . What is Normalization ?
What is Normalization ?
Normalization is a process of eliminating Redundant data and storing the related information in a table.
1. Eliminating Redundant data.
2. Faster update
3. Improve performance
4. Performance in indexes
Let we see different Normalization forms
1. First Normal Form (1NF)
If a Table is said to be 1NF then it should satisfy following rules.
View is the additional layer of table, which will protect the table by exposing the appropriate columns to the external one.example view is like a Window of a home, through window no one can't see the whole home. through window it can allow only the particular room thats it. Views is used to expose the required column to the others
CREATE TABLE products
(
ID INT ,
PRODUCTNAME VARCHAR(50),
CATEGORY VARCHAR(20),
PRICE INT
)
SELECT * FROM dbo.products
CREATE VIEW PRO_VIEW
WITH ENCRYPTION
AS
SELECT PRODUCTNAME,PRICE FROM DBO.PRODUCTS
WITH CHECK OPTION
Here is used to encrypt the view code so no one can see the code, in the above code products table have four columns but in view we are exposing two columns alone.Using view we can insert , update and delete data in the table.
Following rules must be follow for insert , update and delete using view.
· If you are using a view to insert data, then your view should have a single select and also all the mandatory columns of the “being edited” table must be included in the view unless the table has a default values for all NOT NULL columns of the table.
· Secondly don’t forget, for views with “WITH CHECK” options enabled, it’s important to keep in mind that the data begin inserted qualifies in the WHERE clause of the view and is certain to be selected by the view. Simply put the data you insert is picked up while you select from your view.
· If the view is having joins with more than one table, then most cases chances of modifying capabilities are negligible unless INSTEAD OF Triggers are in place to handle the request.
INSERT INTO pro_view VALUES ('WM',23000)
20 . What is Normalization ?
What is Normalization ?
Normalization is a process of eliminating Redundant data and storing the related information in a table.
1. Eliminating Redundant data.
2. Faster update
3. Improve performance
4. Performance in indexes
Let we see different Normalization forms
1. First Normal Form (1NF)
If a Table is said to be 1NF then it should satisfy following rules.
- Each cell must have one value
- Eliminating Duplicate Columns
- Create a separate table for group of related data and each row must be identify by primary key.
That means each cell must have single value and each row should be uniquely identified by Primary key
For Example :
Name
|
Department
|
Phone Number
|
Rajesh
|
Computer
|
3452342,1234563,2345612
|
Suresh
|
Electronics
|
2398521,2323177,5302994
|
Praba
|
Civil
|
3958218
|
In the above we can see the duplicate columns phone numbers have more than one value , we have to eliminate that and create a group of related data with Unique row identification by specifying a primary key for the table
Rule 1. By applying above rule each cell must have one value above table changes like below
Name
|
Department
|
Phone Number
|
Phone Number
|
Phone Number
|
Rajesh
|
Computer
|
3452342
|
1234563
|
2345612
|
Suresh
|
Electronics
|
2398521
|
2323177
|
5302994
|
Praba
|
Civil
|
3958218
|
Rule 2 & 3 . By applying second rule and third rule no more duplicate columns and each row must be unique is applied to above table.
Id
|
Name
|
Department
|
Phone Number
|
1
|
Rajesh
|
Computer
|
3452342
|
2
|
Rajesh
|
Computer
|
1234563
|
3
|
Rajesh
|
Computer
|
2345612
|
4
|
Suresh
|
Electronics
|
2398521
|
5
|
Suresh
|
Electronics
|
2323177
|
6
|
Suresh
|
Electronics
|
5302994
|
7
|
Praba
|
Civil
|
3958218
|
2. Second Normal Form (2NF)
The Table must be in second normal form , Then it should satisfy the following rules.
- It should satisfy first normal form
- Separate the particular columns ,values are duplicated in each row should be place in separate table
- Create the relationship between the tables
From the above table we can see the column name and department are repeated in each row ,This two columns can be maintained in another table and make a relationship between these two tables
EmpId
|
Name
|
Department
|
1
|
Rajesh
|
Computer
|
2
|
Suresh
|
Electronics
|
3
|
Praba
|
Civil
|
Id
|
EmpId
|
PhoneNumber
|
1
|
1
|
3452342
|
2
|
1
|
1234563
|
3
|
1
|
2345612
|
4
|
2
|
2398521
|
5
|
2
|
2323177
|
6
|
2
|
5302994
|
7
|
3
|
3958218
|
In the above table Empid is played as Primary key for the first table and foreign key for the second table.
3. Third Normal Form (3NF)
The table must be in 3NF,if it is satisfying the following rules
- Must be in 2NF
- Separate the columns that are not dependent upon the primary key of the table.
Product
|
Price
|
Tax
|
LED
|
23000
|
20%
|
AC
|
15000
|
10%
|
Fridge
|
12000
|
15%
|
From the above table you can see that Tax Column is not dependent on Product Primary key column, It is dependent on Price so we separate that in to two different table.
Product
|
Price
|
LED
|
23000
|
AC
|
15000
|
Fridge
|
12000
|
Price
|
Tax
|
23000
|
20%
|
15000
|
10%
|
12000
|
15%
|
4. Fourth Normal Form (4NF)
- It should be in 3NF
- The non key columns should be dependent on full primary key instead of partial key , If then separate it.
From the following table "EmployeeName" Non-Key column not dependent on full primary key "ManagerId,EmployeeId,TaskID" it depends upon the EmployeeId Partial Key so it can be separated.
ManagerId
|
EmployeeId
|
TaskID
|
EmployeeName
|
M1
|
E1
|
T1
|
Rajesh
|
M2
|
E1
|
T1
|
Rajesh
|
ManagerId
|
EmployeeId
|
TaskID
|
M1
|
E1
|
T1
|
M2
|
E1
|
T1
|
EmployeeId
|
EmployeeName
|
E1
|
Rajesh
|
21. What is the difference between the SET and SELECT ?
22. What is Indexes ?
Indexes is used to find the records as faster, Two types of indexes are there 1. Clustered and 2. Non- Clustered
A table can only have one clustered index and many non clustered indexes
example:
CREATE CLUSTERED INDEX ind_sx ON dbo.products(ID)
CREATE NONCLUSTERED INDEX ind_nsx ON dbo.products(CATEGORY)
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)
SELECT e.id,e.name ,l.leavedays
FROM employee e
JOIN emp_leave l
ON e.id = l.id
SELECT e.id,e.name ,l.leavedays
FROM employee e
LEFT OUTER JOIN emp_leave l
ON e.id = l.id
SELECT e.id,e.name ,l.leavedays
FROM employee e
RIGHT OUTER JOIN emp_leave l
ON e.id = l.id
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