22521543_BTTH3.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)

-- NGDK <= NGHD

CREATE TRIGGER TRG_NGDK_NGHD ON HOADON
AFTER INSERT, UPDATE AS
BEGIN 
    IF EXISTS (
        SELECT * FROM INSERTED, KHACHHANG
        WHERE (INSERTED.MAKH = KHACHHANG.MAKH AND INSERTED.NGHD >= KHACHHANG.NGDK)
    )
    BEGIN
        ROLLBACK TRANSACTION
        PRINT('NGAY MUA HANG PHAI LON HON HON NGAY DANG KY')
    END
END
GO



CREATE TRIGGER TRG_NGHD_NGDK ON KHACHHANG
AFTER UPDATE AS
BEGIN
    IF EXISTS(
        SELECT * FROM INSERTED, HOADON
        WHERE (INSERTED.MAKH = HOADON.MAKH AND INSERTED.NGDK <= HOADON.NGHD)
    )
    BEGIN
        ROLLBACK TRANSACTION
        PRINT('NGAY DANG KY KHACH HANG THANH VIEN PHAI NHO HON NGAY SINH')
    END
END
GO

-- NGVL <= NGHD

CREATE TRIGGER TRG_NGVL_NGHD ON HOADON
AFTER INSERT, UPDATE AS
BEGIN
IF EXISTS(
    SELECT * FROM INSERTED, NHANVIEN
    WHERE (INSERTED.MANV = NHANVIEN.MANV AND INSERTED.NGHD >= NHANVIEN.NGVL)
)
BEGIN
    ROLLBACK TRANSACTION
    PRINT('NGAY MUA HANG PHAI LON HON NGAY VAO LAM')
END
END
GO

CREATE TRIGGER TRG_NGHD_NGVL ON NHANVIEN
AFTER UPDATE AS
BEGIN
IF EXISTS(
    SELECT * FROM INSERTED, HOADON
    WHERE (INSERTED.MANV = HOADON.MANV AND INSERTED.NGVL <= HOADON.NGHD)
)
BEGIN
    ROLLBACK TRANSACTION
    PRINT('NGAY VAO LAM PHAI NHO HON NGAY MUA HANG')
END
END
GO

-- MOI MOT HOA DON PHAI CO IT NHAT 1 CHI TIET HOA DON

CREATE TRIGGER TRG_CTHD ON HOADON
AFTER DELETE, UPDATE AS
BEGIN
DECLARE @SLHD INT
SELECT @SLHD=COUNT(*) FROM CTHD, DELETED WHERE CTHD.SOHD=DELETED.SOHD 
IF (@SLHD<1)
BEGIN
    ROLLBACK TRANSACTION
    PRINT('MOI MOT HOA DON PHAI CO IT NHAT 1 CHI TIET HOA DON')
END
END
GO

-- TRIGIA CUA MOT HOA DON LA SOLUONG*GIA CUA SAN PHAM TRONG CHI TIET HOA DON

CREATE TRIGGER TRG_TRIGIA ON HOADON
AFTER INSERT AS
BEGIN
DECLARE @SOHD INT
SELECT @SOHD=SOHD FROM INSERTED
UPDATE HOADON SET TRIGIA=0 WHERE HOADON.SOHD=@SOHD
END 
GO

CREATE TRIGGER UPDATE_HD ON HOADON
AFTER UPDATE AS
BEGIN
	UPDATE HOADON SET TRIGIA = (SELECT TRIGIA FROM deleted)
	WHERE SOHD = (SELECT SOHD FROM inserted)
END
GO

CREATE TRIGGER UPDATE_HD_CTHD ON CTHD 
AFTER INSERT AS
BEGIN
	DECLARE @SL INT
    DECLARE @GIA MONEY

	SELECT @GIA=GIA, @SL=SL FROM inserted, SANPHAM
	WHERE inserted.MASP = SANPHAM.MASP
	
    UPDATE HOADON
	SET TRIGIA = TRIGIA + @SL * @GIA
END
GO

-- DOANH SO CUA KHACH HANG LA TONG TRIGIA CUA CAC HOA DON CUA KHACH HANG DO DA MUA

CREATE TRIGGER TRG_DOANHSO ON KHACHHANG
AFTER INSERT, UPDATE AS
BEGIN
DECLARE @DS MONEY
DECLARE @TG MONEY

SELECT @TG=SUM(TRIGIA) FROM INSERTED,HOADON WHERE HOADON.MAKH=INSERTED.MAKH
SELECT @DS=DOANHSO FROM INSERTED

IF(@DS<>@TG)
BEGIN   
    ROLLBACK TRANSACTION
    PRINT('DOANH SO CUA KHACH HANG LA TONG TRIGIA CUA CAC HOA DON CUA KHACH HANG DO DA MUA')
END
END
GO

-- 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
        ) 
    )
)

-- TIM SO HOADON DA MUA TAT CA SANPHAM DO SINGAPORE SAN XUAT

SELECT HOADON.SOHD FROM HOADON INNER JOIN CTHD ON CTHD.SOHD=HOADON.SOHD INNER JOIN SANPHAM ON CTHD.MASP=SANPHAM.MASP WHERE NUOCSX='Singapore'
GROUP BY HOADON.SOHD HAVING COUNT(*)=(SELECT COUNT(*) FROM SANPHAM WHERE NUOCSX='Singapore')

-- TIM SO HOADON MUA IT NHAT TAT CA SAN PHAM DO SINGAPORE SAN XUAT TRONG NAM 2006

SELECT HOADON.SOHD FROM HOADON INNER JOIN CTHD ON CTHD.SOHD=HOADON.SOHD INNER JOIN SANPHAM ON CTHD.MASP=SANPHAM.MASP WHERE NUOCSX='Singapore' AND YEAR(NGHD)=2006
GROUP BY HOADON.SOHD HAVING COUNT(*)=(SELECT COUNT(*) FROM SANPHAM WHERE NUOCSX='Singapore')


-- TIM SO HOA DON KHONG PHAI KHACH HANG THANH VIEN MUA HANG 

SELECT COUNT(SOHD) AS SLHD_NOTKH FROM HOADON
WHERE NOT EXISTS (
    SELECT MAKH FROM KHACHHANG WHERE MAKH=HOADON.MAKH
)

-- HOA DON CO TRIGIA CAO NHAT

SELECT MAX(TRIGIA) AS TRIGIA_MAX FROM HOADON

-- HOA DON CO TRI GIA THAP NHAT

SELECT MIN(TRIGIA) AS TRIGIA_MIN FROM HOADON

-- CO BAO NHIEU SAN PHAM KHAC NHAU DUOC BAN RA TRONG NAM 2006

SELECT COUNT(MASP) AS SLSP FROM SANPHAM
WHERE MASP IN (
    SELECT MASP FROM CTHD INNER JOIN HOADON ON CTHD.SOHD=HOADON.SOHD
    WHERE YEAR(NGHD)=2006
)

-- TRI GIA TRUNG BINH CUA CAC HOA DON BAN RA TRONG NAM 2006

SELECT AVG(TRIGIA) AS TRIGIA_AVG FROM HOADON
WHERE YEAR(NGHD)=2006

-- DOANH THU BAN HANG TRONG NAM 2006

SELECT SUM(TRIGIA) AS DOANHTHU FROM HOADON
WHERE YEAR(NGHD)=2006

-- TIM RA SO HOADON CO DOANH THU CAO NHAT TRONG NAM 2006

SELECT SOHD FROM HOADON
WHERE YEAR(NGHD)=2006 AND HOADON.TRIGIA = (
    SELECT MAX(TRIGIA) FROM HOADON WHERE YEAR(NGHD)=2006
)

-- TIM KHACH HANG DA MUA HOA DON CO SOHD DOANH THU CAO NHAT NAM 2006

SELECT KHACHHANG.MAKH, KHACHHANG.HOTEN FROM KHACHHANG INNER JOIN HOADON ON KHACHHANG.MAKH=HOADON.MAKH
WHERE YEAR(NGHD)=2006 AND HOADON.TRIGIA = (
    SELECT MAX(TRIGIA) FROM HOADON WHERE YEAR(NGHD)=2006
)

-- IN RA BA KHACH HANG DAU TIEN THEO THU TU DOANH SO GIAM DAN

SELECT TOP 3 MAKH, HOTEN, DOANHSO FROM KHACHHANG ORDER BY DOANHSO DESC

-- IN RA  SAN PHAM CÓ GIA BANG 1 TRONG 3 MUC GIA CAO NHAT

SELECT MASP, TENSP FROM SANPHAM WHERE GIA IN (SELECT TOP 3 GIA FROM SANPHAM ORDER BY GIA DESC)

-- IN RA SAN PHAM DO THAILAN SAN XUAT CO GIA BANG 1 TRONG 3 MUC GIA CAO NHAT 

SELECT MASP, TENSP FROM SANPHAM WHERE NUOCSX='Thai Lan' AND GIA IN (SELECT TOP 3 GIA FROM SANPHAM ORDER BY GIA DESC)

-- IN RA SAN PHAM DO TRUNG QUOC SAN XUAT CO GIA BANG 1 TRONG 3 MUC GIA CAO NHAT DO TRUNG QUOC SAN XUAT

SELECT MASP, TENSP FROM SANPHAM WHERE NUOCSX='Trung Quoc' AND GIA IN (SELECT TOP 3 GIA FROM SANPHAM WHERE NUOCSX='Trung Quoc' ORDER BY GIA DESC)

-- IN RA 3 KHACH HANG CO DOANH THU CAO NHAT XEP HANG THEO DOANH SO

SELECT TOP 3 MAKH, HOTEN, DOANHSO FROM KHACHHANG ORDER BY DOANHSO DESC

-- TONG SO SAN PHAM DO TRUNG QUOC SAN XUAT

SELECT COUNT(MASP) AS SLSP_TQ FROM SANPHAM WHERE NUOCSX='Trung Quoc'

-- GIA SAN PHAM CAO NHAT CUA MOI NUOC 

SELECT NUOCSX, MAX(GIA) AS GIA_MAX FROM SANPHAM GROUP BY NUOCSX
SELECT NUOCSX, MIN(GIA) AS GIA_MIN FROM SANPHAM GROUP BY NUOCSX
SELECT NUOCSX,AVG(GIA) AS GIA_AVG FROM SANPHAM GROUP BY NUOCSX

-- DOANH THU BAN MOI NGAY
SELECT NGHD, SUM(TRIGIA) FROM HOADON GROUP BY NGHD

-- TINH TONG SO LUONG SAN PHAM BAN RA TRONG THANG 10/2006

SELECT MASP, SUM(SL) FROM CTHD
INNER JOIN HOADON ON CTHD.SOHD = HOADON.SOHD
WHERE MONTH(NGHD) = 10 AND YEAR(NGHD) = 2006
GROUP BY MASP

-- TINH TONG DOANH THU BAN HANG TUNG THANG 

SELECT DISTINCT MONTH(NGHD), SUM(TRIGIA) FROM HOADON 
WHERE YEAR(NGHD) = 2006 
GROUP BY MONTH(NGHD)

-- TIM HOA DON MUA IT NHAT 4 SAN PHAM KHAC NHAU NAM 2006

SELECT SOHD FROM CTHD
GROUP BY SOHD
HAVING COUNT(DISTINCT MASP) >= 4

-- HOA DON MUA 3 SAN PHAM KHAC NHAU DO VIET NAM SAN XUAT

SELECT SOHD FROM CTHD
INNER JOIN SANPHAM ON SANPHAM.MASP = CTHD.MASP
WHERE NUOCSX = 'Viet Nam'
GROUP BY SOHD
HAVING COUNT(DISTINCT SANPHAM.MASP) = 3 

-- KHACH HANG MUA HANG NHIEU NHAT 

SELECT TOP 1 MAKH, COUNT(SOHD) AS SL FROM HOADON
GROUP BY MAKH 
ORDER BY SL DESC

-- THANG MAY TRONG NAM 2006 CO DOANH THU CAO NHAT

SELECT TOP 1 MONTH(NGHD), SUM(TRIGIA) AS DoanhThu FROM HOADON
WHERE YEAR(NGHD) = 2006
GROUP BY MONTH(NGHD) 
ORDER BY DoanhThu DESC

-- SAN PHAM CO SAN LUONG BAN RA THAP NHAT NAM 2006 

SELECT MASP, SUM(SL) FROM CTHD
GROUP BY MASP
HAVING SUM(SL) <= ALL (
    SELECT SUM(CTHD.SL) FROM CTHD
    INNER JOIN SANPHAM ON SANPHAM.MASP = CTHD.MASP
    GROUP BY CTHD.MASP
)

-- TU MOI NUOC TIM RA SAN PHAM CO GIA CAO NHAT CUA NUOC DO

SELECT NUOCSX, MASP, TENSP, GIA FROM SANPHAM AS SP
WHERE GIA = (
    SELECT MAX(GIA) FROM SANPHAM
    WHERE NUOCSX = SP.NUOCSX
    GROUP BY NUOCSX
)

-- TIM NUOC SAN XUAT CO IT NHAT 3 SAN PHAM KHAC NHAU

SELECT DISTINCT NUOCSX FROM SANPHAM
GROUP BY NUOCSX
HAVING COUNT(DISTINCT GIA) >= 3

-- TRONG 10 KHACH HANG MUA NHIEU NHAT TIM KHACH HANG CO SO LAN MUA HANG NHIEU NHAT 

SELECT TOP 1 MAKH, COUNT(SOHD) AS SL FROM HOADON
WHERE MAKH IN (
    SELECT TOP 10 MAKH FROM KHACHHANG 
    ORDER BY DOANHSO DESC
)
GROUP BY MAKH
ORDER BY SL DESC 

-- 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

-- DANH SACH HOC VIEN CUA LOP "K" THI MON CTRR KHONG DAT (O TAT CA LAN THI)


SELECT HOCVIEN.MAHV, HOCVIEN.HO, HOCVIEN.TEN FROM LOP 
JOIN HOCVIEN ON LOP.MALOP = HOCVIEN.MALOP 
JOIN KETQUATHI ON HOCVIEN.MAHV = KETQUATHI.MAHV 
WHERE LOP.TENLOP = 'K' AND KETQUATHI.MAMH = 'CTRR' AND KETQUATHI.KQUA = 'Khong dat'
GROUP BY HOCVIEN.MAHV, HOCVIEN.HO, HOCVIEN.TEN
HAVING COUNT(DISTINCT KETQUATHI.LANTHI) = (
    SELECT COUNT(DISTINCT LANTHI) FROM KETQUATHI AS KQ
    WHERE HOCVIEN.MAHV = KQ.MAHV AND KQ.MAMH = 'CTRR'
)

-- MON HOC MA GIAO VIEN TEN "Tran Tam Thanh" DAY TRONG HOC KY I NAM 2006

SELECT DISTINCT MAMH FROM GIANGDAY
INNER JOIN GIAOVIEN ON GIAOVIEN.MAGV = GIANGDAY.MAGV
WHERE HOTEN = 'Tran Tam Thanh' AND HOCKY = 1 AND NAM = 2006

-- TEN NHUNG MIN HOC MA GIAO VIEN CHU NHIEM LOP "K11" DAY TRONG HOC KY I NAM 2006

SELECT MONHOC.MAMH, MONHOC.TENMH FROM GIANGDAY  
INNER JOIN GIAOVIEN ON GIANGDAY.MAGV = GIAOVIEN.MAGV
INNER JOIN MONHOC ON GIANGDAY.MAMH = MONHOC.MAMH
INNER JOIN LOP ON LOP.MALOP = GIANGDAY.MALOP
WHERE LOP.MALOP = 'K11' AND LOP.MAGVCN = GIAOVIEN.MAGV AND GIANGDAY.HOCKY = 1 AND GIANGDAY.NAM = 2006

-- HO TEN LOP TRUONG CUA LOP CO GIAO VIEN "Nguyen To Lan" DAY MON "Co So Du Lieu"

SELECT HOCVIEN.HO, HOCVIEN.TEN FROM GIANGDAY  
INNER JOIN GIAOVIEN ON GIANGDAY.MAGV = GIAOVIEN.MAGV
INNER JOIN MONHOC ON GIANGDAY.MAMH = MONHOC.MAMH
INNER JOIN LOP ON LOP.MALOP = GIANGDAY.MALOP
INNER JOIN HOCVIEN ON HOCVIEN.MAHV = LOP.TRGLOP
WHERE GIAOVIEN.HOTEN = 'Nguyen To Lan' AND MONHOC.TENMH = 'Co So Du Lieu'

-- DANH SACH NHUNG MON HOC PHAI HOC LIEN TRUOC MON "Co So Du Lieu"

SELECT MAMH, TENMH FROM MONHOC 
WHERE MAMH IN (
    SELECT DISTINCT MAMH_TRUOC FROM MONHOC 
    INNER JOIN DIEUKIEN ON DIEUKIEN.MAMH = MONHOC.MAMH 
    WHERE MONHOC.TENMH = 'Co So Du Lieu'
)

-- MON "Cau Truc Roi Rac" LA MON HOC BAT BUOC PHAI HOC LIEN TRUOC MON HOC NAO

SELECT DIEUKIEN.MAMH, MONHOC.TENMH FROM DIEUKIEN 
INNER JOIN MONHOC ON MONHOC.MAMH = DIEUKIEN.MAMH
WHERE MAMH_TRUOC = (
    SELECT MAMH FROM MONHOC 
    WHERE TENMH = 'Cau Truc Roi Rac'
)

-- HO TEN GIAO VIEN DAY MON CTRR CHO CA HAI LOP "K11" VA "K12" TRONG CUNG HOC KY 1 NAM 2006

SELECT HOTEN FROM GIAOVIEN
WHERE MAGV IN (
    SELECT MAGV FROM GIANGDAY WHERE MALOP = 'K11'
    INTERSECT
    SELECT MAGV FROM GIANGDAY WHERE MALOP = 'K12'
)

-- HOC VIEN THI KHONG DAT MON CSDL O LAN THI 1 NHUNG CHUA THI LAI MON NAY

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

-- GIAO VIEN KHONG DUOC PHAN CONG GIANG DAY O BAT KY MON HOC NAO


SELECT MAGV, HOTEN FROM GIAOVIEN 
WHERE MAGV IN (
    SELECT MAGV FROM GIAOVIEN
    EXCEPT
    SELECT MAGV FROM GIANGDAY
)

-- GIAO VIEN KHONG DUOC PHAN CONG GIANG DAY MON HOC NAO THUOC KHOA GIAO VIEN DO PHU TRACH


SELECT MAGV, HOTEN FROM GIAOVIEN
WHERE NOT EXISTS (
        SELECT * FROM GIANGDAY
        WHERE GIANGDAY.MAGV = GIAOVIEN.MAGV AND GIANGDAY.MAMH IN (
            SELECT MAMH FROM MONHOC 
            WHERE MONHOC.MAKHOA = GIAOVIEN.MAKHOA
        )
)

-- HO TEN HOC VIEN THUOC LOP "K11" THI MOT MON BAT KY QUA 3 LAN VAN "Khong Dat" HOAC THI LAN THU 2 MON `CTRR` DUOC 5 DIEM

SELECT DISTINCT KETQUATHI.MAHV FROM KETQUATHI 
WHERE LANTHI = 3 AND KQUA = 'Khong dat'
UNION
SELECT DISTINCT KETQUATHI.MAHV FROM KETQUATHI 
INNER JOIN HOCVIEN ON KETQUATHI.MAHV = HOCVIEN.MAHV
WHERE MALOP = 'K11' AND LANTHI = 2 AND DIEM = 5 AND MAMH = 'CTRR'

-- HO TEN GIAO VIEN DAY MON CTRR CHO IT NHAT HAI LOP TRONG CUNG MOT HOC KY CUA MOT MON HOC

SELECT MAGV, COUNT(MALOP) FROM GIANGDAY
WHERE MAMH = 'CTRR'
GROUP BY MAGV, HOCKY, NAM
HAVING COUNT(MALOP) >= 2

-- DANH SACH HOC VIEN VA DIEM THI MON CSDL (CHI LAY DIEM LAN THI SAU CUNG)


SELECT MAHV FROM KETQUATHI AS KQ
WHERE MAMH = 'CSDL' AND LANTHI = (
    SELECT MAX(LANTHI) FROM KETQUATHI
    WHERE MAMH = 'CSDL'
    GROUP BY MAHV
    HAVING MAHV = KQ.MAHV
)

-- DANH SACH HOC VIEN VA DIEM THI MON CO SO DU LIEU (CHI LAY DIEM CAO NHAT CUA CAC LAN THI)

SELECT KETQUATHI.MAHV, MAX(DIEM) AS DIEM FROM KETQUATHI
INNER JOIN MONHOC ON KETQUATHI.MAMH = MONHOC.MAMH
WHERE TENMH = 'Co so du lieu'
GROUP BY MAHV

-- KHOA NAO DUOC THANH LAP SOM NHAT

SELECT MAKHOA, TENKHOA FROM KHOA 
WHERE NGTLAP <= ALL (
    SELECT NGTLAP FROM KHOA
)

-- CO BAO NHIEU GIAO VIEN CO HOC HAM LA "GS" HOAC "PGS"

SELECT HOCHAM, COUNT(MAGV) AS SOGV FROM GIAOVIEN
GROUP BY HOCHAM
HAVING HOCHAM IN ('GS', 'PGS')

-- THONG KE CO BAO NHIEU GIAO VIEN CO HOC VI LA "CN", "KS", "Ths", "TS", "PTS" TRONG MOI KHOA

SELECT MAKHOA, HOCVI, COUNT(MAGV) FROM GIAOVIEN
GROUP BY MAKHOA, HOCVI
ORDER BY MAKHOA

-- MOI MON HOC THONG KE SO LUONG HOC VIEN THEO KET QUA DAT VA KHONG DAT

SELECT MAMH, KQUA, COUNT(MAHV) FROM KETQUATHI AS KQ
WHERE LANTHI IN (
    SELECT MAX(LANTHI) FROM KETQUATHI
    GROUP BY MAHV, MAMH
    HAVING MAHV = KQ.MAHV AND MAMH = KQ.MAMH
)
GROUP BY MAMH, KQUA

-- GIAO VIEN LA GIAO VIEN CHU NHIEM VA DAY LOP DO IT NHAT MOT MON HOC

SELECT MAGV, HOTEN FROM LOP
INNER JOIN GIAOVIEN ON LOP.MAGVCN = GIAOVIEN.MAGV
WHERE EXISTS (
    SELECT * FROM GIANGDAY 
    WHERE MAGV = LOP.MAGVCN AND MALOP = LOP.MALOP
)

-- HO TEN LOP TRUONG LOP CO SI SO CAO NHAT

SELECT TRGLOP FROM LOP 
WHERE SISO >= ALL (
    SELECT SISO FROM LOP
)

-- HOC VIEN CO SO MON DAT DIEM 9, 10 NHIEU NHAT

SELECT HOCVIEN.MAHV, HOCVIEN.HO, HOCVIEN.TEN FROM HOCVIEN
INNER JOIN KETQUATHI ON KETQUATHI.MAHV = HOCVIEN.MAHV
WHERE KETQUATHI.DIEM >= 9
GROUP BY HOCVIEN.MAHV, HOCVIEN.HO, HOCVIEN.TEN
HAVING COUNT(KETQUATHI.DIEM) >= ALL (
    SELECT COUNT(DIEM) FROM KETQUATHI
    WHERE DIEM = 9 OR DIEM = 10
    GROUP BY MAHV
)

-- TRONG TUNG LOP, TIM HOC VIEN CO SO MON DAT DIEM 9, 10 NHIEU NHAT

SELECT HOCVIEN.MAHV, HOCVIEN.HO, HOCVIEN.TEN FROM HOCVIEN 
WHERE HOCVIEN.MAHV IN (
    SELECT HV.MAHV FROM KETQUATHI
    INNER JOIN HOCVIEN AS HV ON HV.MAHV = KETQUATHI.MAHV
    WHERE (KETQUATHI.DIEM >= 9) AND HV.MALOP = HOCVIEN.MALOP
    GROUP BY HV.MAHV
    HAVING COUNT(KETQUATHI.DIEM) >= ALL (
        SELECT COUNT(KQ2.DIEM) FROM KETQUATHI AS KQ2
        INNER JOIN HOCVIEN AS HV2 ON HV2.MAHV = KQ2.MAHV
        WHERE KQ2.DIEM >= 9 AND HV2.MALOP = HOCVIEN.MALOP
        GROUP BY HV2.MAHV
    )
)

-- TRONG TUNG HOC KY CUA TUNG NAM, MOI GIAO VIEN PHAN CONG DAY BAO NHIEU MON HOC, BAO NHIEU LOP

SELECT GIAOVIEN.MAGV, GIAOVIEN.HOTEN, GIANGDAY.NAM, GIANGDAY.HOCKY, COUNT(DISTINCT GIANGDAY.MAMH) AS SOMON, COUNT(DISTINCT GIANGDAY.MALOP) AS SOLOP FROM GIANGDAY  
INNER JOIN GIAOVIEN ON GIANGDAY.MAGV = GIAOVIEN.MAGV
GROUP BY GIAOVIEN.MAGV, GIAOVIEN.HOTEN, GIANGDAY.NAM, GIANGDAY.HOCKY

-- TRONG TUNG HOC KY CUA TUNG NAM, GIAO VIEN GIANG DAY NHIEU NHAT

SELECT GIANGDAY.NAM, GIANGDAY.HOCKY, GIAOVIEN.MAGV, GIAOVIEN.HOTEN FROM GIANGDAY
JOIN GIAOVIEN ON GIANGDAY.MAGV = GIAOVIEN.MAGV
GROUP BY GIAOVIEN.MAGV, GIAOVIEN.HOTEN, GIANGDAY.NAM, GIANGDAY.HOCKY
HAVING COUNT(MAMH) >= ALL (
    SELECT COUNT(MAMH) FROM GIANGDAY AS GD
    WHERE GD.NAM = GIANGDAY.NAM AND GIANGDAY.HOCKY = GD.HOCKY
    GROUP BY GD.MAGV
)

-- MON HOC CO NHIEU HOC VIEN THI KHONG DAT O LAN THI THU NHAT

SELECT MONHOC.MAMH, MONHOC.TENMH FROM KETQUATHI 
JOIN MONHOC ON KETQUATHI.MAMH = MONHOC.MAMH
WHERE LANTHI = 1 AND KQUA = 'Khong dat'
GROUP BY MONHOC.MAMH, MONHOC.TENMH
HAVING COUNT(LANTHI) >= ALL (
    SELECT COUNT(LANTHI) FROM KETQUATHI AS KQ 
    JOIN MONHOC AS MH ON KQ.MAMH = MH.MAMH
    WHERE LANTHI = 1 AND KQUA = 'Khong dat'
    GROUP BY MH.MAMH, MH.TENMH
)

-- HOC VIEN THI MON NAO CUNG DAT LAN 1

SELECT HOCVIEN.MAHV, HOCVIEN.HO, HOCVIEN.TEN FROM KETQUATHI
JOIN HOCVIEN ON KETQUATHI.MAHV = HOCVIEN.MAHV 
WHERE LANTHI = 1 AND KQUA = 'Dat'
GROUP BY HOCVIEN.MAHV, HOCVIEN.HO, HOCVIEN.TEN
HAVING COUNT(DISTINCT KETQUATHI.MAMH) = (
    SELECT COUNT(DISTINCT KETQUATHI.MAMH) FROM KETQUATHI 
    WHERE MAHV = HOCVIEN.MAHV
)

-- HOC VIEN THI MON NAO CUNG DAT (CHI XET LAN THI SAU CUNG)

SELECT HOCVIEN.MAHV, HOCVIEN.HO, HOCVIEN.TEN FROM KETQUATHI
JOIN HOCVIEN ON KETQUATHI.MAHV = HOCVIEN.MAHV 
WHERE LANTHI = (
    SELECT MAX(LANTHI) FROM KETQUATHI AS KQ
    WHERE KQ.MAHV = KETQUATHI.MAHV AND KQ.MAMH = KETQUATHI.MAMH
) AND KQUA = 'Dat'
GROUP BY HOCVIEN.MAHV, HOCVIEN.HO, HOCVIEN.TEN
HAVING COUNT(DISTINCT KETQUATHI.MAMH) = (
    SELECT COUNT(DISTINCT KETQUATHI.MAMH) FROM KETQUATHI 
    WHERE MAHV = HOCVIEN.MAHV
)

-- TIM HOC VIEN DA THI TAT CA CAC MON DEU DAT (CHI XET LAN THI 1)

SELECT HOCVIEN.MAHV, HOCVIEN.HO, HOCVIEN.TEN FROM KETQUATHI
JOIN HOCVIEN ON KETQUATHI.MAHV = HOCVIEN.MAHV 
WHERE LANTHI = 1 AND KQUA = 'Dat'
GROUP BY HOCVIEN.MAHV, HOCVIEN.HO, HOCVIEN.TEN
HAVING COUNT(DISTINCT KETQUATHI.MAMH) = (
    SELECT COUNT(DISTINCT MAMH) FROM MONHOC
)

-- TIM HOC VIEN DA THI TAT CA CAC MON DEU DAT (CHI XET LAN THI SAU CUNG)

SELECT HOCVIEN.MAHV, HOCVIEN.HO, HOCVIEN.TEN FROM KETQUATHI
JOIN HOCVIEN ON KETQUATHI.MAHV = HOCVIEN.MAHV 
WHERE LANTHI = (
    SELECT MAX(LANTHI) FROM KETQUATHI AS KQ
    WHERE KQ.MAHV = KETQUATHI.MAHV AND KQ.MAMH = KETQUATHI.MAMH
) AND KQUA = 'Dat'
GROUP BY HOCVIEN.MAHV, HOCVIEN.HO, HOCVIEN.TEN
HAVING COUNT(DISTINCT KETQUATHI.MAMH) = (
    SELECT COUNT(DISTINCT MAMH) FROM MONHOC
)

-- LOP TRUONG CUA MOT LOP PHAI LA HOC VIEN CUA LOP DO

CREATE TRIGGER CHECK_TRG_LOP ON LOP
AFTER UPDATE, INSERT AS 
BEGIN
    IF NOT EXISTS(
        SELECT * FROM HOCVIEN, inserted
        WHERE inserted.TRGLOP = HOCVIEN.MAHV AND HOCVIEN.MALOP = inserted.MALOP
    )
    BEGIN
        PRINT 'Lớp trưởng của một lớp phải là học viên của lớp đó.'
        ROLLBACK TRANSACTION
    END
END

-- TRUONG KHOA PHAI LA GIAO VIEN CO HOC VI "TS" HOAC "PTS"

CREATE TRIGGER CHECK_TRG_KHOA ON KHOA
AFTER UPDATE, INSERT AS 
BEGIN
    IF (NOT EXISTS(
        SELECT * FROM GIAOVIEN, inserted
        WHERE inserted.TRGKHOA = GIAOVIEN.MAGV AND GIAOVIEN.MAKHOA = inserted.MAKHOA AND GIAOVIEN.HOCVI IN ('TS', 'PTS')
    )) 
    BEGIN
        PRINT 'Trưởng khoa phải là giáo viên thuộc khoa và có học vị “TS” hoặc “PTS”.'
        ROLLBACK TRANSACTION
    END
END

-- HOC VIEN IT NHAT LA 18 TUOI

ALTER TABLE HOCVIEN ADD CONSTRAINT CHECK_TUOI_HV CHECK (YEAR(GETDATE()) - YEAR(NGSINH) >= 18)

-- GIANG DAY MOT MON HOC NGAY BAT DAU DEN NGAY KET THUC

ALTER TABLE GIANGDAY ADD CONSTRAINT CHECK_NGAY_GD CHECK (TUNGAY < DENNGAY)

-- GIAO VIEN KHI VAO LAM IT NHAT LA 22 TUOI

ALTER TABLE GIAOVIEN ADD CONSTRAINT CHECK_TUOI_GV CHECK (YEAR(NGVL) - YEAR(NGSINH) >= 22)

-- TAT CA MON HOC CO SO TIN CHI LY THUYET VA TIN CHI THUC HANH KHONG LECH NHAU QUA 3

ALTER TABLE MONHOC ADD CONSTRAINT CHECK_TINCHI CHECK (
    ABS(TCTH - TCLT) <= 3 OR TCTH = 0
)

-- HOC VIEN CHI DUOC THI MOT MON HOC NAO DO KHI LOP CUA HOC VIEN DA HOC XONG MON HOC NAY

CREATE TRIGGER CHECK_THI_MON_HOC_XONG ON KETQUATHI
AFTER UPDATE, INSERT AS 
BEGIN
    DECLARE @MAMH VARCHAR(10),
            @MAHV CHAR(5), 
            @MALOP CHAR(3),
            @NGTHI SMALLDATETIME,
            @DENNGAY SMALLDATETIME

    SELECT @MAMH = MAMH, @MAHV = MAHV, @NGTHI = NGTHI FROM inserted
    SELECT @MALOP = MALOP FROM HOCVIEN
    WHERE HOCVIEN.MAHV = @MAHV

    SELECT @DENNGAY = DENNGAY FROM GIANGDAY
    WHERE MALOP = @MALOP AND MAMH = @MAMH

    IF @NGTHI < @DENNGAY
    BEGIN
        PRINT 'Học viên chỉ được thi những môn mà lớp của học viên đó đã học xong.'
        ROLLBACK TRANSACTION
    END  
END

-- MOI HOC KY CUA NAM HOC, MOI LOP CHI HOC TOI DA 3 MON

CREATE TRIGGER CHECK_GIANGDAY_MONHOC ON GIANGDAY
AFTER INSERT, UPDATE AS
BEGIN
    DECLARE	@SL INT

    SELECT @SL = COUNT(*) FROM GIANGDAY, inserted
	WHERE GIANGDAY.MALOP = inserted.MALOP AND GIANGDAY.HOCKY = inserted.HOCKY AND GIANGDAY.NAM = inserted.NAM

    IF @SL > 3
    BEGIN
        ROLLBACK TRANSACTION
        PRINT 'Mỗi học kỳ của một năm học, một lớp chỉ được học tối đa 3 môn.'
    END
END

-- SI SO CUA MOT LOP BANG SO LUONG HOC VIEN THUOC LOP DO

CREATE TRIGGER CHECK_LOP_SS ON LOP
AFTER UPDATE, INSERT AS 
BEGIN
    DECLARE @SL INT,
            @SISO INT,
            @LOP CHAR(3)

    SELECT @LOP = MALOP FROM inserted
    
    SELECT @SL = COUNT(*) FROM HOCVIEN
    WHERE MALOP = @LOP

    SELECT @SISO = SISO FROM inserted

    IF @SL <> @SISO
    BEGIN
        PRINT 'Sỉ số của một lớp bằng với số lượng học viên thuộc lớp đó.'
        ROLLBACK TRANSACTION
    END
END

-- TRONG QUAN HE DIEU KIEN GIA TRI CUA THUOC TINH MAMH VA MAMH_TRUOC TRONG CUNG MOT BO KHONG DUOC GIONG NHAU< KHONG TON TAI HAI BO ("A","B") VA ("B","A")

CREATE TRIGGER CHECK_DIEU_KIEN ON DIEUKIEN
AFTER UPDATE, INSERT AS 
BEGIN
    DECLARE @TRUOC VARCHAR(10), @SAU VARCHAR(10)

    SELECT @TRUOC = MAMH_TRUOC, @SAU = MAMH FROM inserted

    IF (@TRUOC = @SAU) OR (EXISTS(
        SELECT * FROM DIEUKIEN 
        WHERE MAMH_TRUOC = @SAU AND MAMH = @TRUOC
    )) 
    BEGIN
        PRINT 'Trong quan hệ DIEUKIEN giá trị của thuộc tính MAMH và MAMH_TRUOC trong cùng một bộ không được giống nhau (“A”,”A”) và cũng không tồn tại hai bộ (“A”,”B”) và (“B”,”A”).'
        ROLLBACK TRANSACTION
    END
END 

-- CAC GIAO VIEN CO CUNG HOC VI, HOC HAM, HE SO LUONG THI MUC LUONG BANG NHAU

CREATE TRIGGER CHECK_GV_LUONG ON GIAOVIEN
AFTER UPDATE, INSERT AS 
BEGIN
    IF (EXISTS(
        SELECT * FROM inserted, GIAOVIEN
        WHERE inserted.HOCHAM = GIAOVIEN.HOCHAM AND inserted.HOCVI = GIAOVIEN.HOCVI AND inserted.MUCLUONG <> GIAOVIEN.MUCLUONG
    ))
    BEGIN
        PRINT 'Các giáo viên có cùng học vị, học hàm, hệ số lương thì mức lương bằng nhau.'
        ROLLBACK TRANSACTION
    END
END
GO

-- HOC VIEN CHI DUOC THI LAI (LAN THI >1) KHI DIEM CUA LAN THI TRUOC DO DUOI 5

CREATE TRIGGER CHECK_THI_LAI ON KETQUATHI
AFTER UPDATE, INSERT AS 
BEGIN
    DECLARE @DIEM NUMERIC(4, 2)
    DECLARE @LANTHIHT INT

    SELECT @LANTHIHT = LANTHI FROM inserted

    IF @LANTHIHT > 1 
    BEGIN
        SELECT @DIEM = KETQUATHI.DIEM FROM KETQUATHI, inserted
        WHERE KETQUATHI.LANTHI = @LANTHIHT - 1 AND inserted.MAHV = KETQUATHI.MAHV AND inserted.MAMH = KETQUATHI.MAMH

        IF @DIEM >= 5 
        BEGIN
            PRINT 'Học viên chỉ được thi lại (lần thi >1) khi điểm của lần thi trước đó dưới 5.'
            ROLLBACK TRANSACTION
        END
    END
END
GO

-- NGAY THI CUA LAN THI SAU PHAI LON HƠN NGAY THI CUA LAN THI TRUOC (CUNG HOC VIEN, CUNG MON HOC)

CREATE TRIGGER CHECK_NG_THI ON KETQUATHI
AFTER UPDATE, INSERT AS 
BEGIN
    DECLARE @NG_THI_HT SMALLDATETIME,
            @NG_THI_TRUOC SMALLDATETIME,
            @LANTHIHT INT
    
    SELECT @LANTHIHT = LANTHI, @NG_THI_HT = NGTHI FROM inserted

    IF @LANTHIHT > 1 
    BEGIN
        SELECT @NG_THI_TRUOC = KETQUATHI.NGTHI FROM KETQUATHI, inserted
        WHERE KETQUATHI.LANTHI = @LANTHIHT - 1 AND inserted.MAHV = KETQUATHI.MAHV AND inserted.MAMH = KETQUATHI.MAMH
        
        IF @NG_THI_HT <= @NG_THI_TRUOC 
        BEGIN
            PRINT 'Ngày thi của lần thi sau phải lớn hơn ngày thi của lần thi trước (cùng học viên, cùng môn học).'
            ROLLBACK TRANSACTION
        END
    END
END
GO

-- HOC VIEN CHI DUOC THI NHUNG MON HOC MA LOP HOC VIEN DO DA HOC XONG

CREATE TRIGGER CHECK_THI_MON_HOC_XONG ON KETQUATHI
AFTER UPDATE, INSERT AS 
BEGIN
    DECLARE @MAMH VARCHAR(10),
            @MAHV CHAR(5), 
            @MALOP CHAR(3),
            @NGTHI SMALLDATETIME,
            @DENNGAY SMALLDATETIME

    SELECT @MAMH = MAMH, @MAHV = MAHV, @NGTHI = NGTHI FROM inserted
    SELECT @MALOP = MALOP FROM HOCVIEN
    WHERE HOCVIEN.MAHV = @MAHV

    SELECT @DENNGAY = DENNGAY FROM GIANGDAY
    WHERE MALOP = @MALOP AND MAMH = @MAMH

    IF @NGTHI < @DENNGAY
    BEGIN
        PRINT 'Học viên chỉ được thi những môn mà lớp của học viên đó đã học xong.'
        ROLLBACK TRANSACTION
    END  
END
GO

-- KHI PHAN CONG GIANG DAY MON HOC PHAI XET DEN THU TU TRUOC SAU GIUA CAC MON HOC (SAU KHI HOC XONG MON CUA NHUNG MON HOC TRUOC MOI DUOC HOC NHUNG MON LIEN SAU)

CREATE TRIGGER TRG_PHANCONG_GIANGDAY ON GIANGDAY
AFTER INSERT, UPDATE AS
BEGIN
	DECLARE @MAMH CHAR(10), 
            @MALOP CHAR(3),
            @MAMH_TRUOC CHAR(10)
	
	SELECT @MALOP = MALOP, @MAMH = MAMH FROM inserted 
	SELECT @MAMH_TRUOC = MAMH_TRUOC FROM DIEUKIEN WHERE @MAMH = MAMH

	IF (@MAMH_TRUOC IS NOT NULL) AND (NOT EXISTS (
        SELECT * FROM GIANGDAY WHERE
        MAMH = @MAMH_TRUOC AND MALOP = @MALOP
    ))
	BEGIN
		PRINT 'Khi phân công giảng dạy một môn học, phải xét đến thứ tự trước sau giữa các môn học (sau khi học xong những môn học phải học trước mới được học những môn liền sau)..'
		ROLLBACK TRANSACTION
	END
END

-- GIAO VIEN CHI DUOC PHAN CONG DAY NHUNG MON THUOC KHOA GIAO VIEN DO PHU TRACH

CREATE TRIGGER CHECK_PHAN_CONG_GD ON GIANGDAY
AFTER INSERT, UPDATE AS
BEGIN
    DECLARE	@MAKHOA_MON CHAR(4), @MAKHOA_GV CHAR(4)

	SELECT @MAKHOA_MON = MONHOC.MAKHOA, @MAKHOA_GV = GIAOVIEN.MAKHOA FROM INSERTED, MONHOC, GIAOVIEN
	WHERE inserted.MAMH = MONHOC.MAMH AND inserted.MAGV = GIAOVIEN.MAGV

    IF @MAKHOA_GV <> @MAKHOA_MON
    BEGIN
        PRINT 'Giáo viên chỉ được phân công dạy những môn thuộc khoa giáo viên đó phụ trách.'
        ROLLBACK TRANSACTION
    END
END

-- CAP NHAT GIA TRI DIEM TRUNG BINH TAT CA CAC MIN HOC CHO MOI HOC VIEN

CREATE TRIGGER UPDATE_DIEMTB ON KETQUATHI
AFTER INSERT, UPDATE AS
BEGIN
    DECLARE @DIEM_TB NUMERIC(4, 2)
    DECLARE @MAHV CHAR(5)

    SELECT @MAHV = MAHV FROM inserted

    SELECT @DIEM_TB = AVG(DIEM) FROM KETQUATHI
    WHERE MAHV = @MAHV AND LANTHI = (
        SELECT MAX(LANTHI) FROM KETQUATHI AS KQ
        WHERE KQ.MAHV = KETQUATHI.MAHV AND KQ.MAHV = MAHV
    )

    UPDATE HOCVIEN SET DIEMTB = @DIEM_TB
END
GO

CREATE TRIGGER UPDATE_DIEMTB_DEL ON KETQUATHI
AFTER DELETE AS
BEGIN
    DECLARE @DIEM_TB NUMERIC(4, 2)
    DECLARE @MAHV CHAR(5)

    SELECT @MAHV = MAHV FROM deleted

    SELECT @DIEM_TB = AVG(DIEM) FROM KETQUATHI
    WHERE MAHV = @MAHV AND LANTHI = (
        SELECT MAX(LANTHI) FROM KETQUATHI AS KQ
        WHERE KQ.MAHV = KETQUATHI.MAHV AND KQ.MAHV = MAHV
    )

    UPDATE HOCVIEN SET DIEMTB = @DIEM_TB 
    WHERE MAHV = @MAHV
END
GO

-- CAPNHAT GIA TRI CHO COT XEPLOAI TRONG QUAN HE HOCVIEN NHU SAU:
-- o Nếu DIEMTB  9 thì XEPLOAI =”XS”
-- o Nếu 8  DIEMTB < 9 thì XEPLOAI = “G”
-- o Nếu 6.5  DIEMTB < 8 thì XEPLOAI = “K”
-- o Nếu 5  DIEMTB < 6.5 thì XEPLOAI = “TB”
-- o Nếu DIEMTB < 5 thì XEPLOAI = ”Y”

CREATE TRIGGER TRG_XEPLOAI_UPDATE ON HOCVIEN
AFTER UPDATE AS
BEGIN
    DECLARE @DIEM_HT NUMERIC(4, 2)
    DECLARE @MAHV CHAR(5)
    DECLARE @DIEM_TRUOC NUMERIC(4, 2)

    SELECT @DIEM_HT = DIEMTB, @MAHV = MAHV FROM inserted
    SELECT @DIEM_TRUOC = DIEMTB FROM deleted

    IF @DIEM_HT <> @DIEM_TRUOC
    BEGIN
        IF @DIEM_HT >= 9 
            UPDATE HOCVIEN SET XEPLOAI = 'XS' WHERE MAHV = @MAHV 

        IF @DIEM_HT >= 8 AND @DIEM_HT < 9 
            UPDATE HOCVIEN SET XEPLOAI = 'G' WHERE MAHV = @MAHV 
        
        IF @DIEM_HT >= 6.5 AND @DIEM_HT < 8 
            UPDATE HOCVIEN SET XEPLOAI = 'K' WHERE MAHV = @MAHV 
        
        IF @DIEM_HT >= 5 AND @DIEM_HT < 6.5 
            UPDATE HOCVIEN SET XEPLOAI = 'TB' WHERE MAHV = @MAHV 
        
        IF @DIEM_HT < 5
            UPDATE HOCVIEN SET XEPLOAI = 'Y' WHERE MAHV = @MAHV 
    END
END

-- hoac

UPDATE HOCVIEN SET XEPLOAI = 'XS' WHERE DIEMTB >= 9
UPDATE HOCVIEN SET XEPLOAI = 'G' WHERE DIEMTB >= 8 AND DIEMTB < 9
UPDATE HOCVIEN SET XEPLOAI = 'K' WHERE DIEMTB >= 6.5 AND DIEMTB < 8
UPDATE HOCVIEN SET XEPLOAI = 'TB' WHERE DIEMTB >= 5 AND DIEMTB < 6.5
UPDATE HOCVIEN SET XEPLOAI = 'Y' WHERE DIEMTB < 5