SQL Server Interview Questions.

Self Join-

9. What is  ISNULL()

ISNULL(‘LastName’,’No Last Name’)

Here are LastName returns null then “No Last Name” will be your result.

10. Union vs UnionAll

Both are used to combine more than one select queries.

Union removes duplicate records, but Union All doesn’t remove duplicate records.

A union is slower than Union All as Union needs to perform a distinct sort.

11. Union vs Join

Union combine rows from 2 or more tables but JOIN combine columns from 2 or more tables.

12. Cast vs Convert

Use to convert one data type to another.

CAST (expression AS data_type [(length)])

CONVERT (data_type [(length)], expression[style])

13. Mathematical Functions in SQL Server:

ABS(-80.8) returns 80.8

CEILING(20.3) returns 21

FLOOR(15.2) returns 15

POWER(2,3) returns 8

SQRT(81) returns 9

14. User Define Functions in SQL Server:

• Scalar function 
• Inline table valued function 
• Multi statement table valued function 

A scalar function may or may not have a parameter and returns a value.

CREATE FUNCTION myfunction(@parameter1 datatype, @parameter2 datatype)

RETURNS return_datatype

AS

BEGIN

–WRITE FUNCTION CODE HERE

RETURN return value

END

For eg age calculation from DOB can be a scalar function.

15. Inline Table Valued Function

It returns Table.

CREATE FUNCTION myfunction(@city nvarchar(10))

RETURNS TABLE

AS

Return (Select id, name from Table1 where city = @city)

Above function will return the result in a table based on the passed parameter.

So in sql query, we can utilize this function like this:

select * from myfunction(‘New Delhi’)

16. Multi-Statement Table-Valued Function:

It can have BEGIN and END, whereas inline table valued function can’t have.

17. Query to create Temporary Table

Create Table #Table(id int, name varchar(50), city varchar(50))

Temporary table gets created in TempDB and are automatically deleted when they are no longer used.

Types of Temporary Table:

Local and Global –

Local temporary table

It is available for the connection that has created and dropped when the connection gets closed.

Global Temporary Table:

Create Table ##Table1 (id int, name varchar(50))

Global temporary tables are available for all connection.

18. Group By Clause

Below SQL query to get record Group By Year –

19. What is Indexing?

Indexing is a process to make data retrieval faster in a SQL database. Indexes can be created on Tables as well as on Views.

Indexing in SQL is similar to indexes in a book which helps us to find a particular word faster by providing the location of that word.

There are 2 types of Indexing in SQL

Cluster index – It arranges tables in a physical order. Due to this reason, a table can have only one clustered index.

To check a cluster index on a table

Execute sp_helpindex tablename

By default, a clustered index is applicable on a table on the primary key.
But we can change the cluster index key.
To do this we need to delete the existing cluster index and write below query.

create clustered Index CLS_IDX_tble1_firstname_city on table1 (firstname desc, city asc)

It will create a clustered index on table1 for first name and city column. As it is applicable on more than one column, so it is called composite cluster index.
So, a table can have only one clustered index.

Non-clustered index – A table can have more than one non-clustered index. It uses row address to find the record.
A nonclustered index can be understood with index given in a text book.
A clustered index is faster than nonclustered index.

create NonClustered Index NIDX_tble1 on table1(firstname)

20. What is a Cursor?

A cursor is a database object by which we can process and manipulate each and every row. A cursor is useful in a scenario where someone wants to fetch a row and do some manipulation on that row.

Types of Cursor

Forward Only
Static
Keyset
Dynamic

21. What is a Trigger?

A trigger in SQL is a process that automatically executes after meeting certain conditions.
For eg – When a new employee record gets inserted into the Employee Master database, the new record should be created in related tables like Salary Process.

22. SQL Constraints

It is used to specify the rule for data in a table.

Following constraints are available in SQL- NOT NULL, UNIQUE, PRIMARY, FOREIGN KEY etc.

24. SQL Query to find duplicate values in a table.

Below query to check records with duplicate email id.

SELECT firstname, COUNT(email) 
FROM users
GROUP BY email
HAVING COUNT(email) > 1

25. SQL Query to fetch nth highest salary from Employee table

select MAx(salary) as salary from tblEmployee
where salary not in (select top 3 salary from tblEmployee order by salary desc)

26. SQL Query to find the employee who is a manager.

Below is the table structure –

CREATE TABLE employees (
emp_id int NOT NULL,
emp_name varchar(45) DEFAULT NULL,
mng_id int DEFAULT NULL,
PRIMARY KEY (emp_id)
)

Insert few records in employees table

INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (1,’AA’,’4′);
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (2,’XYZ’,’1′);
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (3,’PQR’,’2′);
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (4,’Mark’,’1′);
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (5,’Tom’,’4′);

SQL query to get a list of the employee who is a manager

SELECT DISTINCT e.emp_id AS ‘manager_id’, e.emp_name AS ‘manager_name’
FROM employees e, employees m WHERE e.emp_id = m.mng_id

Output-

27.  SQL query to get Max salary from each department

SELECT DeptID, MAX(Salary) FROM Employee  GROUP BY DeptID