Database Practice Review

01 · OBJECTIVES & BIG PICTURE

Objectives

By the end of Week 6, students should be able to:

• Consolidate everything covered in the practice sessions so far
• Read and analyze a real-world management problem
• Identify the Entities, Attributes, and Relationships involved
• Convert from an ER/EER model to a relational model
• Identify the Primary Key, Foreign Key, and Candidate Key
• Create a database and tables with SQL, and set up integrity constraints
• Perform SELECT, WHERE, ORDER BY, GROUP BY, HAVING, JOIN, Subquery, aggregate functions, INSERT, UPDATE, DELETE
• Combine multiple SQL techniques to solve a complete problem
• Spot and fix common mistakes when writing queries

The Big Picture

Students need to look back over the whole process of building a database:

02 · REVIEWING THE DATABASE MODEL

Data Model

Entity

An Entity is an object that needs to be managed within the system. Example - a student management system:

Attribute

Example - the SINHVIEN Entity:

03 · KEYS IN A DATABASE

Keys in a Database

Primary Key

A Primary Key uniquely identifies a record:

CREATE TABLE SinhVien (
    MaSV VARCHAR(10) PRIMARY KEY,
    HoTen NVARCHAR(100),
    NgaySinh DATE
);

MaSV cannot be duplicated and cannot be NULL.

Foreign Key

A Foreign Key creates a relationship between tables:

CREATE TABLE SinhVien (
    MaSV VARCHAR(10) PRIMARY KEY,
    HoTen NVARCHAR(100),
    MaLop VARCHAR(10),

    FOREIGN KEY (MaLop)
        REFERENCES Lop(MaLop)
);

Relationship - one class has many students:

04 · REVIEWING DATABASE CREATION & INSERT

Creating a Database & INSERT

Example: building a student and academic results management database with 4 tables, LOP, SINHVIEN, MONHOC, KETQUA, related as follows:

Creating the tables

CREATE TABLE Lop (
    MaLop VARCHAR(10) PRIMARY KEY,
    TenLop NVARCHAR(100)
);

CREATE TABLE MonHoc (
    MaMH VARCHAR(10) PRIMARY KEY,
    TenMH NVARCHAR(100),
    SoTinChi INT
);

CREATE TABLE SinhVien (
    MaSV VARCHAR(10) PRIMARY KEY,
    HoTen NVARCHAR(100),
    MaLop VARCHAR(10),

    FOREIGN KEY (MaLop)
        REFERENCES Lop(MaLop)
);

INSERT

INSERT INTO Lop
VALUES
('HTTT01', N'Hệ thống thông tin 01'),
('HTTT02', N'Hệ thống thông tin 02');

INSERT INTO SinhVien
VALUES
('SV001', N'Nguyễn Văn An', 'HTTT01'),
('SV002', N'Trần Văn Bình', 'HTTT01'),
('SV003', N'Lê Văn Cường', 'HTTT02');

05 · REVIEWING SELECT, WHERE & LIKE

SELECT, WHERE & LIKE

SELECT

Retrieve all data:

SELECT *
FROM SinhVien;

Select specific columns and give them an alias:

SELECT
    MaSV AS [Mã sinh viên],
    HoTen AS [Họ tên]
FROM SinhVien;

WHERE

Find students in class HTTT01:

SELECT *
FROM SinhVien
WHERE MaLop = 'HTTT01';

Multiple conditions:

SELECT *
FROM SinhVien
WHERE MaLop = 'HTTT01'
AND HoTen LIKE N'Nguyễn%';

LIKE - % and _

See the full details on both wildcard characters in Week 2 - Special Operators. Quick review:

-- Starts with "Nguyễn"
WHERE HoTen LIKE N'Nguyễn%'

-- Ends with "An"
WHERE HoTen LIKE N'%An'

-- Contains "Văn"
WHERE HoTen LIKE N'%Văn%'

-- Exactly 4 characters, starting "PB0" + 1 any character
WHERE MaPB LIKE 'PB0_'

06 · ORDER BY & AGGREGATE FUNCTIONS

ORDER BY & Aggregate Functions

ORDER BY

Sort students by name:

SELECT *
FROM SinhVien
ORDER BY HoTen ASC;   -- Change to DESC to sort descending

Aggregate functions

Functions to remember: COUNT(), SUM(), AVG(), MIN(), MAX(). Example - how many students are there:

SELECT COUNT(*) AS SoLuongSinhVien
FROM SinhVien;

07 · GROUP BY & HAVING

GROUP BY & HAVING

GROUP BY

Count the number of students in each class:

SELECT
    MaLop,
    COUNT(*) AS SoLuong
FROM SinhVien
GROUP BY MaLop;

HAVING

Only keep classes with 2 or more students:

SELECT
    MaLop,
    COUNT(*) AS SoLuong
FROM SinhVien
GROUP BY MaLop
HAVING COUNT(*) >= 2;

08 · REVIEWING JOIN

JOIN

This is the core topic of the review week. To display Student ID, Full Name, and Class Name:

SELECT
    SV.MaSV,
    SV.HoTen,
    L.TenLop
FROM SinhVien SV
JOIN Lop L
    ON SV.MaLop = L.MaLop;

INNER JOIN

Only returns records that have a match in both tables - if a class has no students yet, that class won't appear in the result.

LEFT JOIN

Shows every class, including ones with no students yet - this is a question type students commonly run into during practice:

SELECT
    L.MaLop,
    L.TenLop,
    SV.MaSV,
    SV.HoTen
FROM Lop L
LEFT JOIN SinhVien SV
    ON L.MaLop = SV.MaLop;

JOIN across multiple tables

Example: given LOP ➜ SINHVIEN ➜ KETQUA ➜ MONHOC. Query which student took which course and what score they got:

SELECT
    SV.MaSV,
    SV.HoTen,
    MH.TenMH,
    KQ.Diem
FROM SinhVien SV
JOIN KetQua KQ
    ON SV.MaSV = KQ.MaSV
JOIN MonHoc MH
    ON KQ.MaMH = MH.MaMH;

This is the kind of comprehensive query students need to master before finishing the practical portion of the course.

09 · REVIEWING SUBQUERY

Subquery

Find students whose grade is higher than the average grade:

SELECT *
FROM KetQua
WHERE Diem > (
    SELECT AVG(Diem)
    FROM KetQua
);

The thinking:

10 · REVIEWING UPDATE & DELETE

UPDATE & DELETE

UPDATE

Fix a student's name:

UPDATE SinhVien
SET HoTen = N'Nguyễn Văn Nam'
WHERE MaSV = 'SV001';

DELETE

DELETE FROM SinhVien
WHERE MaSV = 'SV001';

Same idea - always double-check the WHERE clause before running DELETE.

11 · PRACTICE REQUIREMENTS

Practice Requirements

This week's formal exercise uses the classic "Suppliers - Parts - Shipments" schema (Goods Management) - the schema, sample data, and all 36 questions are on the exercise page below.

12 · CHALLENGE PROBLEMS

Challenge Problems

Extra practice with the Training Management schema (LOP, SINHVIEN, MONHOC, KETQUA) used throughout the theory sections above - no longer the formal exercise, but still useful for building reflexes. For stronger students to attempt in addition - each has a hidden hint, click to reveal.

13 · COMMON MISTAKES

Common Mistakes

Mistake 1 - Wrong JOIN condition

-- Wrong
JOIN Lop L ON SV.MaSV = L.MaLop

-- Correct
JOIN Lop L ON SV.MaLop = L.MaLop

Mistake 2 - Using WHERE instead of HAVING

-- Wrong logic
WHERE COUNT(*) > 2

-- Correct
GROUP BY MaLop
HAVING COUNT(*) > 2

Mistake 3 - Forgetting GROUP BY

-- Wrong
SELECT MaLop, COUNT(*)
FROM SinhVien;

-- Must include GROUP BY
SELECT MaLop, COUNT(*)
FROM SinhVien
GROUP BY MaLop;

Mistake 4 - UPDATE/DELETE without a WHERE

Always check with SELECT before running UPDATE or DELETE:

SELECT *
FROM SinhVien
WHERE MaSV = 'SV001';

14 · REVIEW CHECKLIST

Review Checklist

Students should self-assess before the practical exam:

Database

• Understand Entity, Attribute, Relationship
• Can draw an ERD
• Can identify Primary Key, Foreign Key

Basic SQL

• CREATE DATABASE, CREATE TABLE
• INSERT, SELECT, WHERE, ORDER BY, LIKE

Advanced SQL

• GROUP BY, HAVING
• COUNT, SUM, AVG, MIN, MAX
• INNER JOIN, LEFT JOIN, Subquery

Data manipulation & synthesis

• Check with WHERE before running UPDATE, DELETE
• Read the problem, identify tables, relationships, and keys before writing SQL
• Can explain their own SQL statements