SQL Practice – Online Learning Platform
■ Estimated Duration: 4 Hours
Section 1: Database Schema
CREATE TABLE Students ( student_id INTEGER PRIMARY KEY, first_name TEXT NOT
NULL, last_name TEXT NOT NULL, email TEXT UNIQUE NOT NULL, registration_date
DATE ); CREATE TABLE Instructors ( instructor_id INTEGER PRIMARY KEY, first_name
TEXT NOT NULL, last_name TEXT NOT NULL, expertise TEXT ); CREATE TABLE
Courses ( course_id INTEGER PRIMARY KEY, course_name TEXT NOT NULL, category
TEXT, instructor_id INTEGER, price REAL, FOREIGN KEY (instructor_id) REFERENCES
Instructors(instructor_id) ); CREATE TABLE Enrollments ( enrollment_id INTEGER
PRIMARY KEY, student_id INTEGER, course_id INTEGER, enrollment_date DATE,
progress INTEGER CHECK (progress BETWEEN 0 AND 100), FOREIGN KEY
(student_id) REFERENCES Students(student_id), FOREIGN KEY (course_id)
REFERENCES Courses(course_id) ); CREATE TABLE Payments ( payment_id INTEGER
PRIMARY KEY, student_id INTEGER, amount REAL, payment_date DATE,
payment_method TEXT, FOREIGN KEY (student_id) REFERENCES
Students(student_id) ); CREATE TABLE Reviews ( review_id INTEGER PRIMARY KEY,
student_id INTEGER, course_id INTEGER, rating INTEGER CHECK (rating BETWEEN 1
AND 5), comment TEXT, review_date DATE, FOREIGN KEY (student_id) REFERENCES
Students(student_id), FOREIGN KEY (course_id) REFERENCES Courses(course_id) );
Section 2: Insert Sample Data
INSERT INTO Students VALUES (1, 'Alice', 'Johnson', 'alice.j@[Link]',
'2024-01-10'), (2, 'Bob', 'Smith', 'bob.s@[Link]', '2024-02-12'), (3, 'Cathy', 'Brown',
'cathy.b@[Link]', '2024-03-15'); INSERT INTO Instructors VALUES (1, 'David',
'Wilson', 'Data Science'), (2, 'Emma', 'Davis', 'Web Development'), (3, 'Frank', 'Moore',
'Graphic Design'); INSERT INTO Courses VALUES (1, 'Python for Beginners',
'Programming', 1, 79.99), (2, 'Advanced JavaScript', 'Web Development', 2, 89.99), (3,
'UI/UX Design Fundamentals', 'Design', 3, 59.99); INSERT INTO Enrollments VALUES (1,
1, 1, '2024-04-01', 80), (2, 2, 1, '2024-04-05', 50), (3, 3, 3, '2024-05-01', 90); INSERT INTO
Payments VALUES (1, 1, 79.99, '2024-04-02', 'Credit Card'), (2, 2, 79.99, '2024-04-06',
'PayPal'), (3, 3, 59.99, '2024-05-02', 'Credit Card'); INSERT INTO Reviews VALUES (1, 1,
1, 5, 'Excellent introduction to Python!', '2024-04-10'), (2, 2, 1, 4, 'Good course but could
use more exercises.', '2024-04-15'), (3, 3, 3, 5, 'Loved the design examples!',
'2024-05-10');
Section 3: SQL Exercises
1 1. List all students with their registration dates.
2 2. Show all courses with their instructor names.
3 3. Display all enrollments along with student and course names.
4 4. Find the total number of courses each instructor teaches.
5 5. Retrieve the average rating for each course.
6 6. List all students who have enrolled but not completed (progress < 100).
7 7. Find the total payment amount per student.
8 8. Show all students who paid using 'Credit Card'.
9 9. Find students who have never written a review.
10 10. Display courses with an average rating higher than 4.5.
11 11. Find which instructor has the most enrolled students.
12 12. Retrieve top 2 most popular courses based on enrollments.
13 13. Show courses with total revenue over 100 USD.
14 14. Create a view named CourseSummary showing course_name, instructor,
total_students, average_rating.
15 15. List all reviews that include the word 'good'.
16 16. Count the number of 5-star reviews per instructor.
17 17. Update the progress of student_id=2 in course_id=1 to 100.
18 18. Delete any review with rating lower than 3.
19 19. Use a subquery to find students who spent above the average total spending.
20 20. Show the top 3 students by total payments.
21 21. Display each course category and total enrollment count.
22 22. Use CASE WHEN to categorize students based on spending (High, Medium, Low).
23 23. Combine all instructor and student names using UNION.
24 24. Find courses with no enrollments using LEFT JOIN.
25 25. Create a report showing instructor name, total revenue, and average course rating.