02-Library Database Exercise 1 - 10 SQL Questions and Answers (Video in Khmer)

Hello my friend! Welcome to Library Database Exercise 1! Today we practice writing SQL SELECT queries together using the Library Database we built in previous lesson.

If you not create database and tables yet, please go read and setup first here:
👉 Library Database Example - Create Tables with Data Download

📐 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 by yourself in MySQL Workbench first!
3. Then check answer code below to see if your query is correct.

---

Part 1: Questions 1 to 5 (Basic SELECT & WHERE)

Question 1: Show all information about authors

SELECT * FROM authors;

Question 2: Show all types of books

SELECT * FROM types;

Question 3: Show all books that have page count greater than 200 pages

SELECT * FROM books WHERE pageCount > 200;

Question 4: Show all books with pages between 100 and 200 pages

-- Option 1: Using BETWEEN operator
SELECT * FROM books WHERE pageCount BETWEEN 100 AND 200;

-- Option 2: Using >= and <= operator
SELECT * FROM books WHERE pageCount >= 100 AND pageCount <= 200;

Question 5: Show all student classes (show unique class only, no duplicate)

SELECT DISTINCT class FROM students;

---

📺 Watch Video Tutorial for Questions 1 to 5

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

---

Part 2: Questions 6 to 10 (Filtering & JOIN)

Question 6: Show names of all authors

SELECT name FROM authors;

Question 7: Show book name and author name for books written by author "Jack" (INNER JOIN)

SELECT B.name AS book_name, A.name AS author_name
FROM authors A 
INNER JOIN books B ON A.authorID = B.authorID 
WHERE A.name = 'jack';

Question 8: Show name, gender, birth date, and class of all students

SELECT name, gender, birthDate, class FROM students;

Question 9: Show all female students with name, birth date, and class

SELECT name, birthDate, class 
FROM students 
WHERE gender = 'F';

Question 10: Show all female students with point greater than or equal to 800

SELECT * 
FROM students 
WHERE gender = 'F' AND point >= 800;

---

📺 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 SQL Beginner: When searching text in WHERE clause like WHERE gender = 'F' or WHERE name = 'jack', SQL string values must be inside single quotes '...'! If you forget quotes, MySQL will think it is a column name and throw an error!