DBMS Lab Cycle 4 (sailor,boat,reserve)

Create the following tables and execute the queries given below

sailor

boat

reserve

CREATE TABLE sailors(
sid NUMBER(3),
sname varchar2(100),
rating NUMBER(2),
age NUMBER(3),
PRIMARY KEY(sid)
);

INSERT INTO sailors
VALUES(22,'Dustin',7,45);

INSERT INTO sailors
VALUES(29,'Brutas',1,33);

INSERT INTO sailors
VALUES(31,'Luber',8,55);

INSERT INTO sailors
VALUES(32,'Andy',8,25);

INSERT INTO sailors
VALUES(58,'Rusty',10,35);

INSERT INTO sailors
VALUES(64,'Horatio',9,35);

INSERT INTO sailors
VALUES(71,'Zorba',10,16);

INSERT INTO sailors
VALUES(74,'Horatio',9,35);

INSERT INTO sailors
VALUES(85,'Art',3,25.5);

INSERT INTO sailors
VALUES(95,'Bob',3,63.5);

CREATE TABLE boats(
bid NUMBER(3),
bname VARCHAR2(50),
color VARCHAR2(25),
PRIMARY KEY(bid)
);

INSERT INTO boats
VALUES(101,'Interlake','Blue');

INSERT INTO boats
VALUES(102,'Interlake','Red');

INSERT INTO boats
VALUES(103,'Clipper','Green');

INSERT INTO boats
VALUES(104,'Marine','Red');

CREATE TABLE reserves(
sid NUMBER(3) REFERENCES sailors(sid),
bid NUMBER(3) REFERENCES boats(bid),
booking_day DATE
);

INSERT INTO reserves
VALUES(22,101,'10-October-1998');

INSERT INTO reserves
VALUES(22,102,'10-October-1998');

INSERT INTO reserves
VALUES(22,103,'10-August-1998');

INSERT INTO reserves
VALUES(22,104,'10-July-1998');

INSERT INTO reserves
VALUES(31,102,'11-October-1998');

INSERT INTO reserves
VALUES(31,103,'11-June-1998');

INSERT INTO reserves
VALUES(31,104,'11-December-1998');

INSERT INTO reserves
VALUES(64,101,'09-May-1998');

INSERT INTO reserves
VALUES(64,102,'09-August-1998');

INSERT INTO reserves
VALUES(74,103,'09-August-1998');

--> Find the names and ages of all sailors.

SELECT sname,age FROM sailors;

--> Find all information of sailors who have reserved boat number 101.

SELECT * FROM sailors WHERE sid IN (SELECT sid FROM reserves WHERE bid=101);

--> Find all sailors with rating above 7.

SELECT sid,sname FROM sailors WHERE rating > 7;

--> Find the names of sailors who have reserved boat no 103.

SELECT sname FROM sailors WHERE sid IN (SELECT sid from reserves WHERE bid=103);

--> Find the names of sailors who have reserved a red boat, and list in the order of age.

SELECT sname,age FROM sailors WHERE sid IN (SELECT sid FROM reserves WHERE bid in (SELECT bid FROM boats WHERE color = 'Red')) ORDER BY age;

--> Find the names of sailors who have reserved either a red or green boat.

SELECT sname FROM sailors WHERE sid IN (SELECT sid FROM reserves WHERE bid in (SELECT bid FROM boats WHERE color = 'Red' OR color = 'Green'));

--> Find the colors of boats reserved by “Luber”.

SELECT  UNIQUE color FROM boats WHERE  bid IN ((SELECT bid FROM reserves WHERE sid IN (SELECT sid FROM sailors WHERE sname LIKE 'Luber')));

--> Find the names of sailors who have reserved both red and green boats.

SELECT sid,sname FROM sailors WHERE sid IN(SELECT sid FROM reserves WHERE bid IN (SELECT bid FROM boats WHERE color IN ('Red','Green')));

--> Find the names of sailors who have reserved at least one boat.

SELECT sname,sid FROM sailors WHERE sid IN (SELECT UNIQUE sid FROM reserves WHERE sid IN (SELECT sid FROM sailors));

--> Find the ids and names of sailors who have reserved two different boats on the same day.

SELECT DISTINCT s.sid, s.sname
FROM sailors s, Reserves r1, Reserves r2
WHERE s.sid = r1.sid AND s.sid = r2.sid
AND r1.booking_day = r2.booking_day AND r1.bid <> r2.bid;

--> Find the name and the age of the youngest sailor.

SELECT sname,age FROM sailors WHERE age =( SELECT min(age) FROM sailors);

--> Find the names and ratings of sailor whose rating is better than some sailor called Horatio.

SELECT s1.sid,s1.sname,s1.rating FROM sailors s1 WHERE s1.rating > ANY (SELECT s2.rating FROM sailors s2 where s2.sname = 'Horatio');

--> Find the names of sailors who have reserved all boats.

SELECT sname FROM sailors s
WHERE NOT EXISTS (SELECT * FROM boats b WHERE NOT EXISTS (SELECT *FROM reserves r WHERE r.sid=s.sid AND r.bid=b.bid));

--> Count the number of different sailor names.

SELECT sname,COUNT(sname) as count FROM sailors group by sname;

--> Calculate the average age of all sailors.

SELECT avg(age) as "Average sailor age" FROM sailors;

--> Find the average age of sailors for each rating level.

SELECT AVG(age),rating FROM sailors GROUP BY rating;

--> Find the average age of sailors for each rating level that has at least two sailors.

SELECT AVG(age),rating FROM sailors GROUP BY rating HAVING COUNT(*) >=2;