Lab Session 7 - Practical Assignment 1 Review

By Mitchell Welch

University of New England

Reading

• Chapters 4 and 5 from Fundamentals of Database Systems by Elmazri and Navathe

Summary

• Housekeeping
• Practical Assignment 1 Hints

Housekeeping

• Please use todays practical exercise to review and complete practical assignment 1.
• The assignment description is available from: http://turing.une.edu.au/~comp389/assignments/display_assignment.php?assignment=2
• Please send through any questions via email or on the discussion boards.
• Make sure that you submit your assignment via turing.une.edu.au.

Practical Assignment 1 Hints

Question 1

• This one is fairly simple, just make sure that you join all of the table correctly via the foreign key fields. Otherwise you will end up with spurious records!

Question 2

• Again make sure that your tables are all joined via the foreign keys.
• Here you should be using the LIKE operator with a suitable expression to return records with titles that match the pattern.

Question 3

• In this one you are performing some mathematical operations on the retail_prace column (multiplying it be 1.25)

Question 4

• Some more joins! Make sure you key those foreign keys joined correctly.

Question 5

• Use the sum aggregate function along with some maths on the retail and cost fields.
• Remember to name your columns correctly.

Question 6

• Lots of joins! Double check to make sure there are no spurious tuples.
• You will need to subtract the sum of the cost from the sum of the retail.
• But make sure you GROUP BY the appropriate fields!

Question 7

• Nested queries with using the EXISTS clause
• Have a close look at the examples on page 121 of the text.

Question 8

• Universal Quantification using the EXISTS clause.
• Have a close look at the examples on page 121 of the text.
• You will end up with a query that contains multiple sub-queries.