-- ELECT DISTINCT <<result_columns>>
-- FROM <<source_tables>>
-- WHERE <<conditions_for_inclusion>>

-- query: all student names major in ITEC.

-- select distinct  -- columns in the results.
-- from  -- relations that contain the data
-- where  -- conditions that are applied for the results.

select distinct fname, lname -- columns in the results. [2]
from  student -- relations that contain the data [1]
where major = 'ITEC';  -- conditions that are applied for the results. [3]

-- [1] Gather and merge all needed table.
-- [2] vertical subsetting.
-- [3] horizontal subsetting.

-- Q2. ALl information of faculty in the department CSCI.

-- Expected output:
select distinct facId, fname, lname, deptCode, rank  -- [2]
from faculty  -- [1]
where deptCode = 'CSCI';  -- [3]

select distinct *  -- [2]
from faculty  -- [1]
where deptCode = 'CSCI';  -- [3]

-- Q3.Faculty names ranks of the department 'Computer Science'


select distinct fname, lname, rank
from faculty, department  -- department to look up the deptCode of 'Computer Science'
where deptName = 'Computer Science';

select distinct faculty.fname, faculty.lname, faculty.rank  -- using full names.
from faculty, department  -- department to look up the deptCode of 'Computer Science'
where department.deptName = 'Computer Science';

-- explicitly state that the common attributes (foreign key) should be the same in the tables.

select distinct faculty.fname, faculty.lname, faculty.rank  -- using full names.
from faculty, department  -- department to look up the deptCode of 'Computer Science'
where faculty.deptCode = department.deptCode
and department.deptName = 'Computer Science';

select distinct f.fname, f.lname, f.rank  -- using full names.
from faculty f, department d  -- alias
where f.deptCode = d.deptCode
and d.deptName = 'Computer Science';

-- q4. student names that enroll in a class offered by Andrew Bryne.

select distinct s.fname, s.lname
from student s, enroll e, class c, faculty f -- [1]
where s.stuId = e.stuId  -- equalities of common attributes
and e.classId = c.classId
and c.facId = f.facId
and f.fname = 'Andrew'  -- query logic
and f.lname = 'Bryne';

-- q5. student names that enroll in a class offered by Sharon Mannes.

select distinct s.fname, s.lname
from student s, enroll e, class c, faculty f -- [1]
where s.stuId = e.stuId  -- equalities of common attributes
and e.classId = c.classId
and c.facId = f.facId
and f.fname = 'Sharon'  -- query logic
and f.lname = 'Mannes';


-- [1] Gather and merge all needed table.
-- [2] vertical subsetting ->  constructing the expressions in the results.
-- [3] horizontal subsetting.

-- q5. student names (in one column) and major dept code that enroll 
-- in a class offered by Sharon Mannes.

select distinct concat(s.fname, ' ', s.lname) as "student", -- function concat, label "student"
	major
from student s, enroll e, class c, faculty f -- [1]
where s.stuId = e.stuId  -- equalities of common attributes
and e.classId = c.classId
and c.facId = f.facId
and f.fname = 'Sharon'  -- query logic
and f.lname = 'Mannes';