We have seen more SQL, including the material from Sections 6.3 and 6.4 (through 6.4.3) of the book, stopping before 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 mideterm is scheduled for 5/7.
1. (Reading Assignment) Read Sections 6.3, and 6.4.1, 6.4.2 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 computer science students (LastName, SID) who haven't enrolled in any computer science courses.
List students who enrolled in classes in 2011 and 2013, but not in 2012. Hint: you won't need the course table for this.
List student groups which don't have any members from Chicago.
3. (University SQL, 20pt) Write SQL for the following problems on the university database. Run each query. Submit screenshots of both query and output.
List courses in which both graduate and undergraduate students have enrolled (not necessarily in the same quarter).
List students who did not enroll in a course within 2 years of starting at the university (i.e. not in the year they started, and not in the following year either).
List the oldest studentgroup(s), that is the studentgroup (or groups) which has (have) the earliest founding year. Note: The query needs to keep working when the studentgroup table is updated, of course, so no hard-coding of 1999 is allowed; also, don't use max or min (which we haven't seen yet).
For each student group list the name of the group and the senior member(s) of that group. (Senior member here means that the student started in the earliest year that any member of that group started, e.g. the senior member of HerCTI is Abigail Winter, since she joined in 2010, before the other members.)
4. (UNIQUE, 5pt) A little bit tricker, maybe, but not much so. If we had the UNIQUE construct, this would be reasonably straightforward, but we don't, so we need to work around it: 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). As usual, include screenshot of query and testrun.