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?

No

24. How many types of Triggers available in SQL Server?

There are two types of Triggers:

1. Instead-of Trigger
2. 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:

1. Clustered
2. Non-Clustered

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 separate 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.

1.

SELECT d.Name, MAX(e.SALARY)
FROM Employee e JOIN Dept d
ON e.DeptID = d.ID
Group BY e.DeptID, d.Name

2.

SELECT d.Name, Max(Salary)
FROM Employee e RIGHT OUTER JOIN Dept d
ON e.DeptID = d.ID
GROUP BY d.Name