Write the queries for the information requested using the data below - Find the

Question

Write the queries for the information requested using the data below -

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

Find the names of sailors who have reserved both a red boat and a green boat.

Find the names and ages of the sailor that reserved boat 3

Find the sids of all sailors who have reserved red boats but not green boats.

DATA

DROP DATABASE sr;
CREATE DATABASE sr;
USE sr;

CREATE TABLE sailor(
sid int(5),
sname VARCHAR(30) NOT NULL,
rating int,
age double,
CONSTRAINT sailor_pk PRIMARY KEY(sid)
)ENGINE = INNODB;

CREATE TABLE boat(
bid INT NOT NULL auto_increment,
bname VARCHAR(30),
color VARCHAR(10),
CONSTRAINT boat_pk PRIMARY KEY(bid)
)ENGINE = INNODB;

CREATE TABLE reservation(
reservation_num INT NOT NULL auto_increment,
sid int(5),
bid int(3),
day DATE,
price double,
CONSTRAINT reservation_pk PRIMARY KEY(reservation_num),
CONSTRAINT reservation_fk1 FOREIGN KEY (sid) REFERENCES sailor(sid),
CONSTRAINT reservation_fk2 FOREIGN KEY (bid) REFERENCES boat(bid)
  
) ENGINE = INNODB;

  
-- The following SQL statements populate the sailor table

INSERT INTO sailor(sid, sname, rating, age)
VALUES (22, 'DUSTIN', 7, 45.0);

INSERT INTO sailor(sid, sname, rating, age)
VALUES (29, 'BRUTUS', 1, 33.0);

INSERT INTO sailor(sid, sname, rating, age)
VALUES (31, 'LUBBER', 8, 55.5);

INSERT INTO sailor(sid, sname, rating, age)
VALUES (32, 'ANDY', 8, 25.5);

INSERT INTO sailor(sid, sname, rating, age)
VALUES (58, 'RUSTY', 10, 35.0);

INSERT INTO sailor(sid, sname, rating, age)
VALUES (64, 'HORATIO', 7, 35.0);

INSERT INTO sailor(sid, sname, rating, age)
VALUES (71, 'ZORBA', 10, 16.0);

INSERT INTO sailor(sid, sname, rating, age)
VALUES (74, 'HORATIO', 9, 35.0);

INSERT INTO sailor(sid, sname, rating, age)
VALUES (85, 'ART', 3, 25.5);

INSERT INTO sailor(sid, sname, rating, age)
VALUES (95, 'BOB', 3, 63.5);

-- The following SQL statements populate the boat table

INSERT INTO boat (bname, color)
VALUES ( 'INTERLAKE', 'BLUE');

INSERT INTO boat ( bname, color)
VALUES ( 'INTERLAKE', 'RED');

INSERT INTO boat ( bname, color)
VALUES ( 'CLIPPER', 'GREEN');

INSERT INTO boat ( bname, color)
VALUES ( 'MARINE', 'RED');

-- The following SQL statements populate the reservation table

INSERT INTO reservation (sid, bid, day)
VALUES (22, 1, '1998-10-10');

INSERT INTO reservation (sid, bid, day)
VALUES (22, 2, '1998-10-10');

INSERT INTO reservation (sid, bid, day)
VALUES (22, 3, '1998-10-08');

INSERT INTO reservation (sid, bid, day)
VALUES (22, 4, '1998-10-07');

INSERT INTO reservation (sid, bid, day)
VALUES (31, 2, '1998-11-10');

INSERT INTO reservation (sid, bid, day)
VALUES (31, 3, '1998-11-06');

INSERT INTO reservation (sid, bid, day)
VALUES (31, 4, '1998-11-12');

INSERT INTO reservation (sid, bid, day)
VALUES (64, 1, '1998-09-05');

INSERT INTO reservation (sid, bid, day)
VALUES (64, 2, '1998-09-08');

INSERT INTO reservation (sid, bid, day)
VALUES (74, 3, '1998-09-08');

Explanation / Answer

1) select sname from sailor s,boat b,reservation r where s.sid=r.sid and b.bid=.bid and b.color=red or b.color=green;

2) select sname from sailor s,boat b,reservation r where s.sid=r.sid and b.bid=.bid and b.color=red and b.color=green;

3) select sname,age from sailor s,boat b,reservation r where s.sid=r.sid and b.bid=r.bid and r.bid=3;

4) select sid from sailor s,boat b,reservation r where s.sid=r.sid and b.bid=r.bid and b.color='red';

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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