SQLQuery2.sql

CREATE DATABASE tracuudiemthidh;
USE tracuudiemthidh;
GO

-- Bảng thí sinh
CREATE TABLE thisinh (
    SBD VARCHAR(20) PRIMARY KEY,
    hoten VARCHAR(100) NOT NULL,
    ngaysinh DATE,
    quequan VARCHAR(100),
    truong VARCHAR(100) NOT NULL,
    tinhthanh VARCHAR(100),
    namthi INT,
    diadiemthi VARCHAR(100) NOT NULL,
    CHECK (YEAR(ngaysinh) <= 2008)
);
ALTER TABLE thisinh
ADD sdt VARCHAR(15);
---bang tai khoan
CREATE TABLE taikhoan (
    username VARCHAR(50) PRIMARY KEY,
    password VARCHAR(100),
    SBD VARCHAR(20),
    FOREIGN KEY (SBD) REFERENCES thisinh(SBD)
);
-- Bảng môn thi
CREATE TABLE monthi (
    mamon VARCHAR(20) PRIMARY KEY,
    tenmon VARCHAR(100) NOT NULL
);

-- Bảng điểm thi
CREATE TABLE diemthi (
    SBD VARCHAR(20),
    mamon VARCHAR(20),
    diemthi DECIMAL(4,2),
    diemsauphuckhao DECIMAL(4,2),

    PRIMARY KEY (SBD, mamon),

    FOREIGN KEY (SBD) REFERENCES thisinh(SBD),
    FOREIGN KEY (mamon) REFERENCES monthi(mamon)
);

-- Bảng trường đại học
CREATE TABLE truongdaihoc (
    matruong VARCHAR(20) PRIMARY KEY,
    tentruong VARCHAR(255) NOT NULL,
    diachi VARCHAR(255),
    diem_san DECIMAL(4,2)
);

-- Bảng ngành
CREATE TABLE nganh (
    manganh VARCHAR(20) PRIMARY KEY,
    matruong VARCHAR(20),
    tennganh VARCHAR(255),
    monxettuyen VARCHAR(255),
    diemchuan DECIMAL(4,2),

    FOREIGN KEY (matruong) REFERENCES truongdaihoc(matruong)
);

-- Bảng nguyện vọng
CREATE TABLE nguyenvong (
    id INT IDENTITY(1,1) PRIMARY KEY,
    SBD VARCHAR(20),
    manganh VARCHAR(20),
    Thutu INT,

    FOREIGN KEY (SBD) REFERENCES thisinh(SBD),
    FOREIGN KEY (manganh) REFERENCES nganh(manganh)
);
SELECT 
    SBD,
    mamon,
    COALESCE(diemsauphuckhao, diemthi) AS diem
FROM diemthi;
SELECT 
    SBD,
    SUM(COALESCE(diemsauphuckhao, diemthi)) AS tongdiem
FROM diemthi
WHERE mamon IN ('TOAN','LY','HOA')
GROUP BY SBD;
SELECT 
    SBD,
    SUM(COALESCE(diemsauphuckhao, diemthi)) AS tongdiem
FROM diemthi
WHERE mamon IN ('TOAN','VAN','ANH')
GROUP BY SBD;

--thêm dữ liệu 
INSERT INTO thisinh 
(SBD, hoten, ngaysinh, quequan, truong, tinhthanh, namthi, diadiemthi, sdt)
VALUES
('TS001', 'Nguyen Manh Tien', '2006-03-16', 'Binh Dinh', 'THPT Hung Vuong', 'Binh Dinh', 2024, 'Diem thi 1', '0911111111'),
('TS002', 'Vo Van Minh Vuong', '2006-02-15', 'Vinh Long', 'THPT Trung Vuong', 'Vinh Long', 2024, 'Diem thi 2', '0922222222'),
('TS003', 'Le Tuan Nha', '2006-12-03', 'Ben Tre', 'THPT Truong Vinh Ky', 'Ben Tre', 2024, 'Diem thi 3', '0933333333'),
('TS004', 'Pham Vu Quang', '2006-01-04', 'Quang Binh', 'THPT Tri An', 'Quang Binh', 2024, 'Diem thi 4', '0944444444'),
('TS005', 'Nguyen Van A', '2006-04-30', 'Tra Vinh', 'THPT Long Huu', 'Can Tho', 2024, 'Diem thi 5', '0955555555')
select * from thisinh
-- dữ liệu bảng tài khoản
INSERT INTO taikhoan (username, password, SBD)
VALUES
('tien01', '123456', 'TS001'),
('vuong02', '123456', 'TS002'),
('nha03', '123456', 'TS003'),
('quang04', '123456', 'TS004'),
('vana05', '123456', 'TS005');
--thêm dữ liệu bảng môn thi
INSERT INTO monthi (mamon, tenmon) VALUES
('TOAN', N'Toán'),
('VAN', N'Ngữ Văn'),
('ANH', N'Tiếng Anh'),
('LY', N'Vật Lý'),
('HOA', N'Hóa Học'),
('SINH', N'Sinh Học'),
('SU', N'Lịch Sử'),
('DIA', N'Địa Lý'),
('GDCD', N'Giáo dục công dân');
select * from monthi

--Thêm dữ liệu bảng điểm thi
INSERT INTO diemthi (SBD, mamon, diemthi, diemsauphuckhao) VALUES

-- ===== THÍ SINH 1 (khối A) =====
('TS001','TOAN',8.5,NULL),
('TS001','VAN',7.0,NULL),
('TS001','ANH',8.0,NULL),
('TS001','LY',8.2,NULL),
('TS001','HOA',7.8,NULL),
('TS001','SINH',6.5,NULL),

-- ===== THÍ SINH 2 (khối D) =====
('TS002','TOAN',7.5,NULL),
('TS002','VAN',8.0,NULL),
('TS002','ANH',8.5,NULL),
('TS002','SU',6.5,NULL),
('TS002','DIA',7.0,NULL),
('TS002','GDCD',8.0,NULL),

-- ===== THÍ SINH 3 (học lực cao) =====
('TS003','TOAN',9.2,NULL),
('TS003','VAN',8.8,NULL),
('TS003','ANH',9.0,NULL),
('TS003','LY',9.1,NULL),
('TS003','HOA',8.9,NULL),
('TS003','SINH',8.5,NULL),

-- ===== THÍ SINH 4 (trung bình) =====
('TS004','TOAN',6.5,NULL),
('TS004','VAN',6.8,NULL),
('TS004','ANH',6.0,NULL),
('TS004','SU',5.5,NULL),
('TS004','DIA',6.0,NULL),
('TS004','GDCD',6.5,NULL),

-- ===== THÍ SINH 5 (có phúc khảo) =====
('TS005','TOAN',7.0,7.5),
('TS005','VAN',6.5,7.0),
('TS005','ANH',7.2,NULL),
('TS005','LY',6.8,NULL),
('TS005','HOA',6.5,NULL),
('TS005','SINH',6.0,NULL);

INSERT INTO truongdaihoc (matruong, tentruong, diachi, diem_san) VALUES

-- ĐẠI HỌC (công lập)
('TNMT', 'ĐH Tài Nguyên & Môi Trường', NULL, 18.5),
('BKH', 'ĐH Bách Khoa', NULL, 22.0),
('KHTN', 'ĐH Khoa Học Tự Nhiên', NULL, 19.0),
('CNTT', 'ĐH Công Nghệ Thông Tin', NULL, 23.5),
('CN', 'ĐH Công Nghiệp', NULL, 17.8),
('SP', 'ĐH Sư Phạm', NULL, 19.5),
('SPKT', 'ĐH Sư Phạm Kỹ Thuật', NULL, 21.0),
('KHXH', 'ĐH KHXH & Nhân Văn', NULL, 20.5),
('KT', 'ĐH Kinh Tế', NULL, 22.5),
('KTL', 'ĐH Kinh Tế - Luật', NULL, 21.5),
('LUAT', 'ĐH Luật', NULL, 23.0),
('KTTRUC', 'ĐH Kiến Trúc', NULL, 19.0),
('MO', 'ĐH Mở', NULL, 17.5),
('NL', 'ĐH Nông Lâm', NULL, 17.0),

-- ĐẠI HỌC (tư thục)
('HB', 'ĐH Quốc Tế Hồng Bàng', NULL, 15.0),
('NTT', 'ĐH Nguyễn Tất Thành', NULL, 15.0),
('HUTECH', 'ĐH Công Nghệ TP.HCM (HUTECH)', NULL, 17.0),
('UEF', 'ĐH Kinh Tế - Tài Chính (UEF)', NULL, 18.0),
('KT-CN', 'ĐH Kỹ Thuật - Công Nghệ', NULL, 16.0),
('CNTP', 'ĐH Công Nghiệp Thực Phẩm', NULL, 16.0),
('IUH', 'ĐH Công Nghiệp TP.HCM', NULL, 17.0),
('VLU', 'ĐH Văn Lang', NULL, 18.0),
('HSU', 'ĐH Hoa Sen', NULL, 17.0),
('FPT', 'ĐH FPT', NULL, 19.0),

-- CAO ĐẲNG
('CDCT', 'Cao Đẳng Công Thương', NULL, 13.5),
('CDE', 'Cao Đẳng Kinh Tế Đối Ngoại', NULL, 14.5),
('CDTD', 'Cao Đẳng Công Nghệ Thủ Đức', NULL, 13.5),
('CDGTVT', 'Cao Đẳng GTVT', NULL, 14.0);
select * from truongdaihoc

INSERT INTO nganh (manganh, matruong, tennganh, monxettuyen, diemchuan) VALUES

-- ===== TNMT =====
('CNTT_TNMT','TNMT',N'Công nghệ thông tin','A00,D01',22.0),
('QLDD_TNMT','TNMT',N'Quản lý đất đai','A00,D01',20.0),
('MT_TNMT','TNMT',N'Môi trường','A00,B00',19.5),
('KT_TNMT','TNMT',N'Kỹ thuật trắc địa','A00',20.0),
('QLTN_TNMT','TNMT',N'Quản lý tài nguyên','A00,B00',19.0),

-- ===== BKH =====
('CNTT_BKH','BKH',N'Công nghệ thông tin','A00,D01',27.5),
('CK_BKH','BKH',N'Cơ khí','A00',25.0),
('DIEN_BKH','BKH',N'Điện - Điện tử','A00',26.0),
('XD_BKH','BKH',N'Xây dựng','A00',24.5),
('CNTP_BKH','BKH',N'Công nghệ thực phẩm','A00,B00',24.0),

-- ===== KHTN =====
('CNTT_KHTN','KHTN',N'Công nghệ thông tin','A00,D01',26.5),
('TOAN_KHTN','KHTN',N'Toán học','A00',24.0),
('LY_KHTN','KHTN',N'Vật lý','A00',23.5),
('HOA_KHTN','KHTN',N'Hóa học','A00',23.0),
('SINH_KHTN','KHTN',N'Sinh học','B00',22.0),

-- ===== CNTT =====
('CNTT_UIT','CNTT',N'Công nghệ thông tin','A00,D01',28.0),
('HTTT_UIT','CNTT',N'Hệ thống thông tin','A00,D01',26.5),
('ATTT_UIT','CNTT',N'An toàn thông tin','A00,D01',27.0),
('KHDL_UIT','CNTT',N'Khoa học dữ liệu','A00,D01',27.5),
('MMT_UIT','CNTT',N'Mạng máy tính','A00,D01',26.0),

-- ===== CN =====
('CNTT_CN','CN',N'Công nghệ thông tin','A00,D01',20.0),
('CK_CN','CN',N'Cơ khí','A00',19.0),
('DIEN_CN','CN',N'Điện','A00',19.5),
('KT_CN','CN',N'Kế toán','A00,D01',19.0),
('QTKD_CN','CN',N'Quản trị kinh doanh','A00,D01',19.5),

-- ===== SP =====
('CNTT_SP','SP',N'Công nghệ thông tin','A00,D01',24.0),
('SPTOAN','SP',N'Sư phạm Toán','A00',25.5),
('SPVAN','SP',N'Sư phạm Văn','C00',25.0),
('SPEN','SP',N'Sư phạm Anh','D01',25.5),
('SPHOA','SP',N'Sư phạm Hóa','A00',24.5),

-- ===== SPKT =====
('CNTT_SPKT','SPKT',N'Công nghệ thông tin','A00,D01',26.0),
('OTO_SPKT','SPKT',N'Công nghệ ô tô','A00',25.0),
('DIEN_SPKT','SPKT',N'Điện tử','A00',25.5),
('CK_SPKT','SPKT',N'Cơ khí','A00',24.5),
('XD_SPKT','SPKT',N'Xây dựng','A00',24.0),

-- ===== KHXH =====
('BAOCHI','KHXH',N'Báo chí','C00,D01',26.0),
('TTQT','KHXH',N'Truyền thông','C00,D01',25.5),
('LS','KHXH',N'Lịch sử','C00',24.0),
('DL','KHXH',N'Du lịch','C00,D01',25.0),

-- ===== KT =====
('CNTT_KT','KT',N'Công nghệ thông tin','A00,D01',25.0),
('QTKD_KT','KT',N'Quản trị kinh doanh','A00,D01',27.0),
('KTQT_KT','KT',N'Kinh tế quốc tế','A00,D01',27.5),
('MARKETING_KT','KT',N'Marketing','A00,D01',27.0),
('KETOAN_KT','KT',N'Kế toán','A00,D01',26.5),

-- ===== KTL =====
('CNTT_KTL','KTL',N'Công nghệ thông tin','A00,D01',24.5),
('LUAT_KTL','KTL',N'Luật kinh tế','A00,D01',26.5),
('KT_KTL','KTL',N'Kinh tế','A00,D01',26.0),
('TAICHINH_KTL','KTL',N'Tài chính','A00,D01',25.5),
('LOG_KTL','KTL',N'Logistics','A00,D01',26.0),

-- ===== LUAT =====
('CNTT_LUAT','LUAT',N'Công nghệ thông tin','A00,D01',24.0),
('LUAT','LUAT',N'Luật','C00,D01',27.5),
('LUATQT','LUAT',N'Luật quốc tế','C00,D01',26.5),
('LUATKT','LUAT',N'Luật kinh tế','C00,D01',27.0),
('QT_LUAT','LUAT',N'Quản trị','A00,D01',24.5),

-- ===== KTTRUC =====
('CNTT_KTTRUC','KTTRUC',N'Công nghệ thông tin','A00,D01',22.0),
('KTTRUC','KTTRUC',N'Kiến trúc','D01',24.0),
('NOITHAT','KTTRUC',N'Thiết kế nội thất','D01',23.0),
('XD_KTTRUC','KTTRUC',N'Xây dựng','A00',22.5),
('QH','KTTRUC',N'Quy hoạch','A00',22.0),

-- ===== MO =====
('CNTT_MO','MO',N'Công nghệ thông tin','A00,D01',20.0),
('QTKD_MO','MO',N'Quản trị kinh doanh','A00,D01',20.0),
('LUAT_MO','MO',N'Luật','C00,D01',21.0),
('KT_MO','MO',N'Kế toán','A00,D01',20.0),
('TC_MO','MO',N'Tài chính','A00,D01',20.5),

-- ===== NL =====
('CNTT_NL','NL',N'Công nghệ thông tin','A00,D01',18.5),
('THUY_SAN','NL',N'Nuôi trồng thủy sản','B00',18.0),
('LAM','NL',N'Lâm nghiệp','B00',17.5),
('NONG','NL',N'Nông học','B00',18.0),
('CHANN','NL',N'Chăn nuôi','B00',18.0),

-- ===== HB =====
('CNTT_HB','HB',N'Công nghệ thông tin','A00,D01',17.0),
('DUOC_HB','HB',N'Dược học','B00',21.0),
('QTKD_HB','HB',N'Quản trị kinh doanh','A00,D01',17.5),
('DL_HB','HB',N'Du lịch','C00,D01',17.0),
('KT_HB','HB',N'Kế toán','A00,D01',17.0),

-- ===== NTT =====
('CNTT_NTT','NTT',N'Công nghệ thông tin','A00,D01',17.0),
('Y_NTT','NTT',N'Y khoa','B00',22.0),
('DUOC_NTT','NTT',N'Dược','B00',21.5),
('QTKD_NTT','NTT',N'Quản trị kinh doanh','A00,D01',17.5),
('DL_NTT','NTT',N'Du lịch','C00,D01',17.0),

-- ===== HUTECH =====
('CNTT_HT','HUTECH',N'Công nghệ thông tin','A00,D01',18.0),
('QTKD_HT','HUTECH',N'Quản trị kinh doanh','A00,D01',17.5),
('TKDH_HT','HUTECH',N'Thiết kế đồ họa','V00',18.0),
('DL_HT','HUTECH',N'Du lịch','C00,D01',17.0),
('KT_HT','HUTECH',N'Kế toán','A00,D01',17.5),

-- ===== UEF =====
('CNTT_UEF','UEF',N'Công nghệ thông tin','A00,D01',19.0),
('QTKD_UEF','UEF',N'Quản trị kinh doanh','A00,D01',19.0),
('MARKETING_UEF','UEF',N'Marketing','A00,D01',19.5),
('TCNH_UEF','UEF',N'Tài chính','A00,D01',19.0),
('LOG_UEF','UEF',N'Logistics','A00,D01',19.5),

-- ===== KT-CN =====
('CNTT_KTCN','KT-CN',N'Công nghệ thông tin','A00,D01',17.0),
('CK_KTCN','KT-CN',N'Cơ khí','A00',16.5),
('DIEN_KTCN','KT-CN',N'Điện','A00',17.0),
('QTKD_KTCN','KT-CN',N'Quản trị','A00,D01',17.0),
('KT_KTCN','KT-CN',N'Kế toán','A00,D01',17.0),

-- ===== CNTP =====
('CNTT_CNTP','CNTP',N'Công nghệ thông tin','A00,D01',18.0),
('CNTP_CNTP','CNTP',N'Công nghệ thực phẩm','A00,B00',18.0),
('SH_CNTP','CNTP',N'Sinh học','B00',17.5),
('KT_CNTP','CNTP',N'Kế toán','A00,D01',17.5),
('QTKD_CNTP','CNTP',N'Quản trị','A00,D01',17.5),

-- ===== IUH =====
('CNTT_IUH','IUH',N'Công nghệ thông tin','A00,D01',20.0),
('QTKD_IUH','IUH',N'Quản trị kinh doanh','A00,D01',19.5),
('KETOAN_IUH','IUH',N'Kế toán','A00,D01',19.0),
('CNTP_IUH','IUH',N'Công nghệ thực phẩm','A00,B00',18.5),
('DIEN_IUH','IUH',N'Điện','A00',19.0),

-- ===== VLU =====
('CNTT_VLU','VLU',N'Công nghệ thông tin','A00,D01',18.0),
('TKDH_VLU','VLU',N'Thiết kế đồ họa','V00',18.0),
('QTKD_VLU','VLU',N'Quản trị kinh doanh','A00,D01',18.5),
('DL_VLU','VLU',N'Du lịch','C00,D01',18.0),
('KT_VLU','VLU',N'Kế toán','A00,D01',18.0),

-- ===== HSU =====
('CNTT_HSU','HSU',N'Công nghệ thông tin','A00,D01',18.0),
('DL_HSU','HSU',N'Du lịch','A00,D01',18.0),
('QTKD_HSU','HSU',N'Quản trị','A00,D01',18.0),
('KT_HSU','HSU',N'Kế toán','A00,D01',18.0),
('NN_HSU','HSU',N'Ngôn ngữ Anh','D01',18.5),

-- ===== FPT =====
('CNTT_FPT','FPT',N'Công nghệ thông tin','A00,D01',21.0),
('AI_FPT','FPT',N'Trí tuệ nhân tạo','A00,D01',22.0),
('SE_FPT','FPT',N'Kỹ thuật phần mềm','A00,D01',21.5),
('GD_FPT','FPT',N'Thiết kế đồ họa','D01',20.0),
('QTKD_FPT','FPT',N'Quản trị kinh doanh','A00,D01',20.5);
SELECT 
    nv.SBD,
    nv.manganh,
    n.tennganh,
    nv.Thutu,

    CASE 
        WHEN n.monxettuyen LIKE '%A00%' THEN tA.tongdiem
        WHEN n.monxettuyen LIKE '%D01%' THEN tD.tongdiem
        WHEN n.monxettuyen LIKE '%B00%' THEN tB.tongdiem
        WHEN n.monxettuyen LIKE '%C00%' THEN tC.tongdiem
        ELSE 0
    END AS tongdiem,

    n.diemchuan,

    CASE 
        WHEN 
        CASE 
            WHEN n.monxettuyen LIKE '%A00%' THEN tA.tongdiem
            WHEN n.monxettuyen LIKE '%D01%' THEN tD.tongdiem
            WHEN n.monxettuyen LIKE '%B00%' THEN tB.tongdiem
            WHEN n.monxettuyen LIKE '%C00%' THEN tC.tongdiem
            ELSE 0
        END >= n.diemchuan 
        THEN N'ĐẬU'
        ELSE N'RỚT'
    END AS ketqua

FROM nguyenvong nv
JOIN nganh n ON nv.manganh = n.manganh

-- Khối A00
LEFT JOIN (
    SELECT SBD, SUM(COALESCE(diemsauphuckhao, diemthi)) AS tongdiem
    FROM diemthi
    WHERE mamon IN ('TOAN','LY','HOA')
    GROUP BY SBD
) tA ON nv.SBD = tA.SBD

-- Khối D01
LEFT JOIN (
    SELECT SBD, SUM(COALESCE(diemsauphuckhao, diemthi)) AS tongdiem
    FROM diemthi
    WHERE mamon IN ('TOAN','VAN','ANH')
    GROUP BY SBD
) tD ON nv.SBD = tD.SBD

-- Khối B00
LEFT JOIN (
    SELECT SBD, SUM(COALESCE(diemsauphuckhao, diemthi)) AS tongdiem
    FROM diemthi
    WHERE mamon IN ('TOAN','HOA','SINH')
    GROUP BY SBD
) tB ON nv.SBD = tB.SBD

-- Khối C00
LEFT JOIN (
    SELECT SBD, SUM(COALESCE(diemsauphuckhao, diemthi)) AS tongdiem
    FROM diemthi
    WHERE mamon IN ('VAN','SU','DIA')
    GROUP BY SBD
) tC ON nv.SBD = tC.SBD

ORDER BY nv.SBD, nv.thutu;

SELECT 
    ts.SBD,
    ts.hoten,
    COUNT(nv.id) AS so_nguyenvong,
    COUNT(nv.id) * 10000 AS tongtien
FROM thisinh ts
LEFT JOIN nguyenvong nv ON ts.SBD = nv.SBD
GROUP BY ts.SBD, ts.hoten;