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 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. (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/DC courses at the 200 and 400 level (that is, course numbers of the type 2xx and 4xx).
b) List computer science students (SID, Lastname, FIrstname) who are presidents of a student group.
c) List all graduate students (SID, Lastname, FIrstname) enrolled in an undergraduate corse (coursenr < 400).
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, 15pt) 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 dropped/created/populated 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.