Juicd (part 2)

The final part of the project is to pull together all the different aspects of SQL that we have learned so far and apply them to a made-up database for Juicd. These will primarily be marked on correctness - whether they do the right thing or not - but I may check some of the code if it is particularly slow or similar.

The database schema is available in the file juicd.sql and a sample dataset is in data.txt. You should load the schema and the data into your own server, and then use MySQL Workbench to get the structure diagram for the schema.

A faster way to insert the data is to download juicdump3.sql and upload this file to your server. NOTE: This is the new dataset with data for about six months.

Post to help1402 for guidance if there is any ambiguity in how to interpret the questions (but please do not post actual code unless you have okayed it with me first).

The remaining questions will be added to this file in the next few days.

Basic queries (6 marks)

Place all 10 queries into a single text file with the name basic.sql so that they can be automatically run by a script.

These queries are all single SQL queries that require nothing more than joins and elementary use of the aggregate functions with GROUP BY. All students should be able to write queries at this level for the exam.

• What SQL query will produce the row of customer information for the customer with Juicd card number 1000?
• What SQL query will determine the total number of orders in the database (the output should be a scalar)?
• What SQL query will list the names and addresses of the outlet managers? (The table manages contains information about managers.)
• What SQL query will list the names and addresses of the employees who work full-time at a single Juicd store?
• What SQL query will list the names, addresses and total working percentage of all Juicd employees (so someone who works 50% at one store and 25% at another store works a total of 75% of their time). Only include employees that are currently working (i.e. appear in the worksAt table).
• What query will produce a table listing the minimum, maximum and average number of Juicd points outstanding?
• What SQL query will list each line manager (by name) together with the number of employees they supervise? (supervision is in the lineMgr table)?

• What SQL query will list the address of each outlet, together with the total number of orders that they have served?

  Each cup of juice is represented by a row in the JuiceCup table which stores the cupId and the size of the cup. The actual juices that make up each cup of juice are stored in the table comprises which lists the juices and their percentages. For example, SELECT * FROM comprises WHERE cupid = 1000 tells us that this cup of juice is 40% Juice #7, 40% of Juice #8 and 20 % of Juice #20;

• What SQL query will list the actual juices by name and their percentages for this particular juice cup (i.e. the juice with cupId 1000)?

• What SQL command will determine the price in cents of the JuiceCup with cupId 1000? (The table Juice contains the price in cents-per-ml of each juice).

More complex queries (8 marks)

These queries are more complex than the basic SQL queries and may involve more exotic join conditions, such as outer joins, subqueries and simple stored procedures or functions.

Place all queries and function definitions into a single text file with the name complex.sql so that they can be automatically run by a script.

• What SQL query will list the total number of customer orders per outlet (listed by outlet address) per day of the week? (In other words, Outlet 1s orders on Mondays, Tuesdays etc.)
• What SQL query will list the cup ids of all the JuiceCup that use more than three different ingredients? (You may assume that each row of comprises for a given JuiceCup is actually a different ingredient.)
• What single SQL query will list the names and addresses of all Juicd employees who are not working at any of the outlets at the moment?
• What SQL query will list all the customer orders (by orderId) that consist only of juices (no non-juice items)?

A stored function is similar to a stored procedure, except that it returns a value that can be used in a SQL statement just like the other SQL functions. See this Stored Function Tutorial for some more details on how to declare the function and return the values (ignore the DETERMINISTIC keyword).

• Write a function juiceCupCost(id INT) RETURNS DOUBLE that will be called with the id of a JuiceCup and then return the cost (in cents) of that particular JuiceCup. For example, Juice Cup 10 is a 400ml juice that is a 50-50 mixture of raspberry-pear, and it costs $3.40.

mysql> select juiceCupCost(10);
+------------------+
| juiceCupCost(10) |
+------------------+
|              340 |
+------------------+

• Write a function juiceOrderCost(id INT) RETURNS DOUBLE that returns the total price of the juice-component of an order. Obviously this function should use your previous function for the cost of each individual JuiceCup.

Additional functionality (6 marks)

Place all queries and view/procedure definitions into a single text file with the name additional.sql so that they can be automatically run by a script.

• Write a stored function totalOrderCost(id INT) RETURNS DOUBLE that returns the total cost (in cents) of the order with ordernumber id. (You should use your previous functions, so make sure you test them well!)

• Create a view CustomerPricedOrder with columns date DATE,customerId INT, orderId INT and orderCost DOUBLE that provides a more accessible way for the DB user to run queries regarding customer orders. (The columns have their natural meanings)

• Juicd runs a Customer Of The Month promotion, and sends coupons and other rewards to the highest spending customer each month. Write a stored procedure listCofM() that creates a table customerOfMonth(year INT, month TEXT, COfM INT, cOfMemail TEXT) that lists for each year and month (given as "January", "February" etc) the customer number and email of the biggest spender for that month (you may ignore the possibility that there may be joint winners).

Written with StackEdit.