【SQL练习】现在有三张数据表，一张为学生信息表students，一张为学生分数表score，还有一张为年级课程表class，表结构如下：

现在有三张数据表，一张为学生信息表students，一张为学生分数表score，还有一张为年级课程表class，表结构如下：

create table students(
       stuId varchar2(3),
       name varchar2(20),
       age number(3),
       class number(1)
);

-- 添加注释
comment on table students is '学生信息表';
comment on column students.stuId is '学号';
comment on column students.name is '姓名';
comment on column students.age is '年龄';
comment on column students.class is '班级';
insert into students (stuId, name, age, class) 
values ('001', '张三', 6, 1);
insert into students (stuId, name, age, class) 
values ('002', '李四', 7, 2);
insert into students (stuId, name, age, class) 
values ('003', '王五', 8, 3);
commit;

create table scores(
     stuId varchar2(3),
     course varchar2(20),
     score number(3)  
)

-- 添加注释
comment on table scores is '学生成绩表';
comment on column scores.stuId is '学号';
comment on column scores.course is '课程';
comment on column scores.score is '成绩';

insert into scores (stuId, course, score) values ('001', '语文', 88);
insert into scores (stuId, course, score) values ('001', '数学', 79);
insert into scores (stuId, course, score) values ('002', '数学', 85);
insert into scores (stuId, course, score) values ('003', '语文', 95);
insert into scores (stuId, course, score) values ('003', '数学', 80);
insert into scores (stuId, course, score) values ('003', '英语', 60);
commit;

create table classs(
       class number(1),
       course varchar2(20)
)

-- 添加注释
comment on table classs is '学生班级表';
comment on column classs.class is '班级';
comment on column classs.course is '课程';

insert into classs (class, course) values (1, '语文');
insert into classs (class, course) values (1, '数学');
insert into classs (class, course) values (2, '语文');
insert into classs (class, course) values (2, '数学');
insert into classs (class, course) values (3, '语文');
insert into classs (class, course) values (3, '数学');
insert into classs (class, course) values (3, '英语');
commit;

1.用一条sql查询出每个年级参加每门科目考试的总人数及平均成绩。

SELECT 
    s.class AS 年级,
    sc.course AS 科目,
    COUNT(sc.stuId) AS 总人数,
    ROUND(AVG(sc.score), 2) AS 平均成绩
FROM scores sc
JOIN students s ON sc.stuId = s.stuId
GROUP BY s.class, sc.course
ORDER BY s.class, sc.course;

第一个查询：通过连接students和scores表，按年级和科目分组，计算每组的计数和平均分数。

select st.*, sc.course, sc.score,
count(*) over(partition by st.class, sc.course) 总人数, 
avg(sc.score) over(partition by st.class, sc.course) 平均成绩 
from students st 
inner join scores sc on st.stuid = sc.stuid;

2.用一条sql查询出每个年级参加了所有科目考试的学生的总人数。

SELECT 
    s.class AS 年级,
    COUNT(DISTINCT s.stuId) AS 总人数
FROM students s
WHERE NOT EXISTS (
    SELECT c.course 
    FROM classs c 
    WHERE c.class = s.class
    AND NOT EXISTS (
        SELECT 1 
        FROM scores sc 
        WHERE sc.stuId = s.stuId 
        AND sc.course = c.course
    )
)
GROUP BY s.class
ORDER BY s.class;

第二个查询：使用双重NOT EXISTS来确保学生参加了该年级的所有课程（通过classs表定义）。

with t1 as (select distinct st.class, st.stuid, count(sc.stuid) over(partition by sc.stuid) num
from students st inner join scores sc on st.stuid = sc.stuid order by st.class)
select t1.class, sum(case when t1.num = 3 then 1 else 0 end) over(partition by t1.class) total from t1;

3.用一条sql查询2年级参加了所有科目考试并且每门课程的成绩都在80分以上的学生信息。

SELECT s.stuId AS 学号, s.name AS 姓名, s.age AS 年龄, s.class AS 班级
FROM students s
WHERE s.class = 2
AND NOT EXISTS (
    SELECT c.course 
    FROM classs c 
    WHERE c.class = s.class
    AND NOT EXISTS (
        SELECT 1 
        FROM scores sc 
        WHERE sc.stuId = s.stuId 
        AND sc.course = c.course
        AND sc.score >= 80
    )
);

第三个查询：针对2年级学生，同样使用双重NOT EXISTS来确保学生参加了所有课程且每门成绩≥80分。

with t1 as (select st.class, st.stuid, sc.course, sc.score, count(sc.stuid) over(partition by sc.stuid) num
from students st inner join scores sc on st.stuid = sc.stuid where st.class = 2),
t2 as (select t1.stuid, t1.num, min(t1.score) minScore from t1 group by t1.stuid, t1.num)
select t2.stuid from t2 where t2.minScore >= 80 and t2.num = 3;