Task $6\mathrm{.}1$

List the customer id$,$ name, job id$,$ and job description for all bookjobs and order by cust$\_$id$.$ You are required to answer the question with a join, showing how it can be done using only a WHERE condition.

Remember: KNOW THY SCHEMA.

The required data comes from more than one table. Which tables are required $($where does customer name come from and where does job description come from$)?$ How are the tables linked? What is the foreign key?

Task $6\mathrm{.}2$

List the customer id$,$ name, job id$,$ and job description for all bookjobs and order by cust$\_$id$.$ You are required to answer the question with a join, showing how it can be done using only JOIN...ON$.$ The is the same a the previous question just using different join syntax.

Remember: KNOW THY SCHEMA.

The required data comes from more than one table. Which tables are required $($where does customer name come from and where does job description come from$)?$ How are the tables linked? What is the foreign key?

Task $6\mathrm{.}3$

List the job id$,$ job description, purchase order number, item id$,$ and item description for all entries in the po$\_$items table. Order the result by job$\_$id$,$ po$\_$id$,$ and then item$\_$id$.$

You are required to answer the question with a join, showing how it can be done using only WHERE clause.

Remember: KNOW THY SCHEMA.

The required data comes from more than one table. Which tables are required? How are the tables linked? What are the foreign keys?

HINT: this query requires information$/$linking across $4$ tables as you need to link from bookjobs to the items tables...KNOW THY SCHEMA.

Task $6\mathrm{.}4$

List the job id$,$ job description, purchase order number, item id$,$ and item description for all entries in the po$\_$items table. Order the result by job$\_$id$,$ po$\_$id$,$ and then item$\_$id$.$

You are required to answer the question with a join, showing how it can be done using only JOIN...ON clause. This is the same question as above but with a different join syntax

Remember: KNOW THY SCHEMA.

Task $6\mathrm{.}5.$

What is the total cost of ordered items for each purchase order id$?$ Answer the question using a NATURAL JOIN.

Remember: KNOW THY SCHEMA.

HINT: use a GROUP BY and rename the calculation column to 'Total Cost'

Task $6\mathrm{.}6$

For each publisher with more than $$2000$ in purchase orders, list the customer id$,$ name, credit code, and total of the orders. Use a WHERE clause to perform the join across the five required tables.

Task $6\mathrm{.}7$

For all purchase orders with quantity greater than the average purchase order quantity, list the publisher$$s name, city, credit code, and quantity. Order by the quantity. Use a JOIN...ON clause to join tables.

HINT: You will need to use a sub$-$query.

Task $6\mathrm{.}8$

List $($once$)$ the customer id and name of each publisher with a current job. Use which ever join method you like, but do not use a GROUP BY clause.