Most Asked SQL queries in any Interview.
1.Employees nth Max Salary(Ex. Employee's 3rd Salary)
Solution:
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;
OR
SELECT MAX(salary) AS third_highest_salary
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);
2. Find out employees name who are getting 2nd highest salary.(More than 1 employee is getting
second highest salary)
Solution:
SELECT name, salary
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);
3.Separate the first and last names from a full name
Solution:
SELECT
full_name,
SUBSTRING(full_name, 1, INSTR(full_name, ' ') - 1) AS first_name,
SUBSTRING(full_name, INSTR(full_name, ' ') + 1) AS last_name
FROM employees;
4.Find Duplicate records in a table
Solution:
SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
5.Delete duplicate records while keeping 1
Solution:
DELETE FROM table_name
WHERE id NOT IN (
SELECT MIN(id)
FROM table_name
GROUP BY columnname
);
6.Get the list of the employees who joined in the last 6 months.
Solution:
SELECT *
FROM employees
WHERE join_date >= DATE_ADD(CURDATE(), INTERVAL -6 MONTH);
7.List out the employees who are working as manager as well
Solutions:
SELECT DISTINCT e1.*
FROM employees e1
JOIN employees e2
ON e1.id = e2.manager_id;
8.Find the Employee With the Maximum Salary in Each Department
Solution:
SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;
9.Check If Two Tables Have the Same Data
Solution:
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;
10.Retrieve Employees With a Salary Greater Than the Average Salary
Solution:
SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
11.Find Department with more than 5 employee
Solution:
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;
12.Find Employees Whose Names Contain 'John'
Solution:
SELECT *
FROM employees
WHERE name LIKE '%John%';
13.Retrieve Last 5 Records From a Table
Solution:
SELECT *
FROM employees
ORDER BY id DESC
LIMIT 5;
14.Find employees whose names contain exactly two occurrences of the letter 'a'
Solution:
SELECT *
FROM employees
WHERE name LIKE '%a%a%' AND name NOT LIKE '%a%a%a%';
15.Extract the last 3 characters of a name
Solution:
SELECT name, SUBSTRING(name, LENGTH(name) - 2, 3) AS last_three_characters
FROM employees;
1.:What are the different types of SQL statements?
DDL (Data Definition Language): CREATE, ALTER, DROP.
DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE.
DCL (Data Control Language): GRANT, REVOKE.
TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT.
17.What is the difference between DELETE and TRUNCATE?
DELETE removes specific rows based on a condition and can be rolled back.
TRUNCATE removes all rows from a table but cannot be rolled back (in most systems)
18.What is the difference between UNION and UNION ALL?
UNION removes duplicate records, whereas UNION ALL includes all records.
19.What are the different types of joins in SQL?
Inner Join: Returns matching rows from both tables.
Left Join: Returns all rows from the left table and matching rows from the right table.
Right Join: Returns all rows from the right table and matching rows from the left table.
Full Outer Join: Returns all rows when there is a match in either table.
20.What is candidate Key
A candidate key is a column, or a combination of columns, in a database table that can uniquely
identify each row in that table. It is a candidate to become the primary key of the table, as it satisfies
the following properties.
21.Window Functions in SQL
Window functions are SQL functions that perform calculations across a set of rows related to the
current row, but unlike aggregate functions, they do not collapse rows into a single output. Instead,
they return a value for each row in the dataset.
RANK(), DENSE_RANK(), ROW_NUMBER()
22.How do you insert multiple rows at once?
Solution:
INSERT INTO employees (id, name, department, salary, join_date)
VALUES
(2, 'Jane Doe', 'Finance', 60000.00, '2023-02-01'),
(3, 'Mike Ross', 'IT', 70000.00, '2023-03-01');
23.How do you delete all records from a table but keep the structure?
Solution:
DELETE FROM employees;
24.Copy employees with a salary greater than 60,000 into the new_employees table.
Solution:
INSERT INTO new_employees (id, name, department, salary)
SELECT id, name, department, salary
FROM employees
WHERE salary > 60000;
25.copy only the structure of a table (without copying the data) into a new table
Solution:
CREATE TABLE new_table
AS
SELECT *
FROM existing_table
WHERE 1 <> 1;
