ho much it cost
1 Victoria University Sydney BCO2149 Database Systems Total Marks: 50 Assignment 1 (SQL) S1 2018 Instructions Each question in this assignment is worth 2 marks. Students are required to attempt all questions in this assignment. Students should save all their queries and screenshot of the corresponding output in a word file and submit this file to Assignment 1 dropbox in VU Collaborate. The Retail database used in this assignment can be obtained from the Assignments directory or module within the BCO2149 VU Collaborate space. Retail Database Table Structure Customer (CustNo, F_Name, LastName, Street, Suburb, PostCode, Balance) Department (DepartNo, DepartName) SalesRep (SalesRepNo, SalesRepLastName, SalesRepFName, DepartNo(fk)) Orders (OrderNo, CustNo(fk), OrdDate, SalesRepNo(fk), OrdStatus) OrderLine (OrderNo(fk), ProductNo(fk), QtyPurchased, PurchasePrice) Product (ProductNo, ProdDescription, QtyOnHand, UnitPrice) The Retail database maintains information about Customers, Departments, Sales Representatives, and Orders. The Customer table maintains information about customers. The Department table stores information about departments. The SalesRep table stores information on all Sales Representatives and the Department they belong to. The Orders table stores information relating to Orders, a unique Order number, the date the Order was placed and the Customer who placed the order. The OrderLine table maintains information about Products that were ordered on each specific Order. Purchase Price represents a Product's Unit Price at the point of sale. The Product table maintains information about products. Students will need to use the catalog function within winSQL in order to ascertain the data types of each field. Questions Students are to develop SQL queries to answer the following questions: 1. What are the names (Last and First Name) of all the sales representatives that work within the ‘Electrical’ department? 2. Display all the details of customers that have a balance between 600 and 2000 dollars inclusive. 2 3. Which products have the letter 'o' in their description field? List the product number and description. Sort the output by description in descending order. 4. Display the names (Last Name and First Name) of all customers that have placed an order. Do not display any repeats. 5. Display all the Orders (Order Number, Order Date, and Order Status) that have an Order Status of ‘C’. 6. Which product(s) have not been placed on any order? Display the Product Number only. 7. How many orders has customer 1040 placed? 8. For each order display the full name of the customer and sales representative, the order number, order date and the product number and description of each product ordered. 9. Display the customer numbers of those customers who do not have a current order in the order table. 10. What is the total amount (QtyPurchased*PurchasePrice) of all products on each order? Display the order number and the total amount. Rename the calculated column to "Total Amount". 11. What is the description of the product that has the highest "QtyPurchased" on any specific order? 12. How many sales representatives are there in each department? Display the full department name and the number of sales representatives. Rename the calculated field as "Number of Employees". 13. Which customer (First and Last Name) has the highest balance? 14. Which customers have placed more than three orders? (Display the customer number and the number of orders). 15. Display the number of orders that have an order status of 'C' and an order status of 'N'. Display the status and count only. Order the output by status in ascending order. 16. Display the names of the sales representatives that have taken three (3) or more orders. (Display the sales representatives Surname and First Name and the count. 17. List all the orders that were not taken by sales representative 'Karen Salt'. Display the Order Number and Order Date for each order. 18. What product has the second highest unit price? Display the Product Number and the Description. 19. What is the name of the customer who placed the order 12640? 20. How many times has ProductNo ‘DP03’ been ordered? 21. Are there any products that have not been ordered? List the product number and product name. 22. What are the names (First and last) of the customers that have ordered a product described as a 'Walkman'? 23. Display all details of products that have a quantity on hand of greater than 15 and a unit price of greater than 120 dollars. 24. What is the name (last name and First Name) of the sales representative that is assigned to order 12800? 25. How many orders were taken by sales person ‘S5’ in the year 2002? 3 Customer CustNo F_Name LastName Street Suburb PostCode Balance 1040 Clair Rose 519 Hope St Hopesville 3511 402.75 1234 Ben Smith 481 South Rd Southville 3422 818.75 1456 Mary Sweet 12 Horse Pl Angle Bay 3244 2770.75 2567 Mathew Jones 73 North Rd Northville 3255 23.50 3115 Adam Wright 66 Bay St Oceanside 3444 1825.75 4120 Sally Adams 16 Elm Drv Moomridge 3455 1817.50 5220 Mary Nelson 108 Pine Ln Castlemaine 3666 698.75 5670 Tran Dinh 808 Ridge Pl Harper 3687 4402.40 5870 Maria Lopez 512 Acland St St Kilda 3356 114.60 6220 Dan Martin 41 Ships Ln Waterside 3771 1045.75 Department DepartNo DepartName 01 Electrical 02 Furnishings 03 Auto 04 Manchester 05 Clothing SalesRep SalesRepNo SalesRepLastName SalesRepFName DepartNo S1 Smith Bill 01 S2 Sheedy Kevin 03 S3 Walsh Ken 01 S4 Salt Karen 05 S5 Jenkin Peter 04 S6 Ibrahim Michael 03 S7 Dakic Dan 02 S8 Parker Lenny 02 S9 James Julie 05 Product ProductNo ProdDescription Qtyonhand UnitPrice AX12 Iron 104 24.95 AZ52 Dartboard 20 12.95 BA74 Basketball 40 29.95 BH22 Microwave 95 124.95 BT04 Gas Griller 11 159.99 BZ66 Washer Plate 52 450.00 CA14 Floor Mats 78 39.99 CB03 Bike 44 254.00 CP22 Seat Covers 68 28.50 CP33 Hub Caps 28 38.95 CP55 Pillows 33 49.50 CX11 Blender 112 22.95 CZ81 Treadmill 68 349.95 DF02 Mother Board 12 349.95 DP01 Mouse Mat 68 10.00 DP03 Color Scanner 13 244.00 DP04 Organizer 12 390.00 DP19 Walkman 50 167.90 DP95 Laser Printer 22 356.95 DP98 Digital Camera 3 1349.95 4 Orders OrderNo CustNo OrdDate SalesRepNo OrdStatus 12111 1234 21/10/2000 S2 C 12112 1234 15/01/2001 S2 C 12113 1234 11/02/2002 S3 C 12114 1234 2/03/2003 S1 N 12121 2567 2/09/2002 S4 C 12489 1234 2/09/2000 S1 C 12490 1456 12/12/2002 S6 C 12491 1456 18/05/2002 S6 C 12495 1456 22/11/2002 S4 C 12497 1456 18/02/2003 S7 N 12501 1040 22/09/2000 S4 C 12502 1040 14/07/2001 S8 C 12505 1040 2/06/2002 S7 C 12508 1040 17/08/2002 S3 N 12509 1040 23/10/2002 S5 N 12610 5220 12/04/2001 S8 C 12620 5220 22/05/2002 S6 C 12630 5220 17/02/2003 S9 N 12640 5670 12/09/2001 S4 C 12666 2567 18/07/2001 S1 C 12710 5870 17/11/2002 S2 N 12777 2567 2/10/2002 S3 N 12800 6220 14/07/2001 S5 C 12810 6220 28/11/2002 S7 C 12815 6220 13/05/2002 S5 C 12820 6220 2/09/2002 S1 N 12888 1456 2/09/2001 S5 C 5 Orderline OrderNo ProductNo QtyPurchased PurchasePrice 12111 DP01 110 9.00 12112 DP95 1 320.00 12113 CA14 1 35.50 12113 CB03 1 250.00 12114 DP03 1 240.00 12121 DP95 1 340.00 12489 AX12 11 21.95 12489 DP04 2 330.95 12490 AZ52 1 11.20 12490 BA74 3 24.50 12490 CZ81 1 300.00 12491 BT04 1 149.99 12491 BZ66 1 399.99 12495 CB03 4 179.99 12495 CX11 2 22.95 12497 AZ52 2 12.95 12497 BA74 4 24.95 12497 CP33 2 35.50 12501 BT04 3 149.99 12502 CZ81 2 325.99 12505 DP03 1 220.00 12505 DP19 1 150.00 12505 DP95 2 340.00 12508 DP19 1 160.00 12509 DF02 2 310.00 12509 DP98 1 1150.00 12610 DP19 1 1250.00 12620 BH22 1 110.00 12620 BT04 1 150.00 12630 CB03 1 250.00 12640 CZ81 1 324.00 12640 DP19 1 1250.00 12666 CB03 3 250.00 12710 CP22 1 23.00 12710 CP33 1 35.50 12777 DP19 3 150.00 12800 BH22 1 130.00 12810 DP04 1 310.00 12810 DP95 3 310.00 12815 CB03 2 250.00 12820 CA14 2 34.50 12820 CP55 11 41.00 12820 CX11 1 22.00 12888 CP55 1 44.50 12888 CX11 1 22.50