The database schema is available in the file juicd.sql and a sample dataset is i

Question

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.

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 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 JuiceCup table which stores thecupId 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).

lineMgr upervisee INT upervisor INT man eld I eld INT address VARCHAR mpld INT Indexes Indexes phone CHAR Indexes ustome jCardNum INT mpld INT name VARCHAR address TEXT email VARCHAR(128) Indexes Indexes eld I mpld INT Indexes CustomerOrder orderlD customerlD INT employeelD INT(11 has eCu upld INT date quantity INT Indexes Indexes hasNonJuice ce cupld INT quantity INT Indexes size INT Indexes NonJuice comprises ce jld INT(1 pName VARCHAR(128) ame VARCHA perltem DOUBLE percentage Indexes perMI DOUBLE Indexes Indexes

Explanation / Answer

1) select * from customer where jcardnum = '1000';

2) select count(*) from customerorder;

3) select a.name ,a.address from employee a,manages b where a.jEmpid = b.jEmpid;

4) select a.name ,a.address from employee a,worksat b where b.percentage = '100' and a.jEmpid = b.jEmpid;

5) select a.name ,a.address , b.percentage from employee a,worksat b where a.jEmpid = b.jEmpid;

6) select MIN(jPoints) as minpoint, MAX(jPoints) as maxpoint ,AVG(jPoints) as avgpoint from (select jPoints from customer);

7) select supervisor,supervisee from linemgr;

8) select a.address ,count(b.orderid) from outlet a,customerorder b,worksat c where a.jStoreid = c.jStoreid;

9) select a.jname,b.percentage from juice a,comprises b where a.jid = b.juiceid and b.cupid = '1000';

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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