Perform SQL queries using JOINS

Description

Write a series of Oracle SQL query statements using the OrderEntry database which consists of the tables and rows as shown on pages 121 - 123 of your text.

This assignment will be completed using Oracle. You may use Oracle 9i or later. The CS Athena remote terminal server has SQL*Plus installed and available for student use. Instructions for using the Athena server can be found at
http://rbhilton.com/wsucs/cs2550/CS2550TSInstructions.pdf

Complete the assignment by performing the following

1. Connect to the WSU/CS Oracle Server, or any other available Oracle server using SQL*Plus.
   Instructions for connecting to terminal server and logging on to SQL*Plus can be found at:
   http://rbhilton.com/wsucs/cs2550/CS2550TSInstructions.htm
   
   
2. Download the script file
   http://rbhilton.com/wsucs/cs2550/OrderEntryOracleLoad.sql
   This script file will be used to populate the Order Entry database with data. Note that the assignment is based on an initial starting point. You will need to run the script to ensure that your database is unaltered. If you mistakenly change your database, you will have to rerun the script.
   
   
3. Connect to the titan Oracle server and run the script.
   If you are connecting to Oracle via Athena follow the instructions for running scripts remotely:
   
   
4. Once you have the tables loaded, turn on spooling to a file CS2550A06Spool.txt
   
   
5. Complete the following SQL DML statements:
   (NOTE: The results of the queries below will depend on correct DML statements)
   
   
   1. Add a new order number Order number O4815162 dated 2/18/2007
      The order was taken by Thomas Johnson and consists of:
      Three of Product Number P0036566
      Purchased by customer Jerry Wyatt
      The order should ship to: Maria Wyatt, 4815 16th St., Denver, CO 80222-0022
      
      
   2. On order number O1615141, change the quantity of product number P1445671 from 1 to 6.
      
      
   3. On Order number O2334661, change the customer number to C8654390.
      
      
   4. Delete Order number O1241518.
      
      
      
6. Complete the following Queries:
   1. List each customer number, first and last names, customer balance and the total dollar amount that they have ordered.
   2. List the employee number, the employee name (first and last), the commission rate, the supervising employee name (first and last), and the commission rate of the supervisor.
   3. List the customer number and name (first and last) of all customers from Seattle who have ordered at least 5 products in 2007.
   4. List the number of 19-inch color monitors that have been sold to customers in Denver.
   5. List the names (first and last) and city for all of Joe Jenkin's customers.
   6. List the product name and quantity for all products purchased by Candy Kendall.
   7. List the names of products that have a product quantity-on-hand that is more than 10 times the number of that product that sold in January 2007.
   8. List each customer city, state and the number of products shipped to that city in February of 2007.
   9. List the names of the product manufacturers with products sold in February 2007. (Eliminate Duplicates)
   10. Provide an alphabetic listing of all customers and employees. To the side of each name list whether they are a customer or an employee;
       
       

Number each SQL statement and put them into a single text file named CS2550A06.txt  and upload that file, along with the spool file, CS2550A06Spool.txt using the Assignment Uploader.