CS 2550 SQL Assignment #7
SQL Queries - Nested Queries, Difference and Divide

Perform SQL divide and difference queries using sub queries

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 CS2550A07Spool.txt

  5. Complete the following Queries:
    1. List the names (first and last name) of employees who have never sold a color monitor.
    2. List the names of Employees that had at least one order in both January and February 2007.
    3. List the name and balance of the customer with the highest balance.
    4. List the name of the city and number of customers for the city with the greatest number of customers
    5. List the name of the city that received the lowest number of shipments in February 2007.
    6. List the average number of products sold per order.
    7. List the names of customers who had shipment to someone other than themselves.  Along with the customer name give the shipping name.
    8. List the names of employees with above average commission rates.
    9. List the product name and price for the product will the highest total January 2007 dollar sales.
    10. List the names of Employees that have never sold a Connex product.

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