Student Exam Database Part 3: 10 SQL SELECT statement - Exercise #1

Student Exam Database Part 3: 10 SQL SELECT statement - Exercise #1

This is first excercise for Student Exam database part 3 in MySQL speak Khmer. Videos below are in Khmer which explain step by step.

SQL SELECT statement is the most use query to collect and summary data from the database base on a certain condition. The example questions below are mix with simple and complex query with tables.

Learn More Introduction to Database (SQL) in Khmer with videos: Chapter 1 | Chapter 2 | Chapter 3

Here is an ER Diagram (Table Relationship) helping you to understand more about this sample database

Questions in Khmer in the picture below

Below are 10 SQL Questions with Answers.
1. Show all student information who from Siam Reap

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

2. Show all course name which teach by Mr. Visal

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

3. Show all course name and teacher name for all courses

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

4. Show all student name that start with letter K

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

5. Show student information who born in 2000 and not from phnom penh

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

EP17 លំហាត់ទី១ ផ្នែកទី១ សំណួរចម្លើយ SQL statement នៃ Student Exam Database
This video explain step by step from question 1 to 5 in detail.

6. Show total number of students from provincal

SELECT city, count(*) as Counts
    FROM students
    GROUP BY city;

7. Show total number of student that have id equal to 10

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

8. Show all students name, gender and total score

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

9. Show all students information who haven't take exam even one course.

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

10. Show student name and score for Khmer subject.

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

EP17 លំហាត់ទី១ ផ្នែកទី២ សំណួរចម្លើយ SQL statement នៃ Student Exam Database
This video explain in detail from question 6 to 10

Free Khmer Ebook Download (PDF): Database | Microsoft Access | Python Programming