PTA sql 语句练习1

目录

R10-34 查询非计算机系中比计算机系任意一个学生年龄小的学生

R10-40 查询平均成绩超过所有学生选课平均成绩的学生学号和平均成绩，列名用中文显示

R10-41 查询李勇选修了哪些课程，列出课程号

R10-42 查询和李勇专业相同的其他学生信息

R10-43 查询选修的各课程平均成绩高于85分的学生学号、姓名和专业

 R10-44 查询计算机科学专业刘晨选修课程的课程名

R10-45 查询选修了张兵老师授课的学生学号、课程号和成绩

R10-46 查询选修C01课且成绩高于此课程平均成绩的学生姓名

R10-47 查询修课平均成绩最高的学生学号

---

R10-34 查询非计算机系中比计算机系任意一个学生年龄小的学生

select *
from student S
where S.sage <= (
    select min(sage)
    from student S 
    where S.sdept = '计算机系'
)
and S.sdept not in ('计算机系')

R10-40 查询平均成绩超过所有学生选课平均成绩的学生学号和平均成绩，列名用中文显示

select sno as '学号' , AVG(grade) as '平均成绩'
from SC
group by sno 
having AVG(grade) > (
    select AVG(grade)
    from SC
)

R10-41 查询李勇选修了哪些课程，列出课程号

select cno
from Student S
join SC on S.sno = SC.sno
where S.sname = '李勇'

R10-42 查询和李勇专业相同的其他学生信息

select *
from Student as S 
where S.major in (
    select  major
    from Student 
    where sname = '李勇'
)

R10-43 查询选修的各课程平均成绩高于85分的学生学号、姓名和专业

select sname
from Student as S inner join SC as s on S.sno = s.sno
where s.cno = 'C01' and s.grade > (
    select AVG(grade)
    from SC
    group by cno
    having cno = 'C01'
)

 R10-44 查询计算机科学专业刘晨选修课程的课程名

select Course.cname
from Student , SC , Course
where Student.sname = '刘晨' and Student.sno = SC.sno and SC.cno = Course.cno

R10-45 查询选修了张兵老师授课的学生学号、课程号和成绩

SELECT s.sno, sc.cno, sc.grade
FROM Student s
INNER JOIN SC sc ON s.sno = sc.sno
INNER JOIN Teacher t ON sc.tno = t.Tno
WHERE t.Tname = '张兵';

R10-46 查询选修C01课且成绩高于此课程平均成绩的学生姓名

select sname
from Student as S inner join SC as s on S.sno = s.sno
where s.cno = 'C01' and s.grade > (
    select AVG(grade)
    from SC
    group by cno
    having cno = 'C01'
)

R10-47 查询修课平均成绩最高的学生学号

SELECT sno
FROM (
    SELECT sno, AVG(grade) as avg_grade
    FROM SC
    GROUP BY sno
) AS student_avg
ORDER BY avg_grade DESC
LIMIT 1;