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.
- 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