SQL Interview Question and Answer for freshers

SQL Interview Question and Answer

Question 1: what is difference between Truncate and delete command.

Ans:Truncate and delete both commands are used to delete all rows from the table but they have some major difference so before using this command we should be aware about the use of command and their result

Truncate command	Delete command
It’s a DDL command (data definition language)	It’s a DML command(data manipulation language)
We can not use trigger with Truncate command	We can use trigger with delete command
It can not be roll back	Roll back is possible in delete command
It is faster than delete because it will not log deleted rows in log space.	Slower than truncate command because this command log all the deleted rows in log space.
We can not use where clause with truncate	We can use where clause with delete command.

Question 2: what is the difference between primary key and unique key?

Ans.Both of the key is used to make the column unique means if we define any column as primary or unique key it will enforce the uniqueness of that column but have some differences

Primary Key	Unique Key
It doesn’t allow Null Values	It allows one Null value
Primary key creates cluster index on that column	It creates Non cluster Index on that column.
A table can have only one primary key	But it can have multiple unique key

Question 3: How you will delete duplicate record from the table.

Ans: Duplicate records come to table if we have not used key concept when we created the table means we have not make any column as primary key or  another source is when we are taking data from some other  source .

Basically by two ways we can delete the duplicate record from our table

1. Using Temporary table:

·        First of all create temp table from main table

·        Insert the result of Group By query into temporary table

·        Truncate the original table

·        Insert all the values of temporary table to main table

·         

2. without Temporary Table:

Question 4: write a query to find second highest salary from Employee table.

Ans:

select min(Employ_Sal) from Employee where Employ _Sal in

(select distinct top 2 Employ_Sal from Employee_Test order by Employ_Sal desc)

Note: in Mysql top command can be replaced by limit command

Similarly we can find nth highest salary.

Explanation: First inner Query will give us top 2 max salaries in descending order and outer query will use this result and find the minimum of this and give the second highest salary from employee table.

Question 5: What is Lock Escalation?

Ans: Basically locks are used to maintain Data Concurrency and Consistency in a Multi-user Environment where multiple user can access the data at the same time.