drop table Enrollment;
drop table Offering;
drop table Course;
drop table Student;
drop table Instructor;
drop table Person;

USE db363anha0404;

-- item 1 Create table Person
create table Person (
Name char (20),
ID char (9) not null,
Address char (30),
DOB date,
Primary Key (ID));

-- item 2 Create table Instructor
create table Instructor (
InstructorID char (9) not null references Person (ID),
Rank char (12),
Salary int,
primary key (InstructorID) );

-- item 3 Create table Student
create table Student (
StudentID char (9) not null,
Classification varchar (10),
GPA double, MentorID char (9) references Instructor (InstructorID),
CreditHours int);

-- item 4 Create table Course
create table Course (
CourseCode char (6) not null,
CourseName char (50),
PreReq char (6));

-- item 5 Create table Offering
create table Offering (
CourseCode char (6) not null,
SectionNo int not null,
InstructorID char (9) not null references Instructor (InstructorID),
primary key (CourseCode, SectionNo));

-- item 6 Create table Enrollment
create table Enrollment (
CourseCode char(6) NOT NULL,
SectionNo int NOT NULL,
StudentID char(9) NOT NULL references Student,
Grade char(4) NOT NULL,
primary key (CourseCode, StudentID),
foreign key (CourseCode, SectionNo) references Offering(CourseCode, SectionNo));

/* Item 7-12 - Load Person, Instructor, Student, Course, Offering, and Enrollment tables. */
-- item 7
load xml local infile '/home/ahan37/Documents/ComS363/Person.xml'
into table Person
rows identified by '<Person>';

-- item 8
load xml local infile '/home/ahan37/Documents/ComS363/Instructor.xml'
into table Instructor
rows identified by '<Instructor>';

-- item 9
load xml local infile '/home/ahan37/Documents/ComS363/Student.xml'
into table Student
rows identified by '<Student>';

-- item 10
load xml local infile '/home/ahan37/Documents/ComS363/Course.xml'
into table Course
rows identified by '<Course>';

-- item 11
load xml local infile '/home/ahan37/Documents/ComS363/Offering.xml'
into table Offering
rows identified by '<Offering>';

-- item 12
load xml local infile '/home/ahan37/Documents/ComS363/Enrollment.xml'
into table Enrollment
rows identified by '<Enrollment>';

-- item 13
select s.StudentID, s.MentorID
from Student s
where (s.GPA > 3.8 and s.Classification = 'junior') or ( s.GPA > 3.8 and s.Classification = 'senior');

-- item 14
select distinct e.CourseCode, e.SectionNo
from Enrollment e, Student s
-- where e.StudentID = s.StudentID and s.Classification = 'Sophomore';
where e.StudentID in (select s.StudentID
	from Student s
		where s.Classification = 'Sophomore');

-- item 15
select distinct p.Name, i.Salary
from Person p, Instructor i
where p.ID = i.InstructorID and p.ID in (select s.MentorID
		from Student s
		where s.Classification = 'Freshman');

-- item 16
select sum(i.Salary)
from Instructor i
where i.InstructorID not in (select o.InstructorID from Offering o);

-- item 17
select p.Name, p.DOB
from Person p
where Year(p.DOB) = 1976 and p.ID in (select Student.StudentID from Student);

-- item 18
select p.Name, i.Rank
from Person p, Instructor i
where p.ID = i.InstructorID and  p.ID not in (select Offering.InstructorID from Offering)
and i.InstructorID not in (select Student.MentorID from Student);

-- item 19
select p.ID, p.Name, p.DOB
from Person p
where p.DOB = (select max(p.DOB) from Person p)
and p.ID in (select Student.StudentID from Student);

-- item 20
select p.ID, p.DOB, p.Name
from Person p
where p.ID not in (select Student.StudentID from Student)
and p.ID not in (select Instructor.InstructorID from Instructor);

-- item 21
select p.Name, count(*) as 'NumberofStudents'
from Person p, Instructor i, Student s
where i.InstructorID = p.ID
and s.MentorID = p.ID
and s.MentorID = i.InstructorID
group by p.Name;

-- item 22
select count(*) as 'NumberofStudents', avg(s.GPA) as 'Average GPA'
from Student s
group by s.Classification;

-- item 23
select e.CourseCode, count(e.CourseCode) as NumberofEnrollment
from Enrollment e
group by e.CourseCode
order by NumberofEnrollment asc
limit 1;

-- item 24
select distinct e.StudentID, s.MentorID
from Student s, Enrollment e, Offering o
where e.CourseCode = o.CourseCode
and o.InstructorID = s.MentorID and e.StudentID = s.StudentID;


-- item 25
select s.StudentID, p.Name, s.CreditHours
from Student s
join Person p on  p.ID = s.StudentID
where year(p.DOB) >= 1976
and s.Classification = 'Freshman';

-- item 26
insert into Person (Name, ID, Address, DOB)
values ('Briggs Jason','480293439', '215 North Hyland Avenue','1975-01-15 00:00:00');

insert into Student (StudentID, Classification, GPA, MentorID, CreditHours)
values ('480293439', 'Junior', 3.48, '201586985', 75);

insert into Enrollment (CourseCode, SectionNo, StudentID, Grade)
values ('CS311', 2, '480293439', 'A');

insert into Enrollment (CourseCode, SectionNo, StudentID, Grade)
values ('CS330', 1, '480293439', 'A');

select *
from Person p
where p.Name= 'Briggs Jason';

select *
from Student s
where s.StudentID= '480293439';

select *
from Enrollment e
where e.StudentID = '480293439';

-- item 27
delete from Enrollment
where StudentID in (select s.StudentID from Student s
						where s.GPA < 0.5);

delete from Student
where GPA < 0.5;

select *
from Student s
where s.GPA < 0.5;

-- item 28
update Instructor
set Salary = Salary + (Salary*0.1)
where InstructorID = (select p.ID from Person p
						where p.Name = 'Ricky Ponting')
and (select distinct count(*)
	from (select * from Instructor) i, Person p, Offering o, Enrollment e, Student s
    where p.ID = i.InstructorID
	and p.Name = 'Ricky Ponting' and o.InstructorID = i.InstructorID
	and e.CourseCode = o.CourseCode and e.StudentID = s.StudentID
	and e.Grade = 'A') >= 5;

select i.Salary
from Instructor i
where i.InstructorID = (select ID from Person
							where Name = 'Ricky Ponting');

-- item 29
insert into Person (Name, ID, Address, DOB)
values ('Trevor Horns', '000957303','23 Canberra Street', '1964-11-23 00:00:00');

select *
from Person p
where year(p.DOB) = 1964;

-- item 30
delete Enrollment, Student from Enrollment
inner join Student on Student.StudentID = Enrollment.StudentID
where Enrollment.StudentID = (select p.ID from Person p
					where p.Name = 'Jan Austin');

select * from Student
where StudentID = (select p.ID from Person p
					where p.Name = 'Jan Austin');