Part I: Copy and paste the Data Definition Commands in the file "Sailor Query DDL and Data" to create the database and tables for the boat reservation system

Part II: Populating Tables
1. copy and paste the Insert Statements to populate the tables with the data:

Part III: Basic Queries - write the queries to satisfy the following
1. List  the Sailor ID, Sailor Name and Age for all of the Sailors
2. List the Boat ID, Boat Name for all of the Boats
3. List the reservations dates for all Reservations
4. Find all of the Boats that are Red  and list boat name and boat color

5. Find all of the Boats with the name "Interlake" only list the Boat ID 
6. Find all sailors with a rating greater than 7. List the Sailor name and age 
7. Find the names and ages of all sailors.
8. Find the reservations on 9/8/1998

You will have to provide your own create database statement for the script

 

 

DROP TABLE if exist reservation;
DROP TABLE if exist boat;
DROP TABLE if exist sailor;
CREATE TABLE sailor(
sid
int(5),
sname VARCHAR(30) NOT NULL,
rating
int,
age
double,
CONSTRAINT sailor_pk PRIMARY KEY(sid)
)ENGINE = INNODB;
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);
CREATE TABLE boat(
bid
INT NOT NULL auto_increment,
bname VARCHAR(30),
color VARCHAR(10),
CONSTRAINT boat_pk PRIMARY KEY(bid)
)ENGINE = INNODB;
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');
CREATE TABLE reservation(
reservation_num INT NOT NULL auto_increment,
sid
int(5), bid
int(3),
rdate 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;
INSERT INTO reservation (sid, bid, rdate)
VALUES (22, 1, '1998-10-10');
INSERT INTO reservation (sid, bid, rdate)
VALUES (22, 2, '1998-10-10');
INSERT INTO reservation (sid, bid, rdate)
VALUES (22, 3, '1998-10-08');
INSERT INTO reservation (sid, bid, rdate)
VALUES (22, 4, '1998-10-07');
INSERT INTO reservation (sid, bid, rdate)
VALUES (31, 2, '1998-11-10');
INSERT INTO reservation (sid, bid, rdate)
VALUES (31, 3, '1998-11-06');
INSERT INTO reservation (sid, bid, rdate)
VALUES (31, 4, '1998-11-12');
INSERT INTO reservation (sid, bid, rdate)
VALUES (64, 1, '1998-09-05');
INSERT INTO reservation (sid, bid, rdate)
VALUES (64, 2, '1998-09-08');
INSERT INTO reservation (sid, bid, rdate)
VALUES (74, 3, '1998-09-08');

ComputerScienceExpert

(11)

Status NEW Posted 28 Apr 2017 08:04 AM My Price 9.00

-----------

Attachments

file 1493368635-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer