03-Library Database Exercise 2 - 10 More SQL Questions and Answers (Video in Khmer)

Hello my friend! Welcome to Library Database Exercise 2! Today we practice writing more SQL SELECT queries together. This lesson has a bit more advanced query like COUNT(), GROUP BY, HAVING, and Subquery!

If you not create database or not practice Exercise 1 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
👉 Library Database Exercise 3 - 5 Advanced 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 query by yourself in MySQL Workbench first!
3. Then check answer code below to verify your result.

---

Part 1: Questions 1 to 5 (COUNT, GROUP BY & HAVING)

Question 1: Show total number of books and set column title alias as 'Total Book'

SELECT COUNT(*) AS 'Total Book' 
FROM books;

Question 2: Show total number of books which have page count 200 and above

SELECT COUNT(*) AS 'Total Book' 
FROM books
WHERE pagecount >= 200;

Question 3: Show student name and count for students who borrowed books more than 30 times (JOIN, GROUP BY, HAVING)

SELECT s.name, COUNT(b.studentID) AS Counts 
FROM students s 
INNER JOIN borrows b ON s.studentID = b.studentID
GROUP BY s.name 
HAVING Counts > 30;

Question 4: Show total number of books borrowed in year 2016

SELECT COUNT(*) 
FROM borrows 
WHERE YEAR(takenDate) = 2016;

Question 5: Show total number of books borrowed in each year

SELECT YEAR(takenDate) AS Years, COUNT(*) AS Counts 
FROM borrows
GROUP BY Years;

---

📺 Watch Video Tutorial for Questions 1 to 5

This video explain step-by-step solution for Questions 1 to 5 in Khmer language:

---

Part 2: Questions 6 to 10 (Subqueries & Aggregation)

Question 6: Show total number of unique students who borrowed books

SELECT COUNT(DISTINCT studentID) 
FROM borrows;

Question 7: Show all student information who NEVER borrowed any book (Subquery with NOT IN)

SELECT * 
FROM students s 
WHERE s.studentID NOT IN (
    SELECT studentID FROM borrows
);

Question 8: Show total number of books with type 'Comics' (using INNER JOIN)

SELECT COUNT(*) 
FROM books b 
INNER JOIN types t ON b.typeID = t.typeID 
WHERE t.name = 'Comics';

Question 9: Show total number of books with type 'Comics' (using Subquery)

SELECT COUNT(*) 
FROM books 
WHERE typeID IN (
    SELECT typeID FROM types WHERE name = 'Comics'
);

Question 10: Show book type name and total book count in each type

SELECT t.name, COUNT(b.typeID) AS Counts
FROM types t 
INNER JOIN books b ON t.typeID = b.typeID
GROUP BY t.name;

---

📺 Watch Video Tutorial for Questions 6 to 10

This video explain step-by-step solution for Questions 6 to 10 in Khmer language:

---

💡 Tip for Advanced SQL: When filtering aggregated results (like COUNT(*) > 30), you CANNOT use WHERE clause! You MUST use HAVING clause after GROUP BY! Remember: WHERE filters rows before grouping, HAVING filters results after grouping!