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 co
esponding 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, Subu
, 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
elong 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 cu
ent 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 Subu
PostCode Balance
1040 Clair Rose 519 Hope St Hopesville XXXXXXXXXX
1234 Ben Smith 481 South Rd Southville XXXXXXXXXX
1456 Mary Sweet 12 Horse Pl Angle Bay XXXXXXXXXX
2567 Mathew Jones 73 North Rd Northville XXXXXXXXXX
3115 Adam Wright 66 Bay St Oceanside XXXXXXXXXX
4120 Sally Adams 16 Elm Drv Moomridge XXXXXXXXXX
5220 Mary Nelson 108 Pine Ln Castlemaine XXXXXXXXXX
5670 Tran Dinh 808 Ridge Pl Harper XXXXXXXXXX
5870 Maria Lopez 512 Acland St St Kilda XXXXXXXXXX
6220 Dan Martin 41 Ships Ln Waterside XXXXXXXXXX
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 I
ahim Michael 03
S7 Dakic Dan 02
S8 Parker Lenny 02
S9 James Julie 05
Product
ProductNo ProdDescription Qtyonhand UnitPrice
AX12 Iron XXXXXXXXXX
AZ52 Dartboard XXXXXXXXXX
BA74 Basketball XXXXXXXXXX
BH22 Microwave XXXXXXXXXX
BT04 Gas Griller XXXXXXXXXX
BZ66 Washer Plate XXXXXXXXXX
CA14 Floor Mats XXXXXXXXXX
CB03 Bike XXXXXXXXXX
CP22 Seat Covers XXXXXXXXXX
CP33 Hub Caps XXXXXXXXXX
CP55 Pillows XXXXXXXXXX
CX11 Blender XXXXXXXXXX
CZ81 Treadmill XXXXXXXXXX
DF02 Mother Board XXXXXXXXXX
DP01 Mouse Mat XXXXXXXXXX
DP03 Color Scanner XXXXXXXXXX
DP04 Organizer XXXXXXXXXX
DP19 Walkman XXXXXXXXXX
DP95 Laser Printer XXXXXXXXXX
DP98 Digital Camera XXXXXXXXXX
4
Orders
OrderNo CustNo OrdDate SalesRepNo OrdStatus
XXXXXXXXXX/10/2000 S2 C
XXXXXXXXXX/01/2001 S2 C
XXXXXXXXXX/02/2002 S3 C
XXXXXXXXXX/03/2003 S1 N
XXXXXXXXXX/09/2002 S4 C
XXXXXXXXXX/09/2000 S1 C
XXXXXXXXXX/12/2002 S6 C
XXXXXXXXXX/05/2002 S6 C
XXXXXXXXXX/11/2002 S4 C
XXXXXXXXXX/02/2003 S7 N
XXXXXXXXXX/09/2000 S4 C
XXXXXXXXXX/07/2001 S8 C
XXXXXXXXXX/06/2002 S7 C
XXXXXXXXXX/08/2002 S3 N
XXXXXXXXXX/10/2002 S5 N
XXXXXXXXXX/04/2001 S8 C
XXXXXXXXXX/05/2002 S6 C
XXXXXXXXXX/02/2003 S9 N
XXXXXXXXXX/09/2001 S4 C
XXXXXXXXXX/07/2001 S1 C
XXXXXXXXXX/11/2002 S2 N
XXXXXXXXXX/10/2002 S3 N
XXXXXXXXXX/07/2001 S5 C
XXXXXXXXXX/11/2002 S7 C
XXXXXXXXXX/05/2002 S5 C
XXXXXXXXXX/09/2002 S1 N
XXXXXXXXXX/09/2001 S5 C
5
Orderline
OrderNo ProductNo QtyPurchased PurchasePrice
12111 DP XXXXXXXXXX
12112 DP XXXXXXXXXX
12113 CA XXXXXXXXXX
12113 CB XXXXXXXXXX
12114 DP XXXXXXXXXX
12121 DP XXXXXXXXXX
12489 AX XXXXXXXXXX
12489 DP XXXXXXXXXX
12490 AZ XXXXXXXXXX
12490 BA XXXXXXXXXX
12490 CZ XXXXXXXXXX
12491 BT XXXXXXXXXX
12491 BZ XXXXXXXXXX
12495 CB XXXXXXXXXX
12495 CX XXXXXXXXXX
12497 AZ XXXXXXXXXX
12497 BA XXXXXXXXXX
12497 CP XXXXXXXXXX
12501 BT XXXXXXXXXX
12502 CZ XXXXXXXXXX
12505 DP XXXXXXXXXX
12505 DP XXXXXXXXXX
12505 DP XXXXXXXXXX
12508 DP XXXXXXXXXX
12509 DF XXXXXXXXXX
12509 DP XXXXXXXXXX
12610 DP XXXXXXXXXX
12620 BH XXXXXXXXXX
12620 BT XXXXXXXXXX
12630 CB XXXXXXXXXX
12640 CZ XXXXXXXXXX
12640 DP XXXXXXXXXX
12666 CB XXXXXXXXXX
12710 CP XXXXXXXXXX
12710 CP XXXXXXXXXX
12777 DP XXXXXXXXXX
12800 BH XXXXXXXXXX
12810 DP XXXXXXXXXX
12810 DP XXXXXXXXXX
12815 CB XXXXXXXXXX
12820 CA XXXXXXXXXX
12820 CP XXXXXXXXXX
12820 CX XXXXXXXXXX
12888 CP XXXXXXXXXX
12888 CX XXXXXXXXXX