GkZSCD9H

1--1--
2select *
3from Student
4where studentDOB between '1990-01-01' and '1990-12-31'
5
6--2--
7insert into Class(classCode, className) values ('Class1000', 'New Class')
8insert into Student(studentID, studentFName, studentLName, studentGender, studentDOB, studentAddress) values ('ST1000', 'New', 'Student', 'False', '1992-4-18', 'FU Hoa Lac')
9insert into Student_Class(studentID, classCode) values ('ST1000', 'Class1000')
10
11--3--
12select S.studentID, S.studentFName, S.studentLName, C.classCode, C.className
13from Student S left outer join Student_Class SC on S.StudentID = SC.studentID left outer join Class C on SC.classCode = C.classCode 
14
15--4--
16select *
17from Student S
18where not exists
19	(select *
20	from Student_Scholarship SSc
21	where S.studentID = SSc.StudentID)
22
23--5--
24select Class.classCode, Class.className, count(Student_Class.StudentID) as NumberOfStudents
25from Class left join Student_Class on Class.classCode = Student_Class.classCode
26group by Class.classCode, Class.className
27order by NumberOfStudents desc
28
29--6--
30select S.studentID,
31	case when sum(E.examScore) is null then 0
32	else sum(E.examScore) / (select count(subjectCode) from Subject)
33	end
34from Student S left outer join Exam E on S.studentID = E.studentID
35	and E.examDate =
36	(select max(examDate) from Exam E2 where E2.studentID = E.studentID and E.subjectCode = E2.subjectCode)
37group by S.studentID
38
39--7--
40create procedure SP_AddEvenNumberToDummy
41	@Threhold INT
42as
43begin
44	declare @i INT
45	set @i = 0	
46
47	while (@i <= @Threhold)
48	begin
49			insert into DummyTable(value) values (@i)
50			set @i = @i +2;
51	end
52end
53
54--8--
55create trigger Trigger_NoMoreTwoScho on Student_Scholarship
56after insert, update
57as
58begin
59	if exists(
60		select *
61		from Student_Scholarship
62		group by StudentID
63		having count(schoName) > 2
64	)
65	begin
66		raiserror('co loi', 1, 1)
67		rollback
68	end
69end
70
71--9--
72create table Employee
73(
74	ID int primary key,
75	Name nvarchar(50)
76)
77
78create table Product
79(
80	Code int primary key,
81	Name nvarchar(50),
82	price int
83)
84
85create table Sale
86(
87	employeeID int foreign key references Employee(ID),
88	productID int foreign key references Product(Code),
89	date dateTime,
90	primary key (employeeID, productID)
91)