--
INSERT INTO school VALUES ('LAW', 'College of Law');

INSERT INTO school VALUES ('BUS', 'College of Busy People');

INSERT INTO school VALUES ('BUSP', 'College of Busy People');

INSERT INTO school VALUES ('BSP', 'College of Busy People');

INSERT INTO school VALUES ('SCE', 'Computing and Engineering');
INSERT INTO school VALUES ('CCE', 'SCience and ENgineering');

INSERT INTO department VALUES 
    ('MUSC','Music','ART',5);
    
INSERT INTO school VALUES ('ART', 'College of Arts');

DELETE FROM department WHERE deptCode = 'MUSC';

DELETE FROM school WHERE schoolCode = 'CSE';

-- Q3.
-- 1.	Fname: student.fname
-- 2.	LName: student.lname
-- 3.	Major: department.deptName

--
SELECT DISTINCT -- 1. output column
    s.fname, s.lname,
    d.deptName AS major
-- [2]
-- 1.	Student
-- 2.	Department  
   
FROM student AS s,  -- alias
    department AS d
    -- 2. source
-- [3]    1.	Join conditions: student.major = department.deptCode
-- 2.	Problem condition: minor in ITEC
-- a.	Student.minor = ‘ITEC’
WHERE s.major = d.deptCode
AND s.minor = 'ITEC';
-- 3. conditions