📘 Article Content:
Preparing for a SQL interview? This guide walks you through some of the most common SQL coding questions you'll likely encounter — from basic selects to advanced joins and aggregations. Each question comes with a sample query and explanation to help you build confidence for your next technical interview.
🔹 1. Select All Columns from a Table
sql
SELECT * FROM employees;
🔹 2. Select Specific Columns
sql
SELECT employee_id, first_name, last_name FROM employees;
🔹 3. WHERE Clause
Select employees with last name "Smith".
sql
SELECT * FROM employees WHERE last_name = 'Smith';
🔹 4. AND, OR, and NOT Operators
Employees in "Sales" earning more than 50000.
sql
SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;
🔹 5. ORDER BY
Sort employees by last name.
sql
SELECT * FROM employees ORDER BY last_name ASC;
🔹 6. INSERT INTO
Insert a new employee.
sql
INSERT INTO employees (employee_id, first_name, last_name, department, salary)
VALUES (123, 'John', 'Doe', 'Engineering', 60000);
🔹 7. UPDATE
Update salary for employee 123.
sql
UPDATE employees SET salary = 65000 WHERE employee_id = 123;
🔹 8. DELETE
Remove employee with ID 123.
sql
DELETE FROM employees WHERE employee_id = 123;
🔹 9. Aggregate Functions
Find the average salary.
sql
SELECT AVG(salary) FROM employees;
🔹 10. GROUP BY
Count employees per department.
sql
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department;
🔹 11. HAVING Clause
Departments with more than 10 employees.
sql
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;
🔹 12. JOIN
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;
🔹 13. 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;
🔹 14. Subquery
Find employees with the highest salary.
sql
SELECT * FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
🔹 15. CASE Statement
Grade employees by 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;
🔹 16. 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;
🔹 17. INTERSECT
Common employee IDs in two tables.
sql
SELECT employee_id FROM current_employees
INTERSECT
SELECT employee_id FROM former_employees;
🔹 18. EXCEPT
Current employees not in former employees.
sql
SELECT employee_id FROM current_employees
EXCEPT
SELECT employee_id FROM former_employees;
🔹 19. LIMIT and OFFSET
First 10 employees:
sql
SELECT * FROM employees
LIMIT 10;
Next 10 after skipping the first 10:
sql
SELECT * FROM employees
LIMIT 10 OFFSET 10;
🔹 20. 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 20 SQL coding questions cover essential operations you'll likely face in interviews, including filtering, joining, grouping, and working with subqueries and set operations. Practice them hands-on using a local or cloud SQL environment to reinforce your understanding.