QWz9bLyq

1/*Build a view, named ReqView02, in order to display the name and the difference between the final
2grade and the admission grade, for each student having the string “escu” inside the name.*/
3
4Create view ReqView02
5(Name, GradeDif)
6as
7Select FirstName+' '+LastName as Name,
8       FinalGrade-AdmissionGrade as GradeDif
9From Student
10where FirstName like '%escu%' or LastName like '%escu%'
11
12Select * from ReqView02
13
14/*Build a view, named ReqView03, in order to display the average admission grade of the scholarship
15students, followed by the average admission grade of the non-scholarship students (two versions).*/
16Alter View ReqView03
17(AAGS, AAGNS)
18As
19Select (Select AVG(AdmissionGrade) from student where status='scholarship') as AAGS,
20       (Select AVG(AdmissionGrade) from student where status='tax') as AAGNS
21
22Select * from ReqView03
23
24/*Build a view, named ReqView04, in order to display the number of students whose name starts with
25each letter of the alphabet (in case they exist).*/
26Create view ReqView04
27(Letter, NoOfStudents)
28as
29Select LEFT(LastName,1) as Letter,
30       Count(StudentID) as NoOfStudents
31	   From Student 
32	   Group By Left(LastName,1)
33Select * from ReqView04
34
35/*Build a view, named ReqView05, in order to display the names of the students whose name starts
36and ends with the same letter*/
37Create View ReqView05
38(Name)
39as
40Select FirstName+' '+LastName as Name
41from Student
42where Left(LastName,1)=Right(LastName,1)
43
44Select * from ReqView05
45
46/*Build a view, named ReqView06, in order to display the name of the student having the largest
47difference between the final grade and the admission grade*/
48Create View ReqView06
49(Name, Grade)
50as
51Select Top 1 FirstName+' '+LastName as Name,
52       FinalGrade-AdmissionGrade as Grade
53	from student
54	Order by FinalGrade-AdmissionGrade desc
55
56SELECT * FROM ReqView06;
57
58/*Build a view, named ReqView07, in order to display the name of the faculty having the largest
59average difference between the final grade and the admission grade.*/
60Create view ReqView07
61(FacultyName)
62as
63Select Top 1 FacultyName as FacultyName
64from student inner join Faculty
65On student.FkfacultyId=faculty.facultyID
66Group By facultyName
67Order by Avg(FinalGrade-AdmissionGrade) Desc
68
69Select * From ReqView07
70/*Build a view, named ReqView08, in order to display the number of faculties in the Faculty table.*/
71Create view ReqView08
72(NbOfFaculties)
73as
74select COUNT(facultyID) as NbOfFaculties
75from Faculty
76
77SELECT * FROM ReqView08
78/*Build a view, named ReqView09, in order to display the number of students born during each year*/
79Create view ReqView09
80(NoOfStudents,bYear)
81as
82SELECT Count(StudentId) as NoOfStudents,
83		Year(Birthdate) as bYear
84From Student
85Group by Year(Birthdate)
86
87Select * From ReqView09
88/*. Build a view, named ReqView10, in order to display the number of students born in each month of
89the year.*/
90Create view ReqView10
91(NoOfStudents,bMonth)
92as
93Select Count(StudentId) as NoOfStudents,
94		Month(Birthdate) as bMonth
95From Student
96Group by Month(Birthdate)
97
98Select * from ReqView10
99/*Build a view, named ReqView11, in order to display the number of students for each faculty.*/
100Create view ReqView11
101(NoOfStudents,FacultyName)
102as
103Select count(StudentId) as NoOfStudents,
104		FacultyName as FacultyName
105from student inner join Faculty
106On student.FkfacultyId=faculty.facultyID
107Group By facultyName
108
109select * from ReqView11
110
111/*Build a view, named ReqView12, in order to display in order to display the number of scholarship
112students for each faculty.*/
113
114Create view ReqView12
115(NoOfSchStudents, FacultyName)
116as
117Select count(StudentId) as NoOfStudents,
118		FacultyName as FacultyName
119from student 
120inner join Faculty
121On student.FkfacultyId=faculty.facultyID
122where status='scholarship'
123Group By facultyName
124
125select * from ReqView12
126
127/*Build a view, named ReqView13, in order to display the faculty names and the number of students
128for the faculties having more than 20 students.*/
129Create view ReqView13
130(FacultyName,NoOfStudents)
131as
132Select count(StudentId) as NoOfStudents,
133		FacultyName as FacultyName
134from student 
135inner join Faculty
136On student.FkfacultyId=faculty.facultyID
137Group By FacultyName
138having count(FkFacultyId) >20
139
140select * from ReqView13
141
142
143
144/*Write the SQL clauses that have the following results:
14519. Build a trigger, named MyTrigger01, so as scholarship students can not be deleted from the Student
146table.
14720. Build a trigger, named MyTrigger02, so as no students born after June 2001 can be added.
14821. Build a trigger, named MyTrigger03, so as no new students can be added for the Faculty of Finance.
14922. Build a trigger, named MyTrigger04, so as no student from the faculty of Marketing can be deleted
150from the Student table.
15123. Build a trigger, named MyTrigger05, so as the admission grade can never be changed for an existing
152student.
15324. Build a trigger, named MyTrigger06, so as the final grade of the students in Accounting can not be
154updated to a value smaller than the existing one.*/
155
156REQUEST 19
157CREATE TRIGGER Student_NoDELETE_Scholarship
158ON Student AFTER DELETE
159AS
160 IF EXISTS (SELECT * FROM Deleted WHERE Scholarship=1)
161 BEGIN
162 RAISERROR ('You can not delete scholarship students!',1,1)
163 ROLLBACK TRANSACTION
164 END
165REQUEST 20
166CREATE TRIGGER Student_NoInsert_AfterJune2001
167ON Student AFTER INSERT
168AS
169IF EXISTS (SELECT * FROM Inserted WHERE BirthDate>'2001-06-30')
170BEGIN
171 RAISERROR ('No students born after June 2001 allowed!',1,1)
172 ROLLBACK TRANSACTION
173END
174REQUEST 21
175CREATE TRIGGER Student_NoInsert_Finance
176ON Student AFTER INSERT
177AS
178IF EXISTS (SELECT * FROM Inserted INNER JOIN Faculty
179 ON Faculty.FacultyID=Inserted.FKFacultyID
180 WHERE Faculty.FacultyName='Finance')
181BEGIN
182 RAISERROR ('No more Finance students allowed!',1,1)
183 ROLLBACK TRANSACTION
184END
185REQUEST 22
186CREATE TRIGGER Student_NoDELETE_Marketing
187ON Student AFTER DELETE
188AS
189IF EXISTS (SELECT * FROM Deleted INNER JOIN Faculty
190 ON Deleted.FKFacultyID=Faculty.FacultyID
191 WHERE Faculty.FacultyName='Marketing')
192BEGIN
193 RAISERROR ('You can not delete students from Marketing!',1,1)
194 ROLLBACK TRANSACTION
195END
196REQUEST 23
197CREATE TRIGGER Student_Update_AdmGrade_ReadOnly
198ON Student
199AFTER UPDATE
200AS
201IF EXISTS (SELECT * FROM Inserted INNER JOIN Deleted
202 ON Inserted.StudentID=Deleted.StudentID
203 WHERE Inserted.AdmGrade<>Deleted.AdmGrade)
204BEGIN
205 RAISERROR('You can not change the admission grade!',1,1)
206 ROLLBACK TRANSACTION
207END
208REQUEST 24
209CREATE TRIGGER Student_Update_FinalGrade_NoLower
210ON Student
211AFTER UPDATE
212AS
213IF EXISTS (SELECT * FROM Inserted INNER JOIN Deleted
214 ON Inserted.StudentID=Deleted.StudentID
215WHERE Inserted.StudentID IN
216(SELECT StudentID FROM Student INNER JOIN Faculty
217ON Student.FKFacultyID=Faculty.FacultyID
218WHERE FacultyName='Accounting')
219 AND Inserted.FinalGrade<Deleted.FinalGrade)
220BEGIN
221 RAISERROR('You can not lower the final grade for Accounting students!',1,1)
222 ROLLBACK TRANSACTION
223END