Top 50 Relational Database Interview Questions with Answers

Basic SQL Queries

Q1: Fetch all records from the Employee table.

SELECT * FROM Employee;

Q2: Select employee name and salary from the Employee table.

SELECT name, salary FROM Employee;

Q3: Fetch distinct job titles from the Employee table.

SELECT DISTINCT job_title FROM Employee;

Q4: Filter employees with salary greater than 50000.

SELECT * FROM Employee WHERE salary > 50000;

Q5: Find employees whose name starts with ‘A’.

SELECT * FROM Employee WHERE name LIKE 'A%';

Q6: Find employees whose name ends with ‘n’.

SELECT * FROM Employee WHERE name LIKE '%n';

Q7: Fetch employees from departments 1, 3, and 5.

SELECT * FROM Employee WHERE department_id IN (1, 3, 5);

Q8: Filter employees with salary between 30000 and 60000.

SELECT * FROM Employee WHERE salary BETWEEN 30000 AND 60000;

Q9: Find employees who do not belong to department 10.

SELECT * FROM Employee WHERE department_id <> 10;

Q10: Sort employees by salary descending.

SELECT * FROM Employee ORDER BY salary DESC;

Aggregation & Grouping

Q11: Find the average salary of all employees.

SELECT AVG(salary) FROM Employee;

Q12: Count the number of employees in each department.

SELECT department_id, COUNT(*) FROM Employee GROUP BY department_id;

Q13: Find the maximum and minimum salary in each department.

SELECT department_id, MAX(salary), MIN(salary) FROM Employee GROUP BY

department_id;

Q14: Show total salary per department.

SELECT department_id, SUM(salary) FROM Employee GROUP BY department_id;

Q15: Filter departments having more than 5 employees.

SELECT department_id FROM Employee GROUP BY department_id HAVING COUNT(*) > 5;

Q16: Find departments where the average salary is greater than 60000.

SELECT department_id FROM Employee GROUP BY department_id HAVING AVG(salary) >

60000;

Date Functions
Q17: Fetch employees who joined in the last 30 days.

SELECT * FROM Employee WHERE joining_date >= CURRENT_DATE - INTERVAL '30 days';

Q18: Get employees whose joining year is 2023.

SELECT * FROM Employee WHERE EXTRACT(YEAR FROM joining_date) = 2023;

Q19: Count employees who joined in the current year.

SELECT COUNT(*) FROM Employee WHERE EXTRACT(YEAR FROM joining_date) =

EXTRACT(YEAR FROM CURRENT_DATE);

Q20: Fetch employees who joined between ‘2022-01-01’ and ‘2022-12-31’.

SELECT * FROM Employee WHERE joining_date BETWEEN '2022-01-01' AND '2022-12-31';

Joins
Q21: Get a list of all employees with their department names.

SELECT e.name, d.department_name FROM Employee e JOIN Department d ON

e.department_id = d.id;

Q22: Show employee names with their manager’s name.

SELECT e.name AS employee, m.name AS manager FROM Employee e LEFT JOIN Employee
m ON e.manager_id = m.id;
Q23: Find all employees and their project names.

SELECT e.name, p.project_name FROM Employee e LEFT JOIN Project p ON e.project_id =

p.id;

Q24: List customers and the orders they placed.

SELECT c.name, o.order_id FROM Customer c JOIN Orders o ON c.id = o.customer_id;

Q25: Show orders even if the customer is missing.

SELECT o.order_id, c.name FROM Orders o LEFT JOIN Customer c ON o.customer_id = c.id;

Q26: Find products that were never ordered.

SELECT * FROM Product p LEFT JOIN OrderDetails od ON p.id = od.product_id WHERE

od.product_id IS NULL;

Q27: List departments and the number of employees, including empty departments.

SELECT d.department_name, COUNT(e.id) FROM Department d LEFT JOIN Employee e ON

d.id = e.department_id GROUP BY d.department_name;

Subqueries
Q28: Fetch employees who earn more than the average salary.

SELECT * FROM Employee WHERE salary > (SELECT AVG(salary) FROM Employee);

Q29: Get employees whose salary is the second highest.

SELECT * FROM Employee WHERE salary = (SELECT MAX(salary) FROM Employee WHERE
salary < (SELECT MAX(salary) FROM Employee));

Q30: Get department names where no employees work.

SELECT department_name FROM Department WHERE id NOT IN (SELECT DISTINCT

department_id FROM Employee);

Q31: List employees who do not manage anyone.

SELECT * FROM Employee WHERE id NOT IN (SELECT DISTINCT manager_id FROM

Employee WHERE manager_id IS NOT NULL);

Q32: Find employees with the same salary as another employee.

SELECT * FROM Employee e1 WHERE EXISTS (SELECT 1 FROM Employee e2 WHERE

e1.salary = e2.salary AND e1.id <> e2.id);
Window Functions
Q33: Show employee salary along with department-wise average salary.

SELECT name, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS

avg_dept_salary FROM Employee;

Q34: Rank employees by salary within each department.

SELECT name, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER

BY salary DESC) AS rank FROM Employee;

Q35: Assign row numbers to employees ordered by salary.

SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num FROM
Employee;

Q36: Find cumulative salary per department using SUM OVER.

SELECT name, department_id, salary, SUM(salary) OVER (PARTITION BY department_id

ORDER BY name) AS cum_salary FROM Employee;

Set Operations
Q37: Find common employees in two departments (INTERSECT).

SELECT name FROM Employee WHERE department_id = 1 INTERSECT SELECT name FROM
Employee WHERE department_id = 2;

Q38: Find employees in Dept A but not in Dept B (EXCEPT).

SELECT name FROM Employee WHERE department_id = 1 EXCEPT SELECT name FROM
Employee WHERE department_id = 2;

Q39: Combine employee lists from two similar tables (UNION).

SELECT name FROM Employee_2023 UNION SELECT name FROM Employee_2024;

Data Manipulation
Q40: Write an INSERT query for adding a new employee.

INSERT INTO Employee (name, salary, department_id) VALUES ('John Doe', 50000, 2);

Q41: Write an UPDATE query to increase salary by 10%.

UPDATE Employee SET salary = salary * 1.10;

Q42: Delete employees who have left the company.

DELETE FROM Employee WHERE status = 'left';

Q43: Write a query to update department for all employees in a location.

UPDATE Employee SET department_id = 3 WHERE location = 'Delhi';

Constraints and Null Handling

Q44: Find employees where email is NULL.

SELECT * FROM Employee WHERE email IS NULL;

Q45: Count employees where phone number is NOT NULL.

SELECT COUNT(*) FROM Employee WHERE phone IS NOT NULL;

Q46: Enforce unique constraint on email column (explain).

ALTER TABLE Employee ADD CONSTRAINT unique_email UNIQUE (email);

Q47: Check for duplicate employee names.

SELECT name, COUNT(*) FROM Employee GROUP BY name HAVING COUNT(*) > 1;

Other Practical Scenarios

Q48: Write a query to remove duplicate rows (using ROW_NUMBER).

DELETE FROM Employee WHERE id NOT IN (SELECT MIN(id) FROM Employee GROUP BY
name, salary);

Q49: Write a query to transpose rows into columns (PIVOT logic).

SELECT department_id, MAX(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS

Male_Count, MAX(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS Female_Count FROM
Employee GROUP BY department_id;

Q50: Get the employee with the nth highest salary.

SELECT * FROM (SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank FROM
Employee) AS ranked WHERE rank = 3;