create table student(
	snum numeric(9,0) primary key,
	sname varchar(30),
	major varchar(25),
	standing varchar(2),
	age numeric(3,0)
	);

create table youngstudent(
	snum numeric(9,0) primary key,
	sname varchar(30),
	major varchar(25),
	standing varchar(2),
	age numeric(3,0)
	);


create table faculty(
	fid numeric(9,0) primary key,
	fname varchar(30),
	deptid numeric(2,0)
	);

create table class(
	name varchar(40) primary key,
	meets_at varchar(20),
	room varchar(10),
	fid numeric(9,0),
	foreign key (fid) references faculty(fid)
	);

create table enrolled(
	snum numeric(9,0),
	cname varchar(40),
	primary key(snum,cname),
	foreign key (snum) references student(snum),
	foreign key (cname) references class(name)
	);

delimiter //
CREATE TRIGGER studentupdate after insert ON student
       FOR EACH ROW
       BEGIN
           IF NEW.age <18 THEN
               insert into youngstudent(snum,sname,major,standing,age) values
                (NEW.snum,NEW.sname,NEW.major,NEW.standing,NEW.age);
           END IF;
       END;//
delimiter ;

insert into student(snum,sname, major,standing,age) values
        (051135593,'Maria White','English','SR',21), 
        (060839453,'Charles Harris','Architecture','SR',22),
	(099354543,'Susan Martin','Law','JR',20),
	(112348546,'Joseph Thompson','Computer Science','SO',19),
	(115987938,'Christopher Garcia','Computer Science','JR',20),
	(132977562,'Angela Martinez','History','SR',20),
	(269734834,'Thomas Robinson','Psychology','SO',18),
	(280158572,'Margaret Clark','Animal Science','FR',18),
	(301221823,'Juan Rodriguez','Psychology','JR',20),
	(318548912,'Dorthy Lewis','Finance','FR',18),
	(320874981,'Daniel Lee','Electrical Engineering','FR',17),
	(322654189,'Lisa Walker','Computer Science','SO',17),
	(348121549,'Paul Hall','Computer Science','JR',18),
	(351565322,'Nancy Allen','Accounting','JR',19),
	(451519864,'Mark Young','Finance','FR',18),
	(455798411,'Luis Hernandez','Electrical Engineering','FR',17),
	(462156489,'Donald King','Mechanical Engineering','SO',19),
	(550156548,'George Wright','Education','SR',21),
	(552455318,'Ana Lopez','Computer Engineering','SR',19),
	(556784565,'Kenneth Hill','Civil Engineering','SR',21),
	(567354612,'Karen Scott','Computer Engineering','FR',18),
	(573284895,'Steven Green','Kinesiology','SO',19),
	(574489456,'Betty Adams','Economics','JR',20),
	(578875478,'Edward Baker','Veterinary Medicine','SR',21);
	 
insert into faculty(fid,fname,deptid) values
        (142519864,'Ivana Teach',20),
	(242518965,'James Smith',68),
	(141582651,'Mary Johnson',20),
	(011564812,'John Williams',68),
	(254099823,'Patricia Jones',68),
	(356187925,'Robert Brown',12),
	(489456522,'Linda Davis',20),
	(287321212,'Michael Miller',12),
	(248965255,'Barbara Wilson',12),
	(159542516,'William Moore',33),
	(090873519,'Elizabeth Taylor',11),
	(486512566,'David Anderson',20),
	(619023588,'Jennifer Thomas',11),
	(489221823,'Richard Jackson',33),
	(548977562,'Ulysses Teach',20);

insert into class(name, meets_at, room, fid) values 
        ('Data Structures','MWF 10','R128',489456522),
	('Database Systems', 'MWF 12:30-1:45','1320 DCL',142519864),
	('Operating System Design','TuTh 12-1:20','20 AVW',489456522), 
	('Archaeology of the Incas','MWF 3-4:15','R128',248965255),
	('Aviation Accident Investigation','TuTh 1-2:50','Q3',011564812),
	('Air Quality Engineering','TuTh 10:30-11:45','R15',011564812),
	('Introductory Latin','MWF 3-4:15','R12',248965255),
	('American Political Parties','TuTh 2-3:15','20 AVW',619023588),
	('Social Cognition','Tu 6:30-8:40','R15',159542516),
	('Perception','MTuWTh 3','Q3',489221823),
	('Multivariate Analysis','TuTh 2-3:15','R15',090873519),
	('Patent Law','F 1-2:50','R128',090873519),
	('Urban Economics','MWF 11','20 AVW',489221823),
	('Organic Chemistry','TuTh 12:30-1:45','R12',489221823),
	('Marketing Research','MW 10-11:15','1320 DCL',489221823),
	('Seminar in American Art','M 4','R15',489221823),
	('Orbital Mechanics','MWF 8','1320 DCL',011564812),
	('Dairy Herd Management','TuTh 12:30-1:45','R128',356187925),
	('Communication Networks','MW 9:30-10:45','20 AVW',141582651),
	('Optical Electronics','TuTh 12:30-1:45','R15',254099823),
	('Intoduction to Math','TuTh 8-9:30','R128',489221823);


insert into enrolled(snum,cname) values
	(112348546,'Database Systems'),
	(115987938,'Database Systems'),
	(348121549,'Database Systems'),
	(322654189,'Database Systems'),
	(552455318,'Database Systems'),
	(455798411,'Operating System Design'),
	(552455318,'Operating System Design'),
	(567354612,'Operating System Design'),
	(112348546,'Operating System Design'),
	(115987938,'Operating System Design'),
	(322654189,'Operating System Design'),
	(567354612,'Data Structures'),
	(552455318,'Communication Networks'),
	(455798411,'Optical Electronics'),
	(301221823,'Perception'),
	(301221823,'Social Cognition'),
	(301221823,'American Political Parties'),
	(556784565,'Air Quality Engineering'),
	(099354543,'Patent Law'),
	(574489456,'Urban Economics');

--Question 5.1.4
select distinct S.sname
from Student S
where S.snum in (Select E1.snum
                 from Enrolled E1, Enrolled E2, 
                 Class C1, Class C2
                 where E1.snum=E2.snum 
                       and E1.cname <> E2.cname
                       and E1.cname=C1.name
                       and E2.cname=C2.name
                       and C1.meets_at=C2.meets_at);

-- Question 5.1.5
select distinct f.fname
from Faculty F
where not exists (select distinct C.room
                  from Class C
                  where not exists ( Select *
                                     from Class C1
                                     where C1.fid=F.fid and
                                     C1.room=C.room));

select distinct f.fname
from faculty f
where 5 > (select count(e.snum)
          from class c, enrolled e
          where c.name=e.cname and c.fid=f.fid); 

select f.fname, count(*)  as CourseCount
from faculty f, class c
where f.fid=c.cid
group by f.fid, f.fname
having any( c.room <>'R128' );

select f.fname, count(*) as CourseCount
from faculty f, class c
where f.fid=c.fid and 
   (not exists( select * 
               from class c1
               where c1.room <>'R128' and c1.fid=f.fid))
group by f.fid, f.fname; 

select distinct s.sname
from student s
where s.snum not in (Select E.snum from Enrolled E);

select S.age, S.standing
from Student s
group by s.age, s.standing
having s.standing in (Select s1.standing
                      from student s1
                      where s1.age=s.age
                      group by s1.standing, s1.age
                      having count(*) >= all (select count(*)
                                              from student s2
                                              where s1.age=s2.age
                                              group by s2.standing,s2.age));