-- List of student names (in a single column) that do not enroll 
-- in a class taught by faculty 1,011.

-- expected result.

[a] From the table class:
    fac id 1011 teaches class with id 10000, 10001, 11000.
    
[b] From the table enroll: stuid taking these classes: 100,000 and 100,001
    10000: stuid 100,000 and 100,001, 100,002
    10001: stuid 100,000 and 100,001
    
[c] From the table student:
    all students: 100000 to 100009
    
[d] Students not taking from fac Id = 1001 = [c] - [b]
    stuid 100003 to 100009
    
-- break down to steps:

[a] stuid of all students: 100000 to 100009

column: stuid
source: student
condition: no

select distinct stuid
from student;

select distinct stuid
from student
order by stuId;

[b] stuid of those not taking a class from 1011

output column: stuid
source: class, enroll (taking)
condition: class.facId = 1011 (problem condition)
    join condition: foreign key = primary key in the parent table.
    enroll.classId = class.classId
    
select distinct e.stuId
from class c, enroll e
where e.classId = c.classId
and c.facId = 1011;


[c] [a] - [b]: 100003-100009

[a] + condition: stuID not in [b]

select distinct s.stuid
from student s
where s.stuId not in -- [b]
   (select distinct e.stuId
    from class c, enroll e
    where e.classId = c.classId
    and c.facId = 1011);
    
[d] names of [c]

select distinct s.stuId, s.fname, s.lname
from student s
where s.stuId not in -- [b]
   (select distinct e.stuId
    from class c, enroll e
    where e.classId = c.classId
    and c.facId = 1011);

[e] names of [c] in one column.

select distinct concat(s.fname, ' ', s.lname)  -- function
from student s
where s.stuId not in -- [b]
   (select distinct e.stuId
    from class c, enroll e
    where e.classId = c.classId
    and c.facId = 1011);
    
select distinct concat(s.fname, ' ', s.lname) as name -- function
from student s
where s.stuId not in -- [b]
   (select distinct e.stuId
    from class c, enroll e
    where e.classId = c.classId
    and c.facId = 1011);
    
select distinct concat(s.fname, ' ', s.lname) as "Student name" -- function
from student s
where s.stuId not in -- [b]
   (select distinct e.stuId
    from class c, enroll e
    where e.classId = c.classId
    and c.facId = 1011);
    
Q7.  List the names of students taking both classes id #10000 and #10004.
Q. Names of courses with classes taught by faculty 1014 and 1016.
 
-- Expected:

[a] class taught by 1014: classid: 10003, 10004; courseId: 2020 and 2021
   class table

column: courseId
source: class
condition: facId = 1014

select distinct c.courseId from class c where c.facId = 1014;


[b] class taught by 1016: classid: 11003; courseID: 2020
   class table

   select distinct c.courseId from class c where c.facId = 1016; 

   
[c] answer courseId: 2020

select co.courseId
from course co
where co.courseId in -- [a]
    (select distinct c.courseId from class c where c.facId = 1014)
and co.courseId in -- [b]
    (select distinct c.courseId from class c where c.facId = 1016);


[d] answer: Introduction to Information Systems
    table course
    
 select co.name
from course co
where co.courseId in -- [a]
    (select distinct c.courseId from class c where c.facId = 1014)
and co.courseId in -- [b]
    (select distinct c.courseId from class c where c.facId = 1016);   
    
select co.name
from course co
where co.courseId in -- [a]
    (select distinct c.courseId from class c where c.facId = 1014)
and co.courseId in -- [b]
    (select distinct c.courseId from class c where c.facId = 1016); 


-- course name for classes taught by 1014.
output: course.name
source: course (output), class (taught by 1014)
condition: class.facId = 1014 (problem condition)
    join condition class.courseId (FK) = course.courseId (PK)
    
select distinct co.name
from course co, class c
where co.courseId = c.courseId
and c.facId = 1014;

-- answer:
select distinct co.name
from course co, class c1, class c2
where co.courseId = c1.courseId
and c1.facId = 1014
and co.courseId = c2.courseId
and c2.facId = 1016;