Question
we will writes UPDATE and DELETE Statements for tables in the Sailor Boat Reservation Database . Update the rating of a Sailor Update the rating
we will writes UPDATE and DELETE Statements for tables in the Sailor Boat Reservation Database .
Update the rating of a Sailor
Update the rating of a group of Sailors
Update the age of a sailor
Update the age of a group of sailors
Update the color of a boat
Delete A sailor based on a condition
Delete a Reservation based on a condition
database below:
Overview: This file will create the tables and load the data
-- that will be used in the class exercises.
--
/* NOTE: It is good practice to use the "drop table" statement
before the "create table" statement in case the table
already exists.
*/
drop database if exists srdb1;
create database if not exists srdb1;
use srdb1;
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),
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;
-- 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, 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');
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started