SQL FIND OUT ALL THE DEPT OR MAJOR FROM TEACHER AND STUDENT

DROP TABLE ENROLLMENT; DROP TABLE COURSE; DROP TABLE STUDENT; DROP TABLE TEACHER;

create table TEACHER ( TEACHER_ID NUMBER not null primary key, LAST_NAME VARCHAR2(35), FIRST_NAME VARCHAR2(35), DEPT VARCHAR2(15), RANK VARCHAR2(15) ) ;

create table STUDENT ( STUDENT_ID NUMBER not null primary key, LAST_NAME VARCHAR2(35), FIRST_NAME VARCHAR2(35), MAJOR VARCHAR2(15), PHONE VARCHAR2(15), TEACHER_ID NUMBER, FOREIGN KEY (TEACHER_ID) REFERENCES TEACHER(TEACHER_ID) ) ;

create table COURSE ( COURSE_ID NUMBER not null primary key, COURSE_NUM VARCHAR2(15), TITLE VARCHAR2(35), DEPT VARCHAR2(15) ) ;

create table ENROLLMENT ( STUDENT_ID NUMBER, COURSE_ID NUMBER, PRIMARY KEY (STUDENT_ID, COURSE_ID), FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT(STUDENT_ID), FOREIGN KEY (COURSE_ID) REFERENCES COURSE(COURSE_ID) ) ;

INSERT INTO TEACHER VALUES (1, 'WONG', 'WC', 'CIS', 'PROFESSOR'), (2, 'Scuchenik', 'Marek', 'CSc', 'PROFESSOR'), (3, 'Jenning', 'Geroge', 'MATH', 'PROFESSOR'), (4, 'Sheu', 'Myron', 'CIS', 'PROFESSOR'), (5, 'Chen', 'CHi-wen', 'CIS', 'ASST PROFESSOR'), (6, 'Kowalski', 'Kaz', 'CSC', 'PROFESSOR');

INSERT INTO STUDENT VALUES (1, 'SMITH', 'JOHN', 'CIS', '310-242-1011', 1), (2, 'Lee', 'Peter', 'MAT', '612-242-1022', 3), (3, 'Lopez', 'Pual', 'CIS', '310-242-1234', 4), (4, 'Jones', 'Bill', 'CSC', '310-242-7777', 1);

INSERT INTO COURSE VALUES (1, 'CIS373' , 'DB Mgt' , 'CIS'), (2, 'CSC351' , 'Data Structure' , 'CSC'), (3, 'MAT190' , 'Calculus' , 'MAT'), (4, 'CIS375' , 'SA&D' , 'CIS'), (5, 'CSC121' , 'Intro Programming' , 'CSC'), (6, 'CIS480' , 'Programming in R' , 'CIS');

INSERT INTO ENROLLMENT VALUES (1 , '1'), (1 , '2'), (2 , '5'), (2 , '6'), (3 , '1'), (3 , '2'), (3 , '3'), (4 , '2'), (4 , '3');

-- DO THE FOLLOWING PROJECTION

-- (18) PROJECT DEPT FROM TEACHER

-- (19) PROJECT MAJOR FROM STUDENT

-- NOW USE THE INFO OF (18) AND (19) TO ANSWER THE FOLLOWING QUESTIONS

-- (20) FIND OUT ALL THE DEPT OR MAJOR FROM TEACHER AND STUDENT

-- (21) FIND OUT WHICH DEPT DOES NOT HAVE STUDENT MAJORING IN

-- (22) FIND OUT THE DEPTS WHO HAVE STUDENT MAJORING IN

-- (23) CREATE A STUDENT_TEACHER VIEW THAT HAS 4 ATTRIBUTES: -- STUDENT LAST NAME, FIRST NAME, TEAHCER LAST NAME AND FIRST NAME

-- (24) PRINT THE STUDENT_TEACHER VIEW

-- (25) WHO IS THE ADVISOR OF PETER LEE