Project 1: SQL Refresher

This assignment is to refresh your memory (or update your memory) on SQL and core relational database concepts.  At the start of the class, you should have received an email containing your UTCS Oracle Microlab account and password. No group projects are allowed. You need to do the following:

• Install Oracle SQLDeveloper on a Windows machine to use in this assignment.  Installation instructions are described here.  Oracle XE is mentioned in these instructions -- this is a 'personal' version of Oracle that you can run on your laptop and that you don't need to use the Microlab server.  If you want to use XE, that's fine for this assignment.  (However, be aware that outer joins may not work in XE, as it is dealing with an older version of Oracle than the Microlab server).

• Next, run the following SQL script, which will create a set of relations and populate them with tuples.  You will use this database to process the following queries.

Now write SQL queries to answer the following questions.  Useful and short tutorials on SQL can be found here and here, should you need them.

1. What is the price of the part named "Dirty Harry"?

2. What orders have been shipped after date '03-feb-95'?

3. What are the ono and cname values of customers whose orders have not been shipped (i.e., the shipped column has a null value)?

4. Retrieve the names of parts whose quantity on hand (QOH) is between 20 and 70.

5. Get all unique pairs of cno values for customers that have the same zip code.

6. Create a nested SQL select statement that returns the cname values of customers who have placed orders with employees living in Fort Dodge.

7. What orders have been shipped to Wichita?

8. Get the pname values of parts with the lowest price.

9. What is the name of the part with the lowest price? (use qualified comparison in your predicate, i.e., <=all).

10. What parts cost more than the most expensive Land Before Time part? (Hint: you should use pattern-matching, e.g., pname like 'Land Before Time%').

11. Write a correlated query to return the cities of zipcodes from which an order has been placed.

12. Get cname values of customers who have placed at least one part order through employee with eno = 1000.

13. Get the total number of customers.

14. Get the pname values of parts that cost more than the average cost of all parts.

15. For each part, get pno and pname values along with the total sales in dollars.

16. For each part, get pno and pname values along with the total sales in dollars, but only for total sales exceeding $200.

17. Repeat the last 2 queries, except this time create a view to simplify your work. Define the view and each query on that view.

18. Delete order 1021 and its order details.

19. Increate the cost of all parts by 5%.

20. Retrieve employees by name in reverse alphabetical order.

21. What tuples of Employees and Zipcodes do not participate in a join of these relations? Use the outerjoin and minus operations.
    

Submit a single SQL script with comments that processes the above queries in the order listed.  Comments are lines starting with --.  An example format of your submission is:

-- query 1: what is the price of the part named "Dirty Harry"?

select ...

-- query 2: What orders have been shipped after date '03-feb-95'?

select ...