Student Exam Database Part 3 - 10 SQL SELECT Questions and Answers (Video in Khmer)

Hello my friend! Today we continue with Part 3 of our Student Exam Database project!

In this lesson, we practice writing 10 SQL SELECT queries to query and summarize data from our 4 tables (Students, Teachers, Courses, Exam). We will use INNER JOIN, LIKE, YEAR(), GROUP BY, SUM(), and Subqueries! Video tutorials are in Khmer language, very easy to follow! Now let get start!

If you not create tables or insert data yet, please check here first:
👉 Student Exam Database Part 1 - Create Tables in MySQL
👉 Student Exam Database Part 2 - Insert Data and Download Example

---

📐 ERD Diagram Reference

Here is the ER Diagram of our Student Exam 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 verify your result.

---

Part 1: Questions 1 to 5 (Filtering & Basic JOIN)

Question 1: Show all student information who are from 'Siem Reap'

SELECT * 
FROM Students 
WHERE city = 'Siem Reap';

---

Question 2: Show all course names taught by teacher 'Visal' (INNER JOIN)

SELECT c.courseName
FROM courses c 
INNER JOIN teachers t ON c.teacherID = t.teacherID
WHERE t.name = 'visal';

---

Question 3: Show all course names and teacher names for all courses

SELECT t.name AS teacher_name, c.courseName
FROM courses c 
INNER JOIN teachers t ON c.teacherID = t.teacherID;

---

Question 4: Show all student information whose name starts with letter 'K'

SELECT * 
FROM students 
WHERE name LIKE 'K%';

---

Question 5: Show student information for students born in year 2000 and NOT from 'Phnom Penh'

SELECT * 
FROM students
WHERE YEAR(dateOfBirth) = 2000 AND city != 'Phnom Penh';

---

📺 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 (GROUP BY, SUM & Subquery)

Question 6: Show total number of students from each city (province)

SELECT city, COUNT(*) AS Counts
FROM students
GROUP BY city;

---

Question 7: Show student name and total exam score for student with ID = 10

SELECT s.name, SUM(e.score) AS total_score
FROM students s 
INNER JOIN Exam e ON s.studentID = e.studentID
WHERE s.studentID = 10;

---

Question 8: Show all student names, gender, and their total exam score

SELECT s.name, s.gender, SUM(e.score) AS total_score
FROM students s 
INNER JOIN Exam e ON s.studentID = e.studentID
GROUP BY s.name, s.gender;

---

Question 9: Show all student information for students who have NOT taken any exam yet (Subquery with NOT IN)

SELECT * 
FROM Students 
WHERE studentID NOT IN (
    SELECT studentID FROM Exam
);

---

Question 10: Show student name and score for 'Khmer' subject

SELECT s.name, e.score
FROM Students s 
INNER JOIN Exam e ON s.studentID = e.studentID
INNER JOIN Courses c ON e.courseID = c.courseID
WHERE c.courseName = 'Khmer';

---

📺 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 JOINs: When joining 3 tables together (like Students, Exam, and Courses in Question 10), chain the INNER JOIN clauses sequentially using their matching Primary Key and Foreign Key pairs!