CS 2550 SQL Assignment #8
SQL Queries - Outer Joins
Due March 9th Prior to Class

Perform SQL queries using outer 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 CS2550A08Spool.txt

  5. Complete the following Queries:
    1. List the employee number, first and last name of all employees and the number of ColorMeg products that they have sold.
    2. List all of the customer cities and the total dollar amount of February 2007 sales for each city.
    3. List the name of each employee and the highest price of the items they sold in January 2007.
    4. For each order city, list the average order amount for February 2007 sales.
    5. For each customer state, list the number and dollar amount of orders from that state during the month of February 2007.

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