成都企业网站设计制作辽宁省建设工程信息网诚信库

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

在这里插入图片描述