Prelim 2018 Paper II
SQL - shoeStoreDB
1.1 Display all clients from region 2. Sort your results with the last_name in
descending order. [3]
1.1 Display all clients from region 2. Sort your results with the last_name in
descending order. [3]
SELECT * from tblClients
WHERE region = 2
ORDER BY last_name DESC;
1.2 Display all clients born in the year 2000 that also have an active account. [3]
1.2 Display all clients born in the year 2000 that also have an active account. [3]
SELECT * from tblClients
WHERE YEAR(birth_date) = 2000 AND active = true;
1.3 Display the initials of all clients in the database as Initials. [3]
1.3 Display the initials of all clients in the database as Initials. [3]
SELECT LEFT(first_name,1) & LEFT(last_name,1) as Initials
FROM tblClients;
1.4 Display all sneakers that have the colour Blue on them. [4]
1.4 Display all sneakers that have the colour Blue on them. [4]
SELECT * FROM tblSneakers
WHERE name LIKE “*Blue*";
1.5 Display the average sneaker price in the store as AverageSneakerPrice,
rounded off to zero decimal places. [3]
1.5 Display the average sneaker price in the store as AverageSneakerPrice,
rounded off to zero decimal places. [3]
SELECT ROUND(AVG(price)) AS AverageSneakerPrice FROM tblSneakers
1.6 Show the number of orders dispatched per region during the month of
December. [5]
1.6 Show the number of orders dispatched per region during the month of
December. [5]
SELECT region, COUNT(dispatched) FROM tblClients, tblOrders
WHERE dispatched = true AND MONTH(date) = 12
AND tblClients.id = tblOrders.clientID
GROUP BY region;
1.7 Display the id, name and price of all sneakers that did not sell any units in
the database. [5]
1.7 Display the id, name and price of all sneakers that did not sell any units in
the database. [5]
SELECT tblSneakers.id, name, price
FROM tblSneakers
WHERE tblSneakers.id NOT IN
(SELECT tblSneakers.id FROM tblSneakers, tblOrderItems, tblOrders
WHERE tblSneakers.id = tblOrderItems.sneakerID;