DROP DATABASE IF EXISTS jeff_db;
CREATE DATABASE IF NOT EXISTS jeff_db;
USE jeff_db;

#
# Table structure for table STUDENT
#
CREATE TABLE STUDENT
(
  STUDENT_ID       	int NOT NULL auto_increment PRIMARY KEY,
  FIRST_NAME       	varchar(40) NOT NULL,
  LAST_NAME    		varchar(40) NOT NULL,
  GENDER	        enum('M','F') default 'M',
  UNIQUE KEY UK_NAME (FIRST_NAME, LAST_NAME),
  INDEX keyIndex(LAST_NAME(40))
) ENGINE=InnoDB;

#
# Table structure for table COURSE
#
CREATE TABLE COURSE
(
  COURSE_ID       	int NOT NULL auto_increment PRIMARY KEY,
  COURSE_NAME       	varchar(40) NOT NULL,
  COURSE_NUMBER		int,
  UNIQUE KEY UK_NAME (COURSE_NAME, COURSE_NUMBER)
) ENGINE=InnoDB;

#
# Table structure for table COURSE
#
CREATE TABLE REGISTRATION
(
  REGISTRATION_ID      	int NOT NULL auto_increment PRIMARY KEY,
  STUDENT_ID		int,
  COURSE_ID		int,
  FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT(STUDENT_ID),
  FOREIGN KEY (COURSE_ID) REFERENCES COURSE(COURSE_ID)
) ENGINE=InnoDB;


insert into STUDENT (FIRST_NAME, LAST_NAME, GENDER)
VALUES ('John', 'Doe', 'M')

insert into STUDENT (FIRST_NAME, LAST_NAME, GENDER)
VALUES ('Tom', 'Jones', 'M')

insert into STUDENT (FIRST_NAME, LAST_NAME, GENDER)
VALUES ('Sally', 'Smith', 'F')

insert into STUDENT (FIRST_NAME, LAST_NAME, GENDER)
VALUES ('Jennifer', 'Smith', 'F')

insert into COURSE (COURSE_NAME, COURSE_NUMBER)
VALUES ('Physics', 101)

insert into COURSE (COURSE_NAME, COURSE_NUMBER)
VALUES ('French', 101)

insert into COURSE (COURSE_NAME, COURSE_NUMBER)
VALUES ('French', 201)

#John Doe in Physics 101
insert into REGISTRATION (STUDENT_ID, COURSE_ID)
VALUES (1, 1);

#Sally Smith in Physics 101
insert into REGISTRATION (STUDENT_ID, COURSE_ID)
VALUES (3, 1);

#Sally Smith in French 201
insert into REGISTRATION (STUDENT_ID, COURSE_ID)
VALUES (3, 3);

#Tom Jones in Physics 101
insert into REGISTRATION (STUDENT_ID, COURSE_ID)
VALUES (2, 1);

#Tom Jones in French 101
insert into REGISTRATION (STUDENT_ID, COURSE_ID)
VALUES (2, 2);




#select all students whose last name begins with S
select * from STUDENT S
WHERE LAST_NAME LIKE 'S%'

#select all the students who are registered, and what they are registered for
#ordered by name
select S.*, C.*
FROM STUDENT S, COURSE C, REGISTRATION R
WHERE S.STUDENT_ID = R.STUDENT_ID
AND C.COURSE_ID = R.COURSE_ID
ORDER BY S.LAST_NAME, S.FIRST_NAME

#select the count of male students
select count(*)
from STUDENT
where gender = 'M'

#select all the students in the Physics 101 class
select S.FIRST_NAME, S.LAST_NAME
FROM STUDENT S, COURSE C, REGISTRATION R
WHERE S.STUDENT_ID = R.REGISTRATION_ID
AND R.COURSE_ID = C.COURSE_ID
AND C.COURSE_NAME = 'Physics'
AND C.COURSE_NUMBER = 101

#select the number of people registered for Physics 101
select count(*)
from COURSE C, REGISTRATION R
where C.course_name = 'Physics'
and C.course_number = 101
and C.course_id = R.course_id

#select the student names who are registered for classes
#and how many classes they are registered for
select count(*), S.LAST_NAME, S.FIRST_NAME
from STUDENT S, REGISTRATION R
WHERE S.STUDENT_ID = R.STUDENT_ID
GROUP BY S.FIRST_NAME, S.LAST_NAME
ORDER BY S.LAST_NAME, S.FIRST_NAME

#select all students who are not registered using sub query
select * from STUDENT S
WHERE S.STUDENT_ID NOT IN (SELECT STUDENT_ID FROM REGISTRATION)

#select all students who are not registered using left join
select * from STUDENT S
LEFT JOIN REGISTRATION R ON S.STUDENT_ID = R.STUDENT_ID
WHERE R.STUDENT_ID IS NULL