Understanding fundamental SQL concepts is essential for database development and analysis roles. This guide walks through commonly asked SQL interview questions with clear examples to help you prepare efficiently.
1. Create Employee Table
sql
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT,
salary DECIMAL(10, 2)
);
2. Create Department Table
sql
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100)
);
3. Select All Columns from a Table
sql
SELECT * FROM employees;
4. Select Specific Columns from a Table
sql
SELECT employee_id, first_name, last_name FROM employees;
5. WHERE Clause
Select employees where the last name is 'Smith':
sql
SELECT * FROM employees WHERE last_name = 'Smith';
6. AND, OR, NOT Operators
Select employees from Sales department with a salary over 50,000:
sql
SELECT * FROM employees WHERE department_id = 'Sales' AND salary > 50000;
7. ORDER BY Clause
Sort employees by last name in ascending order:
sql
SELECT * FROM employees ORDER BY last_name ASC;
8. INSERT INTO Statement
Add a new employee:
sql
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
VALUES (123, 'John', 'Doe', 1, 60000);
9. UPDATE Statement
Update an employee’s salary:
sql
UPDATE employees SET salary = 65000 WHERE employee_id = 123;
10. DELETE Statement
Remove an employee record:
sql
DELETE FROM employees WHERE employee_id = 123;
11. Aggregate Functions
Calculate average salary:
sql
SELECT AVG(salary) FROM employees;
12. GROUP BY Clause
Count employees per department:
sql
SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id;
13. HAVING Clause
Find departments with more than 10 employees:
sql
SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;
14. INNER JOIN
List employees with their department names:
sql
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
15. LEFT JOIN
Include employees without departments:
sql
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
16. Subquery
Get employees with the highest salary:
sql
SELECT * FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
17. CASE Statement
Assign grades based on salary:
sql
SELECT employee_id, first_name, last_name, salary,
CASE
WHEN salary >= 70000 THEN 'A'
WHEN salary BETWEEN 50000 AND 69999 THEN 'B'
ELSE 'C'
END AS grade
FROM employees;
18. UNION
Combine employees and managers:
sql
SELECT employee_id, first_name, last_name FROM employees
UNION
SELECT manager_id AS employee_id, first_name, last_name FROM managers;
19. INTERSECT
Find common employee IDs:
sql
SELECT employee_id FROM current_employees
INTERSECT
SELECT employee_id FROM former_employees;
20. EXCEPT
Find current employees not in former employees:
sql
SELECT employee_id FROM current_employees
EXCEPT
SELECT employee_id FROM former_employees;
21. LIMIT and OFFSET
Get the first 10 employees:
sql
SELECT * FROM employees LIMIT 10;
Skip first 10 and fetch next 10:
sql
SELECT * FROM employees LIMIT 10 OFFSET 10;
22. Self Join
Find employee pairs in the same department:
sql
SELECT e1.employee_id, e1.first_name, e1.last_name,
e2.employee_id, e2.first_name, e2.last_name
FROM employees e1
JOIN employees e2
ON e1.department_id = e2.department_id AND e1.employee_id <> e2.employee_id;
Final Thoughts
These examples cover a broad range of SQL concepts frequently asked in technical interviews. Mastering these will help you confidently approach SQL-related questions and showcase your ability to work with relational databases.