SET DEFINE OFF; Good morning. Can someone please help with the solution to this

Question

SET DEFINE OFF;

Good morning. Can someone please help with the solution to this question? Thanks in advance.

--Delete preexisting tables

DROP TABLE boats cascade constraints;

DROP TABLE reserves cascade constraints;

DROP TABLE sailors cascade constraints;

--remove any ghost tables

PURGE RECYCLEBIN;

--create tables

CREATE TABLE boats( bid integer,

bname char(20),

color char(20),

PRIMARY KEY (bid));

CREATE TABLE sailors( sid integer,

sname char(30),

rating number,

age number,

PRIMARY KEY (sid));

CREATE TABLE reserves( sid integer,

bid integer,

day date,

PRIMARY KEY (sid,bid,day),

CONSTRAINT FK_BID_RES FOREIGN KEY (bid) REFERENCES boats(bid),

CONSTRAINT FK_SID_RES FOREIGN KEY (sid) REFERENCES sailors(sid));

--insert data into tables

--boats

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');

commit;

--sailors

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

INSERT INTO sailors VALUES (29, 'Brutus', 1, '33.0');

INSERT INTO sailors VALUES (31, 'Lubber', 8, '55.5');

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

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

INSERT INTO sailors VALUES (64, 'Horatio', 7, '35.0');

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

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

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

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

commit;

--reserves

INSERT INTO reserves VALUES (22, 101, TO_DATE('1998-10-10','YYYY-MM-DD'));

INSERT INTO reserves VALUES (22, 102, TO_DATE('1998-10-10','YYYY-MM-DD'));

INSERT INTO reserves VALUES (22, 103, TO_DATE('1998-10-08','YYYY-MM-DD'));

INSERT INTO reserves VALUES (22, 104, TO_DATE('1998-10-07','YYYY-MM-DD'));

INSERT INTO reserves VALUES (31, 102, TO_DATE('1998-11-10','YYYY-MM-DD'));

INSERT INTO reserves VALUES (31, 103, TO_DATE('1998-11-06','YYYY-MM-DD'));

INSERT INTO reserves VALUES (31, 104, TO_DATE('1998-11-12','YYYY-MM-DD'));

INSERT INTO reserves VALUES (64, 101, TO_DATE('1998-09-05','YYYY-MM-DD'));

INSERT INTO reserves VALUES (64, 102, TO_DATE('1998-09-08','YYYY-MM-DD'));

INSERT INTO reserves VALUES (74, 103, TO_DATE('1998-09-08','YYYY-MM-DD'));

commit;

Using the above Sailorsschema (sailors.sql)–run this script file if you have not already done so and answer the questionsthat follow:

Write and run the following queries.

Do not show duplicates (50points)

1.Print the names of sailors who have reserved at least two boats

2.Print the names of sailors whose rating is within (inclusive) 5 and 8 and have reserved more than two boats

3.Find the average age of sailors who have a rating greater than 7 and have reserved a boat named Clipper or Interlake

4.Find the age of the oldest sailor for each rating level that has reserved a redboat

5.Find the sailors whose rating is worse than some sailor named Andy

Explanation / Answer

All Queries have been executed and tested on mysql.

1. Aleast 2 imples >=2.

Query:

SELECT SNAME FROM sailors WHERE sid IN (SELECT sid FROM reserves GROUP BY sid HAVING COUNT(*) >= 2);

Output:

SNAME
Dustin
Lubber
Horatio

2.

Query:

SELECT SNAME FROM sailors WHERE rating >=5 and rating <=8 AND sid IN (SELECT sid FROM reserves GROUP BY sid HAVING COUNT(*) > 2);

Output:

SNAME
Dustin
Lubber

3. Only rating level 8 and 9 (above level 7) have booked Clipper or Interlake.

Query:

SELECT AVG(rating) FROM sailors WHERE rating > 7 AND sid IN (SELECT sid FROM reserves where bid IN (SELECT bid from boats where bname='Interlake' OR bname = 'Clipper'));

Output:

AVG(rating);
8.5000

4.

Query:

SELECT rating,MAX(age) FROM sailors WHERE sid IN (SELECT sid FROM reserves where bid IN (SELECT bid from boats where color='red')) GROUP BY rating;

Output:

rating MAX(age)

7 45

8 56

5.

Query:

SELECT sname from sailors where rating < (SELECT rating from sailors where sname = 'Andy');

Output:

sname
Dustin
Brutus
Horatio
Art
Bob

There are multiple correct ways in which the queries can be executed. I have tried to implement them in the easiest way possible. Do comment if you need any further explanations or edit to the answer.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.