Workshop $$ Week $8$ MySQL Practical Exercise

Exercise $1$

This exercise is similar to week $5.$ Students at this stage should be illustrated how to create tables, alter tables, and write complex SQL queries.

Note: Use SQL commands to create the database, tables, records, and Queries.

$1.$ Create the database sbrcommands and the following tables sailors, boats, and reserves which are reproduced as follows:

sailors$($sid: VARCHAR$(2)$ PK$,$ sname: VARCHAR$(7),$ rating: SMALLINT, age: DOUBLE

boats$($bid: VARCHAR$(3)$ PK$,$ bname: VARCHAR $(10),$ colour: VARCHAR$(6)$

reserves$($sid: VARCHAR$(2)$ FK$,$ bid: VARCHAR$(3)$ FK$,$ day: DATE$)$

sid sname rating age sid bid Day bid bname colour

$22$ Dustin $745\mathrm{.}02210110/10/98101$ Interlake blue

$29$ Brutus $133\mathrm{.}02210210/10/98102$ Interlake red

$31$ Lubber $855\mathrm{.}52210310/8/98103$ Clipper green

$32$ Andy $825\mathrm{.}52210410/7/98104$ Marine red

$58$ Rusty $1035\mathrm{.}03110211/10/98$

$64$ Horatio $735\mathrm{.}03110311/6/98$ Boats

$71$ Zorba $1016\mathrm{.}03110411/12/98$

$74$ Horatio $935\mathrm{.}0641019/5/98$

$85$ Art $325\mathrm{.}5641029/8/98$

$95$ Bob $363\mathrm{.}5741039/8/98$

Sailors Reserves

$2.$ Enter the test data for the three tables as shown above.

$3.$ Using DISTINCT.

$4.$ Formulate SQL query to find all information of sailors who have reserved boat number $101.$

$5.$ Formulate SQL query to find the names of sailors who have reserved a red boat, and list in the order of age.

$6.$ Find the names of sailors who have reserved at least one boat.

$7.$ Count the number of different sailor names.

$8.$ Calculate the average age of all sailors.

$9.$ Find the ages of sailors whose name begins and ends with B and has at least $3$ characters.

$10.$ Find the sailors with the highest rating.

$11.$ Find the name of the oldest sailor.

$12.$ Find name of the sailors whose rating is greater than equal to $8.$

$13.$ Find boat id$,$ name and color of the boats whose color is not blue and red.