We talked about the DDL part of SQL (Sections 7.1-7.3 of the book), and action statements in SQL (Section 6.5). We are now in the middle of seeing examples of SQL DML (SELECT). This material is in Chapter 6 of the book. We have covered most of 6.1 and will continue with 6.2-6.4 next week.
For details of Oracle SQL, be sure to check out Oracle's comprehensive reference.
1. (Reading Assignment) Read Sections 7.1-7.3, 6.5, and 6.1. If you want to read ahead, check out 6.2 and 6.3.
2. (University Database, 15pt). You want to modify the university database so it supports the following features:
Space on transcripts is limited, so there are only 20 characters for each course name. Add a field short_title that allows you to store an alternative short name for each course. (E.g. 'Theory of Comp' for 'Theory of Computation').
Each student is assigned a mentor, a more senior student. A student has at most one mentor (though some students may have none).
A student can select a grade basis when enrolling in a class; there are three choices: PF(pass/fail), CR (student receives a letter grade), and AU (audit, no grade assigned). Add a field to the database which supports storing this information. Default is CR. This field may not be null, that is, a grade basis must be selected.
Modify university.sql so it can deal with this information. This will require updating the DDL (CREATE TABLE commands) as well as the INSERT commands (e.g. to INSERT cross-listed courses).
For submission: for each of these four tasks include a screenshot of the relevant parts only (i.e. the changes in the CREATE TABLE command and at least one of the inserts you run).
Hint: Part a requires adding a line to the create table command, part b requires adding a line to a create table command and the addition of a foreign key constraint, part c requires the addition of a new field with a default value and a check constraint.
Hint 2: You can also use ALTER TABLE commands to effect all of these changes; this will be less code, but will be a bit harder. A good learning experience though if you want to get to know ALTER TABLE.
3. (Basic SQL, 10pt) In class we wrote a query for "List all students and their expected graduation year", where we calculated the expected graduation year as Started + 4. In this problem, you want to implement a more sophisticated formula: If the student is a 'GRD' student, we expect them to graduate in Started + 2, if they are 'UGRD', then in Started + 4, and if they are 'SAL' (student at large, we don't currently have any such student), then Started + 7. Write a query that returns expected graduation years based on this formula. Include a test-run, including one with an SAL student (you can update one of the current students to be SAL). Hint: research the Oracle CASE statement.
4. (SQL Logic, 15pt) Write SQL for the following problems on the university database. Run the query. Submit screenshots of both query and output and make sure that the output is correct (and if it is not, tell me why you think it's wrong or what went wrong).
a) List students (SID, Lastname, Firstname) from Chicago, who are presidents of a student group.
b) List all CSC/GAM courses at the 200 and 400 level (that is, course numbers of the type 2xx and 4xx).
c) List all graduate students (SID, Lastname, FIrstname) enrolled in an undergraduate corse (coursenr < 400).