Write SQL queries for the given database.

Sailor (sid, sname, rating, age)

Boat (bid, bname, color)

Reserves (sid, bid, date)

i. Find the names of sailors who have reserved ‘red’ boat.

ii. Find the sailor (name) with highest rating.

iii. Find the average age of sailor.

iv. Find the age of youngest sailor for each rating level.

v. Add the new boat to database.

Assume any values for required attributes.

Consider the following tables:

Sailor

Boat

Reserves

1. SELECT sname FROM boat b1, reserves r1, sailor s1 WHERE b1.bid = r1.bid AND s1.sid = r1.sid AND b1.color = 'red' Output:

[Note: Above query uses concept of sub-query. Here we first find maximum rating of sailor in a sub-query and then use the result to gets the sailor’s name]

2.

  SELECT sname

  FROM `sailor`

  WHERE rating IN ( 

  SELECT max(rating) FROM `sailor`

  )

Output:

[Note: Above query uses concept of sub-query. Here we first find maximum rating of sailor in a sub-query and then use the result to gets the sailor’s name]

3.

  SELECT avg(age) AS 'Average Age' 
  FROM `sailor`

Output:

[Note: avg() is an aggregate function in sql. ‘AS’ keyword used to assign an alias to the column or a table]

4.

  SELECT min(age), rating 
  FROM `sailor` 
  GROUP BY rating

Output:

[Note: To get result according to each rating level, we use GROUP BY clause and then use min() aggregate function on age]

5.

  INSERT INTO `boat` (bid, bname, color) 
  VALUES(5, ‘Pecca Ship’, ‘Blue’)

Output:

Boat