The database schema and the sample dataset have been provided in the file juicd.sql. You should...

Transcribed Image Text:

The database schema and the sample dataset have been provided in the file juicd.sql. 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. Post to the Discussion Board in the unit Blackboard for guidance if there is any ambiguity in how to interpret the questions. Basic queries (8 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 tablemanages 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 Juice Cup table which stores the cup Idand 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 command will determine the price in cents of the JuiceCup with cupld 1000? (The table Juice contains the price in cents-per-ml of each juice). More complex queries (6 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. 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)? 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 Juice Cup 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). ● 1 Write a function juice CupCost (id INT) RETURNS DOUBLE that will be called with the id of a Juice Cup 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); +- ---+ I juiceCupCost (10) | ---+ 340 I 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 order number id. (You should use your previous functions, so make sure you test them well!) Create a view Customer PricedOrder 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) highest spending each Juicd runs a Customer Of The Month promotion, and sends coupons and other rewards to the customer month. Write a stored procedure list CofM () 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). The database schema and the sample dataset have been provided in the file juicd.sql. 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. Post to the Discussion Board in the unit Blackboard for guidance if there is any ambiguity in how to interpret the questions. Basic queries (8 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 tablemanages 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 Juice Cup table which stores the cup Idand 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 command will determine the price in cents of the JuiceCup with cupld 1000? (The table Juice contains the price in cents-per-ml of each juice). More complex queries (6 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. 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)? 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 Juice Cup 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). ● 1 Write a function juice CupCost (id INT) RETURNS DOUBLE that will be called with the id of a Juice Cup 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); +- ---+ I juiceCupCost (10) | ---+ 340 I 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 order number id. (You should use your previous functions, so make sure you test them well!) Create a view Customer PricedOrder 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) highest spending each Juicd runs a Customer Of The Month promotion, and sends coupons and other rewards to the customer month. Write a stored procedure list CofM () 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).

Answer rating: 100% (QA)

1 select from customer where jcardnum 1000 2 select count from customerorder 3 select ... View the full answer