BAI 1: QUAN LY HOC VIEN.

-- Bai 1 Quan Ly Hoc Vien

IF DB_ID('QLHV') IS NOT NULL

DROP DATABASE QLHV

CREATE DATABASE QLHV

USE QLSV

-- TAO TABLE MON HOC

IF OBJECT_ID('MONHOC') IS NOT NULL

DROP TABLE MONHOC

CREATE TABLE MONHOC

(

MAMH VARCHAR(10) NOT NULL PRIMARY KEY,

TENMH NVARCHAR(30)NOT NULL,

SOTC INT

)

-- TAO TABLE LOP

IF OBJECT_ID('LOP') IS NOT NULL

DROP TABLE LOP

CREATE TABLE LOP

(

MALP VARCHAR(10) NOT NULL PRIMARY KEY,

TENLP NVARCHAR(30)NOT NULL

)

-- TAO TABLE HOC VIEN

IF OBJECT_ID('HV') IS NOT NULL

DROP TABLE HV

CREATE TABLE HV

(

MAHV VARCHAR(10) NOT NULL,

TENHV NVARCHAR(30)NOT NULL,

DCHV NVARCHAR(10)NOT NULL,

MALP VARCHAR(10)NOT NULL,

PRIMARY KEY (MAHV,MALP)

)

-- TAO TABLE DIEMHV

IF OBJECT_ID('DIEMHV') IS NOT NULL

DROP TABLE DIEMHV

CREATE TABLE DIEMHV

(

MAHV VARCHAR(10) NOT NULL,

MAMH VARCHAR(30)NOT NULL,

DIEM FLOAT ,

PRIMARY KEY(MAHV,MAMH)

)

--- NHAP LIEU

-- NHAP BANG MON HOC

INSERT INTO MONHOC VALUES('M01', N'Cơ Sở Dữ Liệu',3)

INSERT INTO MONHOC VALUES('M02', N'Thiết Kế Web',3)

INSERT INTO MONHOC VALUES('M03', N'Xử Lý Ảnh',4)

select * from MONHOC

--- NHAP LIEU LOP

INSERT INTO LOP VALUES('L01', N'Công Nghệ TT 2009')

INSERT INTO LOP VALUES('L02', N'Quản Trị 2009')

INSERT INTO LOP VALUES('L03', N'Không Biết Lớp Gì Nữa')

select * from LOP

--- NHAP LIEU HV

INSERT INTO HV VALUES('H01', N'Nguyễn Văn A', 'Q1', 'L01')

INSERT INTO HV VALUES('H02', N'Nguyễn Văn B', 'Q2', 'L01')

INSERT INTO HV VALUES('H03', N'Nguyễn Văn C', 'Q1', 'L01')

INSERT INTO HV VALUES('H04', N'Nguyễn Văn B', 'Q3', 'L02')

INSERT INTO HV VALUES('H05', N'Nguyễn Văn A', 'Q3', 'L02')

INSERT INTO HV VALUES('H06', N'Nguyễn Văn A', 'Q2', 'L03')

select * from HV

--- NHAP LIEU DIE HV

INSERT INTO DIEMHV VALUES('H01', 'M01', 5)

INSERT INTO DIEMHV VALUES('H01', 'M02', 4)

INSERT INTO DIEMHV VALUES('H02', 'M01', 7)

INSERT INTO DIEMHV VALUES('H02', 'M02', 5)

INSERT INTO DIEMHV VALUES('H02', 'M03', 6)

INSERT INTO DIEMHV VALUES('H03', 'M01', 7)

SELECT * FROM DIEMHV

--- CÂU 1

SELECT TENMH AS 'TÊN MÔN HỌC', SOTC AS 'SỐ TÍN CHỈ'

FROM MONHOC

WHERE MAMH = 'M01'

--- CÂU 2

SELECT B.MAHV AS 'MÃ SỐ SINH VIÊN', B.TENHV AS 'TÊN HỌC VIÊN'

FROM LOP A, HV B

WHERE A.MALP = B.MALP AND TENLP = N'Quản Trị 2009'

--- CÂU 3

SELECT TENMH AS 'TÊN MÔN HỌC', DIEM AS 'ĐIỂM'

FROM MONHOC A, DIEMHV B

WHERE A.MAMH = B.MAMH AND MAHV = 'H01'

--- CÂU 4

SELECT A.MAHV AS 'MÃ HỌC VIÊN', TENHV AS 'TÊN HỌC VIÊN'

FROM HV A, DIEMHV B, MONHOC C

WHERE A.MAHV = B.MAHV AND C.MAMH = B.MAMH AND TENMH = N'Cơ Sở Dữ Liệu' AND DIEM < 5

--- CÂU 6

SELECT TENMH AS 'TÊN MÔN HỌC CHƯA THI'

FROM MONHOC

WHERE MAMH NOT IN ( SELECT MAMH

FROM DIEMHV

WHERE MAHV = 'H01'

)

--- CÂU 7

SELECT TENHV AS 'SINH VIÊN CÓ ĐIỂM MÔN M01 LỚN NHẤT', DIEM AS 'SỐ ĐIỂM'

FROM HV A, DIEMHV B

WHERE A.MAHV = B.MAHV AND DIEM >= ALL ( SELECT DIEM

FROM DIEMHV)

--- CÂU 8

SELECT TENLP AS 'TÊN LỚP', COUNT(*) AS 'SĨ SỐ'

FROM LOP A, HV B

WHERE A.MALP = B.MALP

GROUP BY TENLP

--- CÂU 9 CHO BIẾT LỚP CÓ SĨ SỐ NHIỀU NHẤT

SELECT TENLP AS 'TÊN LỚP NHIỀU NHẤT', COUNT(*) AS 'SĨ SỐ'

FROM LOP A, HV B

WHERE A.MALP = B.MALP

GROUP BY TENLP

HAVING COUNT(*)>=ALL (SELECT COUNT(*)

FROM LOP A, HV B

WHERE A.MALP = B.MALP

GROUP BY TENLP)

--- CÂU 10 CHO BIẾT ĐIỂM TRUNG BÌNH CỦA TỪNG THẰNG

SELECT TENHV AS 'THẰNG NÀO?', AVG(DIEM) AS 'ĐIỂM TB'

FROM HV A, DIEMHV B

WHERE A.MAHV = B.MAHV

GROUP BY TENHV

--- CÂU 11 CHO BIẾT THẰNG NÀO CÓ ĐIỂM TB LỚN NHẤT?

SELECT TENHV AS 'THẰNG NÀO ĐIỂM TB LỚN NHẤT?', AVG(DIEM) AS 'ĐIỂM TB'

FROM HV A, DIEMHV B

WHERE A.MAHV = B.MAHV

GROUP BY TENHV

HAVING AVG(DIEM) >= ALL (SELECT AVG(DIEM) AS 'ĐIỂM TB'

FROM HV A, DIEMHV B

WHERE A.MAHV = B.MAHV

GROUP BY TENHV)