sql.sql

----------------------------------
--BAI3----------------------------
----------------------------------

--A.6
SELECT  TOP 1 CAST(SUBSTRING(HIEUSO, 1, 1) AS INT) AS BANTHANG, CAULACBO.TENCLB, TINH.TENTINH
			FROM BANGXH JOIN CAULACBO ON BANGXH.MACLB = CAULACBO.MACLB
						JOIN TINH ON  CAULACBO.MATINH = TINH.MATINH

WHERE VONG=3
ORDER BY BANTHANG DESC


--B.1
SELECT CLB.MACLB, CLB.TENCLB, SANVD.TENSAN, TINH.TENTINH, COUNT(*) AS SLCTNN
FROM CAULACBO CLB JOIN CAUTHU  CT ON CLB.MACLB = CT.MACLB
				  JOIN QUOCGIA QG ON CT.MAQG = QG.MAQG
				  JOIN SANVD ON SANVD.MASAN = CLB.MASAN
				  JOIN TINH ON TINH.MATINH = CLB.MATINH
WHERE QG.TENQG!=N'Việt Nam'
GROUP BY CLB.MACLB, CLB.TENCLB, SANVD.TENSAN, TINH.TENTINH
HAVING COUNT(*) > 0

--B.2
SELECT TOP 1 CLB.TENCLB, TINH.TENTINH, (CAST(SUBSTRING(HIEUSO, 1, 1) AS INT)  -
	   CAST(SUBSTRING(HIEUSO, 3, 1) AS INT) ) AS HIEUSO 
FROM BANGXH JOIN CAULACBO CLB ON BANGXH.MACLB = CLB.MACLB
			JOIN TINH ON TINH.MATINH = CLB.MATINH
WHERE VONG = 4
ORDER BY HIEUSO DESC

--B.3
SELECT TOP 1 TRANDAU.NGAYTD, SANVD.TENSAN, TRANDAU.MACLB1, TRANDAU.MACLB2, TRANDAU.KETQUA
FROM BANGXH JOIN CAULACBO CLB ON BANGXH.MACLB = CLB.MACLB
			JOIN TRANDAU ON TRANDAU.MACLB1 = CLB.MACLB OR TRANDAU.MACLB2 = CLB.MACLB
			JOIN SANVD ON TRANDAU.MASAN = SANVD.MASAN
WHERE TRANDAU.VONG=3 
ORDER BY HANG DESC

--B.4
--B.5

--C.1
----DUNG TRIGGER
CREATE TRIGGER CHECK_VITRI
ON CAUTHU
FOR UPDATE, INSERT
AS
	IF(UPDATE(VITRI))
	BEGIN
	DECLARE @VITRI nvarchar(20)
	SET @VITRI=(SELECT VITRI FROM INSERTED)
	IF(@VITRI NOT IN (N'Thủ môn', N'Tiền đạo', N'Trung vệ', N'Hậu vệ', N'Tiền vệ'))
		BEGIN
			PRINT 'VI TRI KHONG HOP LE'
			ROLLBACK TRAN
		END
	END
DROP TRIGGER CHECK_VITRI

----DUNG CONSTRAINT
ALTER TABLE CAUTHU
ADD CHECK (VITRI IN (N'Thủ môn', N'Tiền đạo', N'Trung vệ', N'Hậu vệ', N'Tiền vệ'))

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

--B.3
ALTER TABLE CAUTHU
ADD CHECK (YEAR(GETDATE())-YEAR(NGAYSINH) >= 18)

--B.4
ALTER TRIGGER CHECK_KETQUA
ON TRANDAU
FOR UPDATE, INSERT
AS
	IF(UPDATE(KETQUA))
	BEGIN
	DECLARE 
		@KETQUA VARCHAR(50),
		@BT1 VARCHAR(50),
		@BT2 VARCHAR(50)
	SET @KETQUA=(SELECT KETQUA FROM INSERTED)
	IF(CHARINDEX('-', @KETQUA)=0) 
		BEGIN
			ROLLBACK TRAN
		END		
	SET	@BT1=PARSENAME(REPLACE(@KETQUA, '-', '.'), 2)
	SET	@BT2=PARSENAME(REPLACE(@KETQUA, '-', '.'), 1)
	IF(ISNUMERIC(@BT1)=0 OR ISNUMERIC(@BT2)=0)
		BEGIN
			PRINT N'KẾT QUẢ KHÔNG HỢP LỆ'
			ROLLBACK TRAN
		END			
	END

/*SELECT PARSENAME(REPLACE(KETQUA, '-', '.'), 2), PARSENAME(REPLACE(KETQUA, '-', '.'), 1) FROM TRANDAU
select isnumeric('aaa')
SELECT POSITION ('-',KETQUA) FROM TRANDAU
UPDATE TRANDAU SET KETQUA='MMM' WHERE MASAN='CL'
select charindex('a', '1bc') */

--D.1
CREATE VIEW D1 AS
SELECT MACT, HOTEN, NGAYSINH, DIACHI, VITRI 
FROM CAUTHU JOIN QUOCGIA	ON CAUTHU.MAQG = QUOCGIA.MAQG
			JOIN CAULACBO ON CAULACBO.MACLB = CAUTHU.MACLB
WHERE TENQG = N'Bra-xin'   AND TENCLB = N'SHB Đà Nẵng'

--D.2
--NHƯ TRÊN


----------------------------------
--BAI4----------------------------
----------------------------------

--A.1
CREATE PROC A1
AS PRINT 'Xin Chao'

--A.2
CREATE PROC A2
	@TEN VARCHAR(50)
AS
BEGIN
	PRINT 'Xin Chao ' + @TEN
END
----
EXEC A2 'Sang'

--A.3
CREATE PROCEDURE A3
	@So1 int, 
	@So2 int, 
	@Tong int out
AS 
BEGIN
SET @Tong = @So1 + @So2;
END
----
declare @s int
exec A3 2, 5, @s out
select @s

--A.4
ALTER PROC A4
	@S1 INT,
	@S2 INT,
	@MIN INT OUT,
	@MAX INT OUT 
AS
BEGIN
	IF(@S1>@S2)
		BEGIN 
			SET @MIN=@S2
			SET	@MAX=@S1
		END
	ELSE
		BEGIN 
			SET @MIN=@S1
			SET	@MAX=@S2
		END
END
----
DECLARE @MIN INT, @MAX INT
EXEC A4 414, 7, @MIN OUT, @MAX OUT
SELECT @MIN, @MAX

--A.5
CREATE PROC A5
	@N INT
AS
BEGIN
	DECLARE @I INT
	SET @I = 0
	WHILE (@I<@N)
	BEGIN
		SET @I = @I + 1
		PRINT @I
	END			
END
----
EXEC A5 5

--A.6
CREATE PROC A6
	@N INT
AS
BEGIN
	DECLARE @I INT
	SET @I = 1
	WHILE (@I<=@N)
	BEGIN
		IF(@I%2=0) PRINT @I
		SET @I = @I + 1
	END
END
----
EXEC A6 17

--A.7
--A.8

--B.1
CREATE TRIGGER B1
ON CAUTHU
FOR UPDATE, INSERT
AS
	IF(UPDATE(VITRI))
	BEGIN
	DECLARE @VITRI nvarchar(20)
	SET @VITRI=(SELECT VITRI FROM INSERTED)
	IF(@VITRI NOT IN (N'Thủ môn', N'Tiền đạo', N'Trung vệ', N'Hậu vệ', N'Tiền vệ'))
		BEGIN
			PRINT 'VI TRI KHONG HOP LE'
			ROLLBACK TRAN
		END
	END

--B.2
ALTER TRIGGER B2
ON CAUTHU
FOR INSERT, UPDATE
AS
BEGIN
	DECLARE @SO INT, @MACLB VARCHAR(5)
	SET @SO = (SELECT SO FROM INSERTED)
	SET @MACLB = (SELECT MACLB FROM INSERTED)
	IF ((SELECT COUNT(*)... --SỬA LẠI NHƯ CÂU B.5
	BEGIN
		PRINT N'Số áo này đã tồn tại'
		ROLLBACK TRAN
	END
END
DROP TRIGGER B2

--B.3
ALTER TRIGGER B3
ON CAUTHU
FOR INSERT
AS
BEGIN
	IF(@@ROWCOUNT>0) PRINT N'ĐÃ THÊM CẦU THỦ MỚI'
END	
		
--B.4
CREATE TRIGGER B4
ON CAUTHU
FOR INSERT
AS
BEGIN
	IF EXISTS(SELECT * FROM (SELECT COUNT(*) FROM CAUTHU WHERE MAQG!='VN' GROUP BY MACLB) AS A(COL) WHERE COL>8)
	BEGIN
		PRINT N'TỒN TẠI CLB CÓ QUÁ 8 CTNN'
		ROLLBACK TRAN
	END
END	
	
--B.5
CREATE TRIGGER B5
ON QUOCGIA
FOR INSERT
AS
BEGIN
	DECLARE @TENQG NVARCHAR(100)
	SET @TENQG = (SELECT TENQG FROM INSERTED)
	IF ((SELECT COUNT(*) FROM (SELECT TENQG FROM QUOCGIA WHERE TENQG = @TENQG)	AS A) > 1)
	BEGIN
		PRINT N'QUỐC GIA ĐÃ TỒN TẠI'
		ROLLBACK TRAN
	END
END

--B.6 NHƯ TRÊN

--B.7
CREATE TRIGGER B7
ON TRANDAU
INSTEAD OF UPDATE
AS
IF(UPDATE(KETQUA))
BEGIN
	PRINT N'KHÔNG THỂ SỬA KẾT QUẢ'
	ROLLBACK TRAN
END

--B.8A
CREATE TRIGGER B8A
ON HLV_CLB
FOR UPDATE, INSERT
AS
IF(UPDATE(VAITRO))
BEGIN
	DECLARE @VAITRO nvarchar(20)
	SET @VAITRO=(SELECT VAITRO FROM INSERTED)
	IF(@VAITRO NOT IN (N'HLV Chính', N'HLV Phụ', N'HLV Thể Lực', N'HLV Thủ Môn'))
	BEGIN
		PRINT N'VAI TRÒ KHÔNG HỢP LỆ'
		ROLLBACK TRAN
	END
END

--B.8B
ALTER TRIGGER B8B
ON HLV_CLB
FOR INSERT, UPDATE
AS
IF(UPDATE(VAITRO))
BEGIN
	IF((SELECT COUNT(*) FROM HLV_CLB WHERE VAITRO = (SELECT VAITRO FROM INSERTED) AND MACLB = (SELECT MACLB FROM INSERTED)) > 2)
	BEGIN
		PRINT N'MỖI CLB CÓ TỐI ĐA 2 HLV CHÍNH'
		ROLLBACK TRAN
	END
END


--C.1
DECLARE @C CURSOR
DECLARE @MACT VARCHAR(10), @HOTEN NVARCHAR(50), @VITRI NVARCHAR(50)
SET @C = CURSOR FOR (SELECT MACT, HOTEN, VITRI FROM CAUTHU)
OPEN @C
FETCH NEXT FROM @C INTO @MACT, @HOTEN, @VITRI
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @MACT + '/' + @HOTEN + '/' + @VITRI
	FETCH NEXT FROM @C INTO @MACT, @HOTEN, @VITRI
END
CLOSE @C

--C.2
DECLARE @C CURSOR
DECLARE @MACLB VARCHAR(10), @TENCLB NVARCHAR(50), @TENSAN NVARCHAR(50)
SET @C = CURSOR FOR (SELECT MACLB, TENCLB, TENSAN FROM CAULACBO JOIN SANVD ON CAULACBO.MASAN = SANVD.MASAN)
OPEN @C
FETCH NEXT FROM @C INTO @MACLB, @TENCLB, @TENSAN
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @MACLB + '/' + @TENCLB + '/' + @TENSAN
	FETCH NEXT FROM @C INTO @MACLB, @TENCLB, @TENSAN
END

--C.3 BỎ QUA