· 8 years ago · Dec 08, 2017, 05:58 AM
1/*
2Akash Srinagesh
3Black Coffee Questions
4Section C - Matthew Bond
5BOOM! YO!
6*/
7
8USE UNIVERSITY;
9
10-- 1. Write the query to determine the most-frequent classroom type assigned to business classes held since 1996 during summer quarters.
11SELECT TOP(1) CT.ClassroomTypeName, COUNT(C.ClassroomID) AS ClassFreq
12FROM tblCLASSROOM_TYPE CT
13 JOIN tblCLASSROOM CR ON CR.ClassroomTypeID = CT.ClassroomTypeID
14 JOIN tblCLASS C ON CR.ClassroomID = C.ClassroomID
15 JOIN tblQUARTER Q ON Q.QuarterID = C.QuarterID
16 JOIN tblCOURSE CO ON CO.CourseID = C.ClassID
17 JOIN tblDEPARTMENT D ON CO.DeptID = D.DeptID
18WHERE D.DeptName = 'BUSINESS' AND C.YEAR > '1996' AND Q.QuarterName = 'Summer'
19GROUP BY CT.ClassroomTypeName
20ORDER BY ClassFreq DESC
21
22-- 2. Write the query to determine the 2 most-common special-needs for students with permanent addresses in either California or Texas born between March 6, 1989 and June 4, 2000.
23SELECT TOP(2) SN.SpecialNeedName
24FROM tblSPECIAL_NEED SN
25 JOIN tblSTUDENT_SPECIAL_NEED SSN ON SSN.SpecialNeedID = SN.SpecialNeedID
26 JOIN tblSTUDENT S ON S.StudentID = SSN.StudentID
27WHERE S.StudentPermState IN ('California, CA', 'Texas, TA') AND S.StudentDateOfBirth BETWEEN 'March 6, 1989' AND 'June 4, 2000'
28GROUP BY SN.SpecialNeedName
29ORDER BY COUNT(SSN.SpecialNeedID) DESC
30
31-- 3. Write the query to determine the youngest instructor with an office type of executive suite on West Campus.
32SELECT TOP(1) I.InstructorFname, I.InstructorLname
33FROM tblINSTRUCTOR I
34 JOIN tblINSTRUCTOR_OFFICE IOF ON IOF.InstructorID = I.InstructorID
35 JOIN tblOFFICE_TYPE OT ON OT.OfficeTypeID = IOF.OfficeTypeID
36 JOIN tblOFFICE O ON IOF.OfficeID = O.OfficeID
37 JOIN tblBUILDING B ON B.BuildingID = O.BuildingID
38 JOIN tblLOCATION L ON L.LocationID = B.LocationID
39WHERE L.LocationName = 'West Campus' AND OT.OfficeTypeName = 'Executive Suite'
40ORDER BY I.BirthDate
41
42
43-- 4. Write the query to determine the number of 300-Level accounting classes with a scheduled BeginTime before 11:30 AM any autumn quarter in Lowe Hall during 1990’s.
44SELECT COUNT(C.ClassID)
45FROM tblCLASS C
46 JOIN tblCOURSE CR ON CR.CourseID = C.CourseID
47 JOIN tblDEPARTMENT D ON D.DeptID = CR.DeptID
48 JOIN tblQUARTER Q ON Q.QuarterID = C.QuarterID
49 JOIN tblCLASSROOM CLR ON CLR.ClassroomID = C.ClassroomID
50 JOIN tblBUILDING B ON B.BuildingID = CLR.BuildingID
51 JOIN tblSCHEDULE S ON S.ScheduleID = C.ScheduleID
52WHERE Q.QuarterName = 'Autumn' AND B.BuildingName = 'Lowe Hall' AND C.Year LIKE '199%' AND D.DeptName = 'Accounting' AND CR.CourseName LIKE '3_%_%'
53 AND S.SchedBeginTime < '11:30AM'
54
55-- 5. Write the query to determine the oldest person registered for MATH389 Spring 2016.
56SELECT TOP(1) S.StudentFname, S.StudentLname
57FROM tblSTUDENT S
58 JOIN tblCLASS_LIST CL ON S.StudentID = CL.StudentID
59 JOIN tblCLASS C ON C.ClassID = CL.ClassID
60 JOIN tblQUARTER Q ON Q.QuarterID = C.QuarterID
61 JOIN tblCOURSE CR ON CR.CourseID = C.CourseID
62WHERE Q.QuarterName = 'Spring' AND C.YEAR = '2016' AND CR.CourseName = 'MATH389'
63ORDER BY S.Age
64
65-- 6. Write query to determine total number of dorm rooms of type 'triple' for McMahon Hall.
66SELECT TOP(1) DR.DormroomName, COUNT(DT.DormTypeID) AS TotCount
67FROM tblDORMROOM_TYPE DT
68 JOIN tblDORMROOM DR ON DR.DormRoomTypeID = DT.DormRoomTypeID
69 JOIN tblBUILDING B ON DR.BuildingID = B.BuildingID
70WHERE B.BuildingName = 'McMahon Hall' AND DT.DormRoomTypeName = 'Triple'
71GROUP BY DR.DoorroomName
72ORDER BY TotCount
73
74-- 7. Write the query to list the ratio of female-to-male students in each college for students with the status of ‘suspended’ during April 2015.
75SELECT CAST((
76 SELECT COUNT(S.SutdentId) AS NumberOfWomen
77 FROM tblSTUDENT S
78 JOIN tblSTUDENT_STATUS SS ON SS.StudentId = S.StudentID
79 JOIN tblSTATUS ST ON ST.StatusId = SS.StatusId
80 WHERE ST.StatusName = 'Suspended' AND SS.EndDate <= 'April 2015' AND S.Gender = 'F'
81) AS NUMERIC(6,2)) /
82CAST((
83 SELECT COUNT(S.SutdentId) AS NumberOfMen
84 FROM tblSTUDENT S
85 JOIN tblSTUDENT_STATUS SS ON SS.StudentId = S.StudentID
86 JOIN tblSTATUS ST ON ST.StatusId = SS.StatusId
87 WHERE ST.StatusName = 'Suspended' AND SS.EndDate = 'April 2015' AND SS.BeginDate = 'April 2015' AND S.Gender = 'M'
88) AS NUMERIC(6,2))
89
90-- 8. Write the query to determine the number of faculty in the School of Medicine hired before November 21, 2016.
91SELECT COUNT(S.StaffID) AS NumOfFacultyMedicine
92FROM tblSTAFF S
93 JOIN tblSTAFF_POSITION SP ON SP.StaffID = S.StaffID
94 JOIN tblDEPARTMENT D ON SP.DeptID = D.DeptID
95 JOIN tblCOLLEGE C ON C.CollegeID = D.CollegeID
96WHERE C.CollegeName = 'Medicine' AND SP.BeginDate < 'November 21, 2016'
97
98-- 9. Write the query to determine the number of Administrative staff people were hired in the Medical School between February 12, 2009 and March 28, 2013?
99SELECT COUNT(S.StaffID) AS NumOfFacultyMedicine
100FROM tblSTAFF S
101 JOIN tblSTAFF_POSITION SP ON SP.StaffID = S.StaffID
102 JOIN tblDEPARTMENT D ON SP.DeptID = D.DeptID
103 JOIN tblCOLLEGE C ON C.CollegeID = D.CollegeID
104 JOIN tblPOSITION P ON SP.PositionID = P.PositionID
105 JOIN tblPOSITION_TYPE PT ON PT.PositionTypeID = P.PositionTypeID
106WHERE C.CollegeName = 'Medicine' AND SP.BeginDate BETWEEN 'February 12, 2009' AND 'March 28, 2013' AND PT.PositionTypeName = 'Executive' -- No Admin type in column so executive instead?
107
108-- 10. Write the query to determine the newest building on lower campus that has had a Geology class instructed by Greg Hay before winter 2015.
109SELECT TOP(1) B.BuildingName
110FROM tblBUILDING B
111 JOIN tblCLASSROOM CR ON B.BuildingID = CR.BuildingID
112 JOIN tblCLASS C ON C.ClassroomID = CR.ClassroomID
113 JOIN tblCOURSE CO ON CO.CourseID = C.CourseID
114 JOIN tblDEPARTMENT D ON D.DeptID = CO.DeptID
115 JOIN tblLOCATION L ON B.LocationID = L.LocationID
116 JOIN tblQUARTER Q ON Q.QuarterID = C.QuarterID
117 JOIN tblINSTRUCTOR_CLASS IC ON IC.ClassID = C.ClassID
118 JOIN tblINSTRUCTOR I ON I.InstructorID = IC.InstructorID
119WHERE D.DeptName = 'Geology' AND Q.QuarterName = 'Winter' AND C.YEAR < 2015 AND L.LocationName = 'Lower Campus' AND I.InstructorFName = 'Greg' AND I.InstructorLName = 'Hay'
120ORDER BY B.YearOpened
121
122-- 11. Write the query to determine which instructor has had the same office in Padelford Hall the longest.
123SELECT I.InstructorFname, I.InstructorLname
124FROM tblINSTRUCTOR I
125 JOIN tblINSTRUCTOR_OFFICE IOF ON I.InstructorID = IOF.InstructorID
126 JOIN tblOFFICE O ON O.OfficeID = IOF.OfficeID
127 JOIN tblBUILDING B ON B.BuildingID = O.BuildingID
128WHERE B.BuildingName = 'Padelford Hall'
129ORDER BY DATEDIFF(day, IOF.EndDate, IOF.BeginDate)
130
131-- 12. Write the query to determine which 3 classroom types are most-frequently assigned for 400-level Psychology courses.
132SELECT TOP(3) CT.ClassroomTypeName, COUNT(CT.ClassroomTypeName) AS FreqOfClassroomType
133FROM tblCLASSROOM_TYPE CT
134 JOIN tblCLASSROOM CL ON CL.ClassroomTypeID = CT.ClassroomTypeID
135 JOIN tblCLASS C ON C.ClassroomID = CL.ClassroomID
136 JOIN tblCOURSE CO ON CO.CourseID = C.CourseID
137 JOIN tblDEPARTMENT D ON D.DeptID = CO.DeptID
138WHERE D.DeptName = 'Psychology' AND CO.CourseName LIKE '%4__%'
139GROUP BY ClassroomTypeName
140ORDER BY FreqOfClassroomType DESC
141
142-- 13. Create a stored procedure to hire a new person to an existing staff position.
143GO
144
145CREATE PROCEDURE usp_EnterNewStaffPosition
146@StaffFname VARCHAR(60),
147@StaffLname VARCHAR(60),
148@StaffBeginDate DATE,
149@PositionName VARCHAR(60),
150@DeptName VARCHAR(60)
151AS
152DECLARE @Staff_ID INT
153DECLARE @Position_ID INT
154DECLARE @Dept_ID INT
155
156SET @Staff_ID = (SELECT S.StaffID
157 FROM tblSTAFF S
158 WHERE S.StaffFName = @StaffFname AND S.StaffLName = @StaffLname)
159
160SET @Position_ID = (SELECT P.PositionID
161 FROM tblPOSITION P
162 WHERE P.PositionName = @PositionName)
163
164SET @Dept_ID = (SELECT D.DeptID
165 FROM tblDEPARTMENT D
166 WHERE D.DeptName = @DeptName)
167
168BEGIN TRAN
169 INSERT INTO tblSTAFF_POSITION(StaffID, PositionID, DeptID, StaffPosBeginDate)
170 VALUES (@Staff_ID, @Position_ID, @Dept_ID, @StaffBeginDate)
171COMMIT TRAN
172
173-- 14. Create a stored procedure to create a new class of an existing course.
174GO
175CREATE PROCEDURE usp_InsertClass
176@Quarter VARCHAR(60),
177@CourseName VARCHAR(60),
178@Year VARCHAR(4),
179@ClassroomName VARCHAR(60),
180@ScheduleName VARCHAR(60),
181@Section VARCHAR(2)
182AS
183DECLARE @Course_ID INT
184DECLARE @Quarter_ID INT
185DECLARE @Classroom_ID INT
186DECLARE @Schedule_ID INT
187
188SET @Course_ID = (SELECT C.CourseID
189 FROM tblCOURSE C
190 WHERE C.CourseName = @CourseName)
191
192SET @Quarter_ID = (SELECT Q.QuarterID
193 FROM tblQUARTER Q
194 WHERE Q.QuarterName = @Quarter)
195
196SET @Classroom_ID = (SELECT C.ClassroomID
197 FROM tblCLASSROOM C
198 WHERE C.ClassroomName = @ClassroomName)
199
200SET @Schedule_ID = (SELECT S.ScheduleID
201 FROM tblSCHEDULE S
202 WHERE S.ScheduleName = @ScheduleName)
203
204BEGIN TRAN
205 INSERT INTO tblCLASS(CourseID, QuarterID, YEAR, ClassroomID, ScheduleID, Section)
206 VALUES (@Course_ID, @Quarter_ID, @Year, @Classroom_ID, @Schedule_ID, @Section)
207COMMIT TRAN
208
209-- 15. Create a stored procedure to register an existing student to an existing class.
210GO
211CREATE PROCEDURE usp_RegisterStudent
212@StudentFname VARCHAR(60),
213@StudentLname VARCHAR(60),
214@StudentSoc VARCHAR(60),
215@CourseName VARCHAR(60),
216@Quarter VARCHAR(60),
217@Year VARCHAR(4)
218AS
219DECLARE @Student_ID INT
220DECLARE @Class_ID INT
221
222SET @Student_ID = (SELECT S.StudentID
223 FROM tblSTUDENT S
224 WHERE S.StudentFname = @StudentFname AND S.StudentLname = @StudentLname AND S.StudentSocSecNum = @StudentSoc)
225
226SET @Class_ID = (SELECT C.ClassID
227 FROM tblCLASS C
228 JOIN tblCOURSE CO ON CO.CourseID = C.CourseID
229 JOIN tblQUARTER Q ON Q.QuarterID = C.QuarterID
230 WHERE Q.QuarterName = @Quarter AND CO.CourseName = @CourseName)
231
232BEGIN TRAN
233 INSERT INTO tblCLASS_LIST(StudentID, ClassID, RegistrationDate)
234 VALUES (@Student_ID, @Class_ID, GETDATE())
235COMMIT TRAN
236
237
238-- 16. Create check constraint to restrict the type of instructor assigned to 400-level courses in Biology or Philosophy courses during summer quarters to
239-- Assistant or Associate Professor.
240GO
241CREATE FUNCTION fn_BioOrPHilProffRestriction()
242RETURNS INT
243AS
244BEGIN
245DECLARE @Ret INT = 0
246 IF EXISTS (SELECT *
247 FROM tblINSTRUCTOR I
248 JOIN tblINSTRUCTOR_CLASS IC ON I.InstructorID = IC.InstructorID
249 JOIN tblCLASS C ON C.ClassID = IC.ClassID
250 JOIN tblQUARTER Q ON Q.QuarterID = C.QuarterID
251 JOIN tblCOURSE CO ON C.CourseID = CO.CourseID
252 JOIN tblDEPARTMENT D ON D.DeptID = CO.DeptID
253 JOIN tblINSTRUCTOR_INSTRUCTOR_TYPE IIT ON IIT.InstructorID = I.InstructorID
254 JOIN tblINSTRUCTOR_TYPE IT ON IT.InstructorTypeID = IIT.InstructorTypeID
255 WHERE CO.CourseName LIKE '%4__%' AND D.DeptName IN ('Biology', 'Philosophy') AND Q.QuarterName = 'Summer' AND InstructorTypeName NOT IN ('Assistant Professor', 'Associate Professor'))
256 SET @Ret = 1
257RETURN @Ret
258END
259
260GO
261ALTER TABLE tblINSTRUCTOR_CLASS
262ADD CONSTRAINT CK_BioOrPhilProffRestriction
263CHECK (dbo.fn_BioOrPHilProffRestriction() = 0)
264
265-- 17. Create check constraint to restrict students assigned to dorm rooms on West Campus to be at least 20 years old.
266GO
267CREATE FUNCTION fn_WestCampusAgeRestriction()
268RETURNS INT
269AS
270BEGIN
271DECLARE @Ret INT = 0
272 IF EXISTS (SELECT *
273 FROM tblSTUDENT_DORMROOM SD
274 JOIN tblSTUDENT S ON S.StudentID = SD.StudentID
275 JOIN tblDORMROOM D ON D.DormRoomID = SD.DormRoomID
276 JOIN tblBUILDING B ON D.BuildingID = B.BuildingID
277 JOIN tblLOCATION L ON L.LocationID = B.LocationID
278 WHERE L.LocationName = 'West Campus' AND S.Age < 20)
279 SET @Ret = 1
280RETURN @Ret
281END
282
283GO
284ALTER TABLE tblSTUDENT_DORMROOM
285ADD CONSTRAINT CK_WestCampusAgeRestriction
286CHECK (dbo.fn_WestCampusAgeRestriction() = 0)
287GO
288
289
290-- 18. Write the query to create the following set of procedures:
291-- a. Given the inputs of StudentFname, StudentLname and StudentDateOfBirth will return StudentID as an output parameter
292-- b. Given the input of CourseName will return CourseID as an output parameter
293-- c. Given the input of QuarterName will return QuarterID as an output parameter
294-- d. Given the inputs of CourseName, Year, Quarter and Section will return ClassID as an output parameter (while leveraging nested stored procedures defined above)
295-- e. Given the inputs of StudentFname, StudentLname, StudentDateOfBirth, CourseName, QuarterName, Year and Section will INSERT a new row in tblCLASS_LIST in a
296-- single explicit transaction (while leveraging nested stored procedures defined above).
297
298-- a
299GO
300CREATE FUNCTION fn_ReturnStudentID(@StudentFname VARCHAR(60), @StudentLname VARCHAR(60), @StudentDOB DATE)
301RETURNS INT
302AS
303BEGIN
304DECLARE @Ret INT
305SET @Ret = (SELECT S.StudentID
306 FROM tblSTUDENT S
307 WHERE S.StudentFname = @StudentFname AND S.StudentLname = @StudentLname AND S.StudentDateOfBirth = @StudentDOB)
308RETURN @Ret
309END
310
311-- b
312GO
313CREATE FUNCTION fn_ReturnCourseID(@CourseName VARCHAR(60))
314RETURNS INT
315AS
316BEGIN
317DECLARE @Ret INT
318SET @Ret = (SELECT C.CourseID
319 FROM tblCOURSE C
320 WHERE C.CourseName = @CourseName)
321RETURN @Ret
322END
323
324-- c
325GO
326CREATE FUNCTION fn_ReturnQuarterID(@QuarterName VARCHAR(60))
327RETURNS INT
328AS
329BEGIN
330DECLARE @Ret INT
331SET @Ret = (SELECT Q.QuarterID
332 FROM tblQUARTER Q
333 WHERE Q.QuarterName = @Quarter)
334RETURN @Ret
335END
336
337-- d
338GO
339CREATE FUNCTION ReturnClassID(@CourseName VARCHAR(60), @Year VARCHAR(2), @Section VARCHAR(2), @QuarterName VARCHAR(60))
340RETURNS INT
341AS
342BEGIN
343DECLARE @Ret INT
344DECLARE @Quarter_ID INT = fn_ReturnQuarterID(@QuarterName)
345DECLARE @Course_ID INT = fn_ReturnCourseID(@CourseName)
346SET @Ret = (SELECT C.ClassID
347 FROM tblCLASS C
348 WHERE C.QuarterID = @Quarter_ID AND C.CourseID = @Course_ID)
349RETURN @Ret
350END
351
352-- e
353GO
354CREATE PROCEDURE usp_RegisterStudentOptimized
355@StudentFname VARCHAR(60),
356@StudentLname VARCHAR(60),
357@StudentDOB DATE,
358@CourseName VARCHAR(60),
359@Quarter VARCHAR(60),
360@Year VARCHAR(4),
361@Section VARCHAR(2)
362AS
363DECLARE @Student_ID INT = fn_ReturnStudentID(@StudentFname, @StudentLname, @StudentDOB)
364DECLARE @Class_ID INT = fn_ReturnClassID(@CourseNamel, @Year, @Section, @Quarter)
365
366BEGIN TRAN
367 INSERT INTO tblCLASS_LIST(StudentID, ClassID, RegistrationDate)
368 VALUES (@Student_ID, @Class_ID, GETDATE())
369COMMIT TRAN