Coursework Assessment Brief

 Assessment Task 

 

1. Introduction 

 

This assessment requires you to undertake practical database application development work 

to meet specified requirements and write a report that recommends improvements to the database and researches a topic relating to databases. 

 

This assessment will enable students to demonstrate in full or in part the learning outcomes identified in the unit descriptor. 

Page Break 

2. The Practical Development Work 

 

The practical development work is based on an online electronics shopping company where you work as a Database Analyst/Developer. The entity-relationship diagram and SQL script for creating and populating the database are provided on SOL.  

 

Part 1 - Retrieving Data using SQL 

 

You have been asked to write the following SQL queries for management information purposes. All students should complete questions a, b and c below and to achieve a higher grade, also complete question d. 

 

1. The company want to do a marketing campaign to new shoppers and all female shoppers. Retrieve the first name, surname, email address, gender, date joined, and the current age in years of shoppers who joined on or after 1st Jan 2020 and all female shoppers (irrespective of when they joined). Print date columns in the format DD-MM-YYYY and print ‘Not known’ for any NULL values.  Order results by gender and then by age (highest first). 

Refer to the SQLite Built-in Functions reference on SOL for how to calculate the age and format the dates. 

 

 

SELECT 

shopper_first_name, 

shopper_surname, 

shopper_email_address, 

IFNULL(gender,'Not Known') gender, 

strftime('%d-%m-%Y', date_joined) as date_joined, 

(strftime('%Y', 'now') - strftime('%Y', date_of_birth)) - (strftime('%m-%d', 'now') < strftime('%m-%d', date_of_birth)) as Age 

FROM 

shoppers 

WHERE 

gender = 'F' 

OR date_joined >= '2020-01-01' 

ORDER BY  

gender,Age DESC; 

 

 

 

 

 

 

2. The website requires a customer account history page which will accept the shopper id as a parameter entered by the user at run time. Write a query to retrieve the first name and surname for a specific shopper along with details of all the orders they’ve placed, displaying the order no, order date, product description, seller name, quantity ordered, price (with two decimal places and prefixed by a £ sign) and ordered product status. Print date columns in the format DD-MM-YYYY. Sort the results by order date showing the most recent order first. Test your query for shopper ids 10000 and 10019. 

 

 

select 

s.shopper_first_name, 

s.shopper_surname, 

so.order_id, 

STRFTIME('%d-%m-%Y', so.order_date) as order_date, 

p.product_description, 

s2.seller_name, 

op.quantity, 

Round(op.price,2) as price, 

so.order_status 

from 

shopper_orders so 

inner join shoppers s on 

s.shopper_id = so.shopper_id 

inner join ordered_products op ON 

op.order_id = so.order_id 

inner join products p on 

p.product_id = op.product_id 

inner join sellers s2 on 

op.seller_id = s2.seller_id 

where s.shopper_id = 10000 

 

 

 

 

 

Result Set  

 

 

 

 

 

3. The business relationship manager has asked you to write a summary report on the sellers and products that they have had sold since 1st June 2019. Display the seller account ref, seller name, product code, product description, number of orders, total quantity sold and total value of all sales (with two decimal places and prefixed by a £ sign) for each product they sell. You should also include products that a seller sells but has had no orders for and show any NULL values as 0. Sort results by seller name and then product description. 

 

select 

s2.seller_account_ref, 

 

s2.seller_name, 

 

p2.product_code, 

 

p2.product_description, 

 

Sum(IFNULL(op.quantity,0)) as quantity, 

 

Round(Sum(IFNULL(op.quantity,0)*IFNULL(op.price,0)),2) as Total_sales 

from 

products p2 

 

inner join product_sellers ps on p2.product_id = ps.product_id 

 

inner join sellers s2 on s2.seller_id = ps.seller_id  

 

LEFT join ordered_products op on op.product_id = ps.product_id and op.seller_id = ps.seller_id 

 

group by s2.seller_account_ref, 

s2.seller_name, 

p2.product_code, 

p2.product_description  

ORDER by s2.seller_account_ref,p2.product_description  

 

 

Result set