Interviews SQL Important Queries and its Solution

  • By Sagar Gade
  • September 20, 2024
  • SQL
Interviews SQL Important Queries and its Solution

Interviews SQL Important Queries and its Solution

Prepare for Interviews SQL Important Queries and its Solution: Master essential SQL queries and answers to excel in your next database-related interview.

 

1. Write a query to find the second-highest salary from a table.

SELECT MAX(salary) AS second_highest_salary

FROM employees

WHERE salary < (SELECT MAX(salary) FROM employees);

This query finds the maximum salary (MAX(salary)) that is less than the highest salary.

 

2. How do you find duplicate records in a table, and how do you delete them?

To find duplicates based on a specific column (e.g., email):

 

SELECT email, COUNT(*)

FROM employees

GROUP BY email

HAVING COUNT(*) > 1;

To delete duplicates while keeping only the first occurrence:

 

WITH CTE AS (

    SELECT email, 

           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS row_num

    FROM employees

)

DELETE FROM employees

WHERE id IN (SELECT id FROM CTE WHERE row_num > 1);

This query uses ROW_NUMBER() to assign a unique number to duplicate rows, then deletes the ones that have row_num > 1.

 

3. Write a query to find the nth highest salary in a table.

SELECT DISTINCT salary

FROM employees

ORDER BY salary DESC

LIMIT 1 OFFSET n-1;

Replace n with the position of the salary you want. For example, for the 3rd highest salary, replace n-1 with 2.

 

4. How can you write a query to get the current date and time in SQL?

SELECT CURRENT_TIMESTAMP;

CURRENT_TIMESTAMP returns the current date and time in most SQL databases (e.g., MySQL, PostgreSQL, SQL Server).

 

For Free, Demo classes Call: 020-71179559

Registration Link: Click Here!

 

5. How do you find records that have no match in another table using SQL joins?

To find records of employees that do not have a match in the department table:

 

SELECT e.*

FROM employees e

LEFT JOIN departments d ON e.department_id = d.id

WHERE d.id IS NULL;

This query uses a LEFT JOIN and filters for rows where the department does not exist (IS NULL).

 

6. Write a query to display the first three characters of a string in SQL.

SELECT SUBSTRING(employee_name, 1, 3) AS first_three_characters

FROM employees;

This uses the SUBSTRING() function to extract the first three characters from the employee_name column.

 

  1. Write a SQL query to fetch employees who joined in the last six months.

SELECT *

FROM employees

WHERE join_date >= DATEADD(MONTH, -6, CURRENT_DATE);

DATEADD() is used in SQL Server, and CURRENT_DATE returns the current date. This fetches records where join_date is within the last six months.

In MySQL, you can use DATE_SUB(CURDATE(), INTERVAL 6 MONTH).

 

8. Write a query to display departments that have more than five employees.

SELECT department_id, COUNT(*) AS employee_count

FROM employees

GROUP BY department_id

HAVING COUNT(*) > 5;

This groups by department_id and filters only those departments having more than 5 employees using HAVING COUNT(*) > 5.

 

9. How can you update records in SQL without affecting records that meet certain conditions?

UPDATE employees

SET salary = salary * 1.1

WHERE department_id = 1

AND employee_id NOT IN (SELECT employee_id FROM employees WHERE employee_status = ‘Inactive’);

This query updates salaries by 10% only for employees in Department 1 who are not Inactive.

 

10. How do you write a SQL query to fetch employees whose names start with a particular letter?

SELECT *

FROM employees

WHERE employee_name LIKE ‘A%’;

Replace ‘A%’ with the letter you want. This query fetches all employees whose names start with the letter A. The % symbol is a wildcard for matching any string after the specified letter.

 

Do visit our channel to learn More: Click Here

 

Author:-

Sagar Gade

Call the Trainer and Book your free demo Class for SQL Call now!!!

| SevenMentor Pvt Ltd.

© Copyright 2021 | SevenMentor Pvt Ltd

Submit Comment

Your email address will not be published. Required fields are marked *

*
*