identification of few tables in database sql
a) Use Oracle SQL* Plus, create the schema of this database. Continue by inserting the data records as presented in the handout. Document final tables.
b) Solve the following queries in SQL. For each query, you need to specify the SQL and show the result of each query if applied to the Order-Entry Database
Material, see attachment
1) List the customer number, name (first and last), city, and the balance of customers who reside in Denver with a balance greater than $150 or who reside in Seattle with a balance greater than $300.
2) List the employee number, name (first and last), and phone of employees who have taken orders in January 2013 from customers with balances greater than $300. Remove duplicate rows in the result.
3) List the order number, order date, customer name (first and last), and total amount for orders placed on January 23, 2013. The total amount of an order is the sum of the quantity times the product price of each product on the order.
4) List the product number, product name, sum of the quantity of products ordered, and total order amount (sum of the product price times the quantity) for orders placed in January 2013. Only include products that have more than five products ordered in January 2013. Sort the result in descending order of the total amount.
5) List the employee number, employee name (first and last), commission rate, supervising employee name (first and last), and the commission rate of the supervisor.
6) Using the join operator style, list the product name and the price of all products ordered by Beth Taylor in January 2013. Remove duplicate rows from the result.
7) For Colorado customers, compute the number of orders placed in January 2013 in which the orders contain products made by Connex. The result should include the customer number, last name and number of orders placed in January 2013.
8) For each employee with a commission rate greater than 0.03, compute the total commission earned from orders taken in January 2013. The total commission earned is the total order amount times the commission rate. The result should include the employee number, employee last name and total commission earned.
9) List the product number, name, price and total order value (sum of price times quantity) of products ordered by Colorado customers in January 2013 in web orders. A web order does not have an employee associated with the order.