Skip to content
Related Articles

Related Articles

Improve Article

SQL Interview Questions | Set 2

  • Difficulty Level : Easy
  • Last Updated : 09 Sep, 2019
  1. Difference between Locking, Blocking and Deadlocking
    • Locking: Locking occurs when a connection needs access to a piece of data in a database and it locks it for certain use so that no other transaction is able to access it.
    • Blocking: Blocking occurs when a transaction tries to acquire an incompatible lock on a resource that another transaction has already locked. The blocked transaction remains blocked until the blocking transaction releases the lock.
    • Deadlocking: 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 move forward, as each one is waiting for the other to release the lock.
  2. Delete duplicate data from table so that only first data remains constant

    Managers

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

    Query:

    Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

    DELETE M1 from managers M1, managers M2 where M2.Name=M1.Name AND M1.Id>M2.Id;
    

    Output:

    IdNameSalary
    1Harpreet20000
    2Ravi30000
    3Vinay10000
    7Rajeev4000010Sanjay50000
  3. Find the Name of Employees where First Name, Second Name, and Last Name is given in the table. Some Name is missing such as First Name, Second Name and maybe 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:

  4. Find the Emloyees who were hired in the Last n months
    Finding the Employees who have been hire in the last n months. Here we get desired output by using TIMESTAMPDIFF() mysql function
    Employees
    IDFNameLNameGenderSalaryHiredate
    1RajveerSinghMale300002017/11/05
    2ManveerSinghMale500002017/11/05
    3AshutoshKumarMale400002017/12/12
    4AnkitaSharmaFemale450002017/12/15
    5VijayKumarMale500002018/01/12
    6DilipYadavMale250002018/02/26
    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 an extra column for our understanding which shows the Nth months.

    Output:

  5. Find the Emloyees who hired in the Last n days
    Finding the Employees who have been hired in the last n days. Here we get desired output by using DATEDIFF() mysql function

    Employees

    IDFNameLNameGenderSalaryHiredate
    1RajveerSinghMale300002017/11/05
    2ManveerSinghMale500002017/11/05
    3AshutoshKumarMale400002017/12/12
    4AnkitaSharmaFemale450002017/12/15
    5VijayKumarMale500002018/01/12
    6DilipYadavMale250002018/02/26
    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 indicates 1 to n days which show the Employees who have hired last 1 to 100 days. In this query DiffDay is an extra column for our understanding which shows the Nth days.

    Output:

  6. Find the Employees who were hired in the Last n years
    Finding the Employees who have been hired in the last n years. Here we get desired output by using TIMESTAMPDIFF() MySQL function

    Employees

    IDFNameLNameGenderSalaryHiredate
    1RajveerSinghMale300002010/11/05
    2ManveerSinghMale500002017/11/05
    3AshutoshKumarMale400002015/12/12
    4AnkitaSharmaFemale450002016/12/15
    5VijayKumarMale500002017/01/12
    6DilipYadavMale250002011/02/26
    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 shows 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:

  7. Select all names that start with a given letter
    Here we get desired output by using three different queries

    Employees

    IDFNameLNameGenderSalaryHiredate
    1RajveerSinghMale300002010/11/05
    2ManveerSinghMale500002017/11/05
    3AshutoshKumarMale400002015/12/12
    4AnkitaSharmaFemale450002016/12/15
    5VijayKumarMale500002017/01/12
    6DilipYadavMale250002011/02/26
    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.

Related articles :

  1. SQL Interview Questions | Set 1
  2. Commonly asked DBMS Questions Set 1
  3. Commonly asked DBMS Questions Set 2



My Personal Notes arrow_drop_up
Recommended Articles
Page :