04-Library Database Exercise 3 - 5 Advanced SQL Questions and Answers (Video in Khmer)

Hello my friend! Welcome to Library Database Exercise 3! Today we practice writing 5 more advanced SQL SELECT queries together using the Library Database. In this lesson, we will learn date functions like CURDATE() and YEAR(), plus aggregate functions like SUM(), AVG(), MAX(), and MIN()!

If you not create database or not practice previous exercises yet, please check here first:
👉 Library Database Example - Create Tables with Data Download
👉 Library Database Exercise 1 - 10 SQL Questions and Answers
👉 Library Database Exercise 2 - 10 More SQL Questions and Answers

---

📐 ERD Diagram Reference

Here is the ER Diagram of our Library Database to help you see how tables connect:

---

📝 How to Practice:

1. Read each question carefully.
2. Try write SQL code in MySQL Workbench by yourself first!
3. Then check answer solution below to see if your query is correct.

---

5 Advanced SQL Questions & Answers

Question 1: Show book name and count of how many times each book was borrowed in 2017

SELECT b.name AS book_name, COUNT(o.bookID) AS Counts
FROM books b 
INNER JOIN borrows o ON b.bookID = o.bookID 
WHERE YEAR(o.takenDate) = 2017
GROUP BY b.name;

---

Question 2: Show all student information who are older than 18 years old

SELECT * 
FROM students
WHERE YEAR(CURDATE()) - YEAR(birthdate) > 18;

📌 Note on MySQL Date Functions:

• CURDATE() — returns current system date (e.g. 2026-08-21)
• YEAR(date) — extracts 4-digit year number from a date value

---

Question 3: Show name, gender, and current age of all students

SELECT name, gender, YEAR(CURDATE()) - YEAR(birthdate) AS Age
FROM students;

---

Question 4: Show average student points in class '11A'

SELECT AVG(point) AS average_point 
FROM students
WHERE class = '11A';

---

Question 5: Show total points, average, maximum, and minimum points of students in class '11A'

SELECT 
  SUM(point) AS total_point, 
  AVG(point) AS avg_point, 
  MAX(point) AS max_point, 
  MIN(point) AS min_point
FROM students
WHERE class = '11A';

---

📺 Watch Video Tutorial for Exercise 3

This video explain step-by-step solution for all 5 questions in Khmer language:

---

💡 Tip for MySQL Functions: You can combine multiple aggregate functions like SUM(), AVG(), MAX(), and MIN() in a single SELECT statement! This allows you to get full summary statistics of your data in just 1 fast query!