#Lab Session 4 - Practical Assignment 1 Review
###By Mitchell Welch

###University of New England

---

## Reading
* Chapters 6 and 7 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/~cosc210/assignments/display_notes.php?assignment=2](http://turing.une.edu.au/~cosc210/assignments/display_notes.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

* 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 in the text.

#### Question 8

* Universal Quantification using the `EXISTS` clause.
* Have a close look at the examples in the text.
* You will end up with a query that contains multiple sub-queries.

---