Complete SQL Practice Questions with Answers

(65 Questions)

1. Basic SQL (Beginner Level)

Q: Create a table called Employees
Answer:
CREATE TABLE Employees ( id INT PRIMARY KEY, name VARCHAR(100), department
VARCHAR(50), salary DECIMAL(10,2), hire_date DATE );

Q: Insert at least 10 records into Employees

Answer:
INSERT INTO Employees (id, name, department, salary, hire_date) VALUES (1, 'Alice',
'IT', 60000, '2021-05-01'), (2, 'Bob', 'HR', 45000, '2019-08-15'), (3, 'Charlie',
'Finance', 70000, '2020-01-12'), (4, 'David', 'IT', 55000, '2022-03-20'), (5, 'Eva',
'Finance', 72000, '2021-06-25'), (6, 'Frank', 'IT', 50000, '2018-11-11'), (7,
'Grace', 'HR', 47000, '2022-07-14'), (8, 'Hank', 'Sales', 40000, '2020-10-05'), (9,
'Ivy', 'Sales', 42000, '2021-12-01'), (10, 'Jack', 'Finance', 68000, '2023-01-10');

Q: Select all employees

Answer:
SELECT * FROM Employees;

Q: Retrieve only the name and salary of employees

Answer:
SELECT name, salary FROM Employees;

Q: Find all employees working in the IT department

Answer:
SELECT * FROM Employees WHERE department = 'IT';

Q: Get employees hired after 2020-01-01

Answer:
SELECT * FROM Employees WHERE hire_date > '2020-01-01';

Q: Find employees with a salary greater than 50,000

Answer:
SELECT * FROM Employees WHERE salary > 50000;

Q: Retrieve employees whose names start with A

Answer:
SELECT * FROM Employees WHERE name LIKE 'A%';

Q: Count the total number of employees

Answer:
SELECT COUNT(*) AS total_employees FROM Employees;

Q: Find the maximum, minimum, and average salary

Answer:
SELECT MAX(salary) AS max_salary, MIN(salary) AS min_salary, AVG(salary) AS
avg_salary FROM Employees;

2. Filtering & Sorting

Q: List all employees ordered by salary descending
Answer:
SELECT * FROM Employees ORDER BY salary DESC;

Q: Retrieve the top 5 highest-paid employees

Answer:
SELECT * FROM Employees ORDER BY salary DESC LIMIT 5;

Q: Find employees not in the HR department

Answer:
SELECT * FROM Employees WHERE department <> 'HR';

Q: Get employees whose salary is between 30,000 and 60,000

Answer:
SELECT * FROM Employees WHERE salary BETWEEN 30000 AND 60000;

Q: Retrieve employees who joined in 2022

Answer:
SELECT * FROM Employees WHERE YEAR(hire_date) = 2022;

Q: Display distinct departments from the table

Answer:
SELECT DISTINCT department FROM Employees;

Q: Find employees whose name contains 'an'

Answer:
SELECT * FROM Employees WHERE name LIKE '%an%';

Q: Sort employees by hire_date and salary

Answer:
SELECT * FROM Employees ORDER BY hire_date, salary;

3. Aggregate Functions & GROUP BY

Q: Count employees in each department
Answer:
SELECT department, COUNT(*) FROM Employees GROUP BY department;

Q: Find the average salary in each department

Answer:
SELECT department, AVG(salary) FROM Employees GROUP BY department;

Q: Get the highest salary in each department

Answer:
SELECT department, MAX(salary) FROM Employees GROUP BY department;

Q: Find departments with more than 5 employees

Answer:
SELECT department FROM Employees GROUP BY department HAVING COUNT(*) > 5;

Q: Show total salary expenditure per department

Answer:
SELECT department, SUM(salary) FROM Employees GROUP BY department;

Q: List departments where the average salary is above 60,000

Answer:
SELECT department FROM Employees GROUP BY department HAVING AVG(salary) > 60000;

4. Joins (2+ Tables)

Q: Join Employees with Departments
Answer:
SELECT e.name, d.dept_name FROM Employees e JOIN Departments d ON e.department =
d.dept_id;
Q: Show employees who are assigned to projects
Answer:
SELECT e.name, p.proj_name FROM Employees e JOIN Projects p ON e.id = p.emp_id;

Q: List employees who are not assigned to any project

Answer:
SELECT name FROM Employees WHERE id NOT IN (SELECT emp_id FROM Projects);

Q: Find employees and their project names

Answer:
SELECT e.name, p.proj_name FROM Employees e LEFT JOIN Projects p ON e.id = p.emp_id;

Q: Count how many employees are in each department

Answer:
SELECT d.dept_name, COUNT(e.id) FROM Departments d LEFT JOIN Employees e ON
d.dept_id = e.department GROUP BY d.dept_name;

Q: Get employees working in the 'Finance' department

Answer:
SELECT * FROM Employees e JOIN Departments d ON e.department = d.dept_id WHERE
d.dept_name = 'Finance';

5. Subqueries
Q: Find employees earning more than the average salary
Answer:
SELECT * FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees);

Q: Retrieve employees with the maximum salary

Answer:
SELECT * FROM Employees WHERE salary = (SELECT MAX(salary) FROM Employees);

Q: Find employees whose department has more than 10 employees

Answer:
SELECT * FROM Employees e WHERE e.department IN (SELECT department FROM Employees
GROUP BY department HAVING COUNT(*) > 10);

Q: Show employees who earn the second-highest salary

Answer:
SELECT * FROM Employees WHERE salary = (SELECT MAX(salary) FROM Employees WHERE
salary < (SELECT MAX(salary) FROM Employees));

Q: List employees who do not have any project

Answer:
SELECT * FROM Employees WHERE id NOT IN (SELECT emp_id FROM Projects);

6. Advanced Joins & Set Operations

Q: Get employees who are assigned to more than one project
Answer:
SELECT emp_id, COUNT(*) FROM Projects GROUP BY emp_id HAVING COUNT(*) > 1;

Q: Find departments with no employees

Answer:
SELECT * FROM Departments d WHERE d.dept_id NOT IN (SELECT department FROM
Employees);

Q: List employees who are in IT but not in HR

Answer:
SELECT * FROM Employees WHERE department = 'IT' AND department <> 'HR';
Q: Show employees who are in both Project A and Project B
Answer:
SELECT emp_id FROM Projects WHERE proj_name = 'Project A' INTERSECT SELECT emp_id
FROM Projects WHERE proj_name = 'Project B';

Q: Combine employee lists from two tables using UNION

Answer:
SELECT name FROM Employees UNION SELECT name FROM OldEmployees;

7. Window Functions (Analytic Queries)

Q: Rank employees by salary within each department
Answer:
SELECT name, department, salary, RANK() OVER(PARTITION BY department ORDER BY salary
DESC) AS rank FROM Employees;

Q: Find the top 3 highest-paid employees in each department

Answer:
SELECT * FROM (SELECT name, department, salary, DENSE_RANK() OVER(PARTITION BY
department ORDER BY salary DESC) rnk FROM Employees) t WHERE rnk <= 3;

Q: Calculate the running total of salaries by hire date

Answer:
SELECT name, hire_date, SUM(salary) OVER(ORDER BY hire_date) AS running_total FROM
Employees;

Q: Show each employee’s salary compared to the department average

Answer:
SELECT name, department, salary, AVG(salary) OVER(PARTITION BY department) AS
dept_avg FROM Employees;

Q: Find the difference between each employee’s salary and the previous employee’s salary
Answer:
SELECT name, salary, LAG(salary) OVER(ORDER BY salary) AS prev_salary, salary -
LAG(salary) OVER(ORDER BY salary) AS diff FROM Employees;

8. Constraints & Data Definition

Q: Create a table with constraints
Answer:
CREATE TABLE Departments ( dept_id INT PRIMARY KEY, dept_name VARCHAR(50) UNIQUE );

Q: Add NOT NULL constraint

Answer:
ALTER TABLE Employees ALTER COLUMN salary DECIMAL(10,2) NOT NULL;

Q: Drop hire_date column

Answer:
ALTER TABLE Employees DROP COLUMN hire_date;

Q: Rename Employees table to Staff

Answer:
ALTER TABLE Employees RENAME TO Staff;

Q: Add new column bonus with default

Answer:
ALTER TABLE Employees ADD bonus DECIMAL(10,2) DEFAULT 0;
9. Stored Procedures & Functions
Q: Increase salary by 10% for IT employees
Answer:
CREATE PROCEDURE IncreaseSalary AS UPDATE Employees SET salary = salary * 1.1 WHERE
department = 'IT';

Q: Function to calculate annual salary

Answer:
CREATE FUNCTION AnnualSalary(@salary DECIMAL(10,2)) RETURNS DECIMAL(10,2) AS BEGIN
RETURN @salary * 12 END;

Q: Procedure to insert a new employee safely

Answer:
CREATE PROCEDURE AddEmployee @id INT, @name VARCHAR(100), @dept VARCHAR(50), @salary
DECIMAL(10,2) AS INSERT INTO Employees VALUES(@id, @name, @dept, @salary,
GETDATE());

Q: Trigger to log new employee

Answer:
CREATE TRIGGER LogNewEmployee ON Employees AFTER INSERT AS INSERT INTO EmployeeLog
SELECT * FROM inserted;

Q: Procedure to delete employees hired before 2015

Answer:
CREATE PROCEDURE DeleteOldEmployees AS DELETE FROM Employees WHERE hire_date <
'2015-01-01';

10. Real-World Problem Solving (Pro Level)

Q: Find employees who earn the same salary
Answer:
SELECT * FROM Employees WHERE salary IN (SELECT salary FROM Employees GROUP BY
salary HAVING COUNT(*) > 1);

Q: Find the highest paid employee in each department

Answer:
SELECT department, name, salary FROM Employees e WHERE salary = (SELECT MAX(salary)
FROM Employees WHERE department = e.department);

Q: Find median salary

Answer:
SELECT AVG(salary) AS median FROM (SELECT salary FROM Employees ORDER BY salary
LIMIT 2 - (SELECT COUNT(*) FROM Employees) % 2 OFFSET (SELECT (COUNT(*) - 1) / 2
FROM Employees)) t;

Q: Top 3 earning employees per project

Answer:
SELECT * FROM (SELECT e.name, p.proj_name, salary, RANK() OVER(PARTITION BY
p.proj_name ORDER BY salary DESC) rnk FROM Employees e JOIN Projects p ON e.id =
p.emp_id) t WHERE rnk <= 3;

Q: Find employees who joined in the last 6 months

Answer:
SELECT * FROM Employees WHERE hire_date >= DATEADD(MONTH, -6, GETDATE());

Q: Pivot data: show departments as columns with total salaries

Answer:
SELECT * FROM (SELECT department, salary FROM Employees) src PIVOT (SUM(salary) FOR
department IN ([IT],[HR],[Finance],[Sales])) p;

Q: Find employees with gaps in project assignments

Answer:
 SELECT emp_id, proj_name, start_date, LAG(end_date) OVER(PARTITION BY emp_id ORDER
BY start_date) AS prev_end FROM Projects;

Q: Detect duplicate employee names

Answer:
SELECT name, COUNT(*) FROM Employees GROUP BY name HAVING COUNT(*) > 1;

Q: Find employees who do not have a manager (self join)

Answer:
SELECT e1.name FROM Employees e1 LEFT JOIN Employees e2 ON e1.manager_id = e2.id
WHERE e2.id IS NULL;

Q: Generate report of employees with department, project count, salary

Answer:
SELECT e.name, e.department, COUNT(p.proj_id) AS project_count, SUM(e.salary) AS
total_salary FROM Employees e LEFT JOIN Projects p ON e.id = p.emp_id GROUP BY
e.name, e.department;