SQL Queries for Interview Preparation (MySQL):
Basic to Advanced
Introduction
This document presents a set of 15 SQL (MySQL) queries designed for freshers prepar-
ing for interviews at companies like LTI Mindtree, Infosys, and similar IT firms. The
queries are categorized into basic, intermediate, and advanced levels to demonstrate a
clear progression in complexity. Each question includes a brief description of the task,
and answers are provided at the end.
Basic Level Queries
These queries test fundamental SQL operations, such as data retrieval, insertion, and
modification.
1. Write a query to retrieve all employees from the Employees table (emp_id, first_name,
salary) where the salary is at least 40000.
2. Write a query to insert a new employee into the Employees table with emp_id =
201, first_name = ’Alice’, and salary = 45000.
3. Write a query to update the salary of all employees in the Employees table by
increasing it by 5%.
4. Write a query to delete employees from the Employees table who have a salary less
than 30000.
5. Write a query to count the number of employees in the Employees table.
Intermediate Level Queries
These queries involve joins, aggregations, and subqueries, reflecting common real-world
scenarios.
6. Given two tables, Employees (emp_id, first_name, dept_id, salary) and Departments
(dept_id, dept_name), write a query to list each employees first name and their
department name.
7. Write a query to find the department names from the Departments table that have
no employees assigned (using Employees table).
1
8. Write a query to calculate the average salary for each department in the Employees
table, including only departments with more than 3 employees.
9. Write a query to find employees in the Employees table whose salary is higher than
the average salary across all employees.
10. Write a query to identify duplicate employee records in the Employees table based
on first_name.
Advanced Level Queries
These queries test complex operations, including window functions, pivoting, and date
manipulations.
11. Write a query to assign a rank to each employee in the Employees table based on
their salary within their department.
12. Write a table Orders (order_id, customeri d, order_date, amount)andwriteaquerytof indcustome
13. Given an Attendance table (emp_id, attendance_date), write a query to find the
13.
longest streak of consecutive attendance days for each employee.
14. Write a query to find employees in the Employees table whose salary increased
by more than 10% from the previous year, using a SalaryHistory table (emp_id,
salary, year).
Answers
Basic Level Answers
1. Retrieve employees with salary ≥ 40000:
SELECT emp_id , first_name , salary
FROM Employees
WHERE salary >= 40000;
2. Insert new employee:
INSERT INTO Employees ( emp_id , first_name , salary )
VALUES (201 , ’ Alice ’ , 45000) ;
3. Update salary by 5%:
UPDATE Employees
SET salary = salary * 1.05;
4. Delete employees with salary < 30000:
DELETE FROM Employees
WHERE salary < 30000;
5. Count employees:
2
SELECT COUNT (*) AS employee_count
FROM Employees ;
Intermediate Level Answers
6. List employee names and department names:
SELECT e . first_name , d . dept_name
FROM Employees e
INNER JOIN Departments d ON e . dept_id = d . dept_id ;
7. Departments with no employees:
SELECT d . dept_name
FROM Departments d
LEFT JOIN Employees e ON d . dept_id = e . dept_id
WHERE e . emp_id IS NULL ;
8. Average salary per department (>3 employees):
SELECT d . dept_name , AVG ( e . salary ) AS avg_salary
FROM Employees e
INNER JOIN Departments d ON e . dept_id = d . dept_id
GROUP BY d . dept_name
HAVING COUNT ( e . emp_id ) > 3;
9. Employees above average salary:
SELECT emp_id , first_name , salary
FROM Employees
WHERE salary > ( SELECT AVG ( salary ) FROM Employees ) ;
10. Find duplicate employee records:
SELECT first_name , COUNT (*) AS count
FROM Employees
GROUP BY first_name
HAVING count > 1;
Advanced Level Answers
11. Rank employees by salary within department:
SELECT emp_id , first_name , salary , dept_id ,
RANK () OVER ( PARTITION BY dept_id ORDER BY salary DESC
) AS salary_rank
FROM Employees ;
12. Customers with orders > 1000 in last year:
3
SELECT customer_id , SUM ( amount ) AS total_amount
FROM Orders
WHERE order_date >= DATE_SUB ( CURDATE () , INTERVAL 1 YEAR )
GROUP BY customer_id
HAVING total_amount > 1000;
13. Pivot salaries for departments 10 and 20:
SELECT YEAR ( hire_date ) AS year ,
SUM ( CASE WHEN dept_id = 10 THEN salary ELSE 0 END ) AS
dept_10_salary ,
SUM ( CASE WHEN dept_id = 20 THEN salary ELSE 0 END ) AS
dept_20_salary
FROM Employees
WHERE YEAR ( hire_date ) = 2024
GROUP BY year ;
14. Longest consecutive attendance streak:
WITH AttendanceGroups AS (
SELECT emp_id , attendance_date ,
DATE_SUB ( attendance_date , INTERVAL ROW_NUMBER ()
OVER ( PARTITION BY emp_id ORDER BY
attendance_date ) DAY ) AS group_id
FROM Attendance
)
SELECT emp_id , MIN ( attendance_date ) AS start_date , MAX (
attendance_date ) AS end_date , COUNT (*) AS streak_days
FROM AttendanceGroups
GROUP BY emp_id , group_id
ORDER BY streak_days DESC
LIMIT 1;
15. Salary increase > 10% from previous year:
SELECT emp_id , salary , year
FROM (
SELECT emp_id , salary , year ,
LAG ( salary ) OVER ( PARTITION BY emp_id ORDER BY
year ) AS prev_salary
FROM SalaryHistory
) t
WHERE salary > prev_salary * 1.10;
Preparation Tips
• Practice queries on platforms like LeetCode or HackerRank.
• Understand MySQL-specific functions (e.g., DATE_SUB, window functions).
• Review database concepts like normalization and indexing.
4
• Simulate interview scenarios to build confidence.
