We have nearly completed Chapter 6 on SQL, finishing with aggregation (GROUP BY), covered in Section 6.4. Next week, we'll 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.4.3-6.4.7 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 the query. Submit screenshots of both query and output.
[5pt] List students who are members of at least three groups.
[5pt] List groups that have (strictly) more undergraduate than graduate student members. Hint: start with a query that counts the number of ugrd (or grd) members in a particular student group (e.g. gid = 101).
(5pt) List students that have taken all CSC courses. Note: in our current database that would be CSC 489, CSC 440, CSC 355, and CSC 389, but in your query do not hardcode this list of courses. Your query needs to keep working even if courses are added or removed from the course table. Hint: there are two ways of doing this: using sets, or using counting.
3. (Extended University SQL I, 12pt) The queries in this exercise require you to extend your SQL-DDL for the university database first (as described below). Make sure you read the whole problem first so you know how/what data you need to add.
[2pt] Add a grade field to the enrolled table and assign grades from 0.0 to 4.0 to students enrolled in classes. Include just the new DDL for the enrolled table, including the new/modified data in the table. Hint: you can update the enrolled table with some random data: dbms_random.value() returns a random value in the range (0,1), that could come in handy (with an update statement).
[5pt] Based on this extended university database write and run a query that lists students (SID, Lastname, Firstname) and their GPA, as long as the GPA is at least 2. Note: the GPA (grade point average) is the average grade a student earned in all classes they took.
[5pt] We're worried about grade inflation: list courses (Department, CourseNr, CourseName, Quarter, Year) in which the average student grade has been 3.5 or higher. For the purposes of this problem, a course is a course offered in a particular quarter/year.
4. (Extended University SQL I, 13pt) The queries in this exercise require you to extend your SQL-DDL for the university database first (as described below). Make sure you read the whole problem first so you know how/what data you need to add.
[3pt] Add a table based on the prerequisite relationship: Prerequisite(CID1, CID2) means that course CID2 requires successful completion of course CID1, in other words, CID1 is a prerequiste for CID2. Add some prerequisite records to the university database. Include courses that have multiple prerequisites, e.g. let's say 1092 requires 3111 and 9219 (so the Prerequisite table would include rows (3111,1092) and (9219, 1092) ). Include the DDL for this table as well as a screenshot of the data you made up for this table.
[10pt] We want to start enforcing prerequisites: a student shouldn't be enrolled in a class unless they have passed all the prerequisite courses for that class. Passing a prerequisite course means getting at least a C (2.0) in that course. Write (and run) a query that lists all students who have ever taken a course without having successfully passed all the prerequisites for that course before taking the course. Notes: 1) a prerequisite must be passed before the follow-up course is taken. So if Marcus Brennigan first takes 1092 in Winter 2012 and then takes 3111 in Fall 2013 and passes it, he was still violating the prerequisite condition in Winter 2012 (assuming 1092 has 3111 as a prerequisite) when he took 1092 and his name should be listed. Make sure your data is interesting enough for testing the query. 2) The year listed in enrolled is the academic year. Within the academic year, Fall comes before Winter which comes before Spring. So Fall 2014 comes before Spring 2014 (which is a bit counterintuitive, but it's how DePaul handles it. Think of 2014 as really 2013/2014). Hint: at first ignore the grade (C) requirement, as well as the requirement that the prereq must have been taken successfully prior to the course. Add these requirements step by step. First the grade requirement (C). Then implement "prior" by year (as in: took the course in a previous year). And finally, do the full quarter logic. You can hand in partial work for this, but clearly state how far you got, i.e. which problem you're query is solving. Hint 2: I used double negation to express the query, though there may be other solutions.