mysql列题

发布时间:2024年01月09日

1.查询学过「张三」老师授课的同学的信息

select s.*,sc.cid,c.cname,sc.score,t.tname from t_mysql_teacher t,
t_mysql_course c,
t_mysql_score sc,
t_mysql_student s
where t.tid=c.tid
and c.cid=sc.cid and sc.sid=s.sid and t.tname =‘张三’

2.查询没有学全所有课程的同学的信息

SELECT
s.sid,
s.sname, count( sc.score ) n
FROM
t_mysql_student s,
t_mysql_score sc
WHERE
s.sid = sc.sid
GROUP BY
s.sid,
s.sname
HAVING
n < ( SELECT count( 1 ) FROM t_mysql_course )

3.查询没学过"张三"老师讲授的任一门课程的学生姓名

select * from t_mysql_student where sid not in(
select sc.sid from t_mysql_teacher t,
t_mysql_course c,
t_mysql_score sc,
t_mysql_student s
where t.tid=c.tid
and c.cid=sc.cid and sc.sid=s.sid and t.tname =‘张三’
)

4.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩

SELECT
s.sid,
s.sname,
count( sc.score ) n,
round( avg( sc.score ), 2 )
FROM
t_mysql_score sc,
t_mysql_student s
WHERE
sc.sid = s.sid
AND sc.score < 60 GROUP BY s.sid, s.sname HAVING n >= 2

5.检索" 01 "课程分数小于 60,按分数降序排列的学生信息

SELECT
s.*,
sc.score from
t_mysql_score sc,
t_mysql_student s
WHERE
sc.sid = s.sid
AND sc.score < 60
AND sc.cid = ‘01’
ORDER BY
sc.score DESC

6.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT
s.sid ,s.sname,
round( avg( sc.score ), 2 ) avg,
min( CASE WHEN sc.cid = ‘01’ THEN sc.score END ) 语文,
min( CASE WHEN sc.cid = ‘02’ THEN sc.score END ) 数学,
min( CASE WHEN sc.cid = ‘03’ THEN sc.score END ) 英语

FROM
t_mysql_student s,
t_mysql_score sc
WHERE
s.sid = sc.sid
GROUP BY
s.sid,
s.sname
ORDER BY
avg DESC;

7.查询各科成绩最高分、最低分和平均分:

以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

SELECT
c.cid,
c.cname,
max( sc.score ) 最高分,
min( 最低分 ),
round( avg( sc.score ) ) 平均分,
CONCAT(
ROUND(
sum( IF ( sc.score >= 60, 1, 0 ) / count( sc.score ) * 100, 2 ),
‘%’
) 及格率,
CONCAT(
ROUND(
sum(
IF
( sc.score >= 70 AND sc.score < 80, 1, 0 ) / count( sc.score ) * 100,
2
),
‘%’
) 中等率,
CONCAT(
ROUND(
sum(
IF
( sc.score >= 80 AND sc.score < 90, 1, 0 ) / count( sc.score ) * 100,
2
),
‘%’
) 优良率,
CONCAT(
ROUND(
sum( IF ( sc.score >= 90, 1, 0 ) / count( sc.score ) * 100, 2 ),
‘%’
) 优秀率
FROM
t_mysql_score sc,
t_mysql_course c,
t_mysql_student s,

WHERE
sc.cid = c.cid
AND sc.sid = s.sid
GROUP BY
c.cid c.cname

from t_mysql_score sc,
t_mysql_course c,
t_mysql_student s
where sc.cid=c.cid
and sc.sid =s.sid
GROUP BY
c.cid,
c.cname

在这里插入图片描述

文章来源:https://blog.csdn.net/m0_74018330/article/details/135475922
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。