SQL Server Interview Questions Series - Part 2
22. What is SQL Server Trigger?
A trigger is a special stored procedure which automatically executes when you execute any insert, update, or delete commands on table or view.
23. Can we execute a Trigger directly?
24. How many types of Triggers available in SQL Server?
There are two types of Triggers:
- Instead-of Trigger
- After Trigger
25. What is SQL Instead-of Trigger?
An Instead-of Trigger is a trigger that automatically executed before the execution of insert, update, or delete commands. Instead-of trigger rollback the original command.
CREATE TABLE Temp1 ( ID INT IDENTITY(1,1) PRIMARY KEY, Name varchar(200) ) CREATE TABLE BakTemp1 ( ID INT IDENTITY(1,1) PRIMARY KEY, Name varchar(200) ) CREATE TRIGGER backupTemp1ToBakTemp1 ON TEMP1 INSTEAD OF INSERT AS BEGIN INSERT INTO BakTemp1 SELECT Name FROM INSERTED --inserted table END INSERT INTO Temp1 VALUES ('SQL Interview') SELECT * FROM Temp1 -- No Data SELECT * FROM BakTemp1 -- Data
26. What is SQL After Trigger?
An After Trigger is also automatically executed after the execution of insert, update or delete commands but before the transaction is committed. So you can rollback the original transaction.
CREATE TRIGGER backupTemp1ToBakTemp1 ON TEMP1 AFTER INSERT AS BEGIN INSERT INTO BakTemp1 SELECT Name FROM INSERTED --inserted table END INSERT INTO Temp1 VALUES ('SQL Interview') --Both tables have same data SELECT * FROM Temp1 SELECT * FROM BakTemp1
27. What is ACID rule?
ACID is an acronym of Atomicity, Consistency, Isolation, Durability.
Atomicity guarantee that all the operations in a transactions are either performed or none of them.
Consistency guarantee that transaction move the database from a one consistent state to another consistent state. It never leaves the database into inconstent state.
Isolation guarantee that a transaction does not interfere in other transactions operations.
Durability guarantee after the successfully completion of the transaction all changes are permanent to the database. It can not rollback later.
28. What are Joins in SQL?
A join in sql is a temporary relationship between two ore more tables that we used in sql query to get the data from two or more tables.
29. What is Left Join?
Left join is a temporary relationship between two tables. Left join returns all the rows from the left table and only the matching rows from the right table.
SELECT a.*,b.* FROM Customer a LEFT OUTER JOIN OrderDetails b ON a.ID = b.CustomerID
30. What is Right Join?
Right join is also a temporary relationship between two tables. Right join returns all the rows from the right table and only the matching rows from the left table.
SELECT a.*,b.* FROM OrderDetail a RIGHT OUTER JOIN Customer b ON a.CustomerID = b.ID
31. What is Self Join?
Self join is a join to itself. In self join both the left and right table are the same.
SELECT a.*,b.* FROM MyUser a INNER JOIN MyUser b ON a.UserID = b.UserID
32. What is Cross Join?
In Cross join each row in the left table matchs with the all the rows from the second table. For example, if you have 5 rows in left table and 4 rows in right table then cross join produces 20 rows.
SELECT a.*,b.* FROM Temp1 a CROSS JOIN Temp2 b
33. What is the use of TOP keyword in SQL?
TOP keyword is used to fetch top rows from the table. For example, if you put TOP 5 in query then the query returns only the first five records from the table.
SELECT TOP 5 * FROM Customer
34. What is Index in SQL?
Indexing in SQL is used for fast retrieving the data from the table. There are two types of indexes:
35. What is Clustered Index?
Clustered Index physically sorts the data for the table. Clustered Index works on the Key column. That column is the primary key of the table. There can be only one clustered index on the table.
CREATE CLUSTERED INDEX IX_Cust_ID ON Cust(ID)
36. What is Non-clustered Index?
Non-clustered Index does not physically sort the data for the table. It actually makes seperate memory for logically sorting the table. You can create many non-clustered indexes on the table.
CREATE NONCLUSTERED INDEX IX_Cust_Name ON Cust(Name)
37. What is UNION keyword in SQL?
UNION merge the results of two compatible tables into a single combined table. It removes duplicate rows from the final table.
SELECT ID, Name FROM Cust UNION SELECT ID, Name FROM Cust2
38. What is the difference between UNION and UNION ALL?
UNION ALL returns all rows from both tables but UNION removes duplicate rows from the final combined table.
SELECT ID, Name FROM Cust UNION ALL SELECT ID, Name FROM Cust2
39. Describe some SQL functions.
AVG(): Returns average value
COUNT():Returns the number of rows
MAX(): Returns max value from the rows
MIN(): Returns min value from the rows
SUM(): Sums the columns values
UCASE(): Convert the lowercase characters to uppercase.
LCASE(): Convert the uppercase characters to lowercase.
MID(): Used for extract characters from a text field.
LEN(): Returns the total length of characters in a column value.
40. How to fetch Highest salary from an Employee table.
SELECT MAX(Salary) FROM Employee
41. Write a SQL query to find maximum salary in each department from employee table.
SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID
42. Write a SQL query to find maximum salary and print department name of each depatment from employee table.
There are two ways to write this query.
SELECT d.Name, MAX(e.SALARY) FROM Employee e JOIN Dept d ON e.DeptID = d.ID Group BY e.DeptID, d.Name
SELECT d.Name, Max(Salary) FROM Employee e RIGHT OUTER JOIN Dept d ON e.DeptID = d.ID GROUP BY d.Name