We have nearly completed talking about SQL. We saw material from Sections 6.3 and 6.4 of the book, stopping with aggregation. Next week we will 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 2/12.
1. (Reading Assignment) Read Section 6.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 the query. Submit screenshots of both query and output.
[5pt] List students that belong to at least two 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 (pairs of) students that have taken two or more classes together. Hint: taking a class together means being enrolled in the same class in the same quarter of the same year. Try grouping.
(Extra Credit only, 8pt) List students that have taken all CSC courses. Hint: 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.
3. (Extended University SQL, 25pt) The queries in this exercise require you to extend your SQL-DDL for the university database first (as described in the first two parts). 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).
[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. Include the DDL for this table as well as a screenshot of the data in this table.
[8pt] 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.
[12pt] 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 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 requires 3111) 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 drop the 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 C. Then implement "prior" by year. And finally, do the full quarter logic. Hint 2: I used double negation to express the query, though there may be other solutions.