{"id":2898,"date":"2025-12-08T04:15:52","date_gmt":"2025-12-08T04:15:52","guid":{"rendered":"https:\/\/codeinsightacademy.com\/blog\/?p=2898"},"modified":"2026-01-21T09:20:17","modified_gmt":"2026-01-21T09:20:17","slug":"sql-assignment","status":"publish","type":"post","link":"https:\/\/codeinsightacademy.com\/blog\/sql\/sql-assignment\/","title":{"rendered":"SQL Assignment"},"content":{"rendered":"\n
id<\/th>name<\/th>department<\/th>manager_id<\/th>salary<\/th>city<\/th>created_at<\/th>updated_at<\/th><\/tr><\/thead>
1<\/td>Amit Sharma<\/td>Sales<\/td>NULL<\/td>75000<\/td>Mumbai<\/td>2023-01-10<\/td>2023-06-01<\/td><\/tr>
2<\/td>Priya Patel<\/td>Marketing<\/td>1<\/td>68000<\/td>Ahmedabad<\/td>2023-02-15<\/td>2023-05-18<\/td><\/tr>
3<\/td>Rahul Singh<\/td>IT<\/td>1<\/td>92000<\/td>Bengaluru<\/td>2023-03-05<\/td>2023-04-22<\/td><\/tr>
4<\/td>Neha Gupta<\/td>HR<\/td>2<\/td>61000<\/td>Delhi<\/td>2023-04-20<\/td>2023-05-30<\/td><\/tr>
5<\/td>Karan Verma<\/td>IT<\/td>3<\/td>88000<\/td>Pune<\/td>2023-05-09<\/td>2023-06-10<\/td><\/tr>
6<\/td>Sneha Roy<\/td>Sales<\/td>1<\/td>74000<\/td>Kolkata<\/td>2023-02-11<\/td>2023-05-25<\/td><\/tr>
7<\/td>Rohan Mehta<\/td>Marketing<\/td>2<\/td>70000<\/td>Mumbai<\/td>2023-03-22<\/td>2023-05-17<\/td><\/tr>
8<\/td>Anjali Rao<\/td>IT<\/td>3<\/td>93000<\/td>Hyderabad<\/td>2023-04-15<\/td>2023-06-05<\/td><\/tr><\/tbody><\/table>
table: employees<\/figcaption><\/figure>\n\n\n\n

<\/p>\n\n\n\n

BASIC SELECT (FOUNDATION)<\/h2>\n\n\n\n

1. Display all employees in descending order of salary<\/strong>.<\/h3>\n\n\n\n
\n\n\n\n

2. Display all employees who belong to Mumbai city<\/strong>.
(Assume city is stored inside department or via mapping \u2014 students must clarify)<\/em><\/h3>\n\n\n\n
\n\n\n\n

3. Find all employees whose salary is more than the average salary<\/strong>.<\/h3>\n\n\n\n
\n\n\n\n

4. Display the total sum of salary<\/strong> of all employees.<\/h3>\n\n\n\n
\n\n\n\n

5. Display all unique employee addresses<\/strong>.
(If address column is not available, students must modify table)<\/em><\/h3>\n\n\n\n
\n\n\n\n

6. Display a column called details<\/strong> that shows:
name + address concatenated<\/strong>, along with salary.<\/h3>\n\n\n\n
\n\n\n\n

INTERMEDIATE AGGREGATION & FILTERING<\/h2>\n\n\n\n

7. Display the name(s) of employee(s) having the maximum salary<\/strong>.<\/h3>\n\n\n\n
\n\n\n\n

8. Find the employee(s) having the second highest salary<\/strong>.<\/h3>\n\n\n\n
\n\n\n\n

9. Display the count of employees by address<\/strong>, ordered by employee count.<\/h3>\n\n\n\n
\n\n\n\n

10. Show the total number of employees from Nagpur only<\/strong>.<\/h3>\n\n\n\n
\n\n\n\n

11. Display employees whose names start OR end with a vowel (a,e,i,o,u)<\/strong>.<\/h3>\n\n\n\n
\n\n\n\n

12. Find the employee(s) having maximum salary in each city<\/strong>.<\/h3>\n\n\n\n
\n\n\n\n

13. Display the top 5 highest salaried employees<\/strong>.<\/h3>\n\n\n\n
\n\n\n\n

14. Display the second highest salaried employee using a subquery<\/strong>.<\/h3>\n\n\n\n
\n\n\n\n

15. Show the list of cities where total salary is more than 200000<\/strong>.<\/h3>\n\n\n\n
\n\n\n\n

JOINS BASED ASSIGNMENTS (STUDENT SYSTEM)<\/h2>\n\n\n\n
id<\/th>name<\/th>city<\/th><\/tr><\/thead>
1<\/td>Rahul Kumar<\/td>Patna<\/td><\/tr>
2<\/td>Meera Nair<\/td>Chennai<\/td><\/tr>
3<\/td>Ayesha Khan<\/td>Lucknow<\/td><\/tr>
4<\/td>Arjun Das<\/td>Kolkata<\/td><\/tr>
5<\/td>Sanya Singh<\/td>Jaipur<\/td><\/tr><\/tbody><\/table>
table: students<\/figcaption><\/figure>\n\n\n\n

<\/p>\n\n\n\n

id<\/th>name<\/th><\/tr><\/thead>
1<\/td>Mathematics<\/td><\/tr>
2<\/td>Science<\/td><\/tr>
3<\/td>English<\/td><\/tr>
4<\/td>Computer Science<\/td><\/tr><\/tbody><\/table>
table: subject_master<\/figcaption><\/figure>\n\n\n\n

<\/p>\n\n\n\n

id<\/th>student_id<\/th>department_id<\/th>marks<\/th><\/tr><\/thead>
1<\/td>1<\/td>1<\/td>85<\/td><\/tr>
2<\/td>1<\/td>4<\/td>78<\/td><\/tr>
3<\/td>2<\/td>2<\/td>82<\/td><\/tr>
4<\/td>2<\/td>3<\/td>90<\/td><\/tr>
5<\/td>3<\/td>1<\/td>75<\/td><\/tr>
6<\/td>3<\/td>3<\/td>80<\/td><\/tr>
7<\/td>4<\/td>4<\/td>95<\/td><\/tr>
8<\/td>5<\/td>2<\/td>88<\/td><\/tr><\/tbody><\/table>
table: result<\/figcaption><\/figure>\n\n\n\n

16. Display all students who have appeared for the exam<\/strong>.
(Students who exist in result table)<\/em><\/h3>\n\n\n\n
\n\n\n\n

17. Display:
Student Name, Subject (Department Name), and Marks<\/strong><\/h3>\n\n\n\n

Using:<\/p>\n\n\n\n

  • students<\/li>
  • departments<\/li>
  • result<\/li><\/ul>\n\n\n\n
    \n\n\n\n

    18. Display Student Name and Total Marks<\/strong> only for students whose
    total marks are greater than 80<\/strong>.<\/h3>\n\n\n\n
    \n\n\n\n

    TRIGGERS & EVENTS (ADVANCED)<\/h2>\n\n\n\n

    19. (Trigger Assignment)<\/h3>\n\n\n\n

    Create a trigger<\/strong> such that:<\/p>\n\n\n\n

    Whenever an employee is deleted, their data is automatically inserted into deleted_employees<\/code> table.<\/strong><\/p><\/blockquote>\n\n\n\n

    \n\n\n\n

    20. (Event Scheduler Assignment)<\/h3>\n\n\n\n

    Create a MySQL event<\/strong> that:<\/p>\n\n\n\n

    Automatically deletes audit logs older than 5 minutes<\/strong>, and
    Runs every 2 minutes<\/strong>.<\/p><\/blockquote>\n\n\n\n

    \n\n\n\n

    CHALLENGE<\/h1>\n\n\n\n
    • Find employees who do not have a manager<\/strong><\/li>
    • Find departments having more than 5 employees<\/strong><\/li>
    • Show manager name with employee name<\/strong> (self join)<\/li><\/ul>\n\n\n\n
      \n\n\n\n

      TABLES<\/h1>\n\n\n\n

      \ud83d\udd39 employee<\/h3>\n\n\n\n
      id, name, department, manager_id, salary, created_at, updated_at\n<\/code><\/pre>\n\n\n\n
      \ud83d\udd39 students<\/h3>\n\n\n\n
      id, name\n<\/code><\/pre>\n\n\n\n
      \ud83d\udd39 department<\/h3>\n\n\n\n
      id, name\n<\/code><\/pre>\n\n\n\n
      \ud83d\udd39 result<\/h3>\n\n\n\n
      id, student_id, department_id, marks\n<\/code><\/pre>\n\n\n\n
      \ud83d\udd39 auditlog<\/h3>\n\n\n\n
      id, username, activity, created_at\n<\/code><\/pre>\n\n\n\n
      \n\n\n\n
      <\/p>\n","protected":false},"excerpt":{"rendered":"
      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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[34],"tags":[],"_links":{"self":[{"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/posts\/2898"}],"collection":[{"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/comments?post=2898"}],"version-history":[{"count":10,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/posts\/2898\/revisions"}],"predecessor-version":[{"id":2934,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/posts\/2898\/revisions\/2934"}],"wp:attachment":[{"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/media?parent=2898"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/categories?post=2898"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/tags?post=2898"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}