USE QLBongDa
GO
-- 2A.1
SELECT MACT, HOTEN, SO, VITRI, NGAYSINH, DIACHI
FROM CAUTHU

-- 2A.2
SELECT MACT, HOTEN, SO, VITRI, NGAYSINH, DIACHI
FROM CAUTHU
WHERE SO = 7 AND VITRI = N'Tiền vệ'

-- 2A.3
SELECT TENHLV, NGAYSINH, DIACHI, DIENTHOAI
FROM HUANLUYENVIEN

-- 2A.4
SELECT MACT, HOTEN, SO, VITRI, NGAYSINH, DIACHI
FROM CAUTHU, CAULACBO, QUOCGIA
WHERE CAUTHU.MACLB = CAULACBO.MACLB 
AND CAUTHU.MAQG = QUOCGIA.MAQG 
AND TENQG =  N'Việt Nam'
AND TENCLB = N'BECAMEX BÌNH DƯƠNG'

-- 2A.5
SELECT MACT, HOTEN, SO, VITRI, NGAYSINH, DIACHI
FROM CAUTHU, CAULACBO, QUOCGIA
WHERE CAUTHU.MACLB = CAULACBO.MACLB 
AND CAUTHU.MAQG = QUOCGIA.MAQG 
AND TENQG = N'Bra-xin'
AND TENCLB = N'SHB ĐÀ NẴNG'

-- 2A.6
SELECT *
FROM CAUTHU, CAULACBO, SANVD
WHERE CAUTHU.MACLB = CAULACBO.MACLB
AND CAULACBO.MASAN = SANVD.MASAN
AND SANVD.TENSAN = N'Long An'

-- 2A.7
SELECT MATRAN, NGAYTD, TENSAN, C1.TENCLB AS TENCLB1, C2.TENCLB AS TENCLB2, KETQUA
FROM TRANDAU, CAULACBO C1, CAULACBO C2, SANVD
WHERE TRANDAU.MACLB1 = C1.MACLB AND TRANDAU.MACLB2 = C2.MACLB AND TRANDAU.MASAN = SANVD.MASAN

-- 2A.8
SELECT HUANLUYENVIEN.MAHLV, TENHLV, NGAYSINH, DIACHI, VAITRO, TENCLB
FROM HUANLUYENVIEN, CAULACBO, HLV_CLB, QUOCGIA
WHERE TENQG = N'Việt Nam' AND HUANLUYENVIEN.MAQG = QUOCGIA.MAQG
AND HUANLUYENVIEN.MAHLV = HLV_CLB.MAHLV
AND HLV_CLB.MACLB = CAULACBO.MACLB

-- 2A.9
SELECT TOP 3 TENCLB, DIEM
FROM CAULACBO, BANGXH
WHERE VONG = 3
AND CAULACBO.MACLB = BANGXH.MACLB
ORDER BY DIEM DESC

--2A.10
SELECT HUANLUYENVIEN.MAHLV, TENHLV, DIACHI, NGAYSINH, VAITRO, TENCLB
FROM HUANLUYENVIEN, HLV_CLB, CAULACBO, TINH
WHERE HUANLUYENVIEN.MAHLV = HLV_CLB.MAHLV
AND HLV_CLB.MACLB = CAULACBO.MACLB
AND CAULACBO.MATINH = TINH.MATINH
AND TENTINH = N'Bình Dương'

-- 2B.1
SELECT TENCLB, COUNT(MACT) AS [Số cầu thủ]
FROM CAUTHU, CAULACBO
WHERE CAUTHU.MACLB = CAULACBO.MACLB
GROUP BY TENCLB

-- 2B.2
SELECT TENCLB, COUNT(MACT) AS [Số cầu thủ nước ngoài]
FROM CAUTHU, CAULACBO, QUOCGIA
WHERE CAUTHU.MACLB = CAULACBO.MACLB
AND CAUTHU.MAQG = QUOCGIA.MAQG
AND TENQG != N'Việt Nam'
GROUP BY TENCLB

-- 2B.3
SELECT TENCLB, COUNT(MACT) AS [Số cầu thủ nước ngoài]
FROM CAUTHU, CAULACBO, QUOCGIA
WHERE CAUTHU.MACLB = CAULACBO.MACLB
AND CAUTHU.MAQG = QUOCGIA.MAQG
AND TENQG != N'Việt Nam'
GROUP BY TENCLB
HAVING COUNT(MACT) >= 2

-- 2B.4
SELECT TENTINH, TENCLB, COUNT(MACT) AS [Số lượng cầu thủ]
FROM CAUTHU, CAULACBO, TINH
WHERE CAUTHU.MACLB = CAULACBO.MACLB
AND CAULACBO.MATINH = TINH.MATINH
AND VITRI = N'Tiền đạo'
GROUP BY TENCLB, TENTINH

-- 2B.5
SELECT TOP 1 TENCLB, TENTINH
FROM CAULACBO, TINH, BANGXH
WHERE VONG = 3
AND CAULACBO.MACLB = BANGXH.MACLB
AND CAULACBO.MATINH = TINH.MATINH
AND HANG = 1

-- 2C.1
SELECT TENHLV
FROM HUANLUYENVIEN, HLV_CLB
WHERE HUANLUYENVIEN.MAHLV = HLV_CLB.MAHLV
AND VAITRO != NULL
AND DIENTHOAI = NULL

-- 2C.2
SELECT TENHLV
FROM HUANLUYENVIEN LEFT OUTER JOIN HLV_CLB ON HUANLUYENVIEN.MAHLV = HLV_CLB.MAHLV
WHERE VAITRO = NULL

-- 2C.3
SELECT HOTEN
FROM CAUTHU, BANGXH
WHERE VONG = 3
AND (HANG < 3 OR HANG > 6)
AND CAUTHU.MACLB = BANGXH.MACLB

-- 2C.4
SELECT NGAYTD, TENSAN, CLB1.TENCLB AS TENCLB1, CLB2.TENCLB AS TENCLB2, KETQUA
FROM TRANDAU, SANVD, CAULACBO AS CLB1, CAULACBO AS CLB2, BANGXH
WHERE (BANGXH.VONG = 3 AND HANG = 1)
AND (CLB1.MACLB = BANGXH.MACLB OR CLB2.MACLB = BANGXH.MACLB)
AND TRANDAU.MASAN = SANVD.MASAN
AND CLB1.MACLB = TRANDAU.MACLB1 AND	CLB2.MACLB = TRANDAU.MACLB2

-- 3A.1
SELECT NGAYTD, CLB1.TENCLB AS TENCLB1, CLB2.TENCLB AS TENCLB2, KETQUA
FROM TRANDAU, CAULACBO CLB1, CAULACBO CLB2
WHERE KETQUA LIKE '%-0'
AND (MACLB1 = CLB1.MACLB AND MACLB2 = CLB2.MACLB)

-- 3A.2
SELECT MACT, HOTEN, NGAYSINH
FROM CAUTHU
WHERE HOTEN LIKE N'%_Công_%'

-- 3A.3
SELECT MACT, HOTEN, NGAYSINH
FROM CAUTHU
WHERE HOTEN NOT LIKE N'Nguyễn_%'

-- 3A.4
SELECT MAHLV, TENHLV, NGAYSINH, DIACHI, (year(getdate()) - year(NGAYSINH)) AS TUOI
FROM HUANLUYENVIEN, QUOCGIA
WHERE HUANLUYENVIEN.MAQG = QUOCGIA.MAQG
AND TENQG = N'Việt Nam'
AND (((year(getdate()) - year(NGAYSINH)) >= 35 and (year(getdate()) - year(NGAYSINH)) <= 40))

-- 3A.5
SELECT TENCLB
FROM CAULACBO, HUANLUYENVIEN, HLV_CLB
WHERE (day(NGAYSINH) = 20 and month(NGAYSINH) = 5)
AND HUANLUYENVIEN.MAHLV = HLV_CLB.MAHLV
AND CAULACBO.MACLB = HLV_CLB.MACLB

-- 3A.6
SELECT TOP 1 TENCLB, TENTINH
FROM BANGXH, CAULACBO, TINH
WHERE VONG = 3
AND BANGXH.MACLB = CAULACBO.MACLB
AND CAULACBO.MATINH = TINH.MATINH
ORDER BY HIEUSO DESC

-- 3B.1
SELECT CAULACBO.MACLB, TENCLB, TENSAN, SANVD.DIACHI, COUNT(MACT) AS [SO CT NUOC NGOAI]
FROM CAULACBO, SANVD, CAUTHU, QUOCGIA
WHERE CAULACBO.MACLB = CAUTHU.MACLB 
AND SANVD.MASAN = CAULACBO.MASAN
AND CAUTHU.MAQG = QUOCGIA.MAQG AND TENQG != N'Việt Nam'
GROUP BY CAULACBO.MACLB, TENCLB, TENSAN, SANVD.DIACHI
HAVING (count(MACT) >= 2)


-- 3B.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, BANGXH, (select max(vong) as vongcuoi from BANGXH) v

WHERE VONG = v.vongcuoi
AND CAULACBO.MACLB = BANGXH.MACLB
AND CAULACBO.MATINH = TINH.MATINH
AND (cast(left(HIEUSO, charindex('-', HIEUSO)-1) as int) 
		- cast(right(HIEUSO, len(HIEUSO) - charindex('-', HIEUSO)) as int)) = 
 (select MAX(cast(left(HIEUSO, charindex('-', HIEUSO)-1) as int) 
				- cast(right(HIEUSO, len(HIEUSO) - charindex('-', HIEUSO)) as int))
	from BANGXH where VONG = v.vongcuoi)

select * from BANGXH where vong = 4

-- 3B.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, C1.TENCLB AS TENCLB1, C2.TENCLB AS TENCLB2, KETQUA
FROM TRANDAU, SANVD, CAULACBO C1, CAULACBO C2, BANGXH
WHERE BANGXH.VONG = 3 
AND HANG >= ALL (select HANG from BANGXH where VONG = 3)
AND (TRANDAU.MACLB1 = BANGXH.MACLB OR TRANDAU.MACLB2 = BANGXH.MACLB)
AND TRANDAU.MACLB1 = C1.MACLB AND TRANDAU.MACLB2 = C2.MACLB
AND TRANDAU.MASAN = SANVD.MASAN

-- 3B.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.
SELECT MACLB, TENCLB
FROM CAULACBO
WHERE MACLB IN 
(select MACLB from CAULACBO as C1 where 
(select count(MATRAN) from TRANDAU where TRANDAU.MACLB1 = C1.MACLB or TRANDAU.MACLB2 = C1.MACLB) = 
(select count(CAULACBO.MACLB) from CAULACBO) - 1
)


select distinct MACLB
from CAULACBO c
where not exists (select MACLB from CAULACBO c1 
	except ((select MACLB1 as MACLB from TRANDAU t1 where t1.MACLB2 = c.MACLB)
			union (select MACLB2 as MACLB from TRANDAU t2 where t2.MACLB1 = c.MACLB)
			union (SELECT MACLB from CAULACBO c2 where c2.MACLB = c.MACLB)))

-- 3B.5
-- 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
-- ( chỉ tính sân nhà) tro ng mùa giải năm 2009.
SELECT MACLB, TENCLB
FROM CAULACBO
WHERE MACLB IN 
(select MACLB from CAULACBO as C1 where 
(select count(MATRAN) from TRANDAU where TRANDAU.MACLB1 = C1.MACLB) = 
(select count(CAULACBO.MACLB) from CAULACBO) - 1
)

select distinct MACLB1 
from TRANDAU t1
where not exists (select MACLB from CAULACBO c1 
	except ((select MACLB2 from TRANDAU t2 where t1.MACLB1 = t2.MACLB1) union (SELECT MACLB from CAULACBO c2 where MACLB = t1.MACLB1)))


/*
--
ALTER TABLE CAUTHU
DROP CONSTRAINT CHK_VITRI
GO

-- 3C.1
ALTER TABLE CAUTHU
ADD CONSTRAINT CHK_VITRI CHECK (VITRI IN (N'Thủ môn', N'Tiền đạo', N'Tiền vệ', N'Trung vệ', N'Hậu vệ'));
GO

--
ALTER TABLE HLV_CLB
DROP CONSTRAINT CHK_VAITRO
GO

-- 3C.2
ALTER TABLE HLV_CLB
ADD CONSTRAINT CHK_VAITRO CHECK (VAITRO IN (N'HLV chính', N'HLV phụ', N'HLV thể lực', N'HLV thủ môn'));
GO

--
ALTER TABLE CAUTHU
DROP CONSTRAINT CHK_NGAYSINH
-- 3C.3
ALTER TABLE CAUTHU
ADD CONSTRAINT CHK_NGAYSINH CHECK (year(getdate()) - year(NGAYSINH) >= 18)

--
ALTER TABLE TRANDAU
DROP CONSTRAINT CHK_KETQUA
-- 3C.4
ALTER TABLE TRANDAU
ADD CONSTRAINT CHK_KETQUA CHECK (KETQUA LIKE '%-%')
*/

/*
-- 3D.1
	create view view1
	as
	SELECT ct.MACT, ct.HOTEN, ct.NGAYSINH, ct.DIACHI, ct.VITRI
	FROM CAULACBO as clb, CAUTHU as ct, QUOCGIA as qg
	WHERE ct.MAQG= qg.MAQG
	AND qg.TENQG=N'Bra-xin'
	AND ct.MACLB= clb.MACLB
	AND clb.TENCLB=N'SHB Đà Nẵng'
	select * from view1
-- 3D.2
	create view view2
	as
	SELECT td.MATRAN, td.NGAYTD, svd.TENSAN, clb1.TENCLB as TENCLB1, clb2.TENCLB as TENCLB2, td.KETQUA
	FROM TRANDAU as td , SANVD as svd , CAULACBO as clb1 , CAULACBO as clb2
	WHERE td.MASAN = svd.MASAN
	  AND td.MACLB1 = clb1.MACLB
	  AND td.MACLB2 = clb2.MACLB
	  AND td.VONG = 2
	  AND td.NAM = 2009  ;
	select * from view2
-- 3D.3
	create view view3
	as
	SELECT hlv.MAHLV, hlv.TENHLV, hlv.NGAYSINH, hlv.DIACHI, HLV_CLB.VAITRO, clb.TENCLB, qg.TENQG
	FROM HUANLUYENVIEN as hlv , HLV_CLB , CAULACBO as clb , QUOCGIA as qg
	WHERE hlv.MAHLV = HLV_CLB.MAHLV
	  AND hlv.MAQG = qg.MAQG
	  AND HLV_CLB.MACLB = clb.MACLB
	  AND qg.TENQG = N'Việt Nam'  ;
	select * from view3
-- 3D.4
	create view view4
	as
	SELECT clb.MACLB, clb.TENCLB, svd.TENSAN, svd.DIACHI, count(ct.MACT) as [số lượng cầu thủ nước ngoài]
	FROM CAULACBO as clb , SANVD as svd , CAUTHU as ct
	WHERE clb.MASAN = svd.MASAN
	  AND clb.MACLB = ct.MACLB
	  AND ct.MAQG not like 'VN'
	GROUP BY clb.MACLB, clb.TENCLB, svd.TENSAN, svd.DIACHI
	HAVING count(ct.MACT) > 2  ;
	select * from view4
-- 3D.5
	create view view5
	as
	SELECT TINH.TENTINH , count(ct.MACT) as [số lượng cầu thủ]
	FROM TINH , CAULACBO as clb , CAUTHU as ct
	WHERE TINH.MATINH = clb.MATINH
	  AND clb.MACLB = ct.MACLB
	  AND ct.VITRI = N'Tiền đạo'
	GROUP BY TINH.TENTINH  ;
	select * from view5
-- 3D.6
	create view view6
	as
	SELECT TOP 1 clb.TENCLB , TINH.TENTINH
	FROM CAULACBO as clb , TINH , BANGXH as bxh
	WHERE clb.MATINH = TINH.MATINH
	  AND clb.MACLB = bxh.MACLB
	  AND bxh.VONG = 3 AND bxh.NAM = 2009
	ORDER BY bxh.DIEM DESC  ;
	select * from view6
-- 3D.7
	create view view7
	as
	SELECT TENHLV
	FROM HUANLUYENVIEN
	WHERE DIENTHOAI IS NULL  ;
	select * from view7
-- 3D.8
	create view view8
	as
	SELECT hlv.TENHLV
	FROM HUANLUYENVIEN as hlv , QUOCGIA as qg , HLV_CLB
	WHERE hlv.MAQG = qg.MAQG
	  AND qg.TENQG = N'Việt Nam'
	  AND hlv.MAHLV = HLV_CLB.MAHLV
	  AND HLV_CLB.VAITRO IS NULL  ;
	select *from view8
-- 3D.9
	create view view9
	as
	SELECT MACLB1, MACLB2, NAM, VONG, KETQUA
	FROM TRANDAU as td
	select * from view9
-- 3D.10
	create view view10
	as
	SELECT td.MACLB1 as MACLB, NAM, VONG, KETQUA
	FROM TRANDAU as td
	select *from view10
-- 3D.11
	create view view11
	as
	SELECT td.MACLB2 as MACLB, NAM, VONG, KETQUA
	FROM TRANDAU as td
	select * from view11
-- 3D.12
	create view view12
	as
	SELECT td.NGAYTD, svd.TENSAN, clb1.TENCLB as TENCLB1, clb2.TENCLB as TENCLB2, td.KETQUA
	FROM TRANDAU as td , SANVD as svd , BANGXH as bxh , CAULACBO as clb1 , CAULACBO as clb2
	WHERE (clb1.MACLB = bxh.MACLB OR clb2.MACLB = bxh.MACLB)
	  AND bxh.HANG = 1
	  AND td.MASAN = svd.MASAN
	  AND td.MACLB1 = clb1.MACLB
	  AND td.MACLB2 = clb2.MACLB
	  AND (bxh.VONG = 3 AND bxh.NAM = 2009)  ;
	  select * from view12
-- 3D.13
	create view view13
	as
	SELECT td.NGAYTD, svd.TENSAN, clb1.TENCLB as TENCLB1, clb2.TENCLB as TENCLB2, td.KETQUA
	FROM TRANDAU as td , SANVD as svd , BANGXH as bxh , CAULACBO as clb1 , CAULACBO as clb2
	WHERE (clb1.MACLB = bxh.MACLB OR clb2.MACLB = bxh.MACLB)
	  AND bxh.HANG =  ( SELECT max(HANG) 
					   FROM BANGXH
					   WHERE VONG = 3 AND NAM = 2009 )
	  AND td.MASAN = svd.MASAN
	  AND td.MACLB1 = clb1.MACLB
	  AND td.MACLB2 = clb2.MACLB
	  AND (bxh.VONG = 3 AND bxh.NAM = 2009)  ;
	  select * from view13
	  */