Scribd
Upload
5 views9 pages

Database Schema for Educational System

The document outlines the SQL schema for a database related to an educational institution, including tables for departments, subjects, classes, students, teachers, teaching assignments, and exam results. It includes the creation of tables, their relationships through foreign keys, and sample data insertion for teachers, subjects, students, and classes. The structure supports managing academic information such as course prerequisites and student grades.

Uploaded by

hoangn.24ns
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
5 views9 pages

Database Schema for Educational System

The document outlines the SQL schema for a database related to an educational institution, including tables for departments, subjects, classes, students, teachers, teaching assignments, and exam results. It includes the creation of tables, their relationships through foreign keys, and sample data insertion for teachers, subjects, students, and classes. The structure supports managing academic information such as course prerequisites and student grades.

Uploaded by

hoangn.24ns
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

--NGUYEN HOANG 24NS019

DROP TABLE IF EXISTS KHOA;


DROP TABLE IF EXISTS MONHOC;
DROP TABLE IF EXISTS DIEUKIEN;
DROP TABLE IF EXISTS GIAOVIEN;
DROP TABLE IF EXISTS LOP;
DROP TABLE IF EXISTS HOCVIEN;
DROP TABLE IF EXISTS GIANGDAY;
DROP TABLE IF EXISTS KETQUATHI;

CREATE TABLE KHOA(


MAKHOA VARCHAR(4) PRIMARY KEY ,
TENKHOA VARCHAR(40),
NGTLAP DATE,
TRGKHOA CHAR(4)
);
CREATE TABLE MONHOC(
MAMH VARCHAR(10) PRIMARY KEY ,
TENMH VARCHAR(40),
TCLT TINYINT,
TCTH TINYINT,
MAKHOA VARCHAR(4),
FOREIGN KEY (MAKHOA) REFERENCES KHOA(MAKHOA)

);
CREATE TABLE LOP(
MALOP CHAR(4) PRIMARY KEY ,
TENLOP VARCHAR(40),
TRGLOP CHAR(5),
SISO TINYINT,
MAGVCN CHAR(4),
FOREIGN KEY (TRGLOP) REFERENCES HOCVIEN(MAHV),
FOREIGN KEY (MAGVCN) REFERENCES GIAOVIEN(MAGV)

);
CREATE TABLE HOCVIEN(
MAHV CHAR(5) PRIMARY KEY ,
HO VARCHAR(40),
TEN VARCHAR(10),
NGSINH SMALLDATETIME,
GIOITINH VARCHAR(3),
NOISINH VARCHAR(40),
MALOP CHAR(3),
FOREIGN KEY (MALOP) REFERENCES LOP(MALOP)

);
CREATE TABLE GIANGDAY(
MALOP CHAR(3),
MAMH VARCHAR(4),
MAGV CHAR(4),
HOCKY TINYINT,
NAM SMALLINT,
TUNGAY SMALLDATETIME,
DENNGAY SMALLDATETIME,
PRIMARY KEY (MALOP, MAMH, MAGV),
FOREIGN KEY (MALOP) REFERENCES LOP(MALOP),
FOREIGN KEY (MAMH) REFERENCES MONHOC(MAMH),
FOREIGN KEY (MAGV) REFERENCES GIAOVIEN(MAGV)
);
CREATE TABLE KETQUATHI(
MAHV CHAR(5) ,
MAMH VARCHAR(10) ,
LANTHI TINYINT ,
NGTHI SMALLDATETIME,
DIEM NUMERIC(4,2),
KQUA VARCHAR(10),
PRIMARY KEY (MAHV, MAMH, LANTHI),
FOREIGN KEY (MAHV) REFERENCES HOCVIEN(MAHV),
FOREIGN KEY (MAMH) REFERENCES MONHOC(MAMH)

);
CREATE TABLE GIAOVIEN(
MAGV CHAR(4) PRIMARY KEY ,
HOTEN VARCHAR(40),
HOCVI VARCHAR(10),
HOCHAM VARCHAR(10),
GIOITINH VARCHAR(3),
NGSINH SMALLDATETIME,
NGVL SMALLDATETIME,
HESO NUMERIC(4,2),
MUCLUONG MONEY,
MAKHOA VARCHAR(4),
FOREIGN KEY (MAKHOA) REFERENCES KHOA(MAKHOA)
);
CREATE TABLE DIEUKIEN(
MAMH VARCHAR(10) ,
MANH_TRUOC VARCHAR(10) ,
PRIMARY KEY ( MAMH, MANH_TRUOC),
FOREIGN KEY (MAMH) REFERENCES MONHOC(MAMH),
FOREIGN KEY (MANH_TRUOC) REFERENCES MONHOC(MAMH)

);
INSERT INTO GIAOVIEN (MAGV, HOTEN, HOCVI,HOCHAM, GIOITINH, NGSINH, NGVL, HESO,
MUCLUONG, MAKHOA)
VALUES
('GV01', 'Ho Thanh Son', 'PTS', 'GS', 'Nam', '1950-05-02', '2004-01-11', 5.00,
2250000, 'KHMT'),
('GV02', 'Tran Tam Thanh', 'TS', 'PGS', 'Nam', '1965-12-17', '2004-04-20', 4.50,
2025000, 'HTTT'),
('GV03', 'Do Nghiem Phung', 'TS', 'GS', 'Nu', '1950-01-08', '2004-09-23', 4.00,
1800000, 'CNPM'),
('GV04', 'Tran Nam Son', 'TS' , 'PGS', 'Nam', '1961-02-22', '2005-01-01', 4.50,
2025000, 'KTTT'),
('GV05', 'Mai Thanh Danh', 'ThS', 'GV', 'Nam', '1958-12-03', '2005-01-01', 3.00,
1350000, 'HTTT'),
('GV06', 'Tran Doan Hung', 'ThS' ,'GV', 'Nam', '1973-11-03', '2004-05-20', 4.50,
2025000, 'KHMT'),
('GV07', 'Nguyen Minh Tien', 'ThS', 'GV', 'Nam', '1971-11-23', '2003-01-15', 4.00,
1800000, 'KHMT'),
('GV08', 'Le Thi Tran', 'KS', 'Null', 'Nu', '1974-03-26', '2005-01-01', 2.00,
760500, 'KHMT'),
('GV09', 'Nguyen To Lan', 'ThS', 'GV', 'Nu', '1962-12-31', '2005-01-15', 4.00,
1800000, 'HTTT'),
('GV10', 'Le Tran Anh Loan', 'KS', 'Null', 'Nu', '1972-07-17', '2005-03-15', 2.00,
837000, 'CNPM'),
('GV11', 'Tran Van Anh', 'CN', 'GV', 'Nam', '1980-12-17', '2005-05-15', 2.00,
760500, 'CNPM'),
('GV12', 'Ho Thanh Tung', 'CN', 'GV', 'Nam', '1980-03-29', '2005-05-15', 2.00,
760500, 'KTTT'),
('GV13', 'Nguyen Linh Dan', 'CN', 'GV', 'Nu', '1981-02-23', '2005-05-15', 2.00,
760500, 'KTTT'),
('GV14', 'Truong Minh Chau', 'PGS', 'GV', 'Nam', '1976-11-30', '2005-03-15', 3.00,
1350000, 'MTT'),
('GV15', 'Le Ha Thanh', 'ThS', 'GV', 'Nu', '1978-04-05', '2005-05-15', 3.00,
1350000, 'KHMT');

INSERT INTO DIEUKIEN (MAMH, MANH_TRUOC)


VALUES
('CSDL', 'CTRR'),
('CSDL', 'CTDLGT'),
('CTDLGT', 'THDC'),
('PTTKTT', 'THDC'),
('PTTKTT', 'CTDLGT'),
('DHMT', 'THDC'),
('LTHDT', 'THDC'),
('PTTKHTTT', 'CSDL');

INSERT INTO HOCVIEN(MAHV,HO,TEN,NGSINH,GIOITINH,NOISINH,MALOP) VALUES


('K1101','Nguyen Van','A','27/1/1986','Nam','TPHCM','K11')
,('K1102','Tran Ngoc','Han','14/3/1986','Nu','Kien Giang','K11')
,('K1103','Ha Duy','Lap','18/4/1986','Nam','Nghe An','K11')
,('K1104','Tran Ngoc','Linh','30/3/1986','Nu','Tay Ninh','K11')
,('K1105','Tran Minh','Long','27/2/1986','Nam','TPHCM','K11')
,('K1106','Le Nhat','Minh','24/1/1986','Nam','Ha Noi','K11')
,('K1107','Nguyen Nhu','Nhut','27/1/1986','Nam','Kien Giang','K11')
,('K1108','Nguyen Manh','Tam','27/2/1986','Nam','TPHCM','K11')
,('K1109','Phan Thi Thanh','Tam','27/1/1986','Nu','Vinh Long','K11')
,('K1110','Le Hoai','Thuong','5/2/1986','Nu','Can Tho','K11')
,('K1111','Le Ha','Vinh','25/12/1986','Nam','Vinh Long','K11')
,('K1201','Nguyen Van','B','11/2/1986','Nam','TPHCM','K12')
,('K1202','Nguyen Thi Kim','Duyen','18/1/1986','Nu','TPHCM','K12')
,('K1203','Tran Thi Kim','Duyen','17/9/1986','Nu','Dong Nai','K12')
,('K1204','Truong My','Hanh','19/5/1986','Nu','TPHCM','K12')
,('K1205','Nguyen Thanh','Nam','17/4/1986','Nam','TPHCM','K12')
,('K1206','Nguyen Thi Truc','Nam','17/3/1986','Nu','Kien Giang','K12')
,('K1207','Tran Thi Bich','Thuy','8/2/1986','Nu','Nghe An','K12')
,('K1208','Huynh Thi Kim','Trieu','23/2/1986','Nu','Tay Ninh','K12')
,('K1209','Pham Thanh','Trieu','8/4/1986','Nam','TPHCM','K12')
,('K1210','Ngo Thanh','Tuan','14/2/1986','Nam','TPHCM','K12')
,('K1211','Do Thi','Xuan','9/3/1986','Nu','Ha Noi','K12')
,('K1212','Le Thi Phi','Yen','12/3/1986','Nu','TPHCM','K12')
,('K1301','Nguyen Thi Kim','Cuc','9/6/1986','Nu','Kien Giang','K13')
,('K1302','Truong Thi My','Hien','18/3/1986','Nu','Nghe An','K13')
,('K1303','Le Duc','Hien','21/3/1986','Nam','Tay Ninh','K13')
,('K1304','Le Quang','Hien','18/4/1986','Nam','TPHCM','K13')
,('K1305','Le Thi','Huong','27/3/1986','Nu','TPHCM','K13')
,('K1306','Nguyen Thai','Huu','30/3/1986','Nam','Ha Noi','K13')
,('K1307','Nguyen Hieu','Nghia','28/5/1986','Nam','TPHCM','K13')
,('K1308','Tran Minh','Nghia','18/1/1987','Nam','Kien Giang','K13')
,('K1309','Nguyen Trung','Nghia','22/4/1986','Nam','Nghe An','K13')
,('K1310','Tran Thi Hong','Tham','22/4/1986','Nu','Tay Ninh','K13')
,('K1311','Tran Minh','Thuc','4/4/1986','Nam','TPHCM','K13')
,('K1312','Nguyen Thi Kim','Yen','7/9/1986','Nu','TPHCM','K13');

INSERT INTO MONHOC (MAMH, TENMH, TCLT, TCTH, MAKHOA)


VALUES
('THDC', 'Tin hoc dai cuong', 4, 1, 'KHMT'),
('CTRR', 'Cau truc roi rac', 5, 0, 'KHMT'),
('CSDL', 'Co so du lieu', 3, 1, 'HTTT'),
('CTDLGT', 'Cau truc du lieu va giai thuat', 3, 1, 'KHMT'),
('PTTKTT', 'Phan tich thiet ke thuat toan', 3, 1, 'KHMT'),
('DHMT', 'Do hoa may tinh', 3, 1, 'KHMT'),
('KTMT', 'Kien truc may tinh', 3, 0, 'KHMT'),
('TKCSDL', 'Thiet ke co so du lieu', 3, 1, 'CSDL'),
('PTTKHTTT', 'Phan tich thiet ke he thong thong tin', 4, 1, 'HTTT'),
('HDH', 'He dieu hanh', 3, 0, 'KTMT'),
('MNCPNM', 'Nhap mon cong nghe phan mem', 3, 1, 'CNPM'),
('LTCFW', 'Lap trinh C for win', 3, 1, 'CNPM'),
('LTHDT', 'Lap trinh huong doi tuong', 3,1,'CNPM');
INSERT INTO GIANGDAY (MALOP, MAMH, MAGV, HOCKY, NAM, TUNGAY, DENNGAY)
VALUES
('K11', 'THDC', 'GV07', 2, 2006, '2006-02-01', '2006-12-05'),
('K12', 'THDC', 'GV06', 1, 2006, '2006-02-01', '2006-12-05'),
('K13', 'THDC', 'GV15', 1, 2006, '2006-01-06', '2006-12-05'),
('K11', 'CTRR', 'GV02', 1, 2006, '2006-09-01', '2006-12-15'),
('K12', 'CTRR', 'GV08', 1, 2006, '2006-09-01', '2006-12-15'),
('K13', 'CTRR', 'GV15', 1, 2006, '2006-09-01', '2006-12-15'),
('K11', 'CSDL', 'GV05', 3, 2006, '2006-01-06', '2006-12-15'),
('K13', 'DHMT', 'GV07', 3, 2006, '2006-01-08', '2006-12-15'),
('K12', 'CTDLGT', 'GV15', 3, 2006, '2006-01-08', '2006-12-15'),
('K11', 'HDH', 'GV04', 1, 2007, '2007-02-01', '2007-02-18'),
('K12', 'HDH', 'GV04', 1, 2007, '2007-02-01', '2007-03-20'),
('K11', 'DHMT', 'GV07', 1, 2007,'2007-18-02', '2007-20-03');

INSERT INTO KHOA (MAKHOA, TENKHOA, NGTLAP, TRGKHOA)


VALUES
('KHMT', 'Khoa hoc may tinh', '2005-06-07', 'GV01'),
('HTTT', 'He thong thong tin', '2005-06-07', 'GV02'),
('CNPM', 'Cong nghe phan mem', '2005-06-07', 'GV04'),
('MTT', 'Mang va truyen thong', '2005-10-20', 'GV03'),
('KTMT', 'Ky thuat may tinh', '2005-12-20', NULL);

INSERT INTO LOP (MALOP, TENLOP, TRGLOP, SISO, MAGVCN)


VALUES
('K11', 'Lop 1 khoa 1', 'K1108', 11, 'GV07'),
('K12', 'Lop 2 khoa 1', 'K1205', 12, 'GV09'),
('K13', 'Lop 3 khoa 1', 'K1305', 12, 'GV14');

INSERT INTO KETQUATHI (MAHV, MAMH, LANTHI, NGTHI, DIEM, KQUA) VALUES
('K1101','CSDL',1,'20/7/2006',10.00,'Dat')
,('K1101','CTDLGT',1,'28/12/2006',9.00,'Dat')
,('K1101','THDC',1,'20/5/2006',9.00,'Dat')
,('K1101','CTRR',1,'13/5/2006',9.50,'Dat')
,('K1102','CSDL',1,'20/7/2006',4.00,'Khong Dat')
,('K1102','CSDL',2,'27/7/2006',4.25,'Khong Dat')
,('K1102','CSDL',3,'10/8/2006',4.50,'Khong Dat')
,('K1102','CTDLGT',1,'28/12/2006',4.50,'Khong Dat')
,('K1102','CTDLGT',2,'5/1/2007',4.00,'Khong Dat')
,('K1102','CTDLGT',3,'15/1/2007',6.00,'Dat')
,('K1102','THDC',1,'20/5/2006',5.00,'Dat')
,('K1102','CTRR',1,'13/5/2006',7.00,'Dat')
,('K1103','CSDL',1,'20/7/2006',3.50,'Khong Dat')
,('K1103','CSDL',2,'27/7/2006',8.25,'Dat')
,('K1103','CTDLGT',1,'28/12/2006',7.00,'Dat')
,('K1103','THDC',1,'20/5/2006',8.00,'Dat')
,('K1103','CTRR',1,'13/5/2006',6.50,'Dat')
,('K1104','CSDL',1,'20/7/2006',3.75,'Khong Dat')
,('K1104','CTDLGT',1,'28/12/2006',4.00,'Khong Dat')
,('K1104','THDC',1,'20/5/2006',4.00,'Khong Dat')
,('K1104','CTRR',1,'13/5/2006',4.00,'Khong Dat')
,('K1104','CTRR',2,'20/5/2006',3.50,'Khong Dat')
,('K1104','CTRR',3,'30/6/2006',4.00,'Khong Dat')
,('K1201','CSDL',1,'20/7/2006',6.00,'Dat')
,('K1201','CTDLGT',1,'28/12/2006',5.00,'Dat')
,('K1201','THDC',1,'20/5/2006',8.50,'Dat')
,('K1201','CTRR',1,'13/5/2006',9.00,'Dat')
,('K1202','CSDL',1,'20/7/2006',8.00,'Dat')
,('K1202','CTDLGT',1,'28/12/2006',4.00,'Khong Dat')
,('K1202','CTDLGT',2,'5/1/2007',5.00,'Dat')
,('K1202','THDC',1,'20/5/2006',4.00,'Khong Dat')
,('K1202','THDC',2,'27/5/2006',4.00,'Khong Dat')
,('K1202','CTRR',1,'13/5/2006',3.00,'Khong Dat')
,('K1203','CSDL',1,'20/7/2006',9.25,'Dat')
,('K1203','CTDLGT',1,'28/12/2006',9.50,'Dat')
,('K1203','THDC',1,'20/5/2006',10.00,'Dat')
,('K1203','CTRR',1,'13/5/2006',10.00,'Dat')
,('K1204','CSDL',1,'20/7/2006',8.50,'Dat')
,('K1204','CTDLGT',1,'28/12/2006',6.75,'Dat')
,('K1204','THDC',1,'20/5/2006',4.00,'Khong Dat')
,('K1204','CTRR',1,'13/5/2006',6.00,'Dat')
,('K1301','CSDL',1,'20/12/2006',4.25,'Khong Dat')
,('K1301','CTDLGT',1,'25/7/2006',8.00,'Dat')
,('K1301','THDC',1,'20/5/2006',7.75,'Dat')
,('K1301','CTRR',1,'13/5/2006',8.00,'Dat')
,('K1302','CSDL',1,'20/12/2006',6.75,'Dat')
,('K1302','CTDLGT',1,'25/7/2006',5.00,'Dat')
,('K1302','THDC',1,'20/5/2006',8.00,'Dat')
,('K1302','CTRR',1,'13/5/2006',8.50,'Dat')
,('K1303','CSDL',1,'20/12/2006',4.00,'Khong Dat')
,('K1303','CTDLGT',1,'25/7/2006',4.50,'Khong Dat')
,('K1303','CTDLGT',2,'7/8/2006',4.00,'Khong Dat')
,('K1303','CTDLGT',3,'15/8/2006',4.25,'Khong Dat')
,('K1303','THDC',1,'20/5/2006',4.50,'Khong Dat')
,('K1303','CTRR',1,'13/5/2006',3.25,'Khong Dat')
,('K1303','CTRR',2,'20/5/2006',5.00,'Dat')
,('K1304','CSDL',1,'20/12/2006',7.75,'Dat')
,('K1304','CTDLGT',1,'25/7/2006',9.75,'Dat')
,('K1304','THDC',1,'20/5/2006',5.50,'Dat')
,('K1304','CTRR',1,'13/5/2006',5.00,'Dat')
,('K1305','CSDL',1,'20/12/2006',9.25,'Dat')
,('K1305','CTDLGT',1,'25/7/2006',10.00,'Dat')
,('K1305','THDC',1,'20/5/2006',8.00,'Dat')
,('K1305','CTRR',1,'13/5/2006',10.00,'Dat');

--1
SELECT [Link],[Link]+' '+ [Link] AS HOTEN, [Link],
[Link]
FROM HOCVIEN
INNER JOIN LOP on [Link] = [Link];

--2
SELECT [Link], [Link] + ' ' + [Link] AS HOTEN, [Link],
[Link]
FROM KETQUATHI
JOIN HOCVIEN ON [Link] = [Link]
WHERE [Link] = 'CTRR' AND [Link] = 'K12'
ORDER BY [Link], [Link];

--3
SELECT [Link], [Link]+' '+[Link] AS HOTEN, [Link]
FROM KETQUATHI
JOIN HOCVIEN on [Link] = [Link]
JOIN MONHOC on [Link] = [Link]
WHERE [Link]=1 AND [Link]='Dat';

--4
SELECT [Link], [Link]+' '+[Link] AS HOTEN, [Link]
FROM KETQUATHI
JOIN HOCVIEN on [Link] = [Link]
WHERE [Link]='K11' AND [Link]='CTRR' AND [Link]='Khong Dat'
AND [Link]=1;

--5
SELECT [Link], [Link]+' '+[Link] AS HOTEN
FROM KETQUATHI
JOIN HOCVIEN on [Link] = [Link]
WHERE [Link] LIKE 'K%' AND [Link]='CTRR' AND [Link]='Khong
Dat';

--6
SELECT [Link], [Link]
FROM MONHOC
JOIN GIANGDAY ON [Link] = [Link]
JOIN GIAOVIEN ON [Link] = [Link]
WHERE [Link] = 'Tran Tam Thanh' AND [Link] = 1 AND [Link] =
2006

--7
SELECT
[Link],
[Link]
FROM
GIANGDAY
JOIN
MONHOC ON [Link] = [Link]
JOIN
LOP ON [Link] = [Link]
JOIN
GIAOVIEN ON [Link] = [Link]
WHERE
[Link] = 'K11' AND
[Link] = 1 AND
[Link] = 2006;

--8
SELECT
[Link],
CONCAT([Link], ' ', [Link]) AS HOTEN_LOPTRUONG
FROM
GIANGDAY
JOIN
MONHOC ON [Link] = [Link]
JOIN
GIAOVIEN ON [Link] = [Link]
JOIN
LOP ON [Link] = [Link]
JOIN
HOCVIEN ON [Link] = [Link]
WHERE
[Link] = 'Nguyen To Lan' AND
[Link] = 'Co So Du Lieu';

--9
SELECT
DIEUKIEN.MAMH_TRUOC AS mamh,
[Link] AS tenmh
FROM
DIEUKIEN
JOIN
MONHOC ON DIEUKIEN.MAMH_TRUOC = [Link]
WHERE
[Link] = 'CSDL';

--10
SELECT
[Link] AS mamh,
[Link] AS tenmh
FROM
DIEUKIEN
JOIN
MONHOC ON [Link] = [Link]
WHERE
DIEUKIEN.MAMH_TRUOC = 'CTRR';

--11
SELECT [Link]
FROM GIAOVIEN
JOIN GIANGDAY ON [Link] = [Link]
WHERE [Link] = 'CTRR'
AND [Link] = 1
AND [Link] = 2006
AND [Link] IN ('K11', 'K12')
GROUP BY [Link];

--12
SELECT
[Link] AS mahv,
CONCAT([Link], ' ', [Link]) AS hoten
FROM
KETQUATHI
JOIN
HOCVIEN ON [Link] = [Link]
WHERE
[Link] = 'CSDL' AND
[Link] = 1 AND
[Link] = 'Khong Dat' AND
NOT EXISTS (
SELECT 1
FROM KETQUATHI AS KT2
WHERE [Link] = [Link] AND [Link] = 'CSDL' AND [Link] > 1
);

--13
SELECT
[Link] AS magv,
[Link] AS hoten
FROM
GIAOVIEN
LEFT JOIN
GIANGDAY ON [Link] = [Link]
WHERE
[Link] IS NULL;

--14
SELECT [Link], [Link]
FROM GIAOVIEN
WHERE [Link] NOT IN (
SELECT [Link]
FROM GIANGDAY
JOIN MONHOC ON [Link] = [Link]
WHERE [Link] = [Link]
);

--15
SELECT DISTINCT
[Link] AS mahv,
CONCAT([Link], ' ', [Link]) AS hoten
FROM
HOCVIEN
JOIN
KETQUATHI ON [Link] = [Link]
WHERE
[Link] = 'K11' AND
(
(SELECT COUNT(*)
FROM KETQUATHI KQ
WHERE [Link] = [Link] AND [Link] = 'Khong Dat') > 3
OR
([Link] = 'CTRR' AND [Link] = 2 AND [Link] =
5.00)
);

--16
SELECT [Link]
FROM GIANGDAY
JOIN GIAOVIEN ON [Link] = [Link]
WHERE [Link] = 'CTRR'
GROUP BY [Link], [Link], [Link]
HAVING COUNT(DISTINCT [Link]) >= 2;

--17
SELECT [Link], [Link],[Link], [Link]
FROM HOCVIEN HV
JOIN KETQUATHI KQ ON [Link] = [Link]
WHERE [Link] = 'CSDL'
AND [Link] = (
SELECT MAX(LANTHI)
FROM KETQUATHI
WHERE MAHV = [Link] AND MAMH = 'CSDL'
);

--18
SELECT
[Link] AS mahv,
CONCAT([Link], ' ', [Link]) AS hoten,
MAX([Link]) AS diem
FROM
KETQUATHI
JOIN
HOCVIEN ON [Link] = [Link]
WHERE
[Link] = 'CSDL'
GROUP BY
[Link], [Link], [Link];

--19
SELECT
MAKHOA,
TENKHOA
FROM
KHOA
WHERE
NGTLAP = (SELECT MIN(NGTLAP) FROM KHOA);

--20
SELECT COUNT(*) AS SO_LUONG_GIAO_VIEN
FROM GIAOVIEN
WHERE [Link] IN ('GS', 'PGS');

You might also like