Untitled

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)