Project 1: SQL Refresher

This assignment is to refresh your memory (or update your memory) on SQL and core relational database concepts.  No group projects are allowed.  You can use one of two different platforms for this project:

• An Oracle DBMS or
• Microsoft Access (which comes with MS Office)

• Install Oracle XE 11g, which runs only on linux64 or windows32 platforms.  Download and installation instructions are described here.

• Install Oracle SQLDeveloper on a Windows machine to use in this assignment.  Installation instructions are described here.  

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

• Use the create script in SQL scripts to create an Excel file for six table, and then use Access to load each file as a table.  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. Increase 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.

1. Create a single PDF file that:

• lists each query
• followed by the answer of that query

2. If you used Oracle, 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 ...

3. If you used Access, submit your Excel files and Access database.  Your Access database should clearly label each query.