Continuous Assessment Assignment 2: Systems Implementation

Toy Store MySQL Database Assignment

Yr2 Database Systems Implementation CA2 * MySQL Database Assignment

TOY STORE – QUERY DESIGN

Question 1 (5 Marks)

List all order numbers, with the date the order was placed, where date is formatted like the following:-

Order ID        Order Date
4               2007, June 17
....            ....

SELECT order_id AS 'Order Id', order_date AS 'Order Date' FROM Orders ORDER BY order_date DESC;

for all orders, sort latest order first.

Question 2 (10 Marks)

List all account holders (account.last_name and account.firstname) with their age in years, where the customers name is concatentated as a string.
Sort in alphabetical order E.g.

Customer        Age
Dwight Gordon   34
....            ....

SELECT CONCAT(first_name, ' ',  last_name) AS 'Customer', 
TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS Age FROM account
ORDER BY Customer;

Question 3 (5 Marks)

List all account holders who are using either a yahoo.com or a gmail.com email account. Where all yahoo.com accounts are listed first, followed by all gmail.com accounts, e.g.

Customer        Email Domain
Madeline House  yahoo.com
....            ....

SELECT CONCAT(first_name, ' ',  last_name) AS Customer, 
RIGHT(email,9) AS Email_Domain 
FROM account WHERE EMAIL LIKE ('%yahoo.com') 
OR email LIKE ('%gmail.com') ORDER BY Email_Domain DESC;

Question 4 (10 Marks)

Calculate the percentage split of female and male account holders, e.g.

Gender      % Account Holders
G           52
M           48

SELECT gender AS Gender, 
FORMAT(COUNT(*) / (SELECT COUNT(*) FROM account) * 100,0) 
AS '% Account Holders' 
FROM ACCOUNT WHERE gender = 'M' OR gender = 'F' GROUP BY GENDER;

Question 5 (10 Marks)

Calculate how many orders are placed in each month of the year. You do not need to differentiate by year, i.e. count all the orders placed in January, all placed in February etc e.g.

Month       No. Orders Placed
1           67
2           31
3           45
4           66

SELECT DATE_FORMAT(order_date, '%m') AS 'Month',
COUNT(*) AS 'No. Orders Placed' 
FROM orders GROUP BY DATE_FORMAT(order_date, '%m') 
ORDER BY DATE_FORMAT(order_date, '%m');

Question 6 (10 Marks)

Calculate the average number of items placed across all orders, .e.g

Average Qty Ordered Per Order
12

SELECT (SELECT SUM(quantity) FROM lineitem) / 
(SELECT COUNT(DISTINCT order_id) FROM lineitem) 
AS 'Average Qty Ordered Per Order';

Question 7 (5 Marks)

List the most popular credit card to pay an order with. E.g

Most Popular Credit Card
Visa

SELECT cc_type AS 'Most Popular Credit Card' FROM orders 
GROUP BY cc_type ORDER BY COUNT(cc_type) DESC LIMIT 1;

Question 8 (10 Marks)

List the name of account holders, the order number , order date, and productId for all orders for any products in the Fate/Stay Night series.

Account Holder  OrderNo     Order Date      ProductID
Lara Daryl      147         2008-11-15      115
....            ....            ....            ....

SELECT CONCAT(first_name, " ", last_name),order_id, order_date FROM orders o
JOIN account a ON a.user_id = o.user_id
where order_id IN (SELECT order_id FROM lineitem WHERE item_id IN 
(SELECT item_id FROM item WHERE product_id IN 
(SELECT product_id FROM product WHERE series LIKE 'Fate/Stay Night')));

Question 9 (10 Marks)

Create a view that lists all products and their descriptions, sorted by genre, e.g.

Genre                   Product Name    
Action/Adventure        Akira Book 01 (Manga)
....                ....

Note that some products will appear more than once in the list as the product series may fall under many genres.

CREATE VIEW genre_product_view AS
SELECT genre AS 'Genre', name AS 'Product Name' FROM product p
JOIN xrefseriesgenre x on p.series = x.series ORDER BY genre;

Question 10 (10 Marks)

List each state (account.state_province) for which account holders are registered, with the total value of orders placed within that State. The total price should be rounded to the nearest integer.

State       Total Price of Orders within State  
CA          543
OH          610
....            ....

select sum(total_price) from orders where user_id in 
(select user_id from account where state_province = 'AK');

Question 11 (10 Marks)

List all product numbers, with their list price (selling price), unit price (cost price), and mark-up (% difference profit), where the mark-up between the unit price and what it’s sold for is greater than or equal to the average markup.

Product ID  List Price      Unit Price      Mark-up %
149         19.99           14.99           33
....            ....

SELECT product_id AS 'Product ID', 
listprice AS 'List Price', unitprice AS 'Unit Price',
ROUND(100 * (listprice - unitprice) / unitprice) AS 'Mark-up %' FROM item 
WHERE (100 * (listprice - unitprice) / unitprice) >= 
(SELECT AVG(100 * (listprice - unitprice) / unitprice) FROM item);

Question 12 (5 Marks)

List all order numbers, with their order date, and its ‘Expected Delivery Date’ which is calculated as the order-date plus 3 days.

Order No    Order Date      Expected Delivery Date
12          2008-08-05      2008-08-08
....            ....            ....

select order_date, date_add(order_date, interval 7 day) from orders;