a.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 EXISTS(SELECT SO FROM CAUTHU WHERE SO=@SO AND MACLB=@MACLB)
	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 'HEHEHE'
END	
		
--B.4
CREATE TRIGGER B4
ON CAUTHU
FOR INSERT
AS
BEGIN
	SELECT COUNT(*) FROM CAUTHU WHERE MAQG!='VN' GROUP BY MACLB
	
END