We have seen more SQL, including the material from Sections 6.3 and 6.4 (through 6.4.4) of the book, stopping before the more sophisticated forms of aggregation (GROUP BY). Next week, we'll talk about aggregation, and have one final look at SQL by talking about transactional processing (Section 6.6). Transactions point us to PL/SQL which we will see later in the course. After completing transactions we will start on relational design (Chapter 3).
The in-class midterm is scheduled for Wednesday, 5/6 in CDM 819. Be sure to be early to have time for set-up.
1. (Reading Assignment) Read Sections 6.3, and 6.4.1-6.4.4 of the textbook (Ullman, Widom, A First Course in Database Systems).
2. (University SQL, 15pt) Write SQL for the following problems on the university database. Run each query. Submit screenshots of both query and output. Note: Do not use SQL features (e.g. GROUP BY) that we haven't seen in class.
List studentgroups which do not have any Chicago members.
List courses which were offered (enrolled in) in 2011 and 2013, but not in 2012. Hint: you won't need the student table for this.
List courses in which both graduate and undergraduate students enrolled (not necessarily in the same quarter).
3. (University SQL, 15pt) Write SQL for the following problems on the university database. Run each query. Submit screenshots of both query and output.
List students who did not enroll in a course within 3 years of starting at the university (i.e. not in the year they started, and not in the following two years either).
List the most recent studentgroup(s), that is the studentgroup (or groups) which has (have) the largest founding year. Note: The query needs to keep working when the studentgroup table is updated, of course, so no hard-coding of 2004 is allowed.
List students who are members of at least three groups. Hint: the result of a nested correlated counting query can be used in a condition.
4. (UNIQUE, 5pt) Write a query to list all students (SID, Lastname) who have a unique last name. (In our database, everybody except for the Snowdons and the Patels). If we had the UNIQUE construct, this would be reasonably straightforward, but we don't, so we need to work around it. As usual, include screenshot of query and testrun.