-	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 if not exists DATABASE RSYSTEM;
USE RSYSTEM;


DROP TABLE if exists registration;
DROP TABLE if exists course;
DROP TABLE if exists student;

CREATE TABLE student(
	sid	varchar(10),
	fName	VARCHAR(30) NOT NULL,
	lName	VARCHAR(30) NOT NULL,
	gpa	double,
	dob  date,
	CONSTRAINT sailor_pk PRIMARY KEY(sid)
	)ENGINE = INNODB;

-- NOT NULL auto_increment
CREATE TABLE course(
	crn	INT ,
	cname	VARCHAR(30),
	cDiscription	VARCHAR(10),
	dept varchar(5),
	cNumber int,
	cSection int,
	credits int,
	cyear   char(4),
	CONSTRAINT course_pk PRIMARY KEY(crn)
	)ENGINE = INNODB;

CREATE TABLE  registration(
	registration_num INT NOT NULL auto_increment,
	sid	varchar(10),
	crn	int,
	csection int,
	rdate	TIMESTAMP,
	
	CONSTRAINT registration_pk PRIMARY KEY(registration_num),
	CONSTRAINT registration_fk1 FOREIGN KEY (sid) REFERENCES student(sid),
	CONSTRAINT registration_fk2 FOREIGN KEY (crn) REFERENCES course(crn)
	
	) ENGINE = INNODB; 


INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J0901', 'John','Burke', 4.0, '1988-09-12');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J1902', 'Terrence', 'Williams', 1.69, '1987-12-10');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J2903', 'Jennifer', 'Thompson', 3.8, '1998-06-30');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J3904', 'Jasmine',  'Sloane', 3.8, '1979-03-9');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J4905', 'Joseph', 'Nilan', 2.0, '1982-01-14');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J59906', 'Gregory','Moody', 2.7, '1990-02-17');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J6907', 'Harper', 'Dennis', 3.0, '1991-10-10');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J7908', 'Camella','Poindexter', 3.9, '1994-10-10');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J8909', 'Harper','Dennis', 3.2, '1988-09-19');

INSERT INTO student(sid, fName, lName, gpa, dob) VALUES ('J9910', 'Porsha', 'Maner', 3.9, '1988-05-11');



-- The following SQL statements populate the boat table

INSERT INTO course (crn	, cName, dept, cNumber, csection, credits, cYear) 
VALUES ( 50343,'Data Base Management', 'CIS', 205, 001, 4, '2010' );

INSERT INTO course (crn	, cName, dept, cNumber, csection, credits, cYear)
VALUES ( 50344,'Data Base Management', 'CIS', 205, 002, 4, '2010' );

INSERT INTO course (crn	, cName, dept, cNumber, csection, credits, cYear)
VALUES ( 50345,'Data Base Management', 'CIS', 205, 003, 4, '2010' );

INSERT INTO course (crn	, cName, dept, cNumber, csection, credits, cYear)
VALUES ( 50347,'Operating Systems', 'CIS', 105, 002, 4, '2010' );

INSERT INTO course (crn	, cName, dept, cNumber, csection, credits, cYear)
VALUES ( 50445,' American History', 'HIS', 101, 001, 3, '2010' );

INSERT INTO course (crn	, cName, dept, cNumber, csection, credits, cYear)
VALUES ( 50645,'English', 'ENG', 105, 001, 3, '2010' );

INSERT INTO course (crn	, cName, dept, cNumber, csection, credits, cYear)
VALUES ( 50745,'Calculus I', 'MATH', 105, 001, 3, '2010' );




-- The following SQL statements populate the reservation table

INSERT INTO  registration (sid, crn, csection) VALUES ('J0901', 50745, 001);

INSERT INTO  registration (sid, crn, csection) VALUES ('J0901', 50645, 001);

INSERT INTO  registration (sid, crn, csection) VALUES ('J0901',  50445,001);

INSERT INTO  registration (sid, crn, csection) VALUES ('J0901', 50344, 002);

INSERT INTO  registration (sid, crn, csection) VALUES ('J1902', 50344, 002);

INSERT INTO  registration (sid, crn, csection) VALUES ('J1902', 50745, 001);

INSERT INTO  registration (sid, crn, csection) VALUES ('J1902', 50344, 002);

INSERT INTO  registration (sid, crn, csection) VALUES ('J2903', 50347, 001);

INSERT INTO  registration (sid, crn, csection) VALUES ('J2903', 50343,001);

INSERT INTO  registration (sid, crn, csection) VALUES ('J9910', 50343,001);


-- Answer Similar to question 9
-- using the OR condition
SELECT distinct S.fName, s.lName FROM student S, registration R, course C 
WHERE S.sid = R.sid AND R.crn = C.crn AND (C.dept ='CIS' OR C.dept ='HIS');

-- using the union

SELECT S.fName, s.lName FROM student S, Registration R, course C 
WHERE S.sid = R.sid AND R.crn = c.crn AND c.dept ="CIS" 
UNION 

SELECT S.fName, s.lName FROM student S, Registration R, course C 
WHERE S.sid = R.sid AND R.crn = c.crn AND c.dept ="HIS" 


-- Aanswer Similar to question 10
--  : Finds the names of sailors who have reserved both a cis class and a history class.

SELECT S1.fName, s1.lName 
FROM Student S1, Registration R1, Course C1 
 WHERE S1.sid = R1.sid AND R1.crn = C1.crn AND c1.dept ="CIS"  
       AND S1.sid IN (SELECT S2.sid From Student S2, Registration R2, course C2 
	   WHERE S2.sid = R2.sid AND R2.crn = C2.crn AND c2.dept ="HIS");
	  

	  
	 -- Aanswer Similar to question 12
	 --  : Finds the names of student who have reserved a cis class and not a history class.
	  
	  SELECT distinct S1.fName, s1.lName 
	  FROM Student S1, Registration R1, Course C1 
	  WHERE S1.sid = R1.sid AND R1.crn = C1.crn AND    
            c1.dept ="CIS"  AND S1.sid Not IN (SELECT S2.sid From Student S2, Registration R2, 	  course C2 
		    WHERE S2.sid = R2.sid AND R2.crn = C2.crn AND c2.dept ="HIS");
