Write the following queries in SQL.

1. Consider the following database schema describing students enrolled in sections of classes at a university. student (sid*, sname, sex, age, year, gpa) Year is a mumber between 1 and 5). dept(dname*, numphds) prof(pname*, dname) course(cno*, cname, dname*) (Course numbers are unique within departments. major(dname*, sid*) section(dname*, cno*, sectno*, pname) (Associates an instructor to a section. enroll(sid*, grade, dname*, cno*, sectno*) (Associates students to sections. (a) Find department names and student sids where the student majors in that department, but is not enrolled in any classes taught in that department. (b) Find all student and corresponding class information (including course name and grade) for students who enroll in the class and obtain a grade that is lower than their gpa. (c) For each course with an average grade of at least 3.0, print the course number, department name, enrollment and average grade. Assume that the enrollment of a course is the sum of the enrollment of all its sections. (d) For each department, show information about the student(s) with the highest gpa who major in the department. In particular, for each such student, we want to show the student id, student name and gpa, and the department name the student is major in. If there are ties, show all students with that gpa. (e) Find all courses whose titles contain the word "Programming". () How many different letters are used to start the names of students? Hint: In SQL you can get the first letter of a string s as substring(5,1,1). (g) Give the names of each professor who has in one of his/her classes a student older than 50. List each professor at most once, in alphabetical order. (h) Show the dname, cno and sectno of all course sections together with the total number of students enrolled in the section. Order the output in decreasing order of popularity. (i) Find all information about students who are not enrolled in any classes. (1) List all pairs of professor names where the two professors teach the same course in the same department. Don't repeat any pairs. (k) For each professor, list (in a single record) the total number of that professor's enrollments, the total number of enrollments from students majoring in that professor's department, and the total number of enrollments from students majoring in other departments. Hint: use the SQL CASE statement in the SELECT clause. (1) How many students have taken courses from either "Computer Sciences" or "Civil Engineering"? (Make sure you don't count a student more than once.) (m) Find pairs of student names and professor names such that the given student has taken every section of every course taught by that professor. Don't list a student/professor pair more than once. Hint. This is a tricky query, which will need nesting. 2. The following questions relate to the same schema as above, but do not require execution through the SQL interface. (a) Write an SQL assertion statement to check the constraint that a student cannot have more than two majors. (b) Write an SQL assertion statement to check the constraint that every department must have at least one professor. Explain how this assertion might relate to a total participation constraint on a corresponding entity-relationship diagram. (c) Suppose that the table "dept has an additional attribute budget. Write a trigger that adds $5 to the budget of the department each time a new student signs up for one of that department's classes, and a second trigger that subtracts $5 when a student drops such a class. (a) The university wants to make some of this information available to a student's resident advisor. The advisor should be able to see the classes a student is enrolled in, but not the grade. Create a view that provides this information. (e) Suppose that the database system has access to an identifier (think of something like Columbia's UNI) that comes from the same domain as the sid field for students. Suppose this identifier is available in the system variable $LOGIN which can be used within SQL statements. Create a view that defines the section and course information for the currently logged-in student. (f) Imagine we have three user roles: Administrators (who manage enrollments), students, and resident advisors. Describe a suitable set of table permissions (specified via grant statements) for each role. (Include the views from the previous questions.)