DBS311 Lab 3

Load the table for lab 3 into your Oracle account:  Run a script called PRODUCTS to get this lab3 table into your Oracle account. Do this before the lab. Be prepared to use PRODUCTS in the lab. You should already have loaded the ORDERS, ORDERDETAILS and CUSTOMER tables.

1. The manufacturer’s suggested retail price for a product is 207.80, but on one sale we are going to overcharge a customer (priceeach) more than the MSRP. We are going to charge 214.30 when the order number is 10185 and the product code is S12_1108. You can do this prior to demonstrating the lab, but be prepared to prove that this priceeach is in effect for that orderline in the ORDERLINES table. (1 mark)
2. Determine the order number, quantity ordered, product code and price for all rows in the ORDERDETAILS table that match the minimum price each for all ORDERDETAIL records. (1 mark)
3. Determine all the rows in the PRODUCTS table that match the product code(s) in the ORDERDETAILS table with the highest price. Show only the columns that show below. (2 marks)
4. The manufacture suggested retail price for a 1957 Corvette Convertible is 148.80 and the price to buy it is 69.93 netting the merchant 78.87 with a sale at the suggested price. Show all the product names and the net amount for all the products that fall below the average net amount. (2 marks)

5. Produce the following output of eleven rows from the Customer and Orders table. (1 mark)

6. Determine any products which are not Classic Cars that have exactly the same MSRP as one of the Classic Cars productline (2 marks)
7. We only want to report on the lines where the number of items is greater than the number of items for Planes. (1 mark)