1. Select All Records from a Table
Question: Write a query to fetch all records from the employees table.
SELECT * FROM employees;
Explanation: The * symbol retrieves all columns from the table. It’s the most basic query, but interviewers often start here to ease into SQL.
2. Fetch Specific Columns
Question: Fetch only name and salary from the employees table.
SELECT name, salary FROM employees;
Explanation: Instead of pulling everything, we specify columns for efficiency.
3. Use WHERE Clause
Question: Find employees with salary greater than 50,000.
SELECT name, salary
FROM employees
WHERE salary > 50000;
Explanation: The WHERE clause filters results based on conditions.
4. Sorting Results (ORDER BY)
Question: Display employees ordered by salary in descending order.
SELECT name, salary
FROM employees
ORDER BY salary DESC;
Explanation: Sorting helps organize results. ASC is ascending (default), and DESC is descending.
5. Using DISTINCT
Question: Fetch all unique job titles from employees.
SELECT DISTINCT job_title
FROM employees;
Explanation: The DISTINCT keyword removes duplicate values.
6. Count Total Records
Question: How many employees are in the company?
SELECT COUNT(*) AS total_employees
FROM employees;
Explanation: COUNT(*) counts rows. Aliases (AS) rename output for readability.
7. Find Maximum and Minimum Salary
Question: Get the highest and lowest salary from employees.
SELECT MAX(salary) AS highest_salary,
MIN(salary) AS lowest_salary
FROM employees;
Explanation: Aggregation functions like MAX() and MIN() are frequently asked.
8. Average Salary
Question: What is the average salary of employees?
SELECT AVG(salary) AS average_salary
FROM employees;
Explanation: AVG() calculates mean values.
9. Group By Clause
Question: Find the average salary by department.
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
Explanation: GROUP BY groups rows based on a column, useful for summaries.
10. HAVING Clause
Question: Find departments with an average salary greater than 60,000.
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;
Explanation: HAVING works like WHERE but is used with aggregate functions.
11. LIKE Operator (Pattern Matching)
Question: Find employees whose names start with ‘A’.
SELECT name
FROM employees
WHERE name LIKE 'A%';
Explanation: % matches any sequence of characters.
12. IN Operator
Question: Find employees from departments HR, IT, and Finance.
SELECT name, department
FROM employees
WHERE department IN ('HR', 'IT', 'Finance');
Explanation: IN checks if a value matches any in a list.
13. BETWEEN Operator
Question: Find employees with salary between 40,000 and 70,000.
SELECT name, salary
FROM employees
WHERE salary BETWEEN 40000 AND 70000;
Explanation: BETWEEN is inclusive of boundary values.
14. Inner Join
Question: Fetch employee names with their department names.
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.id;
Explanation: INNER JOIN combines rows from two tables where a match exists.
15. Left Join
Question: Show all employees and their department names, even if no department is assigned.
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id;
Explanation: LEFT JOIN ensures all records from the left table are included.
16. Subquery Example
Question: Find employees earning more than the average salary.
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Explanation: A subquery is a query inside another query.
17. Top N Records (LIMIT)
Question: Show the top 5 highest-paid employees.
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;
Explanation: LIMIT restricts the number of rows returned.
18. Update Query
Question: Increase salary of IT department employees by 10%.
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'IT';
Explanation: UPDATE modifies data in a table.
19. Delete Query
Question: Remove employees who resigned.
DELETE FROM employees
WHERE status = 'Resigned';
Explanation: DELETE removes rows permanently.
20. Create Table Example
Question: Create an employee table with basic columns.
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10,2),
department VARCHAR(50)
);
Explanation: CREATE TABLE defines schema structure.
Final Thoughts
Most interviewers focus on real-world problem-solving with SQL queries, not just theory. Mastering these 20 queries will give you confidence and help you stand out in interviews.
Pro Tip: Always explain your thought process when writing queries in an interview. It shows clarity and strong problem-solving skills.
