Not this also appears in the TMAO$2$ document as Question $6,$ figure $7.$

SpecialismStrings: VARCHAR$(120)$

SpecialismStrings: VARCHAR$(120)$ Emailstrings: VARCHAR$(100)$ CHECK$($VALUE LIKE $\text{'}\%$@$\%\text{'})$

ClientNumbers: CHAR$(5)$

TypeNames: VARCHAR $(120)$ ModelNumbers: VARCHAR $(120)$

ModelNumbers: VARCHAR $(120)$ SerialNumbers: VARCHAR $(100)$

MonetaryValue: $\frac{?}{NU}$ MERIC$(9,2)$ CHECK$($VALUE $0\mathrm{.}0)$

a$.$ Write SQL statements that answer each of the requests for data in the shedspace.txt

tables:

i$.$ Produce a list of the instructor names, rename the output column as experts.

$(2$ marks$)$

ii$.$ How many clients are there?

$(2$ marks$)$

iii. For each machine which has an insurance value of over $310\mathrm{.}00,$ give their

typename and serial number as well as the insurance value.

$(2$ marks$)$

iv$.$ How many machines are of the types 'Desktop Router' or 'Laser Cutter'?

$(3$ marks$)$

v$.$ List the different sponsors currently sponsoring clients $($Hint: NULL is not a

sponsor$).$

$(2$ marks$)$

vi$.$ How many tickets have been issued to clients for the event on $\text{'}2023-10-12\text{'}$ at

$\text{'}19$:$00\text{'}?$

$(3$ marks$)$

vii. How many instructors are in each department? Give the department name and the

number of instructors.

$(3$ marks$)$

viii. For each training event that has unissued tickets, list the date and time of the

event and the number of unissued tickets for that event. Produce the list in

increasing order of tickets available. NOTE: This can be satisfied using just the

Ticket table.

ix$.$ Give the family name and number of clients with the same family name where the

same family name occurs $3$ or more times.

$(3$ marks$)$

$x.$ For each machine, list the type of the machine, the model number, serial number,

and purchase date of the machine. The output should be sorted so that the oldest

machines are first.

$(4$ marks$)$

xi$.$ For each sponsored client who has tickets for more than one training event, list

their first name, family name and sponsor.

$(4$ marks$)$

xii. List any client who does not have a ticket for any training event, give their client

number, family and first names.

$(4$ marks$)$

xiii. Give the typename for any machine type that has not been the focus of a training

event.

$(4$ marks$)$

xiv. For each instructor and each training event they deliver, give their name, the title of

the event, and the number of tickets still available for each event they are

delivering.

$(5$ marks$)$