SQL Server Interview Questions.

In this blog, we will see some of the important SQL Interview Questions and Answers.

1. What is a Primary Key?

The primary key is used to uniquely identify a record. A table can not have more than one primary key.
If a table has userid, email, passport no. and userid is the primary key then how to maintain the uniqueness of other columns such as email and passport no.

That is possible with a Unique key. We can have multiple unique key in a table.

Primary key doesn’t allow nulls, unique key allows one null.

2. What is the Unique Key?

A unique key is used yo uniquely identified a record in a table. We can have multiple unique key in a table. The main difference between Primary key and a Unique key is that Primary key doesn’t allow nulls, unique key allows one null.

3. What is Foreign Key?

A foreign key is one table which has a relation to a primary key in another table.

4. Primary Key vs Foreign Key vs Unique Key

A table can have multiple unique and foreign keys. However, a table can have only one primary key.

Unique key columns are allowed to hold NULL values. A Unique key can accept only one null value.
A Foreign key can hold NULL values.

5. Wild Characters in SQL Query

_ is to specify exactly one character.

Select * from table1 email like ‘_@_.com’

This will result in all email id like a@a.com, x@x.com , y@y.com

[] Any character within the bracket.

Ex:- select * from table where name like ‘[KAS]%’

This will result in all name which starts with either K, or A or S.


[^] Any character within the bracket.

Ex:- select * from table where name like ‘[^KAS]%’

This will result in all name which does not start with either K, or A or S.

What is a View?

A view in SQL is a virtual table based on the result made through SQL query. The view looks similar to a table, View also contains data in form of rows and columns.

6. Where vs Having in SQL Query

From the table, only those record retrieved where gender is Male and then grouped.
Select gender, sum(population) as population from counting
Where gender = ‘Male’
Group by gender


All records are retrieved, they are grouped by gender and then shown records where gender is Male

Select gender, sum(population) as population from counting Group by gender
Having gender = ‘Male’

Can we use Where, Group By and Having Clause in a single SQL Query?
Yes, we can use all 3 clauses in a single SQL Query.

SELECT ID,COUNT(ID) ,Name FROM Table1  where ID > 1000 GROUP BY ID, Name 
HAVING COUNT(ID) > 1

7. What is Normalization?

Normalization is the process of organizing data to reduce redundancy in a database. Normalization basically breaks the database into multiple tables by defining relationships between them.

Forms of Normalization -

1NF
2NF
3NF
4NF

8. Join in SQL Query

Join is used to query data from more than one table. 

Below are the types of JOIN in SQL.

Inner Join – 

Returns only the matching rows in both the tables.


Left outer Join or Left Join-

Returns all the rows from the left table.




Right Join-

Returns all record from the right table.


Full Join-

All matching and non-matching records from left and right table.


Cross Join-

Results no. of records into table A into no. of records in table B.

Self Join-


EmployeeID
Name
ManagerID
1
Mak
2
2
Tom
3
3
Shane
NULL

Here self join require as for Employee ID 1 , Manager is Tom which Employee ID is 2.

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 -

Select count(id) as ID, Year(JoiningDate) as Year  from Employee group by Year(JoiningDate) order by Year

Output -

ID
Year
200
2015
325
2016
455
2017

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.

23. Delete vs Truncate

TruncateDelete
1. Reset table to empty state. It is a DDL command.
2. Where clause can not be used with truncate command.
3. Faster than Delete
4. Rollback is not possible.
1.It is a DML command.
2. Where clause can be used with Delete command.
3. Slower than truncate.
4. Rollback is possible.


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-
manager_id        manager_name
1                         AA
2                        XYZ

4                        Mark

27.  SQL query to get Max salary from each department


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


Comments

Popular

What's new in C# 6.0 language - All new features of C# 6.0 for developers

SharePoint Interview Questions and Answers