| id | name | department | manager_id | salary | city | created_at | updated_at |
|---|---|---|---|---|---|---|---|
| 1 | Amit Sharma | Sales | NULL | 75000 | Mumbai | 2023-01-10 | 2023-06-01 |
| 2 | Priya Patel | Marketing | 1 | 68000 | Ahmedabad | 2023-02-15 | 2023-05-18 |
| 3 | Rahul Singh | IT | 1 | 92000 | Bengaluru | 2023-03-05 | 2023-04-22 |
| 4 | Neha Gupta | HR | 2 | 61000 | Delhi | 2023-04-20 | 2023-05-30 |
| 5 | Karan Verma | IT | 3 | 88000 | Pune | 2023-05-09 | 2023-06-10 |
| 6 | Sneha Roy | Sales | 1 | 74000 | Kolkata | 2023-02-11 | 2023-05-25 |
| 7 | Rohan Mehta | Marketing | 2 | 70000 | Mumbai | 2023-03-22 | 2023-05-17 |
| 8 | Anjali Rao | IT | 3 | 93000 | Hyderabad | 2023-04-15 | 2023-06-05 |
BASIC SELECT (FOUNDATION)
1. Display all employees in descending order of salary.
2. Display all employees who belong to Mumbai city.
(Assume city is stored inside department or via mapping — students must clarify)
3. Find all employees whose salary is more than the average salary.
4. Display the total sum of salary of all employees.
5. Display all unique employee addresses.
(If address column is not available, students must modify table)
6. Display a column called details that shows:
name + address concatenated, along with salary.
INTERMEDIATE AGGREGATION & FILTERING
7. Display the name(s) of employee(s) having the maximum salary.
8. Find the employee(s) having the second highest salary.
9. Display the count of employees by address, ordered by employee count.
10. Show the total number of employees from Nagpur only.
11. Display employees whose names start OR end with a vowel (a,e,i,o,u).
12. Find the employee(s) having maximum salary in each city.
13. Display the top 5 highest salaried employees.
14. Display the second highest salaried employee using a subquery.
15. Show the list of cities where total salary is more than 200000.
JOINS BASED ASSIGNMENTS (STUDENT SYSTEM)
| id | name | city |
|---|---|---|
| 1 | Rahul Kumar | Patna |
| 2 | Meera Nair | Chennai |
| 3 | Ayesha Khan | Lucknow |
| 4 | Arjun Das | Kolkata |
| 5 | Sanya Singh | Jaipur |
| id | name |
|---|---|
| 1 | Mathematics |
| 2 | Science |
| 3 | English |
| 4 | Computer Science |
| id | student_id | department_id | marks |
|---|---|---|---|
| 1 | 1 | 1 | 85 |
| 2 | 1 | 4 | 78 |
| 3 | 2 | 2 | 82 |
| 4 | 2 | 3 | 90 |
| 5 | 3 | 1 | 75 |
| 6 | 3 | 3 | 80 |
| 7 | 4 | 4 | 95 |
| 8 | 5 | 2 | 88 |
16. Display all students who have appeared for the exam.
(Students who exist in result table)
17. Display:
Student Name, Subject (Department Name), and Marks
Using:
- students
- departments
- result
18. Display Student Name and Total Marks only for students whose
total marks are greater than 80.
TRIGGERS & EVENTS (ADVANCED)
19. (Trigger Assignment)
Create a trigger such that:
Whenever an employee is deleted, their data is automatically inserted into
deleted_employeestable.
20. (Event Scheduler Assignment)
Create a MySQL event that:
Automatically deletes audit logs older than 5 minutes, and
Runs every 2 minutes.
CHALLENGE
- Find employees who do not have a manager
- Find departments having more than 5 employees
- Show manager name with employee name (self join)
TABLES
🔹 employee
id, name, department, manager_id, salary, created_at, updated_at
🔹 students
id, name
🔹 department
id, name
🔹 result
id, student_id, department_id, marks
🔹 auditlog
id, username, activity, created_at