DROP TABLE STUDENT cascade constraints;

CREATE TABLE STUDENT ( SID NUMBER(2) PRIMARY KEY, SSN CHAR(11) UNIQUE NOT NULL, firstname VARCHAR2(15), lastname VARCHAR2(15), major VARCHAR2(20), GPA NUMBER(2,1), class VARCHAR2(9) );

DROP TABLE COURSE cascade constraints;

create table COURSE ( CID number(3) primary key, cname varchar2(20), dept varchar2(20) );

DROP TABLE TAKES cascade constraints;

create table TAKES ( T_SID number(2), T_CID number(3), primary key (T_SID, T_CID), foreign key (T_SID) references STUDENT(sid), foreign key (T_CID) references COURSE(CID) ); INSERT INTO STUDENT (sid, ssn, firstname, lastname, major, gpa, class) VALUES (11, '350-33-5939', 'Mary', 'Kay', 'CS', 3.3, 'Freshman'); INSERT INTO STUDENT (sid, ssn, firstname, lastname, major, gpa, class) VALUES (22, '359-12-3040', 'John', 'Blizzard', 'IT', 2.1, 'Senior'); INSERT INTO STUDENT (sid, ssn, firstname, lastname, major, gpa, class) VALUES (33, '319-30-3929', 'Barry', 'Butter', 'IT', 3.0, 'Senior'); INSERT INTO STUDENT (sid, ssn, firstname, lastname, major, gpa, class) VALUES (44, '318-50-2930', 'Ron', 'Beasley', 'CS', 2.9, 'Junior'); INSERT INTO STUDENT (sid, ssn, firstname, lastname, major, gpa, class) VALUES (55, '343-10-9583', 'Sam', 'Farley', 'CS', 3.9, 'Senior'); INSERT INTO STUDENT (sid, ssn, firstname, lastname, major, gpa, class) VALUES (66, '325-76-8869', 'Aria', 'Stake', 'IT', 3.2, 'Senior'); INSERT INTO STUDENT (sid, ssn, firstname, lastname, major, gpa, class) VALUES (77, '356-49-9899', 'Daniel', 'Robert', 'SECURITY', 3.5, 'Sophomore'); INSERT INTO STUDENT (sid, ssn, firstname, lastname, major, gpa, class) VALUES (88, '360-11-2230', 'Dwight', 'Beets', 'NMT', 3.7, 'Junior'); INSERT INTO STUDENT (sid, ssn, firstname, lastname, major, gpa, class) VALUES (99, '347-90-7731', 'Steph', 'Fieri', 'CS', 3.1, 'Senior');

INSERT INTO COURSE (CID, CNAME, DEPT) VALUES (244, 'BI', 'IT'); INSERT INTO COURSE (CID, CNAME, DEPT) VALUES (340, 'AI', 'CS'); INSERT INTO COURSE (CID, CNAME, DEPT) VALUES (378, 'DB', 'IT'); INSERT INTO COURSE (CID, CNAME, DEPT) VALUES (383, 'OS', 'CS');

INSERT INTO TAKES (T_SID, T_CID) values (44, 244); INSERT INTO TAKES (T_SID, T_CID) values (55, 340); INSERT INTO TAKES (T_SID, T_CID) values (99, 340); INSERT INTO TAKES (T_SID, T_CID) values (22, 378); INSERT INTO TAKES (T_SID, T_CID) values (33, 378); INSERT INTO TAKES (T_SID, T_CID) values (44, 378);

SELECT * FROM STUDENT; SELECT * FROM COURSE; SELECT * FROM TAKES;

select firstname, lastname from student where major = 'CS';

select firstname, lastname from student, takes where student.sid = takes.T_SID AND major = 'CS';

select * from takes, course where cid = t_cid;

select DISTINCT cid, cname, dept from takes right outer join course on (course.cid = takes.t_cid);

select firstname, lastname, takes.t_cid from STUDENT left outer join TAKES on student.sid = takes.t_sid;

select firstname, lastname, major, T_CID from STUDENT right outer join TAKES on sid = t_sid AND T_cid = 378;