Notes

CREATE DATABASE QL_DIEMTHI
USE QL_DIEMTHI

CREATE TABLE SINHVIEN
(
MASV VARCHAR(10),
HOTEN NVARCHAR(30) NOT NULL,
LOPHC VARCHAR(10) NOT NULL,
PRIMARY KEY (MASV)
)

CREATE TABLE LOPHOCPHAN
(
MALOPHP VARCHAR(20),
TENHP NVARCHAR(30) NOT NULL,
SOTC INT CHECK(SOTC>30),
PRIMARY KEY(MALOPHP)
)

CREATE TABLE DIEMTHI
(
MASV VARCHAR(10),
MALOPHP VARCHAR(20) NOT NULL,
DIEMTHI NUMERIC CHECK (DIEMTHI>=0),
PRIMARY KEY(MASV,MALOPHP),
FOREIGN KEY (MASV) REFERENCES SINHVIEN(MASV)
ON UPDATE CASCADE,
FOREIGN KEY (MALOPHP) REFERENCES LOPHOCPHAN(MALOPHP)
ON UPDATE CASCADE
)
--IN DS SING VIÊN THUỘC LOPHC
SELECT *
FROM SINHVIEN
WHERE LOPHC = '48S3'

--THỐNG KÊ ĐIỂM THI TB
SELECT AVG(DIEMTHI) AS 'ĐIểmM THI TRUNG BÌNH'
FROM DIEMTHI
WHERE MALOPHP ='1352INFO1611'

--IN DS SV CÓ MÃ HP 1352INFO1611 CÓ ĐIỂM >=5
SELECT *
FROM SINHVIEN S INNER JOIN DIEMTHI D
ON S.MASV = D.MASV
WHERE D.MALOPHP ='1352INFO1611' AND D.DIEMTHI >=5