Victoria University: BCO2149 Database Systems SQL Assignment S1 2018

This document presents a solution to a Database Systems assignment, specifically focusing on SQL queries for a retail database. The assignment, likely for the BCO2149 course at Victoria University, involves creating and executing SQL queries to retrieve and manipulate data from various tables including Customer, Department, SalesRep, Orders, OrderLine, and Product. The solution provides SQL commands for a range of queries, such as selecting data based on department, customer balance, product descriptions, and order statuses. It also includes queries involving joins, grouping, and aggregate functions. The document also contains an ER diagram for the database. The database file is attached separately. This solution is intended to assist students in understanding and completing similar database assignments, and more resources can be found on Desklib.

DATABASE MANAGEMENT SYSTEM

Tables
Customer Table
\
Department Table
Order line Table
1

Orders Table
Product Table
Sales Rep Table (Silberschatz, Korth and Sudarshan, 2011)
2

ER Diagram
The Entity relationship diagram is shown below (Watson and Jones, 2013).
3

Queries
Query – 1
Output
SQL Command
SELECT tblSalesRep.SalesRepNo, tblSalesRep.SalesRepLastName,
tblSalesRep.SalesRepFName, tblDepartment.DepartName
FROM (tblDepartment INNER JOIN tblSalesRep ON tblDepartment.DepartNo =
tblSalesRep.DepartNo) INNER JOIN tblOrders ON tblSalesRep.SalesRepNo =
tblOrders.SalesRepNo
WHERE (((tblDepartment.DepartName)="Electrical"));
4

Query – 2
SQL Command
SELECT tblCustomer.CustNo, tblCustomer.F_Name, tblCustomer.LastName,
tblCustomer.Street, tblCustomer.Suburb, tblCustomer.PostCode, tblCustomer.Balance
FROM tblCustomer INNER JOIN tblOrders ON tblCustomer.CustNo = tblOrders.CustNo
WHERE (((tblCustomer.Balance) Between "600" And "2000"));
5

Query – 3
SQL Command
SELECT tblProduct.ProductNo, tblProduct.ProdDescription
FROM tblProduct INNER JOIN (tblOrders INNER JOIN tblOrderLine ON
tblOrders.OrderNo = tblOrderLine.OrderNo) ON tblProduct.ProductNo =
tblOrderLine.ProductNo
WHERE (((tblProduct.ProdDescription) Like "o*"))
ORDER BY tblProduct.ProdDescription DESC;
6

Query – 4
SQL Command
SELECT tblCustomer.F_Name, tblCustomer.LastName
FROM tblCustomer;
Query – 5
7

SQL Command
SELECT tblOrders.OrderNo, tblOrders.OrdDate, tblOrders.OrdStatus
FROM tblOrders
WHERE (((tblOrders.OrdStatus)="C"));
Query – 6
SQL Command
SELECT tblOrderLine.OrderNo, tblOrderLine.ProductNo
FROM tblProduct INNER JOIN (tblOrders INNER JOIN tblOrderLine ON
tblOrders.OrderNo = tblOrderLine.OrderNo) ON tblProduct.ProductNo =
tblOrderLine.ProductNo;
8

Query – 7
SQL Command
SELECT tblCustomer.CustNo, tblOrders.OrderNo, tblOrders.OrdDate, tblOrders.OrdStatus
FROM tblCustomer INNER JOIN tblOrders ON tblCustomer.CustNo = tblOrders.CustNo
WHERE (((tblCustomer.CustNo)="1040"));
Query – 8
9

SQL Command
SELECT tblCustomer.F_Name, tblCustomer.LastName, tblSalesRep.SalesRepNo,
tblOrders.OrderNo, tblOrders.OrdDate, tblOrders.OrdStatus, tblProduct.ProductNo,
tblProduct.ProdDescription
FROM tblSalesRep INNER JOIN (tblProduct INNER JOIN ((tblCustomer INNER JOIN
tblOrders ON tblCustomer.CustNo = tblOrders.CustNo) INNER JOIN tblOrderLine ON
tblOrders.OrderNo = tblOrderLine.OrderNo) ON tblProduct.ProductNo =
tblOrderLine.ProductNo) ON tblSalesRep.SalesRepNo = tblOrders.SalesRepNo;
Query – 9
10

SQL Command
SELECT tblOrders.OrderNo, tblOrders.OrderNo, tblOrders.OrdDate, tblCustomer.F_Name
FROM tblCustomer INNER JOIN tblOrders ON tblCustomer.CustNo = tblOrders.CustNo;
Query – 10
SQL Command
SELECT tblOrders.OrderNo, Sum(tblOrderLine.Qtyonhand) AS SumOfQtyonhand,
Sum(tblOrderLine.PurchasePrice) AS SumOfPurchasePrice
FROM tblProduct INNER JOIN (tblOrders INNER JOIN tblOrderLine ON
tblOrders.OrderNo = tblOrderLine.OrderNo) ON tblProduct.ProductNo =
tblOrderLine.ProductNo
GROUP BY tblOrders.OrderNo, [Total]=[tblOrderLine]![Qtyonhand]*[tblOrderLine]!
[PurchasePrice];
11