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 KeyA 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 email@example.com, firstname.lastname@example.org , email@example.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 QueryFrom 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 -
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.
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 JoinUnion combine rows from 2 or more tables but JOIN combine columns from 2 or more tables.
12. Cast vs ConvertUse 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)
--WRITE FUNCTION CODE HERE
RETURN return value
For eg age calculation from DOB can be a scalar function.
15. Inline Table Valued FunctionIt returns Table.
CREATE FUNCTION myfunction(@city nvarchar(10))
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 TableCreate 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 tableIt 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 ClauseBelow 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
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 SQLCluster 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
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
|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)
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
27. SQL query to get Max salary from each department
SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID