这项练习的解决方案正确吗？

Question

我试图在操作员中使用2个练习来解决这项练习，但是我有疑问：此查询是否将老师返回他/她相应的课程ID（无需使用连接）？还是我需要在最令人兴趣的查询中加入（我从“课程C”中的第一个查询）中加入？ 这是我的代码：

SELECT C.TCODE, C.CCODE
FROM COURSE C
WHERE C.TCODE NOT IN(
SELECT TCODE
FROM COURSE
WHERE TOPIC <> 'DATABASE'
)
AND C.CCODE IN (
SELECT C1.CCODE
FROM LECTURE L1, COURSE C1
WHERE L1.CCODE = C1.CCODE
GROUP BY CCODE, TCODE
HAVING AVG(AttendingStudent#) = (
SELECT MAX(C3.MEDIE)
FROM (
SELECT C2.TCODE, C2.CCODE, AVG(AttendingStudent#)
FROM LECTURE L2, COURSE C2
WHERE L2.CCODE = C2.CCODE
GROUP BY C2.TCODE, C2.CCODE
) C3
WHERE C3.TCODE = C1.TCODE and C3.CCode = C1.CCode
) 
)

预先感谢。

Answer 1

由于您没有提供任何数据和预期的输出，因此我编造了一些数据，并将列名称更改为对我自己更有意义的东西。随意改造您的特定需求。该解决方案可与Oracle一起使用。

对于测试，您可以使用Oracle Live SQL创建一个帐户。它是一个免费的站点，允许用户访问Oracle数据库。我强烈建议这样做以测试您提出的

代码您要放入。如果您不确定该怎么做，请发布另一个问题。

在Stackoverflow上注意以发布格式的代码以供其他人阅读您，您可以将启动和结尾的代码封装，并连续3个~~~

最后您绝对不应该发布屏幕截图，因为人们通常不愿意在您的表结构中键入您的表格和数据。

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;

TEACHER_ID  FIRST_NAME  LAST_NAME   COURSE_ID   COURSE_NAME
4   Debra   Downing 3   Design