MySQL Assignment-3

Basic Questions

  1. Create a table students with columns id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), age INT NOT NULL.
  2. Insert 5 students into the students table in a single INSERT statement.
  3. Create a table courses with course_id INT PRIMARY KEY AUTO_INCREMENT, course_name VARCHAR(100) UNIQUE.
  4. Insert 3 sample courses.
  5. Create a table enrollments with composite key (student_id, course_id).
  6. Insert enrollment data for 3 students in different courses.
  7. Write SQL to update the age of a student with id = 2 to 25.
  8. Write SQL to delete all students whose age is less than 18.
  9. Select student names along with their enrolled courses (JOIN).
  10. Write SQL to count how many students are enrolled in each course.
  11. Create a table teachers with id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), subject VARCHAR(100).
  12. Insert 3 teachers into the teachers table.
  13. Write SQL to add a foreign key teacher_id to courses referencing teachers.id.
  14. Write SQL to fetch all courses with their teacher names.
  15. Add a created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP column to students.
  16. Write SQL to show the 2 most recently added students.
  17. Create an index on students.name.
  18. Drop the index on students.name.
  19. Insert a new course but rollback the transaction before commit.
  20. Insert a new teacher inside a transaction and commit the changes.

Intermediate Questions

  1. Create a table departments with columns dept_id INT PRIMARY KEY, dept_name VARCHAR(100).
  2. Insert 3 departments in one SQL statement.
  3. Add a column dept_id in teachers and set it as a foreign key referencing departments.
  4. Write SQL to fetch teacher names with their department names (JOIN).
  5. Write SQL to count how many teachers belong to each department.
  6. Create a unique index on teachers.name.
  7. Drop the unique index on teachers.name.
  8. Write SQL to find duplicate course names if any exist.
  9. Write SQL to list students who are not enrolled in any course (LEFT JOIN).
  10. Write SQL to list courses that have no students enrolled (RIGHT JOIN or NOT IN).
  11. Write SQL to select the top 3 highest ages from students.
  12. Create a table payments with pay_id INT PRIMARY KEY, student_id INT, amount DECIMAL(10,2), pay_date DATE.
  13. Insert 5 payment records for different students.
  14. Write SQL to calculate total payment amount for each student.
  15. Write SQL to fetch students who have paid more than 5000 total.
  16. Write SQL to update payment amount for a student and then rollback.
  17. Write SQL to create a savepoint in a transaction, insert data, then rollback to savepoint.
  18. Create a covering index for a query selecting student_id, amount from payments where pay_date > ‘2024-01-01’.
  19. Write SQL to delete all enrollments of a specific student inside a transaction and commit.
  20. Write SQL to transfer 1000 from one student’s payment record to another using a transaction.

Advanced Questions

  1. Create a table employees with emp_id INT PRIMARY KEY, emp_name VARCHAR(100), manager_id INT NULL.
  2. Insert 5 employees with a manager-subordinate hierarchy.
  3. Write SQL for a self-join to show employees with their managers.
  4. Create a table projects with proj_id INT PRIMARY KEY, proj_name VARCHAR(100), dept_id INT.
  5. Insert 3 projects linked to different departments.
  6. Write SQL to fetch all projects with department names (JOIN).
  7. Write SQL to assign employees to projects using a junction table emp_projects(emp_id, proj_id) with composite PK.
  8. Insert data into emp_projects showing at least 2 employees per project.
  9. Write SQL to fetch employee names with their project names (JOIN).
  10. Create a transaction in which you insert into employees, assign them to a project, then rollback the assignment but keep the employee.