WPZ7M2QE

1
2
3---PROC NHẬP VÀO MÃ SV RỒI ĐƯA RA THÔNG TIN ..
4
5	ALTER PROC procedure_there
6	@MASV  nvarchar(20) 
7AS
8BEGIN
9	IF EXISTS(SELECT * FROM SINHVIEN WHERE @MASV = MASV)
10		BEGIN
11			SELECT SINHVIEN.MASV , SINHVIEN.MALOP, GVCN.MAGVCN , GVCN.TENGV FROM SINHVIEN , LOP , GVCN
12			WHERE SINHVIEN.MALOP = LOP.MALOP AND GVCN.MALOP = LOP.MALOP AND SINHVIEN.MASV = @MASV
13		END
14	ELSE
15		BEGIN
16			PRINT N' K ĐƯỢC HỌC MÀ CŨNG ĐÒI CÓ GIÁO VIÊN CHỦ NHIỆM À CƯNG . OUT . => '
17			ROLLBACK TRAN
18		END
19	
20END
21EXEC procedure_there N'MASV_01_01'
22
23------CREATE TABLE THEME 18+
24CREATE TABLE TABLE_RESULT_18
25(
26	MASV INT NOT NULL ,
27	RESULT NVARCHAR(20)
28)
29
30
31-----PROC THEME 18+---------ĐANG UPDATE PELSE WAIT
32SELECT GETDATE()
33
34CREATE PROC PROCESS_RESULT_18
35	@MASV INT
36AS
37BEGIN
38	DECLARE @MASV_NS DATETIME
39	SET @MASV_NS = (SELECT SINHVIEN.NGAYSINH FROM SINHVIEN WHERE SINHVIEN.MASV = @MASV)
40	DECLARE @AGE_TEST NVARCHAR(20)
41	SET @AGE_TESTE = ( YEAR(GETDATE())-YEAR(@MASV_NS) )
42	IF EXISTS(SELECT * FROM SINHVIEN WHERE SINHVIEN.MASV = @MASV) AND @AGE_TEST > 18
43		BEGIN
44			DECLARE @MASV INT
45			DECLARE @RESULT NVARCHAR(50)
46			SET @RESULT = N'CHÚC MỪNG THÍ SINH ĐÃ 18+ ❤'
47			INSERT INTO TABLE_RESULT_18
48			(MASV, RESULT)
49			VALUES(@MASV, @RESULT);
50		END 
51	ELSE
52		BEGIN
53			PRINT 'YOU FAILURE AND TRY AGAIN'
54			ROLLBACK TRAN
55		END
56END
57
58SELECT YEAR(SINHVIEN.TENSV) FROM SINHVIEN WHERE SINHVIEN.MASV = 'MASV_01_01'
59
60------- END 18+----Đang bị lỗi hiện tại chưa fig được 
61
62
63--NHẬP TÊN ĐIỂM VÀ MÃ SINH VIÊN = > ĐƯA RA RESULT 
64
65ALTER PROC procedure_one
66	@diem INT,
67	@MASV NVARCHAR(20)
68AS
69BEGIN
70	IF (@diem > 0) AND EXISTS( SELECT * FROM SINHVIEN WHERE SINHVIEN.MASV = @MASV)
71	BEGIN
72		SELECT SINHVIEN.MASV, DIEMSV.DIEMSV , SINHVIEN.TENSV, MON.TENMON FROM MON, SINHVIEN, dbo.DIEMSV 
73		WHERE DIEMSV.DIEMSV > @diem AND SINHVIEN.MASV = @MASV AND MON.MAMON = DIEMSV.MAMON 
74	END 
75END
76EXEC procedure_one 6 ,N'MASV_01_01'
77
78---END INPUT { ĐIỂM , MASV }
79
80----INPUT{ MÃ LỚP } => OUTPUT{ COUT(*)}
81
82ALTER PROC procedure_for
83	@MALOP NVARCHAR(20),
84	@COUNT INT OUTPUT
85AS
86BEGIN
87	IF EXISTS(SELECT * FROM dbo.SINHVIEN)
88		BEGIN
89			SET @COUNT 😞 SELECT  COUNT(SINHVIEN.MASV) FROM SINHVIEN , LOP 
90						WHERE SINHVIEN.MALOP = LOP.MALOP AND LOP.MALOP = @MALOP
91						GROUP BY LOP.MALOP )
92		END
93    ELSE
94		BEGIN
95			PRINT N' Cạn Lời'
96			ROLLBACK TRAN
97		END
98END
99DECLARE @COUNT INT 
100EXEC procedure_for 'MASV_01_01', @COUNT OUTPUT
101PRINT @COUNT
102
103
104--------END --------
105CREATE VIEW VIEW_DIEMSV AS
106SELECT * FROM dbo.DIEMSV
107
108SELECT * INTO COPY_DIEMSV FROM DIEMSV 
109
110SELECT * FROM COPY_DIEMSV 
111
112ALTER PROC procudure_different
113	@MALOP NVARCHAR(50) 
114AS 
115BEGIN
116	DECLARE @COUNT INT
117	DECLARE @I INT =1
118	DECLARE @MASV NVARCHAR(20)
119	SET @MASV = 'MASV_01'+'0'+CAST( @I AS NVARCHAR(20))
120	SET @COUNT 😞 SELECT  COUNT(SINHVIEN.MASV) FROM SINHVIEN , LOP 
121					WHERE SINHVIEN.MALOP = LOP.MALOP AND LOP.MALOP = @MALOP
122					GROUP BY LOP.MALOP )
123			WHILE (@I < @COUNT + 1)
124			BEGIN
125				DECLARE @DIEM NVARCHAR(20)
126				SET @DIEM = (SELECT DIEMSV.DIEMSV FROM dbo.DIEMSV, SINHVIEN 
127							 WHERE SINHVIEN.MASV = @MASV  )
128				IF (@DIEM >6) 
129					BEGIN
130						UPDATE COPY_DIEMSV
131						SET COPY_DIEMSV.DIEMSV = N'ĐẠT'
132						WHERE COPY_DIEMSV.MASV = @MASV;
133					END
134				ELSE
135					BEGIN
136						UPDATE COPY_DIEMSV
137						SET COPY_DIEMSV.DIEMSV = N'TẠCH'
138						WHERE COPY_DIEMSV.MASV = @MASV;
139					END
140				SET @I = @I +1
141			END
142END 
143EXECUTE procudure_different 'LOP_01'