SQL to create faculty, student, and course tables.

The following SQL implements a portion of this ER diagram.

CREATE TABLE facultymember(id INTEGER PRIMARY KEY, 
   name VARCHAR2(20), 
   office VARCHAR2(10));
INSERT
   INTO facultymember(id,   name,   office)
   VALUES(101,   'Dr. Jekyll',   'M24');
INSERT
   INTO facultymember(id,   name,   office)
   VALUES(102,   'Dr. No',   'M25');
CREATE TABLE student(id INTEGER PRIMARY KEY, 
   name VARCHAR2(20), 
   advisor_id INTEGER, 
   email VARCHAR(20) UNIQUE, 
   FOREIGN KEY(advisor_id) REFERENCES facultymember(id) on delete set null);
INSERT
   INTO student(id,   name)
   VALUES(201,   'Alice');
   INSERT
   INTO student(id,   name,   advisor_id)
   VALUES(202,   'Bob',   101);
   INSERT
   INTO student(id,   name,   advisor_id)
   VALUES(203,   'Charlie',   101);
CREATE TABLE course(id INTEGER primary key, 
   room VARCHAR2(8), 
   title VARCHAR2(20), 
   faculty_id INTEGER, 
   FOREIGN KEY(faculty_id) REFERENCES facultymember(id) on delete SET NULL);
insert into course (id, title, faculty_id)
   values (301,'History',101);
insert into course (id, title, faculty_id)
   values(302,'Physics',102);
CREATE TABLE enrollment (student_id integer, course_id integer,
   primary key (student_id, course_id),
   foreign key (student_id) references student(id) on delete cascade,
   foreign key (course_id) references course(id) on delete cascade);
insert into enrollment(student_id, course_id) values (201,301);
   insert into enrollment(student_id, course_id) values (202,301);
   insert into enrollment(student_id, course_id) values (203,301);

Simple SQL Select statements

select * from facultymember;
select * from course;
select s.name, c.title from student s, course c, enrollment e where 
   s.id=e.student_id and
   c.id=e.course_id;

SQL to delete a row

delete from facultymember where id=101;