You should write queries to answer the following questions.

Use $\text{'}$AS$\text{'}$ to change the column name where appropriate.

Unless otherwise specified, you should use SUB$-$QUERIES and the quantifiers SOME, ANY, ALL, IN and NOT IN to answer the questions. Note that SQLite does not support SOME, ANY, ALL so you should use the equivalent min$/$max aggregate functions in the SUB$-$QUERIES.. You should also write each clause on a separate line.

select staffNo, salary

from Staff

where salary $>$

$($select avg$($salary$)$

from Staff

group by branchNo$)$;

It is recommended that you develop the answer to the question in SQLite outside of Canvas. Add each query to a file one at a time $($as the previous one is determined to be correct$),$ for example, printing$\_$query$\_$ts$4\mathrm{.}1.$sql$.$

In SQLite you can use the command $.$read to execute the SQL commands contained in a file, e$.$g$.$

sqlite$>.$read printing$\_$query$\_$ts$4\mathrm{.}1.$sql

if you turn on command echo it will also output the sql to make it easier for a tutor to help you

sqlite$>.$echo ON

Tasks

Task $4\mathrm{.}1$

List the names and phone numbers of all customers who have a rush job $($jobtype $=$R$)$

Task $4\mathrm{.}2.$

List all items with a price below the average price of an item.

Task $4\mathrm{.}3.$

Find the earliest po$\_$date for each vendor$\_$id in the pos table, include all the columns from the pos table and order by vendor$\_$id$.$ This cannot be done by a GROUP BY$.$ You will need to use a correlated query based on the vendor$\_$id$.$

Task $4\mathrm{.}4.$

Find the po$\_$items with quantity exceeding any $($i$.$e$.$ at least one$)$ quantity for job$\_$id $002.$ Exclude job $002$ from the final output.

Task $4\mathrm{.}5.$

Find all purchase orders $($POS$)$ for publishers with a credit code of C$.$ Remember KNOW THY SCHEMA: pos does not directly connected to publishers. Use SUB$-$QUERIES and the IN operator.

Task $4\mathrm{.}6.$

Find all publishers which have NOT had any bookjobs with pos from vendor$\_$id 'ABC'. Your answer needs to include publishers who have not had any bookjobs at all as well. Use NOT IN and IN$.$

HINT: which publishers have had POS with the vendor with vendor$\_$id 'ABC'?

Task $4\mathrm{.}7.$

Find all those po$\_$items with an item that has a price exceeding $50\mathrm{.}00$ and associated with one or more pos from vendor$\_$id ABC. Use the IN clause.