Tutorial MySQL Practical Exercise-Week 8

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.

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	7	45.0		22	101	10/10/98		101	Interlake	blue
29	Brutus	1	33.0		22	102	10/10/98		102	Interlake	red
31	Lubber	8	55.5		22	103	10/8/98		103	Clipper	green
32	Andy	8	25.5		22	104	10/7/98		104	Marine	red
58	Rusty	10	35.0		31	102	11/10/98
64	Horatio	7	35.0		31	103	11/6/98		Boats
71	Zorba	10	16.0		31	104	11/12/98
74	Horatio	9	35.0		64	101	9/5/98
85	Art	3	25.5		64	102	9/8/98
95	Bob	3	63.5		74	103	9/8/98
Sailors					Reserves

Enter the test data for the three tables as shown above, and formulate following SQL Queries.

Lecturers: The commands are JOIN queries. Explain to students the concepts of join and then help them formulate the queries.

Question 1

Find the sailor ID, boat name, and colour of those boats reserved on 9/8/98.

Question 2

Find the names of sailors who have reserved boat 103.

Question 3

Find the names of sailors who have reserved a red boat.