SIT103 Database -Assignment 1 Trimester 12011 Due Date: Thu 5th May, 2011 Background You will be working with a set of tables for a computer shop. To access these tables in sql create the following synonyms: SQL> CREATE SYNONYM PRODUCT FOR ARTHURA.CSPRODUCT; SQL> CREATE SYNONYM CATEGORY FOR ARTHURA.CSCATEGORY; SQL> CREATE SYNONYM CSORDER FOR ARTHURA.CSORDER; SQL> CREATE SYNONYM ORDERLINE FOR ARTHURA.CSORDERLINE; SQL> CREATE SYNONYM CUSTOMER FOR ARTHURA.CSCUSTOMER; You are to prepare 14 SQL query statements that will provide answers to the following 14 requests for information.Document Preview:SIT103 Database -Assignment 1 Trimester 12011 Due Date: Thu 5th May, 2011 Background You will be working with a set of tables for a computer shop. To access these tables in sql create the following synonyms: SQL> CREATE SYNONYM PRODUCT FOR ARTHURA.CSPRODUCT; SQL> CREATE SYNONYM CATEGORY FOR ARTHURA.CSCATEGORY; SQL> CREATE SYNONYM CSORDER FOR ARTHURA.CSORDER; SQL> CREATE SYNONYM ORDERLINE FOR ARTHURA.CSORDERLINE; SQL> CREATE SYNONYM CUSTOMER FOR ARTHURA.CSCUSTOMER; You are to prepare 14 SQL query statements that will provide answers to the following 14 requests for information. You must submit the syntax of the queries themselves, not all the output (expecting 2 or 3 pages only). 1. List all details of orders made between July and December 2010, by females between 30 and 40 years old, for products that cost more than 200 dollars in the CPU category. 2. For all unpaid orders, show how many days the payment is overdue (based on the order date) and details of the customer that owes the money. Sort the list by days overdue and then by surname. 3. The computer shop wants to know how many orders have been placed by each sex (ie. How many orders were made by males and how many orders were made by females). 4. For each postcode, show the number of orders placed during 2010. 5. Show how many orders were placed in each month of 2010. 6. Create a view displaying the total number of items sold for each product. . Include the product name into the view. Dont forget to take the quantity field into consideration. 7. Using the view in the previous question, show details of the product with the highest number of sales. 8. Show products that have never been sold. 9. Display the name and price of the cheapest and most expensive item in the store. 10. Create a view that displays the total value of each order (ie. The total amount of money that each order was for). 11. Using the view in the previous question, show details of the biggest order ever, including the customer name. 12. Attachments: 11111111.pdf
Use the order calculator below and get started! Contact our live support team for any assistance or inquiry.
[order_calculator]