22521543_BTTH2.sql

-- Ho va ten: Le Minh Tri
-- MSSV: 22521543

-- BAI THUC HANH: QLY_BANHANG


CREATE DATABASE QLY_BANHANG
GO

USE QLY_BANHANG

CREATE TABLE KHACHHANG
(
    MAKH CHAR(4) PRIMARY KEY NOT NULL,
    HOTEN VARCHAR(40) ,
    DCHI VARCHAR(50) ,
    SODT VARCHAR(20),
    NGSINH SMALLDATETIME,
    DOANHSO MONEY,
    NGDK SMALLDATETIME,
)

CREATE TABLE NHANVIEN
(
    MANV CHAR(4) PRIMARY KEY NOT NULL,
    HOTEN VARCHAR(40) ,
    SODT VARCHAR(20),
    NGVL SMALLDATETIME,
)

CREATE TABLE SANPHAM
(
    MASP CHAR(4) PRIMARY KEY NOT NULL,
    TENSP VARCHAR(40),
    DVT VARCHAR(20),
    NUOCSX VARCHAR(40),
    GIA MONEY,
)

CREATE TABLE HOADON
(
    SOHD INT PRIMARY KEY NOT NULL,
    NGHD SMALLDATETIME,
    MAKH CHAR(4) FOREIGN KEY REFERENCES KHACHHANG(MAKH),
    MANV CHAR(4) FOREIGN KEY REFERENCES NHANVIEN(MANV),
    TRIGIA MONEY,
)

CREATE TABLE CTHD
(
    SOHD INT FOREIGN KEY REFERENCES HOADON(SOHD),
    MASP CHAR(4) FOREIGN KEY REFERENCES SANPHAM(MASP),
    SL INT CONSTRAINT PK_CTHD PRIMARY KEY(SOHD,MASP)
)

ALTER TABLE SANPHAM ADD GHICHU VARCHAR(20)

ALTER TABLE KHACHHANG ADD LOAIKH VARCHAR(20)

ALTER TABLE SANPHAM ALTER COLUMN GHICHU VARCHAR(100)

ALTER TABLE SANPHAM DROP COLUMN GHICHU

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

ALTER TABLE KHACHHANG ADD CONSTRAINT GT_LOAIKH CHECK (LOAIKH='Vang lai' OR LOAIKH='Thuong xuyen' OR LOAIKH='Vip')

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

ALTER TABLE KHACHHANG ADD CONSTRAINT NGDK_NGSINH CHECK (NGDK>NGSINH)



-- INSERT VALUE TO NHANVIEN

INSERT INTO NHANVIEN (MANV, HOTEN, SODT, NGVL)
VALUES 
    ('NV01', 'Nguyen Nhu Nhut', '0927345678', '2006-04-13'),
    ('NV02', 'Le Thi Phi Yen', '0987567390', '2006-04-21'),
    ('NV03', 'Nguyen Van B', '0997047382', '2006-04-27'),
    ('NV04', 'Ngo Thanh Tuan', '0913758498', '2006-06-24'),
    ('NV05', 'Nguyen Thi Truc Thanh', '0918590387', '2006-07-20');

-- INSERT VALUE TO KHACHHANG

INSERT INTO KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK)
VALUES
    ('KH01', 'Nguyen Van A', '731 Tran Hung Dao, Q5, TpHCM', '8823451', '1960-10-22', '13060000', '2006-07-22'),
    ('KH02', 'Tran Ngoc Han', '23/5 Nguyen Trai, Q5, TpHCM', '908256478', '1974-04-03', '280000', '2006-07-30'),
    ('KH03', 'Tran Ngoc Linh', '45 Nguyen Canh Chan, Q1, TpHCM', '938776266', '1980-06-12', '3860000', '2006-08-05'),
    ('KH04', 'Tran Minh Long', '50/34 Le Dai Hanh, Q10, TpHCM', '917325476', '1965-03-09', '250000', '2006-10-02'),
    ('KH05', 'Le Nhat Minh', '34 Truong Dinh, Q3, TpHCM', '8246108', '1950-03-10', '21000', '2006-10-28'),
    ('KH06', 'Le Hoai Thuong', '227 Nguyen Van Cu, Q5, TpHCM', '8631738', '1981-12-31', '915000', '2006-11-24'),
    ('KH07', 'Nguyen Van Tam', '32/3 Tran Binh Trong, Q5, TpHCM', '916783565', '1971-04-06', '12500', '2006-12-01'),
    ('KH08', 'Phan Thi Thanh', '45/2 An Duong Vuong, Q5, TpHCM', '938435756', '1971-01-10', '365000', '2006-12-13'),
    ('KH09', 'Le Ha Vinh', '873 Le Hong Phong, Q5, TpHCM', '8654763', '1979-09-03', '70000', '2007-01-14'),
    ('KH10', 'Ha Duy Lap', '34/34B Nguyen Trai, Q1, TpHCM', '8768904', '1983-05-02', '67500', '2007-01-16');

-- INSERT VALUE TO SANPHAM

INSERT INTO SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA)
VALUES
    ('BC01', 'But chi', 'cay', 'Singapore', 3000),
    ('BC02', 'But chi', 'cay', 'Singapore', 5000),
    ('BC03', 'But chi', 'cay', 'Viet Nam', 3500),
    ('BC04', 'But chi', 'hop', 'Viet Nam', 30000),
    ('BB01', 'But bi', 'cay', 'Viet Nam', 5000),
    ('BB02', 'But bi', 'cay', 'Trung Quoc', 7000),
    ('BB03', 'But bi', 'hop', 'Thai Lan', 100000),
    ('TV01', 'Tap 100 giay mong', 'quyen', 'Trung Quoc', 2500),
    ('TV02', 'Tap 200 giay mong', 'quyen', 'Trung Quoc', 4500),
    ('TV03', 'Tap 100 giay tot', 'quyen', 'Viet Nam', 3000),
    ('TV04', 'Tap 200 giay tot', 'quyen', 'Viet Nam', 5500),
    ('TV05', 'Tap 100 trang', 'chuc', 'Viet Nam', 23000),
    ('TV06', 'Tap 200 trang', 'chuc', 'Viet Nam', 53000),
    ('TV07', 'Tap 100 trang', 'chuc', 'Trung Quoc', 34000),
    ('ST01', 'So tay 500 trang', 'quyen', 'Trung Quoc', 40000),
    ('ST02', 'So tay loai 1', 'quyen', 'Viet Nam', 55000),
    ('ST03', 'So tay loai 2', 'quyen', 'Viet Nam', 51000),
    ('ST04', 'So tay', 'quyen', 'Thai Lan', 55000),
    ('ST05', 'So tay mong', 'quyen', 'Thai Lan', 20000),
    ('ST06', 'Phan viet bang', 'hop', 'Viet Nam', 5000),
    ('ST07', 'Phan khong bui', 'hop', 'Viet Nam', 7000),
    ('ST08', 'Bong bang', 'cai', 'Viet Nam', 1000),
    ('ST09', 'But long', 'cay', 'Viet Nam', 5000),
    ('ST10', 'But long', 'cay', 'Trung Quoc', 7000);

-- INSERT VALUE TO HOADON

INSERT INTO HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA)
VALUES
    (1001, '2006-07-23', 'KH01', 'NV01', 320000),
    (1002, '2006-08-12', 'KH01', 'NV02', 840000),
    (1003, '2006-08-23', 'KH02', 'NV01', 100000),
    (1004, '2006-09-01', 'KH02', 'NV01', 180000),
    (1005, '2006-10-20', 'KH01', 'NV02', 3800000),
    (1006, '2006-10-16', 'KH01', 'NV03', 2430000),
    (1007, '2006-10-28', 'KH03', 'NV03', 510000),
    (1008, '2006-10-28', 'KH01', 'NV03', 440000),
    (1009, '2006-10-28', 'KH03', 'NV04', 200000),
    (1010, '2006-11-01', 'KH01', 'NV01', 5200000),
    (1011, '2006-11-04', 'KH04', 'NV03', 250000),
    (1012, '2006-11-30', 'KH05', 'NV03', 21000),
    (1013, '2006-12-12', 'KH06', 'NV01', 5000),
    (1014, '2006-12-31', 'KH03', 'NV02', 3150000),
    (1015, '2007-01-01', 'KH06', 'NV01', 910000),
    (1016, '2007-01-01', 'KH07', 'NV02', 12500),
    (1017, '2007-01-02', 'KH08', 'NV03', 35000),
    (1018, '2007-01-13', 'KH08', 'NV03', 330000),
    (1019, '2007-01-13', 'KH01', 'NV03', 30000),
    (1020, '2007-01-14', 'KH09', 'NV04', 70000),
    (1021, '2007-01-16', 'KH10', 'NV03', 67500),
    (1022, '2007-01-16', NULL, 'NV03', 7000),
    (1023, '2007-01-17', NULL, 'NV01', 330000);

-- INSERT VALUE TO CTHD

INSERT INTO CTHD (SOHD, MASP, SL)
VALUES
    (1001, 'TV02', 10),
    (1001, 'ST01', 5),
    (1001, 'BC01', 5),
    (1001, 'BC02', 10),
    (1001, 'ST08', 10),
    (1002, 'BC04', 20),
    (1002, 'BB01', 20),
    (1002, 'BB02', 20),
    (1003, 'BB03', 10),
    (1004, 'TV01', 20),
    (1004, 'TV02', 10),
    (1004, 'TV03', 10),
    (1004, 'TV04', 10),
    (1005, 'TV05', 50),
    (1005, 'TV06', 50),
    (1006, 'TV07', 20),
    (1006, 'ST01', 30),
    (1006, 'ST02', 10),
    (1007, 'ST03', 10),
    (1008, 'ST04', 8),
    (1009, 'ST05', 10),
    (1010, 'TV07', 50),
    (1010, 'ST07', 50),
    (1010, 'ST08', 100),
    (1010, 'ST04', 50),
    (1010, 'TV03', 100),
    (1011, 'ST06', 50),
    (1012, 'ST07', 3),
    (1013, 'ST08', 5),
    (1014, 'BC02', 80),
    (1014, 'BB02', 100),
    (1014, 'BC04', 60),
    (1014, 'BB01', 50),
    (1015, 'BB02', 30),
    (1015, 'BB03', 7),
    (1016, 'TV01', 5),
    (1017, 'TV02', 1),
    (1017, 'TV03', 1),
    (1017, 'TV04', 5),
    (1018, 'ST04', 6),
    (1019, 'ST05', 1),
    (1019, 'ST06', 2),
    (1020, 'ST07', 10),
    (1021, 'ST08', 5),
    (1021, 'TV01', 7),
    (1021, 'TV02', 10),
    (1022, 'ST07', 1),
    (1023, 'ST04', 6);

-- CREATE TABLE SANPHAM1 FROM SANPHAM
CREATE TABLE SANPHAM1
(
    MASP CHAR(4) PRIMARY KEY NOT NULL,
    TENSP VARCHAR(40),
    DVT VARCHAR(20),
    NUOCSX VARCHAR(40),
    GIA MONEY,
)

INSERT INTO SANPHAM1 SELECT * FROM SANPHAM

-- UPDATE GIASP BY NUOCSX IN SANPHAM1

UPDATE SANPHAM1
SET GIA= GIA+GIA*0.5
WHERE NUOCSX='Thai Lan'

-- UPDATE GIASP BY NUOCSX IN SANPHAM1

UPDATE SANPHAM1
SET GIA= GIA-GIA*0.5
WHERE NUOCSX='Trung Quoc'


-- CREATE TABLE KHACHHANG1 FROM KHACHHANG
CREATE TABLE KHACHHANG1
(
    MAKH CHAR(4) PRIMARY KEY NOT NULL,
    HOTEN VARCHAR(40) ,
    DCHI VARCHAR(50) ,
    SODT VARCHAR(20),
    NGSINH SMALLDATETIME,
    DOANHSO MONEY,
    NGDK SMALLDATETIME,
    LOAIKH VARCHAR(20),
)

INSERT INTO KHACHHANG1 SELECT * FROM KHACHHANG

-- UPDATE LOAIKH IN KHACHHANG

UPDATE KHACHHANG
SET LOAIKH='Vip'
WHERE (
    NGDK < '2007-01-01' AND DOANHSO >= 10000000
) OR (
    NGDK >= '2007-01-01' AND DOANHSO >= 2000000
)

-- PRINT ITEM'S MASP, TENSP IN SANPHAM WHERE NUOCSX='Trung Quoc'

SELECT MASP, TENSP FROM SANPHAM WHERE NUOCSX='Trung Quoc'

-- PRINT ITEM'S MASP, TENSP IN SANPHAM WHERE DVT='cay' OR DVT='quyen'

SELECT MASP, TENSP FROM SANPHAM WHERE DVT='cay' OR DVT='quyen'

-- PRINT ITEM'S MASP, TENSP IN SANPHAM WHERE MASP LIKE 'B%01'

SELECT MASP, TENSP FROM SANPHAM WHERE MASP LIKE 'B%01'

-- PRINT ITEM'S MASP, TENSP IN SANPHAM WHERE NUOCSX='Trung Quoc' AND GIA >=30000 AND GIA<=40000

SELECT MASP,TENSP FROM SANPHAM WHERE (NUOCSX='Trung Quoc' AND GIA >=30000 AND GIA<=40000)

-- PRINT ITEM'S MASP, TENSP IN SANPHAM WHERE NUOCSX='Trung Quoc' OR NUOCSX='Thai Lan' AND GIA >=30000 AND GIA<=40000

SELECT MASP,TENSP FROM SANPHAM WHERE (NUOCSX='Trung Quoc' OR NUOCSX='Thai Lan') AND (GIA >=30000 AND GIA<=40000)

-- PRINT HOADON'S SOHD, TRIGIA WHERE NGHD='2007-01-01' OR NGHD='2007-01-02'

SELECT SOHD, TRIGIA FROM HOADON WHERE NGHD='2007-01-01' OR NGHD='2007-01-02'

-- PRINT HOADON'S SOHD, TRIGIA WHERE YEAR(NGHD)=2007 AND MONTH(NGHD)=1 AND ORDER BY DAY(NGHD) ASC AND TRIGIA DESC

SELECT SOHD, TRIGIA FROM HOADON WHERE YEAR(NGHD)=2007 AND MONTH(NGHD)=1 ORDER BY DAY(NGHD) ASC, TRIGIA DESC

-- PRINT KHACHHANG HAS BOUGHT IN DAY 2007-01-01 BY INNER JOIN TABLE KHACHHANG AND HOADON 

SELECT KHACHHANG.MAKH, HOTEN FROM KHACHHANG INNER JOIN HOADON ON KHACHHANG.MAKH=HOADON.MAKH WHERE NGHD='2007-01-01'

-- Print HOADON.TRIGIA and HOADON.MAHD OF NHANVIEN HAS HOTEN='Nguyen Van B' THAT SELL ON DAY 2006-10-28 BY INNER JOIN TABLE NHANVIEN AND HOADON

SELECT SOHD, TRIGIA FROM HOADON INNER JOIN NHANVIEN ON HOADON.MANV=NHANVIEN.MANV WHERE NGHD='2006-10-28' AND NHANVIEN.HOTEN='Nguyen Van B'

-- PRINT MASP, TENSP THAT KHACHHANG'S HOTEN='Nguyen Van A' HAS BOUGHT BY INNER JOIN TABLE KHACHHANG, HOADON AND CTHD

SELECT SANPHAM.MASP, TENSP FROM SANPHAM INNER JOIN CTHD ON SANPHAM.MASP=CTHD.MASP INNER JOIN HOADON ON CTHD.SOHD=HOADON.SOHD INNER JOIN KHACHHANG ON HOADON.MAKH=KHACHHANG.MAKH WHERE KHACHHANG.HOTEN='Nguyen Van A'

-- PRINT ALL HOADON THAT HAS MASP='BB01' OR MASP='BB02' BY INNER JOIN TABLE HOADON AND CTHD

SELECT HOADON.SOHD FROM HOADON INNER JOIN CTHD ON HOADON.SOHD=CTHD.SOHD WHERE CTHD.MASP='BB01' OR CTHD.MASP='BB02'

-- PRINT ALL HOADON THAT HAS MASP='BB01' AND MASP='BB02' AND CTHD.SL>=10 AND CTHD.SL<=20 BY INNER JOIN TABLE HOADON AND CTHD

SELECT HOADON.SOHD FROM HOADON INNER JOIN CTHD ON HOADON.SOHD=CTHD.SOHD WHERE (CTHD.MASP='BB01' OR CTHD.MASP='BB02') AND (CTHD.SL>=10 AND CTHD.SL<=20)

-- PRINT ALL HOADON THAT HAS BOTH MASP='BB01' AND MASP='BB02' AND CTHD.SL>=10 AND CTHD.SL<=20 BY INNER JOIN TABLE HOADON AND CTHD

SELECT SOHD FROM CTHD 
WHERE MASP = 'BB01' AND SL BETWEEN 10 AND 20
INTERSECT
SELECT SOHD FROM CTHD 
WHERE MASP = 'BB02' AND SL BETWEEN 10 AND 20

-- PRINT ALL SANPHAM's MASP, TENSP BY NUOCSX='Trung Quoc' OR WAS SOLD ON DAY 2007-01-01

SELECT SANPHAM.MASP, TENSP FROM SANPHAM INNER JOIN CTHD ON SANPHAM.MASP=CTHD.MASP INNER JOIN HOADON ON CTHD.SOHD=HOADON.SOHD WHERE NUOCSX='Trung Quoc' OR NGHD='2007-01-01'

-- PRINT ALL SANPHAM CANNOT BE SOLD
SELECT MASP, TENSP FROM SANPHAM
WHERE MASP IN (
    SELECT MASP FROM SANPHAM
    EXCEPT 
    SELECT MASP FROM CTHD
)

-- PRINT ALL SANPHAM CANNOT BE SOLD IN 2006


SELECT MASP, TENSP FROM SANPHAM
WHERE MASP IN (
    SELECT MASP FROM SANPHAM
    EXCEPT 
    (
        SELECT MASP FROM CTHD 
        WHERE SOHD IN (
            SELECT SOHD FROM HOADON 
            WHERE YEAR(NGHD) = 2006
        ) 
    )
)

-- PRINT ALL SANPHAM CANNOT BE SOLD IN 2006 AND BY NUOCSX='Trung Quoc'

SELECT MASP, TENSP FROM SANPHAM
WHERE MASP IN (
    (
        SELECT MASP FROM SANPHAM
        WHERE NUOCSX = 'Trung Quoc'
    )
    EXCEPT 
    (
        SELECT MASP FROM CTHD 
        WHERE SOHD IN (
            SELECT SOHD FROM HOADON 
            WHERE YEAR(NGHD) = 2006
        ) 
    )
)



-- BAI THUC HANH: QLY_GIAOVU

CREATE DATABASE QLY_HOCVU
GO
USE QLY_HOCVU

SET DATEFORMAT dmy;
CREATE TABLE KHOA (
    MAKHOA  VARCHAR(4),
    TENKHOA VARCHAR(40),
    NGTLAP  SMALLDATETIME,
    TRGKHOA CHAR(4),
    PRIMARY KEY (MAKHOA)
) 

CREATE TABLE MONHOC (
    MAMH    VARCHAR(10),
    TENMH   VARCHAR(40),
    TCLT    TINYINT,
    TCTH    TINYINT,
    MAKHOA  VARCHAR(4),
    PRIMARY KEY (MAMH),
    FOREIGN KEY (MAKHOA) REFERENCES KHOA(MAKHOA)
    
)

CREATE TABLE DIEUKIEN (
    MAMH        VARCHAR(10),
    MAMH_TRUOC  VARCHAR(10),
    PRIMARY KEY (MAMH, MAMH_TRUOC)
)

CREATE TABLE GIAOVIEN (
    MAGV        CHAR(4),
    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),
    PRIMARY KEY (MAGV),
    FOREIGN KEY (MAKHOA) REFERENCES KHOA(MAKHOA),
)

CREATE TABLE LOP (
    MALOP   CHAR(3),
    TENLOP  VARCHAR(40),
    TRGLOP  CHAR(5),
    SISO    TINYINT,
    MAGVCN  CHAR(4),
    PRIMARY KEY (MALOP),
)

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

CREATE TABLE GIANGDAY (
    MALOP   CHAR(3),
    MAMH    VARCHAR(10),
    MAGV    CHAR(4),
    HOCKY   TINYINT,
    NAM     SMALLINT,
    TUNGAY  SMALLDATETIME,
    DENNGAY SMALLDATETIME,
    PRIMARY KEY (MALOP, MAMH),
    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 (MAMH) REFERENCES MONHOC(MAMH),
    FOREIGN KEY (MAHV) REFERENCES HOCVIEN(MAHV)
)

-- THEM DU LIEU
INSERT INTO KHOA VALUES ('KHMT', 'Khoa hoc may tinh', '7/6/2005', 'GV01')
INSERT INTO KHOA VALUES ('HTTT', 'He thong thong tin', '7/6/2005', 'GV02')
INSERT INTO KHOA VALUES ('CNPM', 'Cong nghe phan mem', '7/6/2005', 'GV04')
INSERT INTO KHOA VALUES ('MTT', 'Mang va truyen thong', '20/10/2005', 'GV03')
INSERT INTO KHOA VALUES ('KTMT', 'Ky thuat may tinh', '20/12/2005', NULL)

INSERT INTO GIAOVIEN VALUES ('GV01', 'Ho Thanh Son', 'PTS', 'GS', 'Nam', '2/5/1950', '11/1/2004', 5.00, 2250000, 'KHMT')
INSERT INTO GIAOVIEN VALUES ('GV02', 'Tran Tam Thanh', 'TS', 'PGS', 'Nam', '17/12/1965', '20/4/2004', 4.50, 2025000, 'HTTT')
INSERT INTO GIAOVIEN VALUES ('GV03', 'Do Nghiem Phung', 'TS', 'GS', 'Nu', '1/8/1950', '23/9/2004', 4.00, 1800000, 'CNPM')
INSERT INTO GIAOVIEN VALUES ('GV04', 'Tran Nam Son', 'TS', 'PGS', 'Nam', '22/2/1961', '12/1/2005', 4.50, 2025000, 'KTMT')
INSERT INTO GIAOVIEN VALUES ('GV05', 'Mai Thanh Danh', 'ThS', 'GV', 'Nam', '12/3/1958', '12/1/2005', 3.00, 1350000, 'HTTT')
INSERT INTO GIAOVIEN VALUES ('GV06', 'Tran Doan Hung', 'TS', 'GV', 'Nam', '11/3/1953', '12/1/2005', 4.50, 2025000, 'KHMT')
INSERT INTO GIAOVIEN VALUES ('GV07', 'Nguyen Minh Tien', 'ThS', 'GV', 'Nam', '23/11/1971', '1/3/2005', 4.00, 1800000, 'KHMT')
INSERT INTO GIAOVIEN VALUES ('GV08', 'Le Thi Tran', 'KS', 'Null', 'Nu', '26/3/1974', '1/3/2005', 1.69, 760500, 'KHMT')
INSERT INTO GIAOVIEN VALUES ('GV09', 'Nguyen To Lan', 'ThS', 'GV', 'Nu', '31/12/1966', '1/3/2005', 4.00, 1800000, 'HTTT')
INSERT INTO GIAOVIEN VALUES ('GV10', 'Le Tran Anh Loan', 'KS', 'Null', 'Nu', '17/7/1972', '1/3/2005', 1.86, 837000, 'CNPM')
INSERT INTO GIAOVIEN VALUES ('GV11', 'Ho Thanh Tung', 'CN', 'GV', 'Nam', '12/1/1980', '15/5/2005', 2.67, 1201500, 'MTT')
INSERT INTO GIAOVIEN VALUES ('GV12', 'Tran Van Anh', 'CN', 'Null', 'Nu', '29/3/1981', '15/5/2005', 1.69, 760500, 'CNPM')
INSERT INTO GIAOVIEN VALUES ('GV13', 'Nguyen Linh Dan', 'CN', 'Null', 'Nu', '23/5/1980', '15/5/2005', 1.69, 760500, 'KTMT')
INSERT INTO GIAOVIEN VALUES ('GV14', 'Truong Minh Chau', 'ThS', 'GV', 'Nu', '30/11/1976', '15/5/2005', 3.00, 1350000, 'MTT')
INSERT INTO GIAOVIEN VALUES ('GV15', 'Le Ha Thanh', 'ThS', 'GV', 'Nam', '4/5/1978', '15/5/2005', 3.00, 1350000, 'KHMT')

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

INSERT INTO MONHOC VALUES ('THDC', 'Tin hoc dai cuong', 4, 1, 'KHMT')
INSERT INTO MONHOC VALUES ('CTRR', 'Cau truc roi rac', 5, 0, 'KHMT')
INSERT INTO MONHOC VALUES ('CSDL', 'Co so du lieu', 3, 1, 'HTTT')
INSERT INTO MONHOC VALUES ('CTDLGT', 'Cau truc du lieu va giai thuat', 3, 1, 'KHMT')
INSERT INTO MONHOC VALUES ('PTTKTT', 'Phan tich thiet ke thuat toan', 3, 0, 'KHMT')
INSERT INTO MONHOC VALUES ('DHMT', 'Do hoa may tinh', 3, 1, 'KHMT')
INSERT INTO MONHOC VALUES ('KTMT', 'Kien truc may tinh', 3, 0, 'KTMT')
INSERT INTO MONHOC VALUES ('TKCSDL', 'Thiet ke CO so du lieu', 3, 1, 'HTTT')
INSERT INTO MONHOC VALUES ('PTTKHTTT', 'Phan tich thiet ke he thong thong tin', 4, 1, 'HTTT')
INSERT INTO MONHOC VALUES ('HDH', 'He dieu hanh', 4, 0, 'KTMT')
INSERT INTO MONHOC VALUES ('NMCNPM', 'Nhap mon cong nghe phan mem', 3, 0, 'CNPM')
INSERT INTO MONHOC VALUES ('LTCFW', 'Lap trinh C for win', 3, 1, 'CNPM')
INSERT INTO MONHOC VALUES ('LTHDT', 'Lap trinh huong doi tuong', 3, 1, 'CNPM')

INSERT INTO GIANGDAY VALUES ('K11', 'THDC', 'GV07', 1, 2006, '2/1/2006', '12/5/2006')
INSERT INTO GIANGDAY VALUES ('K12', 'THDC', 'GV06', 1, 2006, '2/1/2006', '12/5/2006')
INSERT INTO GIANGDAY VALUES ('K13', 'THDC', 'GV15', 1, 2006, '2/1/2006', '12/5/2006')
INSERT INTO GIANGDAY VALUES ('K11', 'CTRR', 'GV02', 1, 2006, '9/1/2006', '17/5/2006')
INSERT INTO GIANGDAY VALUES ('K12', 'CTRR', 'GV02', 1, 2006, '9/1/2006', '17/5/2006')
INSERT INTO GIANGDAY VALUES ('K13', 'CTRR', 'GV08', 1, 2006, '9/1/2006', '17/5/2006')
INSERT INTO GIANGDAY VALUES ('K11', 'CSDL', 'GV05', 2, 2006, '1/6/2006', '15/7/2006')
INSERT INTO GIANGDAY VALUES ('K12', 'CSDL', 'GV09', 2, 2006, '1/6/2006', '15/7/2006')
INSERT INTO GIANGDAY VALUES ('K13', 'CTDLGT', 'GV15', 2, 2006, '1/6/2006', '15/7/2006')
INSERT INTO GIANGDAY VALUES ('K13', 'CSDL', 'GV05', 3, 2006, '1/8/2006', '15/12/2006')
INSERT INTO GIANGDAY VALUES ('K13', 'DHMT', 'GV07', 3, 2006, '1/8/2006', '15/12/2006')
INSERT INTO GIANGDAY VALUES ('K11', 'CTDLGT', 'GV15', 3, 2006, '1/8/2006', '15/12/2006')
INSERT INTO GIANGDAY VALUES ('K12', 'CTDLGT', 'GV15', 3, 2006, '1/8/2006', '15/12/2006')
INSERT INTO GIANGDAY VALUES ('K11', 'HDH', 'GV04', 1, 2007, '2/1/2007', '18/2/2007')
INSERT INTO GIANGDAY VALUES ('K12', 'HDH', 'GV04', 1, 2007, '2/1/2007', '20/3/2007')
INSERT INTO GIANGDAY VALUES ('K11', 'DHMT', 'GV07', 1, 2007, '18/2/2007', '20/3/2007')

INSERT INTO DIEUKIEN VALUES ('CSDL', 'CTRR')
INSERT INTO DIEUKIEN VALUES ('CSDL', 'CTDLGT')
INSERT INTO DIEUKIEN VALUES ('CTDLGT', 'THDC')
INSERT INTO DIEUKIEN VALUES ('PTTKTT', 'THDC')
INSERT INTO DIEUKIEN VALUES ('PTTKTT', 'CTDLGT')
INSERT INTO DIEUKIEN VALUES ('DHMT', 'THDC')
INSERT INTO DIEUKIEN VALUES ('LTHDT', 'THDC')
INSERT INTO DIEUKIEN VALUES ('PTTKHTTT', 'CSDL')

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

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



ALTER TABLE HOCVIEN ADD GHICHU VARCHAR(100)
ALTER TABLE HOCVIEN ADD DIEMTB NUMERIC(4,2)
ALTER TABLE HOCVIEN ADD XEPLOAI VARCHAR(10)

ALTER TABLE GIAOVIEN ADD CONSTRAINT GV_GIOITINH CHECK (GIOITINH='Nam' OR GIOITINH='Nu')
ALTER TABLE HOCVIEN ADD CONSTRAINT HV_GIOITINH CHECK (GIOITINH='Nam' OR GIOITINH='Nu')


ALTER TABLE KETQUATHI ADD CONSTRAINT DIEMSO_MLT CHECK (DIEM>=0 AND DIEM<=10) 
ALTER TABLE HOCVIEN ADD CONSTRAINT GT_DIEMTB CHECK (DIEMTB>=0 AND DIEMTB<=10)

ALTER TABLE KETQUATHI ADD CONSTRAINT GH_LANTHI CHECK (LANTHI<=3)

ALTER TABLE GIANGDAY ADD CONSTRAINT GH_HOCKY CHECK (HOCKY>=1 AND HOCKY<=3)

ALTER TABLE GIAOVIEN ADD CONSTRAINT GT_HOCVI CHECK (HOCVI='CN' OR HOCVI='KS' OR HOCVI='Ths' OR HOCVI='TS' OR HOCVI='PTS')


-- TANG HE SO CHO GIAO VIEN LA TRUONG KHOA

UPDATE GIAOVIEN
SET HESO=HESO+0.2
WHERE MAGV IN (SELECT TRGKHOA FROM KHOA)

-- CAP NHAT GHICHU CHO HOC VIEN

UPDATE HOCVIEN
SET GHICHU='Cam thi'
WHERE HOCVIEN.MAHV IN (SELECT MAHV FROM KETQUATHI WHERE LANTHI=3 AND DIEM<5)

-- CAP NHAT DIEM TB CHO HOC VIEN

SELECT MAHV, MAMH, MAX(LANTHI) AS LANTHI INTO TEMP FROM KETQUATHI GROUP BY MAHV, MAMH

SELECT KETQUATHI.MAHV, AVG(DIEM) AS DIEM INTO DIEM_TABLE FROM KETQUATHI
JOIN TEMP ON KETQUATHI.MAHV = TEMP.MAHV AND KETQUATHI.MAMH = TEMP.MAMH AND KETQUATHI.LANTHI = TEMP.LANTHI 
GROUP BY KETQUATHI.MAHV 
 
UPDATE HOCVIEN SET DIEMTB = (SELECT DIEM FROM DIEM_TABLE WHERE HOCVIEN.MAHV = DIEM_TABLE.MAHV) 
SELECT * FROM HOCVIEN

DROP TABLE TEMP
DROP TABLE DIEM_TABLE

-- IN RA LOP TRUONG CUA TUNG LOP
SELECT MAHV, HO, TEN, NGSINH, MALOP FROM HOCVIEN 
WHERE MAHV IN (SELECT TRGLOP FROM LOP)

-- IN RA KET QUA THI MON CTRR CUA LOP K12

SELECT HOCVIEN.MAHV, HOCVIEN.HO, HOCVIEN.TEN, KETQUATHI.LANTHI, KETQUATHI.DIEM 
FROM HOCVIEN JOIN KETQUATHI ON HOCVIEN.MAHV = KETQUATHI.MAHV
WHERE HOCVIEN.MALOP = 'K12' AND KETQUATHI.MAMH = 'CTRR'
ORDER BY HOCVIEN.TEN, HOCVIEN.HO

-- IN RA DANH SACH HOC VIEN THI LAN THU NHAT DA DAT

SELECT HOCVIEN.MAHV, HOCVIEN.HO, HOCVIEN.TEN FROM HOCVIEN JOIN KETQUATHI ON HOCVIEN.MAHV = KETQUATHI.MAHV
WHERE KETQUATHI.LANTHI = 1 AND KETQUATHI.KQUA='Dat'

-- IN RA DANH SACH HOC VIEN CUA LOP K11 THI MON CTRR LAN THI THU 1 KHONG DAT

SELECT HOCVIEN.MAHV, HOCVIEN.HO, HOCVIEN.TEN FROM HOCVIEN JOIN KETQUATHI ON HOCVIEN.MAHV = KETQUATHI.MAHV JOIN MONHOC ON KETQUATHI.MAMH = MONHOC.MAMH
JOIN LOP ON HOCVIEN.MALOP = LOP.MALOP
WHERE HOCVIEN.MALOP = 'K11' AND KETQUATHI.MAMH = 'CTRR' AND KETQUATHI.KQUA = 'Khong Dat' and KETQUATHI.LANTHI = 1