--	Instructor: Craig Nelson
--	E-mail: cnelson@ccp.edu
--	Class: CIS 205
--	
-- 
--	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.
*/

CREATE TABLE sailor_mod(
	sid	int(5),
	sname	VARCHAR(30) NOT NULL,
	rating	int,
	age	double,
    mod_type char(1),
	mod_date timestamp
	)ENGINE = INNODB;
	
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;


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; 


--DROP SEQUENCE bid_sequence;
--CREATE SEQUENCE bid_sequence
--	START WITH 101
--	INCREMENT BY 1;

	
-- 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, 102, '1998-09-08');

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


ALTER TABLE reservation ADD COLUMN  rtime              			TIME;

ALTER TABLE boat ADD COLUMN  capacity              		INT;

ALTER TABLE sailor ADD COLUMN  gender              		enum ('F','M');

ALTER TABLE reservation MODIFY  rtime              		TIMESTAMP not null;


ALTER TABLE reservation CHANGE day sdate date; 

ALTER TABLE reservation CHANGE sdate rdate date; 

UPDATE reservation SET rdate =CURDATE();

UPDATE sailor SET gender = "M" where sid = 58;

DELETE FROM salor WHERE age < 18;

DELETE from reservation where sid = 31 and rdate = '1998-11-12' ;

DELIMITER //

CREATE PROCEDURE sailor_ages()
BEGIN
   SELECT sname AS Sailor_Name , age AS Sailor_Age 
   FROM sailor;
END//
DELIMITER ;


DROP PROCEDURE  IF EXISTS sailor_ages;


DELIMITER //

CREATE PROCEDURE display_structures()
BEGIN
   DESCRIBE sailor;
   DESCRIBE boat;
   DESCRIBE reservation;
END//
DELIMITER ;

DROP PROCEDURE  IF EXISTS display_structures;




DELIMITER //
CREATE PROCEDURE sailor_summary(
   OUT rl DECIMAL(8,2),
   OUT rh DECIMAL(8,2),
   OUT ra DECIMAL(8,2)
)
BEGIN
   SELECT Min(rating)
   INTO rl
   FROM sailor;
   SELECT Max(rating)
   INTO rh
   FROM sailor;
   SELECT Avg(rating)
   INTO ra
   FROM sailor;
   select @ratinglow, @ratinghigh, @ratingaverage;
END//

DELIMITER ;

DROP PROCEDURE  IF EXISTS sailor_summary;
CALL sailor_summary(@ratinglow, @ratinghigh, @ratingaverage);

select @ratinglow, @ratinghigh, @ratingaverage;

DELIMITER //

CREATE PROCEDURE ordertotal(
   IN onumber INT,
   OUT ototal DECIMAL(8,2)
)
BEGIN
   SELECT Sum(price)
   FROM reservation
   WHERE sid = onumber
   INTO ototal;
   select ototal; 
END//

DELIMITER ;

DELIMITER //

CREATE PROCEDURE Sailor_added( 
out outv varchar(20)	)
BEGIN
  SELECT "sailor succesfully added" into outv;
 
END//
DELIMITER ;


DELIMITER //

CREATE TRIGGER newsailor AFTER INSERT ON sailor
FOR EACH ROW  
BEGIN

CALL Sailor_added( @hold);
 SELECT @hold;
END;
//

DELIMITER ;

INSERT INTO sailor(sid, sname, rating, age)
VALUES (107, 'Craig', 5, 47);



DELIMITER //

CREATE TRIGGER removesailor AFTER delete ON sailor
FOR EACH ROW  
BEGIN
INSERT INTO sailor_mod(sid, sname, rating, age)
VALUES (old.sid,old.sname,old.rating, old.age);

END;
//
DELIMITER ;

DELIMITER //

CREATE TRIGGER updatesailor AFTER update ON sailor
FOR EACH ROW  
BEGIN
INSERT INTO sailor_mod(sid, sname, rating, age)
VALUES (old.sid,old.sname,old.rating, old.age);

END;
//
DELIMITER ;

