Executing SQL*PLUS...
SQL*Plus: Release 11.2.0.1.0 Production on Wed May 18 10:14:19 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: ellist@oracle11g
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
The user-name for oracle11g is the same as your NSU email' user-name, followed by @oracle11g. The password for Oracle11g is mmis6301
SQL> is the prompt of Oracle11g server, and SQL statements and SQL*PLUS commands can be entered.
Step 2: Copy the database files to your account
1. You will need to make copies of the five (5) database files you'll be using for this assignment. To do so, enter the following commands at the SQL> prompt:
create table tablename as? select *? from ellist.tablename;
2. The five tables are named: CUSTOMER, ORDERS, ORDER_LINE, PARTS, and SALES_REP, so the five commands would be:
create table CUSTOMER as? select *? from ellist.customer;
create table ORDERS as? select *? from ellist.orders;
create table ORDER_LINE as? select *? from ellist.order_line;
create table PARTS as? select *? from ellist.parts;
create table SALES_REP as? select *? from ellist.sales_rep;
Step 3: Execute the SQL commands to accomplish the following
1. Find the part number and description of all parts.
2. List the complete SALES_REP table.
3. Find the names of all the customers who have a credit limit of at least $1,500.
4. Give the order numbers of those orders placed by customer 124 on September 5, 1998. (Hint: When you enter a condition in a DATE field, enclose the date in single quotation marks. For example, to search for all orders placed on September 5, 1998, enter '9/5/9S' in the ORDER_DATE column.)
5. Give the part number, description, and on-hand value (units on hand price) for each part in item class AP. (Remember that the AP must be enclosed in quotation marks.)
6. Find the number and name of all customers whose last name is Nelson.
7. List all details about parts. The output should be sorted by unit price.
8. Find how many customers have a credit limit of $1,000.
9. Find the total of the balances for all the customers represented by sales rep 12.
10. For each order, list the order number, order date, customer number, and customer name.
11. For each order placed on September 5, 1998, list the order number, customer number, and customer name.
12. Find the number and name of all sales reps who represent any customer with a credit limit of $1,000.
13. For each order, list the order number, order date, customer number, customer name, along with the number and name of the sales rep who represents the customer.
14. Change the description of part BT04 to Gas Stove.
15. Add order 12600 to the database. The date of the order is September 6, 1998. The order was placed by customer 311.
16. Delete all customers whose balance is under $100.00 and who are represented by sales rep 12.
Step 4: Copy and submit your SQL session (or sessions)
1. The deliverable for this assignment is a copy of the actual SQL commands you entered in the Oracle 11g server and the resulting output from Oracle
2. Please use the Copy command to copy the entire Oracle session or sessions
3. Paste the Oracle session or sessions into a single Word document and submit via Blackboard
4. Be sure to include a cover page that contains your name, the course name (MMIS630), and the assignment name (SQL Exam) on your Word document