Case Study 1: Student Management System

SQL Case Studies


Objective:


Manage student enrollments, grades, and attendance records.

Tables:



students(id, name, dob, gender)
courses(id, name, credit_hours)
enrollments(student_id, course_id, grade)
attendance(student_id, course_id, date, status)

Tasks:


  1. List all students enrolled in "Mathematics"
  2. Calculate the average grade for each course
  3. List students with more than 3 absences in any course
  4. Generate a report of student GPA

Sample Solution for Task 1:


SELECT s.name 
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id
WHERE c.name = 'Mathematics';


Case Study 2: Online Retail Store



Objective:


Track customer orders, products, and payments.


Tables:


customers(id, name, email)
products(id, name, category, price)
orders(id, customer_id, order_date)
order_items(order_id, product_id, quantity)
payments(order_id, amount, payment_method, status)

Tasks:


  1. Display total revenue per product
  2. List the top 3 customers by total spending
  3. Find customers who haven't ordered in the past 6 months
  4. Generate a sales report by category

Sample Solution for Task 2:


SELECT c.name, SUM(oi.quantity * p.price) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY c.id
ORDER BY total_spent DESC
LIMIT 3;


Case Study 3: Employee Attendance Tracker


Objective:


Monitor employee attendance across departments.


Tables:


employees(id, name, department_id, hire_date)
departments(id, name)
attendance(employee_id, date, status)

Tasks:


  1. Find employees with 100% attendance this month
  2. Count the number of absences per department
  3. List departments with the highest attendance rate
  4. Create monthly summaries of attendance per employee

Sample Solution for Task 3:


SELECT d.name, 
       COUNT(CASE WHEN a.status = 'present' THEN 1 END) * 100.0 / COUNT(*) AS attendance_rate
FROM departments d
JOIN employees e ON d.id = e.department_id
JOIN attendance a ON e.id = a.employee_id
WHERE a.date BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH) AND CURRENT_DATE
GROUP BY d.id
ORDER BY attendance_rate DESC;


Case Study 4: Blogging Platform


Objective:


Analyze user activity, posts, and interactions.

Tables:


users(id, username, email)
posts(id, user_id, title, content, created_at)
comments(id, post_id, user_id, content, created_at)
likes(id, user_id, post_id)

Tasks:


  1. List posts with the number of comments
  2. Identify the most active users by number of posts
  3. Find users who haven't posted anything
  4. Show the most liked posts in the last 30 days

Sample Solution for Task 4:


SELECT p.title, COUNT(l.id) AS like_count
FROM posts p
LEFT JOIN likes l ON p.id = l.post_id
WHERE p.created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY p.id
ORDER BY like_count DESC
LIMIT 5;


Case Study 5: Library Management System


Objective:


Track book loans and membership activity.


Tables:


books(id, title, author, category, available_copies)
members(id, name, membership_date)
loans(book_id, member_id, loan_date, return_date)

Tasks:


  1. List currently loaned books with due dates
  2. Find members who borrowed more than 5 books
  3. Identify overdue books
  4. Show the most borrowed book

Sample Solution for Task 1:


SELECT b.title, m.name AS member_name, l.loan_date, l.return_date
FROM loans l
JOIN books b ON l.book_id = b.id
JOIN members m ON l.member_id = m.id
WHERE l.return_date IS NULL;


Case Study 6: Social Media Mini Clone


Objective:


Manage users, posts, likes, and messaging.


Tables:


users(id, username)
posts(id, user_id, content, created_at)
likes(user_id, post_id)
messages(id, sender_id, receiver_id, content, sent_at)

Tasks:


  1. Find users who received the most messages
  2. Show posts with the highest number of likes
  3. List users who interacted with each other (both liked and messaged)
  4. Calculate average likes per user

Sample Solution for Task 2:


SELECT p.content, COUNT(l.user_id) AS like_count
FROM posts p
LEFT JOIN likes l ON p.id = l.post_id
GROUP BY p.id
ORDER BY like_count DESC
LIMIT 5;


Conclusion


These case studies demonstrate practical applications of SQL in various domains. Each scenario presents common business problems that can be solved with proper database design and SQL queries. Practice these examples to strengthen your SQL problem-solving skills.