Bài tập quản lý Sinh Viên SQL
CREATE DATABASE QLSV
USE QLSV
--Tạo bảng Lớp
CREATE TABLE tblLOP
(
MaLop varchar(10) PRIMARY KEY,
TenLop nvarchar(40) NOT NULL
)
--Tạo bảng Tỉnh
CREATE TABLE tblTINH
(
MaTinh varchar(10) PRIMARY KEY,
TenTinh nvarchar(40) NOT NULL,
)
--Tạo bảng Sinh Viên
CREATE TABLE tblSINHVIEN
MaSv varchar(10) PRIMARY KEY,
HoTen nvarchar(40) NOT NULL,
NgaySinh date NOT NULL,
GioiTinh nvarchar(4) NOT NULL,
MaLop varchar(10) NOT NULL,
MaTinh varchar(10) NOT NULL,
DTB float NOT NULL
)
ALTER TABLE tblSINHVIEN
ADD CONSTRAINT KN_tblSINHVIEN_1 FOREIGN KEY (MaLop) REFERENCES tblLOP(MaLop),
CONSTRAINT KN_tblSINHVIEN_2 FOREIGN KEY (MaTinh) REFERENCES tblTINH(MaTinh)
SELECT *
FROM tblLOP
INSERT INTO tblLOP
VALUES('ML01','CNTTK12A')
INSERT INTO tblLOP
VALUES('ML02','CNTTK12B')
INSERT INTO tblLOP
VALUES('ML03','CNTTK12C')
INSERT INTO tblLOP
VALUES('ML04','CNTTK12D')
INSERT INTO tblLOP
VALUES('ML05','CNTTK12E')
INSERT INTO tblLOP
VALUES('ML06','CNTTK12F')
INSERT INTO tblLOP
VALUES('ML07','CNTTK12G')
SELECT *
FROM tblTINH
INSERT INTO tblTINH
VALUES('MT01',N'Quảng Ninh')
INSERT INTO tblTINH
VALUES('MT02',N'Quảng Bình')
INSERT INTO tblTINH
VALUES('MT03',N'Quảng Trị')
INSERT INTO tblTINH
VALUES('MT04',N'Quảng Nam')
INSERT INTO tblTINH
VALUES('MT05',N'Quảng Ngãi')
INSERT INTO tblTINH
VALUES('MT06',N'Hà Nội')
INSERT INTO tblTINH
VALUES('MT07',N'Quảng Ninh')
INSERT INTO tblTINH
VALUES('MT08',N'Thái Nguyên')
INSERT INTO tblTINH
VALUES('MT09',N'Bắc Giang')
SELECT *
FROM tblSINHVIEN
INSERT INTO tblSINHVIEN
VALUES('SV01',N'Hoàng Vũ Thanh Thủy','3/30/1995',N'Nữ','ML01','MT08',9.5)
INSERT INTO tblSINHVIEN
VALUES('SV02',N'Chu Xuân Linh','3/25/1991','Nam','ML01','MT01',9.5)
INSERT INTO tblSINHVIEN
VALUES('SV03',N'Ngô Doãn Tình','2/20/1995','Nam','ML01','MT02',8)
INSERT INTO tblSINHVIEN
VALUES('SV04',N'Phạm Xuân Tú','3/18/1995','Nam','ML02','MT03',9)
INSERT INTO tblSINHVIEN
VALUES('SV05',N'Dương Xuân Tùng','5/5/1995','Nam','ML02','MT01',8.5)
INSERT INTO tblSINHVIEN
VALUES('SV06',N'Nguyễn Thị Thảo','7/27/1995',N'Nữ','ML03','MT01',6.5)
INSERT INTO tblSINHVIEN
VALUES('SV07',N'Trần Văn Cương','10/19/1995','Nam','ML03','MT04',7.5)
INSERT INTO tblSINHVIEN
VALUES('SV08',N'Dương Thành Đô','1/27/1995','Nam','ML05','MT05',7.5)
INSERT INTO tblSINHVIEN
VALUES('SV09',N'Tô Thành Đồng','12/14/1995','Nam','ML05','MT08',5.5)
INSERT INTO tblSINHVIEN
VALUES('SV10',N'Nguyễn Thị Thương','2/28/1995',N'Nữ','ML05','MT09',7.5)
INSERT INTO tblSINHVIEN
VALUES('SV11',N'Nguyễn Thị A','12/21/1995',N'Nữ','ML05','MT08',4.5)
INSERT INTO tblSINHVIEN
VALUES('SV12',N'Nguyễn Thị B','8/28/1995',N'Nữ','ML07','MT06',4)
--1. Đưa ra thông tin về những sinh viên có điểm trung bình dưới 5
SELECT *
FROM tblSINHVIEN
WHERE DTB < 5
--2. Đưa ra thông tin về sinh viên có địa chỉ ở Thái Nguyên
SELECT *
FROM tblSINHVIEN
WHERE MaTinh IN (SELECT MaTinh
FROM tblTINH
WHERE TenTinh = N'Thái Nguyên' )
--3. Đưa ra thông tin về các lớp học không có sinh viên nào ở Hà Nội
SELECT *
FROM tblLOP
WHERE MaLop NOT IN (SELECT MaLop
FROM tblSINHVIEN
WHERE MaTinh IN (SELECT MaTinh
FROM tblTINH
WHERE TenTinh = N'Hà Nội' )
)
--4. Đưa ra thông tin về các sinh viên có điểm trung bình cao nhất
SELECT *
FROM tblSINHVIEN
WHERE DTB = ( SELECT MAX(DTB)
FROM tblSINHVIEN )
--5. Đưa ra thông tin về các sinh viên có điểm trung bình cao nhất theo từng lớp học.
SELECT MaLop, MaSv, HoTen, NgaySinh, GioiTinh, MaTinh, DTB
FROM tblSINHVIEN AS A
WHERE DTB = (SELECT Max(DTB) FROM tblSINHVIEN as B WHERE A.MaLop = B.MaLop)
ORDER BY MaLop ASC
SELECT A.MaLop,tblLOP.TenLop, MaSv, HoTen, NgaySinh, GioiTinh, MaTinh, a.DTB
FROM ( SELECT MaLop,Max(DTB) AS DTB
FROM tblSINHVIEN
GROUP BY MaLop) AS A,tblSINHVIEN,tblLOP
WHERE A.DTB = tblSINHVIEN.DTB and a.MaLop = tblSINHVIEN.MaLop and a.MaLop = tblLOP.MaLop
ORDER BY MaLop ASC
-- 6. Tạo View để tổng hợp thông tin về các sinh viên có điểm trung bình cao nhất.
CREATE VIEW DTBCaoNhat AS
SELECT *
FROM tblSINHVIEN
WHERE DTB = ( SELECT MAX(DTB)
FROM tblSINHVIEN )
--7. Tạo View để tổng hợp thông tin về các sinh viên có điểm trung bình cao nhất theo từng lớp học.
CREATE VIEW DTBTheoLop AS
SELECT MaLop, MaSv, HoTen, NgaySinh, GioiTinh, MaTinh, DTB
FROM tblSINHVIEN AS A
WHERE DTB = (SELECT Max(DTB) FROM tblSINHVIEN as B WHERE A.MaLop = B.MaLop)
--Tạo bảng view DTB cao nhất của từng lớp
CREATE VIEW MaxDTB_LOP AS
SELECT MaLop,Max(DTB) AS DTB
FROM tblSINHVIEN
GROUP BY MaLop
-- Kết nối với bảng sinh viên :v
CREATE VIEW TT_SV_LOP AS
SELECT tblSINHVIEN.MaLop, MaSv, HoTen, NgaySinh, GioiTinh, MaTinh, tblSINHVIEN.DTB
FROM tblSINHVIEN,MaxDTB_LOP
WHERE tblSINHVIEN.MaLop = MaxDTB_LOP.MaLop AND tblSINHVIEN.DTB = MaxDTB_LOP.DTB
-- Tạo thủ tục để đưa ra sĩ số sinh viên cho từng lớp học (Danh sách đưa ra phải có các thuộc tính sau: MaLop,
CREATE PROC SPSS
AS
SELECT tblLOP.MaLop,TenLop,A.SiSo
FROM (
SELECT MaLop,COUNT(MaSV) AS SiSo
FROM tblSINHVIEN
GROUP BY MaLop
) AS A,tblLOP
WHERE A.MaLop = tblLOP.MaLop
EXEC SPSS
Post a Comment