By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Student (
stuId VARCHAR2(6) PRIMARY KEY,
lastName VARCHAR2(20) NOT NULL,
firstName VARCHAR2(20) NOT NULL,
major VARCHAR2(10),
credits NUMBER(3) DEFAULT 0,
CONSTRAINT Student_credits_cc CHECK ((credits>=0) AND (credits < 150)));
CREATE TABLE Faculty (
facId VARCHAR2(6),
name VARCHAR2(20) NOT NULL,
department VARCHAR2(20),
rank VARCHAR2(10),
CONSTRAINT Faculty_facId_pk PRIMARY KEY (facId));
CREATE TABLE Class (
classNumber VARCHAR2(8),
facId VARCHAR2(6) REFERENCES Faculty (facId) ON DELETE SET NULL,
schedule VARCHAR2(8),
room VARCHAR2(6),
CONSTRAINT Class_classNumber_pk PRIMARY KEY (classNumber),
CONSTRAINT Class_schedule_room_uk UNIQUE (schedule, room));
CREATE TABLE Enroll (
stuId VARCHAR2(6),
classNumber VARCHAR2(8),
grade VARCHAR2(2),
CONSTRAINT Enroll_classNumber_stuId_pk PRIMARY KEY (classNumber, stuId),
CONSTRAINT Enroll_classNumber_fk FOREIGN KEY (classNumber) REFERENCES Class (classNumber) ON DELETE CASCADE,
CONSTRAINT Enroll_stuId_fk FOREIGN KEY (stuId) REFERENCES Student(stuId) ON DELETE CASCADE);
INSERT INTO STUDENT
SELECT 'S1001','Smith','Tom','History',90 FROM DUAL UNION ALL
SELECT 'S1002','Chin','Ann','Math',36 FROM DUAL UNION ALL
SELECT 'S1005','Lee','Perry','History',3 FROM DUAL UNION ALL
SELECT 'S1010','Burns','Edward','Art',63 FROM DUAL UNION ALL
SELECT 'S1013','McCarthy','Owen','Math',0 FROM DUAL UNION ALL
SELECT 'S1015','Jones','Mary','Math',42 FROM DUAL UNION ALL
SELECT 'S1020','Rivera','Jane','CSC',15 FROM DUAL
7 rows affected
INSERT INTO FACULTY
SELECT 'F101','Adams','Art','Professor' FROM DUAL UNION ALL
SELECT 'F105','Tanaka','CSC','Instructor' FROM DUAL UNION ALL
SELECT 'F110','Byrne','Math','Assistant' FROM DUAL UNION ALL
SELECT 'F115','Smith','History','Associate' FROM DUAL UNION ALL
SELECT 'F221','Smith','CSC','Professor' FROM DUAL
5 rows affected
INSERT INTO CLASS
SELECT 'ART103A','F101','MWF9','H221' FROM DUAL UNION ALL
SELECT 'CSC201A','F105','uThF10','M110' FROM DUAL UNION ALL
SELECT 'CSC203A','F105','MThF12','M110' FROM DUAL UNION ALL
SELECT 'HST205A','F115','MWF11','H221' FROM DUAL UNION ALL
SELECT 'MTH101B','F110','MTuTh9','H225' FROM DUAL UNION ALL
SELECT 'MTH103C','F110','MWF11','H225' FROM DUAL
6 rows affected
INSERT INTO ENROLL
SELECT 'S1001','ART103A','A' FROM DUAL UNION ALL
SELECT 'S1001','HST205A','C' FROM DUAL UNION ALL
SELECT 'S1002','ART103A','D' FROM DUAL UNION ALL
SELECT 'S1002','CSC201A','F' FROM DUAL UNION ALL
SELECT 'S1002','MTH103C','B' FROM DUAL UNION ALL
SELECT 'S1010','ART103A',NULL FROM DUAL UNION ALL
SELECT 'S1010','MTH103C',NULL FROM DUAL UNION ALL
SELECT 'S1020','CSC201A','B' FROM DUAL UNION ALL
SELECT 'S1020','MTH101B','A' FROM DUAL
9 rows affected
SELECT DISTINCT FACULTY.NAME
FROM FACULTY
WHERE FACULTY.FACID in (SELECT DISTINCT FACID
FROM CLASS
WHERE CLASSNUMBER IN (SELECT DISTINCT CLASSNUMBER
FROM ENROLL
WHERE STUID IN (SELECT DISTINCT STUID
FROM STUDENT
WHERE FIRSTNAME='Ann' AND
LASTNAME='Chin')));
| NAME |
|---|
| Adams |
| Tanaka |
| Byrne |
SELECT ENROLL.CLASSNUMBER, ENROLL.GRADE
FROM ENROLL
WHERE STUID in (SELECT STUID
FROM STUDENT
WHERE FIRSTNAME='Ann' and LASTNAME='Chin');
| CLASSNUMBER | GRADE |
|---|---|
| ART103A | D |
| CSC201A | F |
| MTH103C | B |
SELECT e.CLASSNUMBER,
e.GRADE,
f.NAME
FROM STUDENT s
INNER JOIN ENROLL e
ON e.STUID = s.STUID
INNER JOIN CLASS c
ON c.CLASSNUMBER = e.CLASSNUMBER
INNER JOIN FACULTY f
ON f.FACID = c.FACID
WHERE s.FIRSTNAME || ' ' || s.LASTNAME = 'Ann Chin'
ORDER BY e.CLASSNUMBER
| CLASSNUMBER | GRADE | NAME |
|---|---|---|
| ART103A | D | Adams |
| CSC201A | F | Tanaka |
| MTH103C | B | Byrne |