Untitled
unknown
plain_text
2 months ago
2.1 kB
3
Indexable
create database QLSinhVien go use QLSinhVien go create table GVCN ( MaGV char(20) primary key, HT Nvarchar (50), DT varchar(20), QueQuan Nvarchar(50) ) create table LOP ( MaLop char(20) primary key, TenLop Nvarchar(50), Khoa int, MaGV char(20) foreign key references GVCN(MaGV) ) create table SINHVIEN ( MaSV char(20) primary key, HoTen Nvarchar(50), GioiTinh Nvarchar(10), MaLop char(20) foreign key references LOP(MaLop) ) insert GVCN values ('GV01',N'Vũ Thị Dương','0978123123',N'Hải Dương'), ('GV02',N'Nguyễn Văn Hiệp','0912118887',N'Hà Nội'), ('GV03',N'Vũ Việt Thắng','0982666666',N'Thái Nguyên') insert LOP values ('2021DHCNTT01',N'Công nghệ thông tin 1',15,'GV01'), ('2021DHCNTT02',N'Công nghệ thông tin 2',15,'GV03'), ('2020DHHTT01',N'Hệ thống thông tin 1',14,'GV03'), ('2020DHHTT02',N'Hệ thống thông tin 1',14,'GV03') insert SINHVIEN values ('S001',N'Nguyễn Thị Minh',N'Nữ','2021DHCNTT01'), ('S002',N'Đào Đăng Hải',N'Nam','2021DHCNTT01'), ('S003',N'Nguyễn Thị Lý',N'Nữ','2020DHHTT01'), ('S004',N'Đào Ngọc Thủy',N'Nam','2020DHHTT01'), ('S005',N'Ngô Thị Mỹ Lệ',N'Nữ','2020DHHTT02') select * from GVCN select * from LOP select * from SINHVIEN --a select * from SINHVIEN where MaLop = '2020DHHTT01' --b select GVCN.MaGV,HT,TenLop from GVCN join LOP on GVCN.MaGV = LOP.MaGV where QueQuan = N'Thái Nguyên' and Khoa = 15 --c select MaSV,HoTen,TenLop,Khoa from SINHVIEN join LOP on SINHVIEN.MaLop = LOP.MaLop where TenLop = N'Hệ thống thông tin 1' and Khoa = 14 order by Khoa ASC, TenLop ASC --d select LOP.MaLop,TenLop,count(MaSV) as SLnu from LOP join SINHVIEN on LOP.MaLop = SINHVIEN.MaLop where GioiTinh = N'Nữ' group by LOP.MaLop,TenLop --e select GVCN.MaGV,HT,count(MaLop) as SoLopCN from GVCN join LOP on GVCN.MaGV = LOP.MaGV group by GVCN.MaGV,HT having count(MaLop)>=2 --f select MaGV,HT from GVCN where MaGV not in (select MaGV from LOP where Khoa = 15)
Editor is loading...
Leave a Comment