We earlier completed the DDL part of SQL (Sections 7.1-7.3 of the book), and talked about action statement 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 6.1 and 6.2 through Section 6.2.3. Next week we will continue with 6.2-6.4.
For details of Oracle SQL, be sure to check out Oracle's comprehensive reference. For (somewhat advanced) advice on SQL in real life, check out SQL for Smarties, by Joe Celko, now in its 5th edition.You can preview much of the book at books.google, and the whole book is available online through Safari via the library.
1. (Reading Assignment) Read Sections 7.1-7.3, 6.5, 6.1, and 6.2 (up to page 262). If you want to read ahead, finish 6.2 and move on to 6.3 and 6.4. (Chapter 3 will be next after that.)
2. (SQL, 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 CSC/IT courses (CID, CourseName, Department, CourseNr) at the 200 and 400 level (that is, course numbers are of the type 2xx and 4xx).
b) List all graduate students (SID, Lastname, FIrstname) enrolled in an undergraduate corse (coursenr < 400).
c) We want a list of long-term students, to that end, write a query which lists students who enrolled in classes which are at least 3 years apart, e.g. 2015 and 2018, or 2014 and 2018, or 2013 and 2017. Note: don't hardcode these options. Your query needs to keep working in future as well. Ignore quarter information. Hint: how often is the student enrolled?
3. (SQL, 15pt) You have relation Orders(OrderID, CustomerID, NumberOfItems, ItemsTotal), Customer(CID, Name, State), StateTax(State, SalesTaxRate).
a) Create SQL-DDL to create these tables (with foreign keys: CustomerID -> CID) and fill them with a small number of records. E.g. in State you could have ('Illinois', 6.25) and ('Michigan', 6) (do not include all US states).
b) Write a query that lists all orders (by OrderID) and the expected price the customer has to pay. Calculate the expected price according to the following rules
The product cost of all items in the order together is ItemsTotal.
To this you need to add shipping costs; shipping costs are calculated as follows: each item by itself costs $0.99 in shipping. Items are bundled in shipments containing (up to) 4 items. Each shipment is $3.99. So two items incur a cost of $5.97 = 2*$0.99 + $3.99, and 5 items $12.93 = 5*$0.99 + 2*$3.99.)
Tax is calculated based on ItemsTotal. Everybody pays a basic sales tax of 4%. We also collect tax based on the customer's state's tax-rate (as recorded in StateTax) . So an Illinois customer pays an additional 6.25% on ItemsTotal (for a total of 10.25% in sales tax) and a Michigan customer 6% (a total of 10%).
c) Improve the query to deal with the following situation: Sometimes our order records are incomplete (i.e. contain null values). If we don't have ItemsTotal, the query should not get an expected price for the order (i.e. it should be null). If NumberOfItems is missing, we assume it is 1. If there's a null entry for a state's sales tax in the table we assume it is 0. (Hint: Recall the coalesce function). Hand in your solution of c) separately from the solution to b).
As always, submit screenshots of both queries and output.
4.(SQL, 10pt) We are creating a student mentoring program at our university. Every student can get assigned a student mentor. We implement this by adding a field mentorID to student which is a foreign key referencing Student(SID).
a) [3pt] Modify the student table (using alter table commands) to add the mentorID field, and make it a foreign key referencing Student(SID). Assign mentors to some (not all) of the students. Hint: Make sure MentorID and SID have the same type.
b) [2pt] Write a query which lists all students (SID, Lastname, Firstname) which have not been assigned a mentor.
c) [5pt] Our university has a rule that student mentors must have matching careers with their mentees (grad with grad, ugrad with ugrad). Write a query which lists all mentees (SID, Lastname) which have been assigned a mentor violating this rule. Hint: how many students are involved in this query?
As always, submit screenshots of both queries and output.
5. (Extra Credit only, 10pt) Implement (in the language of your choice), a system that lets you specify f.k.→p.k relations between tables and based on that information determines whether the tables can be created in some order without adding foreign key constraints later, and outputs some such order if it exists, or the information that no such order exists. Include a test-run for our university database.