ĐỀ TEST SQL – CHỦ ĐỀ: QUẢN LÝ SINH VIÊN

⏱ Thời gian làm bài: 90 phút
 🎯 Mục tiêu đánh giá:

●     Viết SQL chính xác

●     Hiểu nghiệp vụ giáo dục

●     Phát hiện dữ liệu bất thường

●     Tư duy kiểm thử dữ liệu

I. MÔ TẢ HỆ THỐNG & CSDL

Giả định DB: MySQL / PostgreSQL

1️⃣ Bảng students

students (

    id BIGINT PRIMARY KEY,

    student_code VARCHAR(20),

    full_name VARCHAR(100),

    gender VARCHAR(10),       -- male, female

    date_of_birth DATE,

    class_id BIGINT,

    status VARCHAR(20),       -- studying, graduated, dropped

    created_at TIMESTAMP

)

 

2️⃣ Bảng classes

classes (

    id BIGINT PRIMARY KEY,

    class_code VARCHAR(20),

    class_name VARCHAR(100),

    department VARCHAR(100)

)

 

3️⃣ Bảng subjects

subjects (

    id BIGINT PRIMARY KEY,

    subject_code VARCHAR(20),

    subject_name VARCHAR(100),

    credit INT

)

 

4️⃣ Bảng enrollments

enrollments (

    id BIGINT PRIMARY KEY,

    student_id BIGINT,

    subject_id BIGINT,

    semester VARCHAR(20),     -- 2024-1, 2024-2

    score DECIMAL(4,2)

)

 

5️⃣ Bảng attendance_logs

attendance_logs (

    id BIGINT PRIMARY KEY,

    student_id BIGINT,

    subject_id BIGINT,

    attend_date DATE,

    status VARCHAR(20)        -- present, absent

)

 

II. PHẦN A – SQL CƠ BẢN

⏱ Thời gian gợi ý: 35–40 phút

🟢 Câu 1: Danh sách sinh viên đang học

Yêu cầu:
 Lấy student_code, full_name, gender, status của sinh viên có status = 'studying'

🟢 Câu 2: Sinh viên và lớp học

Yêu cầu:
 Lấy danh sách sinh viên kèm class_code, class_name

🟢 Câu 3: Số sinh viên theo khoa

Yêu cầu:
 Thống kê số lượng sinh viên theo department

🟢 Câu 4: Môn học mà sinh viên đăng ký

Yêu cầu:
 Với student_code = 'SV001', liệt kê:

subject_code | subject_name | semester | score

 

🟢 Câu 5: Điểm trung bình của mỗi sinh viên

Yêu cầu:
 Tính điểm trung bình (AVG) của từng sinh viên

✅ Tiêu chí pass Cơ bản

●     JOIN đúng

●     GROUP BY chính xác

●     Không SELECT *

●     SQL chạy được

III. PHẦN B – SQL NÂNG CAO

⏱ Thời gian gợi ý: 50–55 phút

🔵 Câu 6: Sinh viên chưa đăng ký môn học nào

Yêu cầu:
 Liệt kê sinh viên không có bản ghi trong enrollments

🔵 Câu 7: Sinh viên trượt môn

Yêu cầu:
 Liệt kê sinh viên có bất kỳ môn nào có score < 5

🔵 Câu 8: Môn học có số sinh viên đăng ký nhiều nhất

Yêu cầu:
 Tìm môn học có số lượng sinh viên đăng ký cao nhất

🔵 Câu 9: Lần điểm danh gần nhất của mỗi sinh viên

Yêu cầu:
 Với mỗi sinh viên, lấy bản ghi điểm danh mới nhất (ngày & trạng thái)

🔵 Câu 10: Phát hiện dữ liệu bất thường

Yêu cầu:
 Liệt kê:

1. enrollments có student_id không tồn tại
   
   
2. attendance_logs có subject_id không tồn tại
   
   

➡️ Mục tiêu: Data Integrity Testing

🔵 Câu 11 (Bonus – Tư duy hệ thống)

Câu hỏi ngắn:

1. Nên index những cột nào?
   
   
2. Vì sao không nên lưu điểm trung bình sẵn trong bảng students?