School of Accounting, Information Systems and Supply Chain

Page 1 of 2
ISYS2038 Database Design and Development
Tutorial 6

Objectives:
• To get more familiarity with SQL scripting through MySQL Workbench and SQLyog
• To develop more experience with SQL queries, especially with joining tables.

Part A – Practising SQL scripts for basic queries
Using zoo database, write the SQL scripts for the following queries:
a) Display the details of animals that are kept at large cage size and the cage cost is less than
200.

b) Show the total quantity of food per day needed by each animal. Also include the animal ID,
animal name, sex, date of birth and colour.

c) Show the name, DOB, colour and sex of the animals that are taken care by the keeper
called Andrew Peter Hackett.

d) Show the total number of animals that are taken care by each keeper. Include the ID,
surname and given name of the keepers and show the results by descending order of the
surnames.

Part B – Individual Take Home Exercises (Total = 1 mark). Show your work to your tutor in
next class.
Using compshop database, write SQL scripts for the following queries.
a) What is the total revenue for each product code? (Hint: this will require you to join tables,
calculate quantity * price and make use of statistical function)

b) What is the average revenue for each postcode? (Hint: this will require you to join tables,
calculate quantity * price and make use of statistical function)

Part C – Using mobile database, write SQL scripts for the following queries.

1. List all the mobile calls with a duration between 100 and 400 minutes. Display the mobile id,
mobile brandname, phone number, plan name, call date, and call duration.

2. Adjust the previous query to include customer suburb and a concatenation of the customers’
name (for example, Vicki Quah). (Hint: you need to link to a third table.)

3. Adjust the previous query to group the query by customer name, brandname, phonenumber,
and plan name to show the total number of calls. Sort this query by the customer’s name.
Ignore the criterion of showing only call durations between 100 and 400 minutes.

Part D – Using cinema database, write SQL scripts for the following queries.

4. List Staff Number, resigned date, surname, home phone number, hour start for screening and
the screening date. List all males living in Essendon.

5. Show the screenings of Action movies made after 1980.

School of Accounting, Information Systems and Supply Chain

Page 2 of 2
6. Show the screenings of movies directed by Spielberg.

7. Write a query to show each session and include the name of the movie, the name of the
supervisor and the name of the camera operator. Sort the list by the title of the movie.

8. Show how many screenings have been scheduled for each cinema.