/* Trinh Van Duong 20204736 */

/* . Cho biết NGAYTD, TENCLB1, TENCLB2, KETQUA các trận đấu diễn ra vào tháng 3 trên sân 
nhà mà không bị thủng lưới*/
Select NGAYTD as NGAYTD, clb1.TENCLB as TENCLB1, clb2.TENCLB as TENCLB2, KETQUA
from CAULACBO as clb1, CAULACBO as clb2, TRANDAU
where clb1.MACLB = TRANDAU.MACLB1
and clb2.MACLB = TRANDAU.MACLB2
and ( (	(clb1.MASAN = TRANDAU.MASAN and LEFT(TRANDAU.KETQUA, 1) = '0')
		or (clb2.MASAN = TRANDAU.MASAN and RIGHT(TRANDAU.KETQUA, 1) = '0') )
	and MONTH(TRANDAU.NGAYTD) = 3 )

/* 2. Cho biết mã số, họ tên, ngày sinh của những cầu thủ có họ lót là “Công”. */
Select MACT, HOTEN, NGAYSINH
from CAUTHU
where HOTEN like N'%Công%'

/* 3. Cho biết mã số, họ tên, ngày sinh của những cầu thủ có họ không phải là họ “Nguyễn “*/
Select MACT, HOTEN, NGAYSINH
from CAUTHU
where LEFT(HOTEN, 6) <> N'Nguyễn'

/* 4. Cho biết mã huấn luyện viên, họ tên, ngày sinh, đ ịa chỉ của những huấn luyện viên Việt 
Nam có tuổi nằm trong khoảng 35-40*/
Select HUANLUYENVIEN.MAHLV as MAHLV, TENHLV, NGAYSINH, DIACHI
from HUANLUYENVIEN, QUOCGIA
where HUANLUYENVIEN.MAQG = QUOCGIA.MAQG
and ( TENQG like N'%Việt Nam%' and (YEAR(GETDATE()) -  YEAR(NGAYSINH)) between 35 and 40) 

/*5. Cho biết tên câu lạc bộ có huấn luyện viên trưởng sinh vào ngày 20 tháng 8 năm 2019. */
Select TENCLB
from HUANLUYENVIEN, HLV_CLB, CAULACBO
where HUANLUYENVIEN.MAHLV = HLV_CLB.MAHLV
and HLV_CLB.MACLB = CAULACBO.MACLB
and ( VAITRO like N'%HLV Chính%'
	and DAY(HUANLUYENVIEN.NGAYSINH) = 20
	and MONTH(HUANLUYENVIEN.NGAYSINH) = 8 
	and YEAR(HUANLUYENVIEN.NGAYSINH) = 2019 )

/* 6. Cho biết tên câu lạc bộ, tên tỉnh mà CLB đang đóng có số bàn thắng nhiều nhất tính đến 
hết vòng 3 năm 2009*/

/* b. Truy vấn con
1. Cho biết mã câu lạc bộ, tên câu lạc bộ, tên sân vận động, địa chỉ và số lượng cầu thủ nước 
ngoài (Có quốc tịch khác “Việt Nam”) tương ứng của các câu lạc bộ có nhiều hơn 2 cầu 
thủ nước ngoài.*/
			Select CAUTHU.MACLB, TENCLB, TENSAN, SANVD.DIACHI, COUNT(MACT) AS ngoaibinh
	from CAUTHU, QUOCGIA, CAULACBO, SANVD
	WHERE CAUTHU.MAQG = QUOCGIA.MAQG
	and CAUTHU.MACLB = CAULACBO.MACLB
	and CAULACBO.MASAN = SANVD.MASAN
	and (TENQG not like N'%Việt Nam%')
	group by TENCLB
	having COUNT(CAUTHU.MACT) > 2

/* 2. Cho biết tên câu lạc bộ, tên tỉnh mà CLB đang đóng có hiệu số bàn thắng bại cao 
nhất năm 2009*/
Select TENCLB, TENTINH
from CAULACBO, TINH
WHERE CAULACBO.MATINH = TINH.MATINH
AND CAULACBO.MACLB IN (select MACLB
						from BANGXH
						WHERE HANG = 1 and NAM = 2009)

/* 3. Cho biết danh sách các trận đấu ( NGAYTD, TENSAN, TENCLB1, TENCLB2, KETQUA) của 
câu lạc bộ CLB có thứ hạng thấp nhất trong bảng xếp hạng vòng 3 năm 2009.*/
Select NGAYTD, TENSAN, clb1.TENCLB as TENCLB1, clb2.TENCLB as CLB2
from TRANDAU, SANVD, CAULACBO as clb1, CAULACBO as clb2
where TRANDAU.MACLB1 = clb1.MACLB
and clb2.MACLB = TRANDAU.MACLB2
and TRANDAU.MASAN = SANVD.MASAN
and ( TRANDAU.MACLB1 = (select distinct MACLB
						from BANGXH
						where HANG >= ALL(Select convert(int, HANG) from BANGXH)
						and NAM = 2009)
	or TRANDAU.MACLB2 = (select distinct MACLB
						from BANGXH
						where HANG >= ALL(Select convert(int, HANG) from BANGXH) and NAM = 2009))

/* 4. Cho biết mã câu lạc bộ, tên câu lạc bộ đã tham gia thi đấu với tất cả các câu lạc bộ còn lại 
(kể cả sân nhà và sân khách) trong mùa giải năm 2009*/