Question

INTRO TO DATABASE

Consider the Sailors-Boats-Reserves database described below.

Sailors(sid: integer, sname: string, rating: integer, age: real)

Boats(bid: integer, bname: string, color: string)

Reserves(sid: integer, bid: integer, day: date)

Write each of the following queries in SQL.

1. Find the names and ages of sailors who have reserved at least two boats.

2. For each boat reserved by at least 2 distinct sailors, find the boat id and the average age of sailors who reserved it.

Answer 1

Answers:

1. Find the names and ages of sailors who have reserved at least two boats.

select sname,age from Sailors where sid in (select sid from Reserves  group by sid  having count(*)>=2);

2. For each boat reserved by atleast 2 distinct sailors, fine the boat id and the average age of sailors who reserved it.

select r.bid, avg(s.age) from Sailors s, reserves r
where s.sid = r.sid group by r.bid having count(r.sid)>=2;

Summary:

Below are some create table statement to your requirement and insert queries which i used to test above questions.

You can use them if you have not created tables and data in your database. Note these tables are created in oracle db. This might give error if you are using some other db.

But the queries in answer works for all db clients. You can use them in any db.

create table Sailors
(sid number primary key, sname varchar(50), rating number, age number);

create table Boats(
bid number primary key, bname varchar(50),  color varchar(50));


create table Reserves(sid number, bid  number, day date);

insert into Sailors values (1,'Bhaskar',5,26);
insert into Sailors values (2,'Guru',5,26);
insert into Sailors values (3,'Hema',5,27);
insert into Sailors values (4,'Maruti',5,30);
insert into Sailors values (5,'Faruk',5,25);
insert into Sailors values (6,'Sachin',5,26);

insert into Boats values (1,'abc','red');
insert into Boats values (2,'jhh','grey');
insert into Boats values (3,'opo','green');
insert into Boats values (4,'vovo','pink');
insert into Boats values (5,'momo','black');



insert into Reserves values (1,2,to_date('24-09-2020','dd-mm-yyyy'));
insert into Reserves values (3,2,to_date('22-09-2020','dd-mm-yyyy'));
insert into Reserves values (5,4,to_date('13-09-2020','dd-mm-yyyy'));
insert into Reserves values (4,4,to_date('22-09-2020','dd-mm-yyyy'));
insert into Reserves values (2,3,to_date('16-09-2020','dd-mm-yyyy'));
insert into Reserves values (2,5,to_date('18-09-2020','dd-mm-yyyy'));
insert into Reserves values (1,5,to_date('01-09-2020','dd-mm-yyyy'));
insert into Reserves values (5,3,to_date('09-09-2020','dd-mm-yyyy'));
insert into Reserves values (6,2,to_date('23-09-2020','dd-mm-yyyy'));

Output for answer 1:

Output for answer 2:

I know there is lot of information included in this answer, this is all just for your understanding. You may take the point 1 and 2 in Answer section for your requirement.

Hope this will help you. Enjoy!!!