Đề thi giữa kỳ Thực hành cơ sở dữ liệu | Môn Cơ sở dữ liệu - Trường Đại học Bách khoa Hà Nội

lOMoARcPSD| 45315597 Kiểm tra quá trình STT
Học phần: IT3290 – Thực hành CSDL
Ngày: 19/6/2023 Thời gian : 60 min Note:
Mã sinh viên: 20210463................................................
Họ và tên sinh viên: Trần An Khang..............................
Với mỗi câu truy vấn sau, copy câu truy vấn (dạng text) và màn hình chạy gồm cả kết quả
và thanh trạng thái (print screen) Câu 1: (Index)
a. Viết câu truy vấn SQL trả về danh sách các bản ghi trong Inventory có “quan_in_stock” lớn hơn 18
select * from inventory where quan_in_stock>18
b. Viết câu lệnh tạo hash index trên trường “inventory.quan_in_stock” và chỉ ra cây toán
tử sinh ra bởi HQTCSDL cho câu (a)
CREATE INDEX hash_index_quan_in_stock ON inventory USING hash (quan_in_stock);
EXPLAIN SELECT * FROM inventory WHERE quan_in_stock > 18; lOMoARcPSD| 45315597
c. Viết câu lệnh tạo btree index trên trương “inventory.quan_in_stock” và chỉ ra cây toán
tử sinh ra bởi HQTCSDL cho câu (a)
CREATE INDEX btree_index_quan_in_stock ON inventory USING btree (quan_in_stock);
EXPLAIN SELECT * FROM inventory WHERE quan_in_stock > 18; lOMoARcPSD| 45315597
d. Có sự khác nhau giữa câu toán tử sinh ra trong câu (b) và (c) không ? Giải thích Câu b
sử dụng hash table còn câu c sử dụng binary tree
e. Xóa bỏ các index trong câu (b) and (c). Viết câu truy vấn SQL trả về danh sách các bản
ghi trong Inventory có “quan_in_stock” lớn hơn 400, thực hiện lại các yêu cầu b, c, d cho câu truy vấn này.
DROP INDEX btree_index_quan_in_stock ; DROP
INDEX hash_index_quan_in_stock ;
select * from inventory where quan_in_stock>400;
CREATE INDEX hash_index_quan_in_stock ON inventory USING hash (quan_in_stock);
EXPLAIN SELECT * FROM inventory WHERE quan_in_stock > 400;
CREATE INDEX btree_index_quan_in_stock ON inventory USING btree (quan_in_stock);
EXPLAIN SELECT * FROM inventory WHERE quan_in_stock > 400; lOMoARcPSD| 45315597
f. Có sự khác nhau nào giữa kết quả câu d và e không ? Giải thích
Điều kiện lọc > 18 bao gồm điều kiện lọc >400 Câu 2: (Query)
a. Viết câu truy vấn SQL theo các cách khác nhau (join, nested queries, …. ) trả về danh
sách các sản phẩm khác nhau có “quantity_in_stock” (trong quan hệ Inventory) nhỏ hơn 100 JOIN :
SELECT p.prod_id, MAX(i.quan_in_stock) as quantity_in_stock FROM products p
JOIN inventory i ON i.prod_id = p.prod_id GROUP BY p.prod_id
HAVING MAX(i.quan_in_stock) <100 ORDER BY p.prod_id lOMoARcPSD| 45315597 NESTED QUERIES :
SELECT p.prod_id, (SELECT MAX(i.quan_in_stock) FROM inventory i
WHERE i.prod_id = p.prod_id) as quantity_in_stock FROM products p
WHERE (SELECT MAX(i.quan_in_stock)
FROM inventory i WHERE i.prod_id = p.prod_id) < 100; lOMoARcPSD| 45315597
b. So sánh cây toán tử sinh ra bởi HQTCSDL với mỗi câu truy vấn được viết trong (a). Giải thích lý do
Join : dùng hash kết hợp , tính max của quan_in_stock trong inventory sau đó lọc đk
<100 . Cuối cùng sắp xếp kết quả theo prod_id
Nested queries : dùng subquery tính max quan_in_stock sau đó lọc theo điều kiện < 100
c. Tạo các index hợp lý cho mỗi câu truy vấn được viết ở trên, kiểm tra xem index có được
sử dụng không. Giải thích Câu 3 (Trigger)
a. Thêm thuộc tính “orders_value” trong quan hệ Customers. “orders_value” lưu tổng giá
trị các đơn hàng của mỗi khách hàng.
b. Viết trigger đảm bảo yêu cầu nghiệp vụ: thuộc tính “orders_value” phải được cập nhật
theo các thay đổi trong đơn hàng của khách hàng lOMoARcPSD| 45315597 Câu 4: (function)
Viết hàm trả về “hạng” (range) của một khách hàng có mã số khách hàng customerid được
cung cấp như tham số đầu vào. Hạng của khách hàng được xác định như sau: i.
“titan” nếu tổng giá trị đơn hàng lớn hơn hoặc bằng 50.000.000 hoặc giá trị của
một đơn hàng lớn hơn 20.000.000 ii.
“gold” nếu tổng giá trị đơn hàng lớn hơn hoặc bằng 30.000.000 hoặc giá trị của
một đơn hàng lớn hơn 15.000.000 iii.
“silver” cho các trường hợp khác