-- bai tap SQL nhom 8
drop database qlsv
create database qlsv
drop table Khoa
drop table SinhVien
drop table MonHoc
drop table KetQua
use qlsv
create table Khoa(
MaKhoa varchar(5) not null primary key,
TenKhoa varchar(30),
TenTruongKhoa varchar(30),
)
create table SinhVien(
MSSV varchar(10) not null primary key,
HoLot varchar(20),
Ten varchar(10),
NgaySinh date,
GioiTinh varchar(5),
diachi varchar(30),
MaKhoa varchar(5)
constraint fk_sinhvien_khoa
foreign key (MaKhoa) references Khoa(MaKhoa)
)
create table MonHoc(
MaMH varchar(10) not null primary key,
TenMH varchar(30),
SoTiet varchar(5),
)
create table KetQua(
MSSV varchar(10),
MaMH varchar(10),
Diem int,
primary key(MSSV,MaMH),
constraint fk_KetQua_Monhoc_sinhvien
foreign key (MSSV) references SinhVien(MSSV),
foreign key (MaMH) references MonHoc(MaMH)
)
insert into MonHoc values('CSNN', 'Co So Ngon Ngu', '15')
insert into MonHoc values('TVTH', 'Tieng Viet Thuc Hanh', '30')
insert into MonHoc values('SCVH', 'Co So Van Hoa', '15')
insert into MonHoc values('LS', 'Listenning', '45')
insert into MonHoc values('SP', 'Speaking', '15')
insert into MonHoc values('RD', 'Reading', '30')
insert into MonHoc values('WT', 'Writing', '45')
insert into MonHoc values('CSLT', 'Co So Lap Trinh', '15')
insert into MonHoc values('KTLT', 'Ky Thuat Lap Trinh', '30')
insert into MonHoc values('CTDL', 'Cau Truc Du Lieu', '45')
insert into MonHoc values('CSDL', 'Co So Du Lieu', '60')
insert into MonHoc values('HDH', 'He Dieu Hanh', '30')
insert into MonHoc values('MMT', 'Mang May Tinh', '45')
insert into MonHoc values('VTK', 'Ve Ky Thuat', '30')
insert into MonHoc values('VL', 'Vat Ly', '15')
insert into MonHoc values('CLT', 'Co Ly Thuyet', '30')
insert into MonHoc values('VTP', 'Vi Tich Phan', '45')
--Nhap du lieu bang khoa
insert into Khoa values('020', 'Cong Nghe Thong Tin', 'Nguyen Tan Anh')
insert into Khoa values('061', 'Ngoai Ngu - Dong Phuong Hoc', 'Le Thi Bich')
insert into Khoa values('014', 'Ky Thuat Cong Nghe', 'Tran Huu Canh')
--Nhap du lieu bang sinh vien
insert into SinhVien values('08.020.001', 'Le Viet', 'Bang', '4/1/1990', 'Nam', 'Ca Mau', '020')
insert into SinhVien values('08.020.002', 'Nguyen Phu', 'Cao', '2/3/1991', 'Nam', 'Bac Lieu', '020')
insert into SinhVien values('08.020.003', 'Nguyen Anh', 'Chuong', '3/4/1990', 'Nam', 'An Giang', '020')
insert into SinhVien values('08.020.004', 'Le Chi', 'Dung', '12/3/1991', 'Nam', 'Hau Giang', '020')
insert into SinhVien values('08.020.005', 'Tran Huu', 'Hau', '4/26/1990', 'Nam', 'Can Tho', '020')
insert into SinhVien values('08.020.006', 'Nguyen Thi', 'Ha', '4/1/1990', 'Nu', 'Tra Vinh', '020')
insert into SinhVien values('08.020.007', 'Pham Van', 'Hieu', '2/3/1991', 'Nam', 'Tien Giang', '020')
insert into SinhVien values('08.020.008', 'Nguyen Viet', 'Hong', '4/13/1990', 'Nam', 'Ben Tre', '020')
insert into SinhVien values('08.020.009', 'Le Thanh', 'Hoai', '3/20/1991', 'Nam', 'Can Tho', '020')
insert into SinhVien values('08.020.010', 'Nguyen Huy', 'Hoang', '8/26/1990', 'Nam', 'Bac Lieu', '020')
insert into SinhVien values('08.020.011', 'Nguyen Tri', 'Hoang', '4/11/1990', 'Nam', 'Hau Giang', '020')
insert into SinhVien values('08.020.012', 'Huynh Quoc', 'Khai', '2/23/1991', 'Nam', 'Tra Vinh', '020')
insert into SinhVien values('08.020.013', 'Tran Quoc', 'Khanh', '3/24/1990', 'Nam', 'An Giang', '020')
insert into SinhVien values('08.020.014', 'Le Thi Diem', 'Kieu', '9/12/1991', 'Nu', 'Tien Giang', '020')
insert into SinhVien values('08.020.015', 'Ñoan Minh', 'Luan', '4/16/1990', 'Nam', 'Kien Giang', '020')
insert into SinhVien values('08.020.016', 'Le Thi My', 'Ly', '12/1/1990', 'Nu', 'Vinh Long' , '020')
insert into SinhVien values('08.020.017', 'Nguyen Tien', 'Mai', '12/3/1991', 'Nu', 'Can Tho', '020')
insert into SinhVien values('08.020.018', 'Nguyen Ut', 'Nam', '3/24/1990', 'Nam', 'Hau Giang', '020')
insert into SinhVien values('08.020.019', 'Ha Ngoc', 'Ñat', '7/12/1991', 'Nam', 'Ca Mau', '020')
insert into SinhVien values('08.020.020', 'Vo Kim', 'Nga', '5/6/1990', 'Nu', 'Bac Lieu', '020')
insert into SinhVien values('08.061.001', 'Le Thi Anh', 'Ngoc', '10/18/1990', 'Nu', 'Soc Trang', '061')
insert into SinhVien values('08.061.002', 'Pham Chi', 'Nguyen', '12/23/1991', 'Nam', 'Long An', '061')
insert into SinhVien values('08.061.003', 'Pham Thai', 'Phong', '12/14/1990' ,'Nam', 'Soc Trang', '061')
insert into SinhVien values('08.061.004', 'Nguyen Minh', 'Phuong', '7/12/1991', 'Nu', 'Ca Mau', '061')
insert into SinhVien values('08.061.005', 'Nguyen Van', 'Quang', '8/6/1990', 'Nam', 'Can Tho', '061')
insert into SinhVien values('08.061.006', 'Duong Van', 'Sang', '9/11/1990', 'Nam', 'Long An', '061')
insert into SinhVien values('08.061.007', 'Ho Thanh', 'Sang', '12/13/1991', 'Nam', 'Tien Giang', '061')
insert into SinhVien values('08.061.008', 'Ñang Van', 'Tai', '8/7/1990', 'Nam', 'Ben Tre', '061')
insert into SinhVien values('08.061.009', 'Le Hoang Nhat','Thanh', '9/6/1991', 'Nam', 'Can Tho', '061')
insert into SinhVien values('08.061.010', 'Tran Thi', 'Thoa', '8/26/1990', 'Nu', 'Bac Lieu', '061')
insert into SinhVien values('08.061.011', 'Nguyen Thanh', 'Thoai', '10/12/1990', 'Nam', 'Hau Giang', '061')
insert into SinhVien values('08.061.012', 'Le Ñuc', 'Tho', '5/31/1991', 'Nam', 'Tra Vinh', '061')
insert into SinhVien values('08.061.013', 'Nguyen Thi Kim','Thuan', '11/4/1990', 'Nam', 'An Giang', '061')
insert into SinhVien values('08.061.014', 'Pham Thi My', 'Tiem', '3/14/1991', 'Nu', 'Tien Giang', '061')
insert into SinhVien values('08.061.015', 'Duong The', 'Toan', '4/21/1990', 'Nam', 'Kien Giang', '061')
insert into SinhVien values('08.061.016', 'Nguyen Van', 'Toi', '8/18/1990', 'Nam', 'Hau Giang', '061')
insert into SinhVien values('08.061.017', 'Pham Quoc', 'Truong', '12/13/1991', 'Nam', 'Ca Mau', '061')
insert into SinhVien values('08.061.018', 'Vo Anh', 'Tuan', '4/24/1990', 'Nam', 'Bac Lieu', '061')
insert into SinhVien values('08.061.019', 'Tran Thi Cam', 'Tu', '6/12/1991', 'Nu', 'Soc Trang', '061')
insert into SinhVien values('08.061.020', 'Tran Minh', 'Ut', '9/6/1990', 'Nam', 'Long An', '061')
insert into SinhVien values('08.014.001', 'Nguyen Xuan', 'Viet', '6/12/1990', 'Nam', 'Soc Trang', '014')
insert into SinhVien values('08.014.002', 'Nguyen Thi My','Xuan', '2/12/1991', 'Nu', 'Ca Mau', '014')
insert into SinhVien values('08.014.003', 'Vo Thi Ha', 'Xuyen', '8/4/1990', 'Nu', 'Ca Mau', '014')
insert into SinhVien values('08.014.004', 'Nguyen Thi My','Yen', '5/12/1991', 'Nu', 'Bac Lieu', '014')
insert into SinhVien values('08.014.005', 'Tran Ngoc Tu', 'Quyen', '7/26/1990', 'Nu', 'An Giang', '014')
insert into SinhVien values('08.014.006', 'Le Thi', 'Hop', '4/11/1990', 'Nu', 'Hau Giang', '014')
insert into SinhVien values('08.014.007', 'Nguyen Manh', 'Hung', '12/7/1991', 'Nam', 'Can Tho', '014')
insert into SinhVien values('08.014.008', 'Tu Khanh', 'Hung', '12/24/1990', 'Nam', 'Tra Vinh', '014')
insert into SinhVien values('08.014.009', 'Pham Van', 'Hung', '12/12/1991', 'Nam', 'Tien Giang', '014')
insert into SinhVien values('08.014.010', 'Tran Van', 'Huy', '6/26/1990', 'Nam', 'Ben Tre', '014')
insert into SinhVien values('08.014.011', 'Ho Hoang', 'Khai', '9/19/1990', 'Nam', 'Can Tho', '014')
insert into SinhVien values('08.014.012', 'Nguyen Quoc', 'Khuong', '12/13/1991', 'Nam', 'Long An', '014')
insert into SinhVien values('08.014.013', 'Pham Thi Thuy','Lieu', '3/10/1990', 'Nu', 'Soc Trang', '014')
insert into SinhVien values('08.014.014', 'Ho Thuy', 'My', '2/12/1991', 'Nu', 'Ca Mau', '014')
insert into SinhVien values('08.014.015', 'Le Huu Duc', 'Minh', '7/26/1990', 'Nam', 'Ca Mau', '014')
insert into SinhVien values('08.014.016', 'Ñang Thanh', 'Nam', '4/27/1990', 'Nam', 'Ca Mau', '014')
insert into SinhVien values('08.014.017', 'Truong Huu', 'Nghia', '7/31/1991', 'Nam', 'Bac Lieu', '014')
insert into SinhVien values('08.014.018', 'Nguyen Thi Hong','Nhung', '7/4/1990', 'Nu', 'An Giang', '014')
insert into SinhVien values('08.014.019', 'Tran Van', 'Niem', '10/12/1991', 'Nam', 'Hau Giang', '014')
insert into SinhVien values('08.014.020', 'Le Van', 'Ñien', '11/26/1990', 'Nam', 'Can Tho', '014')
insert into SinhVien values('08.014.021', 'Nguyen Huu', 'Phong', '12/30/1991', 'Nam', 'Kien Giang', '014')
--Nhap du lieu bang ket qua
insert into KetQua values('08.020.001', 'CSLT', '8')
insert into KetQua values('08.020.002', 'CSLT', '7')
insert into KetQua values('08.020.003', 'CSLT', '5')
insert into KetQua values('08.020.004', 'CSLT', '6')
insert into KetQua values('08.020.005', 'CSLT', '8')
insert into KetQua values('08.020.006', 'CSLT', '9')
insert into KetQua values('08.020.007', 'CSLT', '7')
insert into KetQua values('08.020.008', 'CSLT', '5')
insert into KetQua values('08.020.009', 'CSLT', '4')
insert into KetQua values('08.020.010', 'CSLT', '7')
insert into KetQua values('08.020.011', 'CSLT', '8')
insert into KetQua values('08.020.012', 'CSLT', '5')
insert into KetQua values('08.020.013', 'CSLT', '8')
insert into KetQua values('08.020.014', 'CSLT', '3')
insert into KetQua values('08.020.015', 'CSLT', '7')
insert into KetQua values('08.020.016', 'CSLT', '8')
insert into KetQua values('08.020.017', 'CSLT', '6')
insert into KetQua values('08.020.018', 'CSLT', '3')
insert into KetQua values('08.020.019', 'CSLT', '6')
insert into KetQua values('08.020.020', 'CSLT', '7')
insert into KetQua values('08.020.001', 'KTLT', '5')
insert into KetQua values('08.020.002', 'KTLT', '7')
insert into KetQua values('08.020.003', 'KTLT', '8')
insert into KetQua values('08.020.004', 'KTLT', '9')
insert into KetQua values('08.020.005', 'KTLT', '5')
insert into KetQua values('08.020.006', 'KTLT', '8')
insert into KetQua values('08.020.007', 'KTLT', '9')
insert into KetQua values('08.020.008', 'KTLT', '6')
insert into KetQua values('08.020.009', 'KTLT', '8')
insert into KetQua values('08.020.010', 'KTLT', '5')
insert into KetQua values('08.020.011', 'KTLT', '8')
insert into KetQua values('08.020.012', 'KTLT', '9')
insert into KetQua values('08.020.013', 'KTLT', '5')
insert into KetQua values('08.020.014', 'KTLT', '8')
insert into KetQua values('08.020.015', 'KTLT', '10')
insert into KetQua values('08.020.016', 'KTLT', '7')
insert into KetQua values('08.020.017', 'KTLT', '8')
insert into KetQua values('08.020.018', 'KTLT', '5')
insert into KetQua values('08.020.019', 'KTLT', '8')
insert into KetQua values('08.020.020', 'KTLT', '4')
insert into KetQua values('08.020.001', 'CTDL', '7')
insert into KetQua values('08.020.002', 'CTDL', '4')
insert into KetQua values('08.020.003', 'CTDL', '8')
insert into KetQua values('08.020.004', 'CTDL', '3')
insert into KetQua values('08.020.005', 'CTDL', '8')
insert into KetQua values('08.020.006', 'CTDL', '9')
insert into KetQua values('08.020.007', 'CTDL', '3')
insert into KetQua values('08.020.008', 'CTDL', '7')
insert into KetQua values('08.020.009', 'CTDL', '8')
insert into KetQua values('08.020.010', 'CTDL', '7')
insert into KetQua values('08.020.011', 'CTDL', '5')
insert into KetQua values('08.020.012', 'CTDL', '8')
insert into KetQua values('08.020.013', 'CTDL', '4')
insert into KetQua values('08.020.014', 'CTDL', '8')
insert into KetQua values('08.020.015', 'CTDL', '4')
insert into KetQua values('08.020.016', 'CTDL', '7')
insert into KetQua values('08.020.017', 'CTDL', '5')
insert into KetQua values('08.020.018', 'CTDL', '7')
insert into KetQua values('08.020.019', 'CTDL', '7')
insert into KetQua values('08.020.020', 'CTDL', '4')
insert into KetQua values('08.020.001', 'CSDL', '7')
insert into KetQua values('08.020.002', 'CSDL', '4')
insert into KetQua values('08.020.003', 'CSDL', '3')
insert into KetQua values('08.020.004', 'CSDL', '7')
insert into KetQua values('08.020.005', 'CSDL', '8')
insert into KetQua values('08.020.006', 'CSDL', '9')
insert into KetQua values('08.020.007', 'CSDL', '3')
insert into KetQua values('08.020.008', 'CSDL', '9')
insert into KetQua values('08.020.009', 'CSDL', '2')
insert into KetQua values('08.020.010', 'CSDL', '9')
insert into KetQua values('08.020.011', 'CSDL', '0')
insert into KetQua values('08.020.012', 'CSDL', '3')
insert into KetQua values('08.020.013', 'CSDL', '6')
insert into KetQua values('08.020.014', 'CSDL', '8')
insert into KetQua values('08.020.015', 'CSDL', '5')
insert into KetQua values('08.020.016', 'CSDL', '7')
insert into KetQua values('08.020.017', 'CSDL', '2')
insert into KetQua values('08.020.018', 'CSDL', '4')
insert into KetQua values('08.020.019', 'CSDL', '7')
insert into KetQua values('08.020.020', 'CSDL', '8')
insert into KetQua values('08.061.001', 'RD', '4')
insert into KetQua values('08.061.002', 'RD', '5')
insert into KetQua values('08.061.003', 'RD', '6')
insert into KetQua values('08.061.004', 'RD', '7')
insert into KetQua values('08.061.005', 'RD', '8')
insert into KetQua values('08.061.006', 'RD', '3')
insert into KetQua values('08.061.007', 'RD', '7')
insert into KetQua values('08.061.008', 'RD', '8')
insert into KetQua values('08.061.009', 'RD', '4')
insert into KetQua values('08.061.010', 'RD', '8')
insert into KetQua values('08.061.011', 'RD', '9')
insert into KetQua values('08.061.012', 'RD', '4')
insert into KetQua values('08.061.013', 'RD', '8')
insert into KetQua values('08.061.014', 'RD', '3')
insert into KetQua values('08.061.015', 'RD', '8')
insert into KetQua values('08.061.016', 'RD', '3')
insert into KetQua values('08.061.017', 'RD', '8')
insert into KetQua values('08.061.018', 'RD', '7')
insert into KetQua values('08.061.019', 'RD', '6')
insert into KetQua values('08.061.020', 'RD', '8')
insert into KetQua values('08.061.001', 'SP', '7')
insert into KetQua values('08.061.002', 'SP', '4')
insert into KetQua values('08.061.003', 'SP', '3')
insert into KetQua values('08.061.004', 'SP', '7')
insert into KetQua values('08.061.005', 'SP', '7')
insert into KetQua values('08.061.006', 'SP', '8')
insert into KetQua values('08.061.007', 'SP', '3')
insert into KetQua values('08.061.008', 'SP', '9')
insert into KetQua values('08.061.009', 'SP', '9')
insert into KetQua values('08.061.010', 'SP', '4')
insert into KetQua values('08.061.011', 'SP', '8')
insert into KetQua values('08.061.012', 'SP', '4')
insert into KetQua values('08.061.013', 'SP', '7')
insert into KetQua values('08.061.014', 'SP', '3')
insert into KetQua values('08.061.015', 'SP', '7')
insert into KetQua values('08.061.016', 'SP', '9')
--Thực hiện các câu truy vấn sau:
--Câu 1: Cho biết thông tin của những sinh viên nu khoa Ky Thuat Cong Nghe
select *
from SinhVien a join Khoa b on a.MaKhoa=b.MaKhoa
where a.GioiTinh='nu' and b.TenKhoa='Ky Thuat cong Nghe'
--Câu 2: Cho biết thông tin của những sinh viên Khoa Cong Nghe Thong Tin có họ Nguyen
select *
from SinhVien a join Khoa b on a.MaKhoa=b.MaKhoa
where b.TenKhoa='Cong Nghe Thong Tin' and a.HoLot like '%Nguyen%'
--Câu 3: Cho biết thông tin của những sinh viên Ngoai Ngu – Dong Phuong Hoc không phải họ Tran
select *
from SinhVien a join Khoa b on a.MaKhoa=b.MaKhoa
where b.TenKhoa='Ngoai Ngu - Dong Phuong Hoc' and a.HoLot not like '%Tran%'
--Câu 4: Cho biết thông tin của những sinh viên ở An Giang có điểm trung bình lớn hơn 6.5
Select*
From SinhVien a join ketqua b on b.mssv=a.mssv
Where a.diachi='an giang' and b.diem>6.5
--Câu 5: Cho biết thông tin của những sinh viên ở Vinh Long, Kien Giang có điểm trung bình trong
select *
from SinhVien a join KetQua b on a.MSSV=b.MSSV
where a.diachi='Vinh Long'or a.diachi='Kien Giang' and b.Diem=(select AVG(Diem) from KetQua)
--Câu 6: Cho biết thông tin của những sinh viên có điểm môn Co so du lieu nhỏ nhất
select *
from SinhVien a join KetQua b on a.MSSV=b.MSSV join MonHoc c on c.MaMH=b.MaMH
where c.TenMH='Co So Du Lieu' and b.Diem=(select min(Diem) from KetQua)
--Câu 7: Tìm 3 sinh viên có điểm lớn nhất môn Speaking
SELECT TOP 3 a.MSSV,HoLot,Ten,Diem
FROM SinhVien a JOIN KetQua b ON a.MSSV=b.MSSV
ORDER BY Diem DESC
--Câu 8: Tìm những sinh viên khoa cong nghe thong tin có điểm trung bình trong khoảng 7 đến 8
select *
from SinhVien a join KetQua b on a.MSSV=b.MSSV join Khoa c on c.MaKhoa=a.MaKhoa
where b.Diem between 7 and 8 and c.TenKhoa='Cong Nghe Thong Tin'
--Câu 9: Cho biết những môn học có số tiết lớn nhất.
select *
from MonHoc
where SoTiet=(select max(SoTiet) from MonHoc)
--Câu 10: Cho biết điểm môn co so lap trinh lớn nhất của những sinh viên có địa chỉ ở Vinh Long
select c.TenMH, MAX(Diem) as diemlonnhat
from SinhVien a join KetQua b on a.MSSV=b.MSSV join MonHoc c on c.MaMH=b.MaMH
where c.TenMH='Co So Lap Trinh'and a.diachi='Vinh Long'
group by c.TenMH