create table dept(
department_id number(2),
department_name varchar2(30),
constraint dept_pk primary key (department_id));
/
INSERT INTO dept(department_id, department_name)
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 2, 'PROGRAMMING' FROM DUAL UNION ALL
SELECT 3, 'DESIGN' FROM DUAL;
CREATE TABLE teachers (
teacher_id number(*,0),
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(25) NOT NULL,
department_id NUMBER(2),
constraint department_id_fk foreign key (department_id) references dept (department_id),
constraint teacher_pk primary key (teacher_id));
INSERT INTO teachers (
teacher_id, first_name, last_name,
department_id)
SELECT 1, 'Ann', 'Abbott', 1 FROM DUAL UNION ALL
SELECT 2, 'Betty', 'Boop', 1 FROM DUAL UNION ALL
SELECT 3, 'Charles', 'Caputo', 2 FROM DUAL UNION ALL
SELECT 4, 'Debra', 'Downing', 3 FROM DUAL;
CREATE TABLE course (
course_id number(*,0),
course_name VARCHAR(25) NOT NULL,
enrolled_students number(*,0) NOT NULL,
teacher_id NUMBER(2),
topic VARCHAR2(20),
constraint teacher_id_fk foreign key (teacher_id) references teachers (teacher_id),
constraint course_pk primary key (course_id));
INSERT INTO course (
course_id, course_name, enrolled_students, teacher_id, topic)
SELECT 1, 'C programming', 35, 1,
'Programming' FROM DUAL UNION ALL
SELECT 2, 'C programming', 28, 2,
'Programming' FROM DUAL UNION ALL
SELECT 3, 'Design', 50, 4,
'Databases' FROM DUAL UNION ALL
SELECT 4, 'SQL', 50, 3,
'Databases' FROM DUAL UNION ALL
SELECT 5, 'SQL', 50, 1,
'Databases' FROM DUAL UNION ALL
SELECT 6, 'C programming', 45, 3,
'Design' FROM DUAL UNION ALL
SELECT 7, 'PLSQL', 50, 4,
'Databases' FROM DUAL;
CREATE TABLE lectures (
lecture_id number,
room_id number,
start_date DATE,
end_date DATE,
course_id number,
attending_students number,
constraint course_id_fk foreign key (course_id) references course (course_id),
constraint lectures_pk primary key (lecture_id));
INSERT INTO lectures (lecture_id, room_id, start_date, end_date, course_id, attending_students)
SELECT 1, 1,
TIMESTAMP '2022-06-20 09:00:00',
TIMESTAMP '2022-06-20 10:45:00',
3, 49 FROM DUAL UNION ALL
SELECT 2, 1,
TIMESTAMP '2022-06-21 09:00:00',
TIMESTAMP '2022-06-21 10:45:00',
3,48 FROM DUAL UNION ALL
SELECT 3, 1,
TIMESTAMP '2022-06-22 09:00:00',
TIMESTAMP '2022-06-22 10:45:00',
3,47 FROM DUAL UNION ALL
SELECT 4, 2,
TIMESTAMP '2022-06-20 11:30:00',
TIMESTAMP '2022-06-20 12:55:00',
4, 39 FROM DUAL UNION ALL
SELECT 5, 2,
TIMESTAMP '2022-06-21 11:30:00',
TIMESTAMP '2022-06-21 12:55:00',
4, 38 FROM DUAL UNION ALL
SELECT 6, 2,
TIMESTAMP '2022-06-22 11:30:00',
TIMESTAMP '2022-06-22 12:55:00',
4, 37 FROM DUAL UNION ALL
SELECT 7,3,
TIMESTAMP '2022-06-20 13:30:00',
TIMESTAMP '2022-06-20 15:55:00',
5, 23 FROM DUAL UNION ALL
SELECT 8,3,
TIMESTAMP '2022-06-21 13:30:00',
TIMESTAMP '2022-06-21 15:55:00',
5, 22 FROM DUAL UNION ALL
SELECT 9,3,
TIMESTAMP '2022-06-22 13:30:00',
TIMESTAMP '2022-06-22 15:55:00',
5, 21 FROM DUAL UNION ALL
SELECT 10,4,
TIMESTAMP '2022-06-22 17:30:00',
TIMESTAMP '2022-06-22 18:55:00',
7, 18 FROM DUAL UNION ALL
SELECT 11,4,
TIMESTAMP '2022-06-22 17:30:00',
TIMESTAMP '2022-06-22 18:55:00',
7, 17 FROM DUAL UNION ALL
SELECT 12,4,
TIMESTAMP '2022-06-22 17:30:00',
TIMESTAMP '2022-06-22 18:55:00',
7, 16 FROM DUAL;
SELECT
t.teacher_id,
t.first_name,
t.last_name,
c.course_id,
c.course_name
FROM teachers t join course c on c.teacher_id = t.teacher_id
WHERE c.topic = 'Databases'
AND c.course_id in (
select course_id
from lectures
group by course_id
order by avg(attending_students) desc
fetch first row with ties
)
ORDER BY t.teacher_id;