use QLSV
go
-- Danh sach cac sinh vien thuoc khoa CNTT, khoa hoc 2002-2006
select SinhVien.* from SinhVien
Left join Lop on SinhVien.MaLop = Lop.MaLop
Left join KhoaHoc on Lop.MaKhoaHoc = KhoaHoc.MaKhoaHoc
Left join Khoa on Lop.MaKhoa = Khoa.MaKhoa
 where Khoa.MaKhoa='CNTT'
and KhoaHoc.namBatDau = 2002
and KhoaHoc.namKetThuc= 2006

-- Thong tin sinh vien ( MSSV, HoTen, NamSinh) cua cac sinh vien hoc som hon tuoi quy dinh ( quy dinh: sinh vien du 18 tuoi khi bat dau khoa hoc )
-- day(getdate()) -> lay ngay trong thang
-- month(getdate()) -> lay thang trong nam
-- year(getdate()) -> lay nam
select SinhVien.* from SinhVien
left join  Lop on SinhVien.MaLop = Lop.MaLop
left join KhoaHoc on Lop.MaKhoaHoc = KhoaHoc.MaKhoaHoc
where KhoaHoc.NamBatDau - SinhVien.NamSinh < 18

-- Sinh Vien khoa CNTT, khoa hoc 2002-2006 chua hoc mon Cau Truc Du Lieu 1
Select SinhVien.* from SinhVien
left join Lop on SinhVien.MaLop = Lop.MaLop
left join Khoa on Lop.MaKhoa = Khoa.MaKhoa
left join KhoaHoc on Lop.MaKhoaHoc = KhoaHoc.MaKhoaHoc
left join MonHoc on MonHoc.MaKhoa = Khoa.MaKhoa
where Khoa.MaKhoa = ' CNTT'
and KhoaHoc.namBatDau = 2002
and KhoaHoc.namKetThuc = 2006
and SinhVien.MaSV not in 
(
Select SinhVien.MaSV from SinhVien
left join Lop on SinhVien.MaLop = Lop.MaLop
left join Khoa on Lop.MaKhoa = Khoa.MaKhoa
left join KhoaHoc on Lop.MaKhoaHoc = KhoaHoc.MaKhoaHoc
left join MonHoc on MonHoc.MaKhoa = Khoa.MaKhoa
where Khoa.MaKhoa = ' CNTT'
and KhoaHoc.namBatDau = 2002
and KhoaHoc.namKetThuc = 2006
and MonHoc.tenMH = N'Cấu trúc dữ liệu 1'
)