WEEK 1 · PRACTICE EXERCISES

Exercises & Answer Key

IT004BTTH1Fitness & Sports Center

The exercise statement is open to read freely. The answer key and detailed explanations are reserved for students currently enrolled in the IT004 practicum class and require an access code from the instructor.

EXERCISE

Fitness & Sports Center (TrungTam_TDTT)

The Week 1 exercise consists of 2 independent requirements, to be carried out in SQL Server Management Studio. Submit each requirement as a separate script file.

Assignment requirement 1 - Design and data entry

Given the "Fitness & Sports Center Management" database schema consisting of the following 6 relations:

PHONGTAP (MAPH, TENPHONG, DIACHI, SUCCHUA, TRANGTHAI)

Predicate: Stores gym room information: room code, room name, address, maximum capacity (number of people), and room status (active, under maintenance, closed…).

Attribute	Data type	Description
`MAPH`	`char(5)`	Gym room code - primary key
`TENPHONG`	`varchar(50)`	Gym room name
`DIACHI`	`varchar(100)`	Gym room address
`SUCCHUA`	`int`	Maximum capacity (number of people)
`TRANGTHAI`	`varchar(20)`	Room status (active, under maintenance, closed…)

HUANLUYENVIEN (MAHLV, HOTEN, CHUYENMON, SDT, EMAIL)

Predicate: Stores trainer information: trainer code, full name, specialty (Yoga, Gym,...), phone number, and contact email.

Attribute	Data type	Description
`MAHLV`	`char(5)`	Trainer code - primary key
`HOTEN`	`varchar(50)`	Full name
`CHUYENMON`	`varchar(50)`	Specialty (Yoga, Gym,...)
`SDT`	`varchar(15)`	Phone number
`EMAIL`	`varchar(50)`	Contact email

HOCVIEN (MAHV, HOTEN, NGSINH, SDT, DIACHI, GIOITINH, NGTG)

Predicate: Stores member information: member code, full name, date of birth, phone number, address, gender (Male, Female), and the date the person joined as a member.

Attribute	Data type	Description
`MAHV`	`char(5)`	Member code - primary key
`HOTEN`	`varchar(50)`	Full name
`NGSINH`	`smalldatetime`	Date of birth
`SDT`	`varchar(15)`	Phone number
`DIACHI`	`varchar(100)`	Member address
`GIOITINH`	`varchar(10)`	Member gender (Male, Female)
`NGTG`	`smalldatetime`	Date the person joined as a member

LOPTAP (MALOP, MAPH, MAHLV, TENLOP, NGAYBD, NGAYKT, TRANGTHAI)

Predicate: Stores class information: class code, room where it takes place, trainer in charge, class name, start date, end date, and class status (in progress, ended, paused…).

Attribute	Data type	Description
`MALOP`	`char(5)`	Class code - primary key
`MAPH`	`char(5)`	Gym room code - foreign key to PHONGTAP
`MAHLV`	`char(5)`	Trainer code - foreign key to HUANLUYENVIEN
`TENLOP`	`varchar(50)`	Class name
`NGAYBD`	`smalldatetime`	Class start date
`NGAYKT`	`smalldatetime`	Class end date
`TRANGTHAI`	`varchar(20)`	Class status (in progress, ended, paused…)

DANGKY (MAHV, MALOP, NGAYDK)

Predicate: Enrollment details: which member registered for which class, and on what date.

Attribute	Data type	Description
`MAHV`	`char(5)`	Member code - primary key, foreign key to HOCVIEN
`MALOP`	`char(5)`	Class code - primary key, foreign key to LOPTAP
`NGAYDK`	`smalldatetime`	Registration date

LICHTAP (MALOP, NGAYTAP, GIOBATDAU, GIOKETTHUC)

Predicate: Stores each class's schedule: the date of a session, the start time, and the end time.

Attribute	Data type	Description
`MALOP`	`char(5)`	Class code - primary key, foreign key to LOPTAP
`NGAYTAP`	`smalldatetime`	Session date - primary key
`GIOBATDAU`	`time`	Session start time - primary key
`GIOKETTHUC`	`time`	Session end time

Full exercise - Week 1 practice (BTTH1) PDF file · full sample data for all 6 relations

Download ↓

Submission requirement (Requirement 1): Save your work in the script file <MSSV>_<HoVaTen>_BTTH1_YC1.sql (MSSV is your student ID, HoVaTen is your full name).

Create the database TrungTam_TDTT, containing the 6 relations PHONGTAP, HUANLUYENVIEN, HOCVIEN, LOPTAP, DANGKY, LICHTAP.
Create the primary keys and foreign keys for the relations above.
Enter data for the 6 relations using the sample data provided in the exercise file.

Assignment requirement 2 - Definition and data manipulation

From this point on, the exercise switches to the shared Sales Management schema (KHACHHANG, NHANVIEN, SANPHAM, HOADON, CTHD).

See the full attributes, sample data, and database script for the Sales Management schema before starting. View schema ›

Submission requirement (Requirement 2): Save your work in the script file <MSSV>_<HoVaTen>_BTTH1_YC2.sql (MSSV is your student ID, HoVaTen is your full name).

Part I - Data Definition Language

Add an attribute GHICHU with data type varchar(20) to the relation SANPHAM.
Add an attribute LOAIKH with data type tinyint to the relation KHACHHANG.
Change the data type of the attribute GHICHU in the relation SANPHAM to varchar(100).
Drop the attribute GHICHU in the relation SANPHAM.
How can the attribute LOAIKH in the relation KHACHHANG be made to store the values "Vang lai", "Thuong xuyen", "Vip", …?
A product's unit of measure can only be one of ("cay", "hop", "cai", "quyen", "chuc").
A product's selling price must be 500 or higher.
An employee's phone number must start with the digit "0".
Each time a customer makes a purchase, they must buy at least 1 product.
The date a customer registers as a member must be later than that person's date of birth.

Part II - Data Manipulation Language

Create the relation SANPHAM1 containing all the data of the relation SANPHAM. Create the relation KHACHHANG1 containing all the data of the relation KHACHHANG.
Update the price with a 5% increase for products manufactured in "Thai Lan" (for the relation SANPHAM1).
Update the price with a 5% decrease for products manufactured in "Trung Quoc" priced at 10,000 or below (for the relation SANPHAM1).
Update the LOAIKH value to "Vip" for customers who registered as members before 1/1/2007 with total sales of 10,000,000 or more, or customers who registered as members on or after 1/1/2007 with total sales of 2,000,000 or more (for the relation KHACHHANG1).

ANSWER

View answer

Protected content

Enter the access code to view the Week 1 answer.

Access code provided by the instructor.

Verified - you now have access to the Week 1 answer.

Full answer for Requirement 1 (3 questions) and Requirement 2 (10 questions in Part I, 4 questions in Part II). Retype each query in SSMS so it actually sticks - the Copy button here is just for show.

QUESTION 1 (REQ. 1) · CREATE DATABASE

Exercise: Create the database TrungTam_TDTT, containing the 6 relations PHONGTAP, HUANLUYENVIEN, HOCVIEN, LOPTAP, DANGKY, LICHTAP.

CREATE DATABASE TrungTam_TDTT;
GO
USE TrungTam_TDTT;
GO

CREATE TABLE PHONGTAP
(
    MAPH CHAR(5) PRIMARY KEY,
    TENPHONG VARCHAR(50),
    DIACHI VARCHAR(100),
    SUCCHUA INT,
    TRANGTHAI VARCHAR(20)
);

CREATE TABLE HUANLUYENVIEN
(
    MAHLV CHAR(5) PRIMARY KEY,
    HOTEN VARCHAR(50),
    CHUYENMON VARCHAR(50),
    SDT VARCHAR(15),
    EMAIL VARCHAR(50)
);

CREATE TABLE HOCVIEN
(
    MAHV CHAR(5) PRIMARY KEY,
    HOTEN VARCHAR(50),
    NGSINH SMALLDATETIME,
    SDT VARCHAR(15),
    DIACHI VARCHAR(100),
    GIOITINH VARCHAR(10),
    NGTG SMALLDATETIME
);

CREATE TABLE LOPTAP
(
    MALOP CHAR(5) PRIMARY KEY,
    MAPH CHAR(5),
    MAHLV CHAR(5),
    TENLOP VARCHAR(50),
    NGAYBD SMALLDATETIME,
    NGAYKT SMALLDATETIME,
    TRANGTHAI VARCHAR(20)
);

CREATE TABLE DANGKY
(
    MAHV CHAR(5),
    MALOP CHAR(5),
    NGAYDK SMALLDATETIME,

    PRIMARY KEY (MAHV, MALOP)
);

CREATE TABLE LICHTAP
(
    MALOP CHAR(5),
    NGAYTAP SMALLDATETIME,
    GIOBATDAU TIME,
    GIOKETTHUC TIME,

    PRIMARY KEY (MALOP, NGAYTAP, GIOBATDAU)
);

WHAT IS IT?

CREATE DATABASE creates 1 empty database, after which you must USE it to switch the working context to that database before creating tables. The primary key is declared right when the table is created: a single-column key uses PRIMARY KEY right after the column, while a composite key (DANGKY, LICHTAP) uses a separate PRIMARY KEY (...) line at the end.

WHY?

The primary key is an internal constraint of the table itself, so declaring it right when the table is created makes the most sense. A foreign key is different - it references another table, so it has to wait until the referenced table exists, which is why it's saved for Question 2.

QUESTION 2 (REQ. 1) · FOREIGN KEY

Exercise: Create the primary keys and foreign keys for the relations above.

ALTER TABLE LOPTAP ADD CONSTRAINT FK_LOPTAP_PHONGTAP
    FOREIGN KEY (MAPH) REFERENCES PHONGTAP(MAPH);
ALTER TABLE LOPTAP ADD CONSTRAINT FK_LOPTAP_HLV
    FOREIGN KEY (MAHLV) REFERENCES HUANLUYENVIEN(MAHLV);
ALTER TABLE DANGKY ADD CONSTRAINT FK_DANGKY_HOCVIEN
    FOREIGN KEY (MAHV) REFERENCES HOCVIEN(MAHV);
ALTER TABLE DANGKY ADD CONSTRAINT FK_DANGKY_LOPTAP
    FOREIGN KEY (MALOP) REFERENCES LOPTAP(MALOP);
ALTER TABLE LICHTAP ADD CONSTRAINT FK_LICHTAP_LOPTAP
    FOREIGN KEY (MALOP) REFERENCES LOPTAP(MALOP);

WHAT IS IT?

The primary keys were already created in Question 1, so Question 2 only needs to add the foreign keys using ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY (...) REFERENCES ...(...) for each linked pair of tables.

WHY?

LOPTAP must already exist (with the primary keys MAPH in PHONGTAP and MAHLV in HUANLUYENVIEN) before a foreign key pointing to it can be created - this is exactly why a foreign key always has to wait until the related tables are already in place.

QUESTION 3 (REQ. 1) · INSERT

Exercise: Enter data for the 6 relations using the sample data provided in the exercise file.

INSERT INTO PHONGTAP (MAPH, TENPHONG, DIACHI, SUCCHUA, TRANGTHAI)
VALUES
    ('PH001', N'Yoga Linh Dam', N'Ha Noi', 25, N'Hoat dong'),
    ('PH002', N'Gym Nguyen Van Cu', N'TP. HCM', 60, N'Hoat dong');

INSERT INTO HUANLUYENVIEN (MAHLV, HOTEN, CHUYENMON, SDT, EMAIL)
VALUES
    ('HL001', N'Nguyen Van An', N'Yoga', '0901234567', 'an.nguyen@tdtt.vn'),
    ('HL002', N'Tran Thi Binh', N'Gym', '0912345678', 'binh.tran@tdtt.vn');

INSERT INTO LOPTAP (MALOP, MAPH, MAHLV, TENLOP, NGAYBD, NGAYKT, TRANGTHAI)
VALUES
    ('LOP001', 'PH001', 'HL001', N'Yoga co ban', '2024-01-05', '2024-03-05', N'Dang hoat dong'),
    ('LOP002', 'PH002', 'HL002', N'Gym tang co', '2024-01-10', '2024-04-10', N'Dang hoat dong');

INSERT INTO HOCVIEN (MAHV, HOTEN, NGSINH, SDT, DIACHI, GIOITINH, NGTG)
VALUES
    ('HV001', N'Le Van Cuong', '2000-05-20', '0923456789', N'12 Tran Phu, Ha Noi', N'Nam', '2024-01-02'),
    ('HV002', N'Pham Thi Dung', '1999-08-15', '0934567890', N'34 Le Loi, TP. HCM', N'Nu', '2024-01-03');

INSERT INTO DANGKY (MAHV, MALOP, NGAYDK)
VALUES
    ('HV001', 'LOP001', '2024-01-05'),
    ('HV002', 'LOP002', '2024-01-10');

INSERT INTO LICHTAP (MALOP, NGAYTAP, GIOBATDAU, GIOKETTHUC)
VALUES
    ('LOP001', '2024-01-08', '18:00:00', '19:00:00'),
    ('LOP002', '2024-01-12', '19:00:00', '20:30:00');

WHAT IS IT?

INSERT INTO ... VALUES (...) adds one row of data at a time into a table; multiple rows can be combined into a single statement by listing several value groups separated by commas.

WHY?

The insert order must follow the foreign-key direction: PHONGTAP and HUANLUYENVIEN before LOPTAP (since LOPTAP references both), HOCVIEN and LOPTAP before DANGKY, and LOPTAP before LICHTAP - because the foreign keys created in Question 2 will block an INSERT if the parent row doesn't exist yet. The data here is only illustrative of the correct syntax - for the actual assignment, enter the full sample data from the attached PDF in the Exercise section.

QUESTION 1 (REQ. 2 - PART I) · ADD COLUMN

Exercise: Add an attribute GHICHU with data type varchar(20) to the relation SANPHAM.

ALTER TABLE SANPHAM ADD GHICHU VARCHAR(20);

WHAT IS IT?

ALTER TABLE ... ADD adds a new column to an existing table; existing rows receive NULL for this new column.

WHY?

SANPHAM already has data, so it can't be re-created with CREATE TABLE - ALTER TABLE must be used to add the column without losing the existing data.

QUESTION 2 (REQ. 2 - PART I) · ADD COLUMN

Exercise: Add an attribute LOAIKH with data type tinyint to the relation KHACHHANG.

ALTER TABLE KHACHHANG ADD LOAIKH TINYINT;

WHAT IS IT?

TINYINT stores a small integer (0-255), which fits when LOAIKH only needs a handful of fixed codes.

WHY?

The exercise only asks to add the column here, not to constrain its values yet - restricting LOAIKH to a few specific labels is handled in Question 5.

QUESTION 3 (REQ. 2 - PART I) · ALTER COLUMN

Exercise: Change the data type of the attribute GHICHU in the relation SANPHAM to varchar(100).

ALTER TABLE SANPHAM ALTER COLUMN GHICHU VARCHAR(100);

WHAT IS IT?

ALTER COLUMN changes the data type (or length) of a column that already exists.

WHY?

The varchar(20) from Question 1 might not leave enough room for a longer note, so the length needs to be extended instead of dropping the column and re-creating it.

QUESTION 4 (REQ. 2 - PART I) · DROP COLUMN

Exercise: Drop the attribute GHICHU in the relation SANPHAM.

ALTER TABLE SANPHAM DROP COLUMN GHICHU;

WHAT IS IT?

DROP COLUMN permanently removes a column along with all the data stored in it.

WHY?

If GHICHU is no longer needed, it should be dropped entirely to avoid a leftover column cluttering up SELECT * later on.

QUESTION 5 (REQ. 2 - PART I) · CHECK ... IN

Exercise: How can the attribute LOAIKH in the relation KHACHHANG be made to store the values "Vang lai", "Thuong xuyen", "Vip", …?

ALTER TABLE KHACHHANG ALTER COLUMN LOAIKH VARCHAR(20);

WHAT IS IT?

LOAIKH needs to store text strings ("Vang lai", "Thuong xuyen", "Vip"...), so it's enough to change the data type from TINYINT (in Question 2) to VARCHAR(20) so the column has room for a string.

WHY?

The exercise ends with an ellipsis "…" after "Vang lai", "Thuong xuyen", "Vip" - meaning the list of values is not closed, and other customer types could exist beyond these 3. So you should not add a CHECK ... IN (...) hard-limiting to exactly these 3 values like in Question 6 - doing so would accidentally block other valid customer types not yet listed. The exercise only asks for the column to be "able to store" these string values, so changing the data type is enough.

QUESTION 6 (REQ. 2 - PART I) · CHECK ... IN

Exercise: A product's unit of measure can only be one of ("cay", "hop", "cai", "quyen", "chuc").

ALTER TABLE SANPHAM ADD CONSTRAINT CK_SANPHAM_DVT
    CHECK (DVT IN (N'cay', N'hop', N'cai', N'quyen', N'chuc'));

Alternative approach

ALTER TABLE SANPHAM ADD CONSTRAINT CK_SANPHAM_DVT
    CHECK (DVT = N'cay' OR DVT = N'hop' OR DVT = N'cai'
        OR DVT = N'quyen' OR DVT = N'chuc');

A chain of = comparisons joined by OR is equivalent to IN (...), but gets verbose once the list has many values - IN remains the recommended way to write it.

WHAT IS IT?

The same CHECK ... IN technique considered for Question 5, applied to the DVT (unit of measure) column of SANPHAM.

WHY?

Unlike Question 5, this exercise lists exactly 5 units of measure with no ellipsis - this is a closed, fixed list, not free-form input. So a hard CHECK ... IN constraint is entirely appropriate here, helping prevent typos or an unintended unit of measure.

QUESTION 7 (REQ. 2 - PART I) · CHECK

Exercise: A product's selling price must be 500 or higher.

ALTER TABLE SANPHAM ADD CONSTRAINT CK_SANPHAM_GIA CHECK (GIA >= 500);

WHAT IS IT?

CHECK with a comparison expression (unlike IN) is used when the constraint is a range of values, not an enumerated list.

WHY?

"500 or higher" is a >= condition - all valid values can't be listed out like in Question 6, so a direct comparison must be used instead.

QUESTION 8 (REQ. 2 - PART I) · CHECK ... LIKE

Exercise: An employee's phone number must start with the digit "0".

ALTER TABLE NHANVIEN ADD CONSTRAINT CK_NHANVIEN_SODT CHECK (SODT LIKE '0%');

WHAT IS IT?

LIKE '0%' matches any string starting with the character "0"; the % stands in for the rest (any length).

WHY?

A "starts with" constraint is about string format, not a specific value or numeric range - CHECK ... LIKE is the right tool for this case.

QUESTION 9 (REQ. 2 - PART I) · CHECK

Exercise: Each time a customer makes a purchase, they must buy at least 1 product.

ALTER TABLE CTHD ADD CONSTRAINT CK_CTHD_SL CHECK (SL >= 1);

WHAT IS IT?

The constraint is placed on the SL (quantity) column of CTHD - each invoice line item must have a purchased quantity of 1 or more.

WHY?

"Buy at least 1 product" is really a quantity constraint on each CTHD row, not on HOADON - because HOADON doesn't directly store product information.

QUESTION 10 (REQ. 2 - PART I) · CHECK

Exercise: The date a customer registers as a member must be later than that person's date of birth.

ALTER TABLE KHACHHANG ADD CONSTRAINT CK_KHACHHANG_NGDK CHECK (NGDK > NGSINH);

WHAT IS IT?

CHECK can compare 2 columns within the same table against each other, not just against a fixed value.

WHY?

Logically, a person can't register as a member before they were born - this constraint keeps the data consistent with real-world meaning.

QUESTION 1 (REQ. 2 - PART II) · SELECT INTO

Exercise: Create the relation SANPHAM1 containing all the data of the relation SANPHAM. Create the relation KHACHHANG1 containing all the data of the relation KHACHHANG.

SELECT * INTO SANPHAM1 FROM SANPHAM;
SELECT * INTO KHACHHANG1 FROM KHACHHANG;

WHAT IS IT?

SELECT * INTO <new table> FROM <source table> both creates a new table and copies all its data at once, without a separate CREATE TABLE.

WHY?

The following questions are all UPDATE statements that could corrupt the original data - creating a copy first lets you experiment on SANPHAM1/KHACHHANG1 without affecting the real SANPHAM/KHACHHANG.

QUESTION 2 (REQ. 2 - PART II) · UPDATE

Exercise: Update the price with a 5% increase for products manufactured in "Thai Lan" (for the relation SANPHAM1).

UPDATE SANPHAM1
SET GIA = GIA * 1.05
WHERE NUOCSX = N'Thai Lan';

WHAT IS IT?

GIA * 1.05 means keeping 100% of the old price and adding 5% more, which is equivalent to a 5% increase.

WHY?

GIA = GIA + 5 is wrong because 5% isn't a fixed number - it depends on each product's own price, so the price must be multiplied by 1.05 for the increase to be correct at every price level.

QUESTION 3 (REQ. 2 - PART II) · UPDATE

Exercise: Update the price with a 5% decrease for products manufactured in "Trung Quoc" priced at 10,000 or below (for the relation SANPHAM1).

UPDATE SANPHAM1
SET GIA = GIA * 0.95
WHERE NUOCSX = N'Trung Quoc' AND GIA <= 10000;

WHAT IS IT?

GIA * 0.95 is equivalent to a 5% decrease (keeping 95% of the old price); the WHERE clause combines 2 conditions with AND.

WHY?

Both conditions (country of manufacture and price level) must be filtered together - leaving out either one would incorrectly discount products that shouldn't be affected.

QUESTION 4 (REQ. 2 - PART II) · UPDATE

Exercise: Update the LOAIKH value to "Vip" for customers who registered as members before 1/1/2007 with total sales of 10,000,000 or more, or customers who registered as members on or after 1/1/2007 with total sales of 2,000,000 or more (for the relation KHACHHANG1).

UPDATE KHACHHANG1
SET LOAIKH = N'Vip'
WHERE (NGDK < '1/1/2007' AND DOANHSO >= 10000000)
   OR (NGDK >= '1/1/2007' AND DOANHSO >= 2000000);

WHAT IS IT?

The condition consists of 2 independent groups joined by OR, each group made up of 2 conditions joined by AND - parentheses are used to group them clearly and avoid mixing up AND/OR precedence.

WHY?

The sales threshold for Vip status differs depending on the registration date (10 million if registered before 1/1/2007, only 2 million if registered on or after 1/1/2007) - the 2 cases must be split with OR instead of combined into a single condition.