Data base schema
Customer Table
SQL
CREATE TABLE `customer` (
`CustNo` int(11) NOT NULL,
`F_Name` varchar(500) NOT NULL,
`LastName` varchar(500) NOT NULL,
`Street` varchar(500) NOT NULL,
`Suburb` varchar(500) NOT NULL,
`PostCode` varchar(500) NOT NULL,
`Balance` float NOT NULL
);
Department Table
SQL :
CREATE TABLE `department` (
`DepartNo` int(11) NOT NULL,
`DepartName` varchar(500) NOT NULL
);
Orderline Table
CREATE TABLE `orderline` (
`OrderNo` int(11) NOT NULL,
`ProductNo` varchar(500) NOT NULL,
`QtyPurchased` int(11) NOT NULL,
`PurchasePrice` float NOT NULL
);
Orders Table
CREATE TABLE `orders` (
`OrderNo` int(11) NOT NULL,
`CustNo` int(11) NOT NULL,
`OrdDate` date NOT NULL,
`SalesRepNo` varchar(500) NOT NULL,
`OrdStatus` varchar(500) NOT NULL
);
Products Table
SQL
CREATE TABLE `product` (
`ProductNo` varchar(500) NOT NULL,
`ProdDescription` varchar(500) NOT NULL,
`Qtyonhand` int(11) NOT NULL,
`UnitPrice` float NOT NULL
);
Salesrep
SQL
CREATE TABLE `salesrep` (
`SalesRepNo` varchar(500) NOT NULL,
`SalesRepLastName` varchar(500) NOT NULL,
`SalesRepFName` varchar(500) NOT NULL,
`DepartNo` int(11) NOT NULL
);
Answers
1.
SELECT salesreplastname,salesrepfname FROM salesrep, department WHERE department.DepartNo=1
and Salesrep.DepartNo = department.DepartNo;
2. SELECT * FROM customer where balance >= 600 and balance <=2000;
3.
SELECT ProductNo,ProdDescription FROM product where proddescription like '%o%' order by
ProdDescription DESC
4.
SELECT F_Name,LastName FROM customer where custNo in(select DISTINCT(custno) from orde
rs);
5.
SELECT OrderNo,OrdDate,OrdStatus FROM orders WHERE OrdStatus='C';
6.
SELECT ProductNo FROM Product WHERE ProductNo not IN(select DISTINCT(ProductNo) from o
rderline);
7.
SELECT count(*) FROM orders WHERE CustNo = 1040;
8.
SELECT concat(customer.F_Name, '
',customer.LastName) as CustomerName,concat( salesrep.SalesRepFName, salesrep.SalesRep
LastName) as Sales_Representative,orders.OrderNo,orders.OrdDate,product.ProductNo,prod
uct.ProdDescription FROM orderline,customer,orders,product,salesrep where orderline.Pr
oductNo = product.ProductNo and orders.OrderNo = orderline.OrderNo and orders.SalesRep
No = salesrep.SalesRepNo and orders.CustNo = customer.CustNo;
9.
SELECT CustNo FROM customer WHERE CustNo not in (SELECT DISTINCT(CustNo) from orders)
10.
SELECT orderno, round(sum(qtypurchased*purchaseprice),2) as 'Total
Amount' from orderline group by orderno;
11.
SELECT product.proddescription from product, orderline where orderline.QtyPurchased =
(select max(qtypurchased) from orderline) and orderline.ProductNo = product.ProductNo;
12.
SELECT departname,count(*) as 'Number of
Employees' FROM salesrep,department WHERE department.DepartNo = salesrep.DepartNo GROU
P by salesrep.DepartNo;
13.
SELECT f_name,LastName FROM customer WHERE Balance = (SELECT max(Balance) from custome
r);
14. SELECT count(*)as ord_qty, CustNo FROM orders GROUP by CustNo having count(*)>3;
15. SELECT count(*),OrdStatus FROM orders GROUP by ordstatus order by OrdStatus;
16.
SELECT count(*) as total_count, salesrep.SalesRepFName,salesrep.SalesRepLastName FROM
orders,salesrep where salesrep.SalesRepNo = orders.SalesRepNo group by orders.salesrep
no having count(*)>=3;
17.
SELECT orderno,orddate FROM orders, salesrep where not (salesrep.SalesRepFName = 'kare
n' and salesrep.SalesRepLastName = 'salt' and salesrep.SalesRepNo = orders.SalesRepNo)
;
18.
SELECT max(unitprice) from product where unitprice not in (SELECT max(unitprice) from
product);
19.
SELECT concat (F_Name,'
',lastname) as Customer_Name FROM customer, orders where customer.CustNo = orders.Cust
No and orders.OrderNo = 12640;
20.
SELECT count(*) FROM orderline where ProductNo = 'DP03';
21.
SELECT * FROM `product` WHERE ProductNo not in (SELECT DISTINCT(ProductNo) from orderl
ine);
22. SELECT DISTINCT concat(F_Name,'
',Lastname) FROM customer, orderline, orders, product where product.ProductNo = orderl
ine.ProductNo and ProdDescription = 'Walkman' and orderline.OrderNo = orders.OrderNo a
nd orders.CustNo = customer.CustNo;
23.
SELECT * from product where qtyonhand>15 and unitprice>120;
24.
SELECT salesrep.salesreplastname , salesrep.SalesRepFName FROM orders, salesre
p where OrderNo = 12800 and orders.SalesRepNo = salesrep.SalesRepNo;
25.
SELECT COUNT(*) as qty FROM orders WHERE SalesRepNo='s5' AND OrdDate>='2002-01-01' and
OrdDate <='2002-12-31';
