-- ITEC 3335 Fall 2018 Homework #1

-- Start with toyu.
-- Q1. The student with id 100001 has received a
-- new grade of A in the class with id 10000.

select * from enroll;

update enroll
set grade = 'A'
where stuId = 100001
and classid = 10000;

select * from enroll;

-- undo:
update enroll
set grade = null
where stuId = 100001
and classid = 10000;

select * from enroll;

-- Q2. Create a temporary table faculty_more that has LL the columns
-- and keys of the table faculty, plus an additional column department,
-- which is the same column as deptName column of department.

create or replace table faculty_more (    
    facId       int not null,
    fname       varchar(20) not null,
    lname       varchar(20) not null,
    deptCode    varchar(4) not null,
    department  varchar(30) not null,
    rank        varchar(25),
    constraint Faculty_more_facId_pk primary key (facId),
    constraint Faculty_more_deptCode_fk foreign key (deptCode) 
        references Department(deptCode));

select * from faculty_more;

drop table if exists faculty_more;

-- Q3 Populate faculty_more using the contents of existing tables.
insert into faculty_more
select f.facId, f.fname, f.lname, f.deptCode, 
    d.deptName as department, f.rank
from faculty f, department d
where f.deptCode = d.deptCode;

select * from faculty_more;

-- Q4. Remove all lecturers from faculty_more

select * from faculty_more;

delete from faculty_more 
where rank = 'lecturer';

select * from faculty_more;

-- Q5. List the student names, their majors and credits
-- for those with credits between 30 and 80 in the following
-- format. Note the names of the result columns. The result
-- is shown in descending order of the number of credits.

select distinct concat(s.fname, ' ', s.lname) as student,
    s.major, s.credits as `Number of credits`
from student s
where s.credits between 30 and 80
order by `Number of credits` desc;

-- Q6. List the names of the faculty members who do not
-- teach any CSCI course.
select distinct concat(f.fname, ' ', f.lname) as 
    "faculty not teaching CSCI classes"
from faculty f
where f.facId not in
    (select c.facId
     from class c join course co on (c.courseId = co.courseId)
     where co.rubric = 'CSCI');
     
-- Q7. List the names of students taking both classes id #10000
-- and #10004.
select distinct distinct concat(s.fname, ' ', s.lname) as student
from student s, enroll e1, enroll e2
where s.stuId = e1.stuId
and s.stuId = e2.stuId
and e1.classId = 10000
and e2.classId = 10004;

-- Q8. List the names of students taking the class id #10000
-- but not #10004.

select distinct distinct concat(s.fname, ' ', s.lname) as student
from student s, enroll e1
where s.stuId = e1.stuId
and e1.classId = 10000
and s.stuId not in
    (select stuId from enroll where classId = 10004);