The final part of the project is to pull together all the different aspects of S

Question

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(http://teaching.csse.uwa.edu.au/units/CITS1402/projects/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.

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 namecomplex.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 givenJuiceCup 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 Tutorialfor some more details on how to declare the function and return the values (ignore theDETERMINISTIC 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 particularJuiceCup. For example, Juice Cup 10 is a 400ml juice that is a 50-50 mixture of raspberry-pear, and it costs $3.40.

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.

Explanation / Answer

Here is the way you can get

SELECT orderId, juiceOrderCost(orderId) FROM customerOrder where orderId < 15;

Here is to get the cup cost:

select juiceCupCost(15);

Here is to get juice order cost

select juiceOrderCost(15);

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.