-- CREATE_PT2.SQL - Adds and Populates TA table and runs complex SQL queries
-- to answer this question:
-- "Is there such a case that student A works as TA for student B in one course 
--  while student B works as a TA for student A in another course? If so, who 
--  are they and what are the courses?"

set echo on
spool K:\lectures\lecture9\create_pt2.log

--drop tables
drop table ta CASCADE CONSTRAINTS;

--create tables
create table ta
(
 studentid number(9,0),
 courseid number(6,0) 
);

--specify primary key (multi-column as primary key)
ALTER TABLE ta
	ADD CONSTRAINT pk_ta PRIMARY KEY (studentid,courseid);
	
--specify foreign key
ALTER TABLE ta
	ADD CONSTRAINT fk_stu_courseid_ta
	FOREIGN KEY (courseid)
	REFERENCES courses (courseid);

ALTER TABLE ta
	ADD CONSTRAINT fk_stu_courseid_ta
	FOREIGN KEY (courseid)
	REFERENCES courses (courseid);

commit;

--insert Jinhua as a student:
insert into students
(studentid, fname, lname, department, year, email, phone)
values (100100106, 'Jinhua','Zhao','DUSP',5,'jinhua@mit.edu','1-617-222-1111');

--insert Jinhua as a student in class 11.220:
insert into stu_cou
(studentid, courseid)
values (100100106, 100101);

--insert Jinhua as a ta for class 11.521:
insert into ta
(studentid, courseid)
values (100100106, 100100);

--insert Jacky as a ta for class 11.220:
insert into ta
(studentid, courseid)
values (100100104, 100101);

--list each TA with all the students that each TA teaches in each class:
select t.studentid TAid, s.studentid
  from ta t, stu_cou s
 where t.courseid = s.courseid
 order by t.studentid, s.studentid;

--drop new views
drop view v_ta_stu;
drop view v_ta_stu_double;
 
--redo above as a view and then list all the students from the 
--     above query that teach their TA in another class:
create view v_ta_stu as
select t.studentid TAid, s.studentid, t.courseid
  from ta t, stu_cou s
 where t.courseid = s.courseid;
 
create view v_ta_stu_double as
select v1.taid, v1.studentid, v1.courseid right_class, v2.courseid left_class
  from v_ta_stu v1, v_ta_stu v2
 where v1.studentid = v2.taid
   and v1.taid = v2.studentid
 order by v1.taid, v1.studentid;
  
--list along with names of students and class using fancy formating:
select 'TA' TA, substr(t.fname,1,10) first_name, substr(t.lname,1,15) last_name, 'teaches' teaches, 
       substr(rtrim(s.fname)||' '||rtrim(s.lname),1,20) student_name, 
	   'in class' in_class, c.courseid, c.coursenumber course
  from v_ta_stu_double v3, students t, students s, courses c
 where v3.taid = t.studentid 
   and v3.studentid = s.studentid
   and v3.right_class = c.courseid
  order by t.lname, t.fname, s.lname, s.fname;
  
--redo v_ta_stu_double query without creating any views
select v1.taid, v1.studentid, v1.courseid right_class, v2.courseid left_class
  from (select t.studentid TAid, s.studentid, t.courseid
          from ta t, stu_cou s
         where t.courseid = s.courseid) v1,
	   (select t.studentid TAid, s.studentid, t.courseid
          from ta t, stu_cou s
         where t.courseid = s.courseid) v2
 where v1.studentid = v2.taid
   and v1.taid = v2.studentid
 order by v1.taid, v1.studentid;

spool off;