0
16kviews
Write SQL queries for the given database.

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.

1 Answer
1
444views

Consider the following tables:

Sailor

sid sname rating age
1 Alan 8 26
2 Arina 7 25
3 Roone 9 30
4 Jeet 9 22
5 Rakesh 6 40

Boat

bid bname color
1 Alpha Ship White
2 Beta Ship Black
3 Gamma Ship Yellow
4 Delta Ship Red

Reserves

sid bid date
1 3 24/01/2015
2 4 25/01/2015
3 4 30/12/2014
  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:

Arina
Roone

[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:

Roone
Jeet

[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:

Average Age
28.6000

[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:

min(age) rating
40 6
25 7
22 9

[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

bid bname color
1 Alpha Ship White
2 Beta Ship Black
3 Gamma Ship Yellow
4 Delta Ship Red
5 Pecca Ship Blue
Please log in to add an answer.