Nguyễn Văn Trãi

Lớp:
Mã sv: 24211400416
________________________________________________________________________
Bài 1:
1. CSDL QUANLIDIEM
CREATE TABLE SINHVIEN
(
TEN VARCHAR(20) CONSTRAINT NN_SINHVIEN NOT NULL,
MASV TINYINT CONSTRAINT PK_SINHVIEN PRIMARY
KEY,
NAM SMALLINT CONSTRAINT CHK_SINHVIEN,
CHECK NAM > ‘0’ AND NAM < ‘2021’,
KHOA CHAR(4) CONSTRAINT NN_SINHVIEN NOT NULL
)
CREATE TABLE MONHOC
(
TENMH VARCHAR(20) CONSTRAINT NN_MONHOC
NOT NULL,
MAMH CHAR(8) CONSTRAINT PK_MONHOC PRIMARY
KEY,
TINCHI TINYINT CONSTRAINT CHK_MONHOC
CHECK TINCHI <= ‘4’,
KHOA CHAR(4) CONSTRAINT NN_MONHOC NOTNULL
)
CREATE TABLE DIEUKIEN
(
 MAMH CHAR(8) CONSTRAINT FK1_DIEUKIEN FOREIGN
KEY
REFERENCES MONHOC(MAMH)
MAMH_TRUOC CHAR(8) CONSTRAINT PK_DIEUKIEN
PRIMARY KEY
)
CREATE TABLE KHOAHOC
(
MAKH INTEGER CONSTRAINT PK_KHOAHOC PRIMARY
KEY
MAMH CHAR(8) CONSTRAINT FK2_KHOAHOC FOREIGN
KEY
REFERENCES MONHOC(MAMH),
HOCKY TINYINT CONSTRAINT CHK_KHOAHOC
CHECK HOCKY > ‘0’ AND HOCKY < ‘3’,
NAM SMALLINT CONSTRAINT CHK_SINHVIEN,
CHECK NAM > ‘0’ AND NAM < ‘2021’,
GIAOVIEN VARCHAR(20) CONSTRAINT NULL_KHOAHOC
NULL,
)
CREATE TABLE KETQUA
(
MASV TINYINT CONSTRAINT FK3_KETQUA FOREIGN
KEY
REFERENCES SINHVIEN(MASV),
MAKH INTEGER CONSTRAINT FK4_KETQUA FOREIGN
KEY
REFERENCES KHOAHOC(MAKH),
DIEM TINYINT CONSTRAINT CHK_KETQUA
 CHECK DIEM <= ‘10’.
)

2. CSDL QUANLINHANVIEN
CREATE TABLE NHANVIEN
(
HOLOT VARCHAR(10) CONSTRAINT NN_NHANVIEN
NOTNULL,
TENNV VARCHAR(20) CONSTRAINT NN_NHANVIEN
NOT NULL,
MANV CHAR(6) CONSTRAINT PK_NHANVIEN
PRIMARY KEY,
NGAYSINH DATETIME,
DIACHI VARCHAR(50),
PHAI CHAR(3) CONSTRAINT CHK_NHANVIEN
CHECK PHAI IN (‘NAM’, ‘NU’),
LUONG INT CONSTRAINT DF_NHANVIEN DEFAULT,
MA_NQL CHAR(9),
PHG INT
)
CREATE TABLE PHONGBAN
(
TENPHG VARCHAR(50) CONSTRAINT NN_PHONGBAN NOT
NULL,
MAPHG CHAR(6) CONSTRAINT PK_PHONGBAN PRIMARY
KEY,
TRPHOG VARCHAR(50),
NGAY_NHANCHUC DATE TIME
)
CREATE TABLE DIADIEM_PHG
(
MAPHG CHAR(6) CONSTRAINT PK_DIADIENPHG PRIMARY
KEY,
DIADIEM VARCHAR(50) CONSTRAINT NN_DIADIEM_PHG
NOT NULL
)
CREATE TABLE DEAN
(
TENDEAN VARCHAR(50) CONSTRAINT NN_DEAN NOT NULL,
MADA CHAR(6) CONSTRAINT PK_DEAN PRIMARY
KEY,
DIADIEM_DA VARCHAR(50),
PHONG TINYINT,
)
CREATE TABLE PHANCONG
(
MANV CHAR(6) CONSTRAINT FK1_PHANCONG FOREIGN
KEY
REFERENCES NHANVIEN(MANV),
SODA CHAR(6) CONSTRAINT FK2_PHANCONG FOREIGN
KEY
RERERENCES DEAN(MADA),

THOIGIAN DECIMAL(3,1)
)
CREATE TABLE THANNHAN
(
MA_NVIEN CHAR(6) CONSTRAINT FK3_THANNHAN
FOREIGN KEY
REFERENCES NHANVIEN(MANV),
TENTN VARCHAR(50) CONSTRAINT NN_THANNHAN
NOT NULL,
PHAI VARCHAR(3) CONSTRAINT CHK_THANNHAN
CHECK PHAI IN (‘NAM’, ‘NU,),
NGSINH DATETIME,
QUANHE VARCHAR(3)
)
3. CDSL QUANLYHOCVIEN
CREATE TABLE KHOAHOC
(
MAKH CHAR(10) CONSTRAINT PK_KHOAHOC PRIMARY
KEY,
TENKH VARCHAR(50) CONSTRAINT NN_KHOAHOC NOT

NULL,
BD DATETIME,
KT DATETIME,
)
CREATE TABLE HOCVIEN
(
MAHV CHAR(10) CONSTRAINT PK_HOCVIEN PRIMARY
KEY,
HO VARCHAR(6) CONSTRAINT NN_HOCVIEN NOT NULL,
TEN VARCHAR(20) CONSTRAINT NN_HOCVIEN NOTNULL,
 NTNS DATETIME,
NN VARCHAR(20)
)
CREATE TABLE GIAOVIEN
MAGV CHAR(10) CONSTRAINT PK_GIAOVIEN PRIMARY
KEY,
HOTEN VARCHAR(50) CONSTRAINT NN_GIAOVIEN NOT
NULL,
NTNS DATETIME,
DIACHI VARCHAR(50)
)
CREATE TABLE LOPHOC
(
MALOP CHAR(8) CONSTRAINT PK_LOPHOC PRIMARY
KEY,
TENLOP VARCHAR(50) CONSTRAINT NN_LOPHOC
NOTNULL,
MAKH CHAR(10) CONSTRAINT FK1_LOPHOC FOREIGN
KEY
REFERENCES KHOAHOC(MAKH),
MAGV CHAR(10) CONSTRAINT FK2_LOPHOC FOREIGN
KEY
REFERENCES GIAOVIEN(MAGV),
SISODK TINYINT CONSTRAINT CHK_LOPHOC
CHECK SISODK <= ‘100’,
LTRG …..(chưa làm)
PHHOC INT
)
CREATE TABLE BIENLAI
(
MAKH CHAR(10) CONSTRAINT FK3_BIENLAI FOREIGN
KEY
REFERENCES KHOAHOC(MAKH),
TENKH VARCHAR(50) CONSTRAINT NN_BIENLAI NOT
NULL,
MALH CHAR(8) CONSTRAINT FK4_BIENLAI FOREIGN
KEY,
REFERENCES LOPHOC(MALOP),
MAHV CHAR(10) CONSTRAINT FK5_BIENLAI FOREIGN
KEY,
REFERENCES HOCVIEN(MAHV),
SOBL CHAR(10) CONSTRAINT PK_BIENLAI PRIMARY
KEY,
DIEM TINYINT CONSTRAINT CHK_BIENLAI
CHECK DIEM <= ‘10’,
KETQUA VARCHAR(4) CONSTRAINT NULL,
XEPLOAI VARCHAR(7) CONSTRAINT NULL,
TIENNOP INT
)

________________________________________________________________________

Bài 2:
1. CSDL QUANLIDIEM
a. In ra tên sinh viên
SELECT TEN
FROM SINHVIEN
b. In ra tên các môn học và số tín chỉ.
SELECT TENMH AND TINCHI
FROM MONHOC
c. Cho biết kết quả học tập của sinh viên có mã số là 8
SELECT *
FROM KETQUA
WHERE MASV = 8
d. Cho biết các mã số môn học phải học ngay trước môn có mã sô COSC3320
SELECT MHTRUOC
FROM DKIEN
WHERE MAMH = ‘COSC3320’
e. Cho biết các mã số môn học phải học ngay sau môn có mã số COSC3320
SELECT MAMH
FROM DIEUKIEN
WHERE MAMH_TRUOC = ‘SOSC3320’
f. Cho biết tên các sinh viên thuộc về khoa phụ trách môn “Toán rời rạc”
SELECT *
FROM MHOC
WHERE TENMH = “Toan Roi Rac”
g. Sửa giá trj cột Nam của sinh viên Sơn Thành 2
UPDATE SINHVIEN
SET NAM = 2
WHERE TEN = ‘SON’
2. CSDL QUANLINHANVIEN
a. Cho biết tên và địa chỉ của các nhân viên sống ở TPHCM theo thứ tự tăng dần tên
SELECT HOLOT, TENNV, DCHI
FROM NHANVIIEN
WHERE DIACHI = ‘TPHCM’
ORDER BY TENNV ASC
b. Cho biết lương nhân viên trên 40 tuổi theo thứ tự tăng dần lương
SELECT *
FROM NHANVIEN
WHERE YEAR(CURRENT) – YEAR(NGAYSINH) > 40
ORDER BY LUONG ASC
c. Liệt kê danh sách những nhân viên (HONV, TENNV) có cùng tên (TENNV) với
người thân
SELECT HOLOT, TENNV
FROM NHANVIEN, THANNHAN
WHERE TENNV = TENTN
d. Với mọi đề án ở “Hà Nội”, liệt kê các mã số đề án (Mada), mã số phòng ban chủ trì đề
án (PHONG), họ tên trưởng phòng (TENNV, HO_NV) cũng như địa chỉ (DCHI) và
ngày sinh (NG_SINH) của người ấy
SELECT [Link], [Link], [Link],
[Link], [Link], [Link]
FROM [Link], [Link]
WHERE PHONG = MAPHG AND
MAPHG = MANV AND
DCHI = ‘HANOI’
e. Cho biết tên những nhân viên phòng số 5 có tham gia đề án “Sản phẩm X” với số giờ
làm việc trên 10 giờ/tuần
SELECT HOLOT, TENNV
FROM DEAN, DIADIEM_PHG, PHONGBAN, NHANVIEN, PHANCONG
WHERE MANV = MA_NVIEN, SODA = MADA, PHONG = 5, TENDA =
‘SAN PHAM X’. THOIGIAN >10
f. Cho biết danh sách những nhân viên (HONV, TENNV) không có thân nhân nào
SELECT HONV, TENNV
FROM NHANVIEN
WHERE MANV NOT IN (
SELECT MA_NVIEN
FROM THANNHAN)
g. Cho biết danh sách những trưởng phòng có tối thiểu một thân nhân
SELECT TENPHONG, HOLOT, TEN, COUNT(MA_NVIEN)
FROM PHONGBAN
WHERE TRPHG > ANY (
SELECT TENTN
FROM THANNHAN
WHERE MA_NVIEN >= 1
GROUP BY TENPHG, HOLOT, TENNV
h. Cho biết danh sách những nhân viên (HONV, TENNV) không làm việc cho bất kỳ đề
án nào
SELECT HOLOT, TENNV
FROM NHANVIEN
WHERE NOT ESISTS(
SELECT *
FROM DEAN
WHERE MANV = MADA)
i. Cho biết danh sách những nhân viên (HONV, TENNV) có trên 2 thân nhân
SELECT TENPHONG, HOLOT, TEN, COUNT(MA_NVIEN)
FROM PHONGBAN, NHANVIEN, THANNHAN
WHERE TRPHG > ANY (
SELECT TENTN
FROM THANNHAN
WHERE MA_NVIEN > 2)
GROUP BY TENPHG, HOLOT, TENNV
j. Tìm tên và địa chỉ của tất cả các nhân viên của phòng “Nghiên cứu”
SELECT *
 FROM PHONGBAN
WHERE TENPHG = ‘PHONG NGHIEN CUU”
k. Cho biết danh sách những nhân viên (HONV, TENNV) được “Nguyễn Thanh Tùng”
phụ trách trực tiếp

SELECT HOLOT, TENNV