CREATE TABLE Faculty (
fss_num		VARCHAR2(20)	CONSTRAINT faculty_nn_fss_num NOT NULL,
name		VARCHAR2(20)	CONSTRAINT faculty_nn_name NOT NULL,
address		VARCHAR2(20),
age		NUMBER,
salary		NUMBER(10,2),
CONSTRAINT pk_faculty PRIMARY KEY (fss_num)
);

CREATE TABLE Section (
c_num		NUMBER		CONSTRAINT section_nn_c_num NOT NULL,
sec_num		NUMBER		CONSTRAINT section_nn_sec_num NOT NULL,
fss_num		VARCHAR2(20),
CONSTRAINT pk_section PRIMARY KEY (c_num, sec_num),
CONSTRAINT fk1_section FOREIGN KEY (c_num) REFERENCES Course(c_num),
CONSTRAINT fk2_section FOREIGN KEY (fss_num) REFERENCES Faculty(fss_num)
);

CREATE TABLE Attends (
sss_num		VARCHAR2(20)	CONSTRAINT attends_nn_sss_num NOT NULL,
c_num		NUMBER		CONSTRAINT attends_nn_c_num NOT NULL,
sec_num		NUMBER		CONSTRAINT attends_nn_sec_num NOT NULL,
CONSTRAINT pk_attends PRIMARY KEY (sss_num, c_num),
CONSTRAINT fk1_attends FOREIGN KEY (sss_num) REFERENCES Student(sss_num),
CONSTRAINT fk2_attends FOREIGN KEY (c_num, sec_num) REFERENCES Section(c_num, sec_num)
);

SELECT faculty.name "Name", count(attends.sss_num) "Total# of Students" 
FROM Faculty, Attends, Section
 WHERE section.fss_num (+) = faculty.fss_num
       AND attends.c_num = section.c_num
       AND attends.sec_num = section.sec_num
  GROUP BY name; 

NAME                 COUNT(ATTENDS.SSS_NUM)
-------------------- ----------------------
finster                                   1
greggory                                  1
will                                      1
tymann                                    2
strouse                                   1
jones                                     2
rich                                      1
loder                                     1

NAME                 COUNT(ATTENDS.SSS_NUM)
-------------------- ----------------------
judd                                      1
kilber                                    4
hardy                                     2
williams                                  2
moriarty                                  1
holmes                                    1
brown                                     3
brindle                                   1

NAME                 COUNT(ATTENDS.SSS_NUM)
-------------------- ----------------------
bowden                                    1

17 rows selected.