Oracle SQL: How to unite two SQL statements when referencing a few tables

Ok, so I have the following TABLES and RECORDS.

-- DDL FOR UNIVERSITY EXAMPLE    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 VALUES('S1001','Smith','Tom','History',90);  INSERT INTO STUDENT VALUES('S1002','Chin','Ann','Math',36);  INSERT INTO STUDENT VALUES('S1005','Lee','Perry','History',3);  INSERT INTO STUDENT VALUES('S1010','Burns','Edward','Art',63);  INSERT INTO STUDENT VALUES('S1013','McCarthy','Owen','Math',0);  INSERT INTO STUDENT VALUES('S1015','Jones','Mary','Math',42);  INSERT INTO STUDENT VALUES('S1020','Rivera','Jane','CSC',15);    INSERT INTO FACULTY VALUES('F101','Adams','Art','Professor');  INSERT INTO FACULTY VALUES('F105','Tanaka','CSC','Instructor');  INSERT INTO FACULTY VALUES('F110','Byrne','Math','Assistant');  INSERT INTO FACULTY VALUES('F115','Smith','History','Associate');  INSERT INTO FACULTY VALUES('F221','Smith','CSC','Professor');    INSERT INTO CLASS VALUES('ART103A','F101','MWF9','H221');  INSERT INTO CLASS VALUES('CSC201A','F105','uThF10','M110');  INSERT INTO CLASS VALUES('CSC203A','F105','MThF12','M110');  INSERT INTO CLASS VALUES('HST205A','F115','MWF11','H221');  INSERT INTO CLASS VALUES('MTH101B','F110','MTuTh9','H225');  INSERT INTO CLASS VALUES('MTH103C','F110','MWF11','H225');    INSERT INTO ENROLL VALUES('S1001','ART103A','A');  INSERT INTO ENROLL VALUES('S1001','HST205A','C');  INSERT INTO ENROLL VALUES('S1002','ART103A','D');  INSERT INTO ENROLL VALUES('S1002','CSC201A','F');  INSERT INTO ENROLL VALUES('S1002','MTH103C','B');  INSERT INTO ENROLL(stuId,classNumber) VALUES('S1010','ART103A');  INSERT INTO ENROLL(stuId,classNumber) VALUES('S1010','MTH103C');      INSERT INTO ENROLL VALUES('S1020','CSC201A','B');  INSERT INTO ENROLL VALUES('S1020','MTH101B','A');    --Figure 5.1  

I need to answer the following query: Find the names of all the teachers that Ann Chin has, along with all her classes and midterm grades from each.

so far I have tried many SELECT combinations but I keep getting duplicate values and returns.

This is the closest I have come:

SQL> 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  --------------------  Byrne  Adams  Tanaka    SQL> SELECT ENROLL.CLASSNUMBER, ENROLL.GRADE    2  FROM ENROLL    3  WHERE STUID in (SELECT STUID FROM STUDENT WHERE FIRSTNAME='Ann' and LASTNAME='Chin');    CLASSNUM GR  -------- --  ART103A  D  CSC201A  F  MTH103C  B  

I basically need to do what these two statements do separately together. Can you please tell me how?

https://stackoverflow.com/questions/66510769/oracle-sql-how-to-unite-two-sql-statements-when-referencing-a-few-tables March 07, 2021 at 05:24AM