Given the following schema. Primary keys are under lined and highlighted in yellowandforeignkeysarebold. The meaning of the relations is straight forward.

 

Student(snum, sFirstName, sLastName, major, level, dateOfBirth, city)

Class(cname, time, room, fid)

Enrolled(snum, cname, grade_points)

Faculty(fid, fFirstName, fLastName, deptid, salary, dateOfBirth)

Department(deptid, deptname, budget, dateStarted)

DepartmentFaculty(deptid, fid)

WriteSQLqueries toanswerthefollowingquestions: (No need to run it on MySQL)

5.1)Findthenamesofall students from Hondaparkwhoareenrolledinaclasstaughtbyfaculty 'Sami Smith'.

5.2)Foreach department,listthe departmentnameandthenumberof facultyinthe department.

5.3)Find the name of the youngest Department, based on when the department was started.

5.4)Createaviewtoincludealistofallcoursenamesalongwiththenameofthefacultywhoteachesthiscourse.(i.e.,viewoutputincludesfLastName, fFirstName, and cname). Sort by cname, fLastName, fFirstName.

5.5)Findnamesof all studentswhoare not enrolled inanyclasses. (Hint:useNOTIN)

5.6)Createastoredprocedurethattakesthreeinputs:afacultyidentifier(fid), a class name (cname)and a numberofbonuspoints.TheprocedurethenupdatestheEnrolledtablebyaddingthebonuspointstothe grade_pointsof class name being taught by that faculty member.

5.7) Create a trigger to increment the budget attribute of the Department table, by $123 when a new row is inserted in the Faculty table. Make sure to update all the Departments the new Faculty member belongs to.