GeeksforGeeks App
Open App
Browser
Continue

# SQL Interview Questions | Set 1

Que-1: Difference between blocking and deadlocking.

• Blocking:
Blocking occurs is a transaction tries to acquire an incompatible lock on a resource that another transaction has already locked. The blocked transaction remain blocked until the blocking transaction releases the lock.
Deadlocking occurs when two or more transactions have a resource locked, and each transaction requests a lock on the resource that another transaction has already locked. Neither of the transactions here can more forward, as each one is waiting for the other to release the lock.

Que-2: Delete duplicate data from table only first data remains constant.

Managers –

IdNameSalary
1Harpreet20000
2Ravi30000
3Vinay10000
4Ravi30000
5Harpreet20000
6Vinay10000
7Rajeev40000
8Vinay10000
9Ravi30000
10Sanjay50000

Query –

```DELETE M1
From managers M1, managers M2
Where M2.Name = M1.Name AND M1.Id>M2.Id; ```

Output –

IdNameSalary
1Harpreet20000
2Ravi30000
3Vinay10000
7Rajeev40000
10Sanjay50000

Que-3: Find the Name of Employees.

Finding the name of Employees where First Name, Second Name and Last Name is given in table. Some Name is missing such as First Name, Second Name and may be Last Name. Here we will use COALESCE() function which will return first Non Null values.

Employees –

IDFNameSNameLNameSalary
1HarpreetSingh30000
2AshuNULLRana50000
3NULLVinayThakur40000
4NULLVinayNULL10000
5NULLNULLRajveer60000
6ManjeetSinghNULL60000

Query –

```SELECT ID, COALESCE(FName, SName, LName) as Name
FROM employees; ```

Output –

Que-4: Find the Employees who hired in the Last n months.

Finding the Employees who have been hire in the last n months. Here we get desire output by using TIMESTAMPDIFF() mysql function.

Employees –

IDFNameLNameGenderSalaryHiredate
1RajveerSinghMale300002017/11/05
2ManveerSinghMale500002017/11/05
3AshutoshKumarMale400002017/12/12
4AnkitaSharmaFemale450002017/12/15
5VijayKumarMale500002018/01/12
7JayvijaySinghMale300002018/02/18
8ReenuKumariFemale400002017/09/19
9AnkitVermaMale250002018/04/04
10HarpreetSinghMale500002017/10/10

Query –

```Select *, TIMESTAMPDIFF (month, Hiredate, current_date()) as DiffMonth
From employees
Where TIMESTAMPDIFF (month, Hiredate, current_date())
Between 1 and 5 Order by Hiredate desc; ```

Note – Here in query 1 and 5 are indicates 1 to n months.which show the Employees who have hired last 1 to 5 months. In this query DiffMonth is a extra column for our understanding which show the Nth months.

Output –

Que-5: Find the Employees who hired in the Last n days.

Finding the Employees who have been hire in the last n days. Here we get desire output by using DATEDIFF() mysql function.

Employees –

IDFNameLNameGenderSalaryHiredate
1RajveerSinghMale300002017/11/05
2ManveerSinghMale500002017/11/05
3AshutoshKumarMale400002017/12/12
4AnkitaSharmaFemale450002017/12/15
5VijayKumarMale500002018/01/12
7JayvijaySinghMale300002018/02/18
8ReenuKumariFemale400002017/09/19
9AnkitVermaMale250002018/04/04
10HarpreetSinghMale500002017/10/10

Query –

```Select *, DATEDIFF (current_date(), Hiredate) as DiffDay
From employees
Where DATEDIFF (current_date(), Hiredate) between 1 and 100 order by Hiredate desc; ```

Note – Here in query 1 and 100 are indicates 1 to n days.which show the Employees who have hired last 1 to 100 days. In this query DiffDay is a extra column for our understanding which show the Nth days.

Output –

Que-6: Find the Employees who hired in the Last n years.

Finding the Employees who have been hire in the last n years. Here we get desire output by using TIMESTAMPDIFF() mysql function.

Employees –

IDFNameLNameGenderSalaryHiredate
1RajveerSinghMale300002010/11/05
2ManveerSinghMale500002017/11/05
3AshutoshKumarMale400002015/12/12
4AnkitaSharmaFemale450002016/12/15
5VijayKumarMale500002017/01/12
7JayvijaySinghMale300002012/02/18
8ReenuKumariFemale400002013/09/19
9AnkitVermaMale250002017/04/04
10HarpreetSinghMale500002017/10/10

Query –

```Select *, TIMESTAMPDIFF (year, Hiredate, current_date()) as DiffYear
From employees
Where TIMESTAMPDIFF (year, Hiredate, current_date()) between 1 and 4 order by Hiredate desc; ```

Note – Here in query 1 and 4 are indicates 1 to n years.which show the Employees who have hired last 1 to 4 years. In this query DiffYear is a extra column for our understanding which show the Nth years.

Output –

Here we get desire output by using three different query.

Employees –

IDFNameLNameGenderSalaryHiredate
1RajveerSinghMale300002010/11/05
2ManveerSinghMale500002017/11/05
3AshutoshKumarMale400002015/12/12
4AnkitaSharmaFemale450002016/12/15
5VijayKumarMale500002017/01/12
7JayvijaySinghMale300002012/02/18
8ReenuKumariFemale400002013/09/19
9AnkitVermaMale250002017/04/04
10HarpreetSinghMale500002017/10/10

Query –

```Select *
From employees
Where Fname like 'A%';

Select *
From employees
Where left(FName, 1)='A';

Select *
From employees
Where substring(FName, 1, 1)='A'; ```

Note – Here every query will give same output and the list of Employees who’s FName start with letter A.

Refer for – SQL Interview Questions | Set 2

My Personal Notes arrow_drop_up