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 (if not all) of 6.1, and some of 6.2. We will continue with 6.2-6.4 next week.
For details of Oracle SQL, be sure to check out Oracle's comprehensive reference. Also, check out my How to write a simple SQL query.
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 a course name on a transcript can only be 20 characters long. Add a field short_title that allows you to store an alternative short name for each course to use on transcripts. (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 grading 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 grading 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 mentor information).
For submission: for each of these three 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 a bit harder. A good learning experience though if you want to get to know ALTER TABLE.
3. (Basic SQL, 10pt) You want to list all students (by name, and SID), and their expected graduation year. You calculate the graduation year as follows: 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 + 5. 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 all graduate students (SID, Lastname, Firstname) from Evanston, who declared COMP-SCI as their program.
b) List the names, department and number of all CSC/GAM/IT courses.
c) List all graduate students (SID, Lastname, Firstname) enrolled in an undergraduate corse (coursenr < 400).