MySQL Assignment-2

Basic Questions

  1. Write SQL to insert a new student record into a students table with fields (id, name, age).
  2. Write SQL to retrieve all columns from a table named employees.
  3. Write SQL to update the salary of an employee with id = 5 in the employees table.
  4. Write SQL to delete a record of a customer with id = 10 from the customers table.
  5. Write SQL to select only name and salary columns froam employees.
  6. Write SQL to filter employees with a salary greater than 50000.
  7. Write SQL to select employees whose age is between 25 and 40.
  8. Write SQL to find all products where the price is not NULL.
  9. Write SQL to sort employees by salary in descending order.
  10. Write SQL to limit results to the first 10 rows from the students table.
  11. Write SQL to skip the first 5 rows and then fetch the next 10 rows from employees.
  12. Write SQL to count the total number of employees.
  13. Write SQL to find the minimum salary from the employees table.
  14. Write SQL to calculate the average price of all products.
  15. Write SQL to find the sum of all salaries in the employees table.
  16. Write SQL to group employees by department and count how many employees each department has.
  17. Write SQL to filter grouped departments where total salary > 1,00,000.
  18. Write SQL to insert multiple rows in a departments table using one statement.
  19. Write SQL to select distinct city names from the customers table.
  20. Write SQL to fetch employees whose name starts with ‘A’.

Intermediate Questions

  1. Write SQL to fetch employees with salary greater than 50000 AND working in department IT.
  2. Write SQL to fetch employees who are NOT in department HR.
  3. Write SQL to select customers who live in either Delhi OR Mumbai.
  4. Write SQL to retrieve all employees who do not have a manager (manager_id IS NULL).
  5. Write SQL to sort customers first by city ascending and then by name descending.
  6. Write SQL to find the top 3 highest salaries from employees.
  7. Write SQL to group products by category and find the average price per category.
  8. Write SQL to count the number of orders placed by each customer.
  9. Write SQL to find employees whose salary is greater than the average salary of all employees.
  10. Write SQL to join employees and departments tables and show employee names with department names.
  11. Write SQL to retrieve all employees even if they are not assigned to any department (LEFT JOIN).
  12. Write SQL to retrieve all departments even if no employee belongs to them (RIGHT JOIN).
  13. Write SQL to retrieve all employees and all departments regardless of match (FULL OUTER JOIN).
  14. Write SQL to perform a self-join on employees to find employees and their managers.
  15. Write SQL to perform a cross join between products and categories.
  16. Write SQL to use a subquery in WHERE clause: find employees who earn more than the average salary.
  17. Write SQL to use a subquery in FROM clause: calculate the max salary from a derived table of departments.
  18. Write SQL to use a subquery in SELECT clause: display employee name and their department’s average salary.
  19. Write SQL using UNION to combine names from employees and customers.
  20. Write SQL using UNION ALL to combine names from employees and customers including duplicates.

Advanced Questions

  1. Write SQL using INTERSECT to find common names in employees and customers tables.
  2. Write SQL using EXCEPT to find employee names that are not present in customers.
  3. Write a correlated subquery to find employees whose salary is greater than the salary of any employee in HR department.
  4. Write a nested subquery to find the department name of the employee with the highest salary.
  5. Write SQL to rank employees by salary using ROW_NUMBER().
  6. Write SQL to rank employees by salary within each department using RANK().
  7. Write SQL to assign dense ranks to employees by salary across all departments using DENSE_RANK().
  8. Write SQL to calculate a running total of employee salaries using SUM() OVER (ORDER BY salary).
  9. Write SQL to calculate the average salary partitioned by department using window functions.
  10. Write SQL to fetch the top 2 highest-paid employees from each department using window functions.