Bài tập quản lý Sinh Viên SQL

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