1.针对下面四张表,写出对应的业务逻辑sql
1、查询每个学生的学号、姓名和每门课的成绩
2、查询都学过2号同学(sid=2)学习过的课程的同学的学号
3、查询“语文(cid=1)”课程比“数学(cid=2)”课程成绩高的所有学生的学号;
4、查询平均成绩大于60分的同学的学号和平均成绩;
5、查询所有同学的学号、姓名、选课数、总成绩;
6、查询姓“李”的老师的个数;
7、查询没学过“叶平”老师课的同学的学号、姓名;
8、查询学过“语文(cid=1)”并且也学过“数学(cid=2)”课程的同学的学号、姓名;
9、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
10、查询课程编号“数学(cid=2)”的成绩比课程编号“语文(cid=1)”课程低的所有同学的学号、姓名;
11、查询所有课程成绩小于60分的同学的学号、姓名;
12、查询没有学全所有课的同学的学号、姓名;
13、按平均成绩从高到低显示所有学生的“语文“、“数学”、“英语”三门的课程成绩,按如下形式显示: 学生
ID,语文,数学,英语,有效课程数,有效平均分
14、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
15、查询男生、女生人数, 以如下形式显示:男生人数,女生人数
16、查询课程名称为“数学”,且分数低于60的学生姓名和分数
17、查询两门以上不及格课程的同学的学号及其平均成绩;
18、检索“cid=4”课程分数小于60,按分数降序排列的同学学号
19.创建课程情况汇总表,汇总表栏目包括课程ID,课程名称,选学人数,合格人数,不合格人数,平均成绩,通过率,各分数段人数。
20.查询结果包含的栏目是:学号、姓名、平均成绩、排名。
参考答案:
1.查询每个学生的学号、姓名和每门课的成绩;
SELECT
student.sid AS 学生ID,
student.sname AS 学生姓名,
(SELECT course.cname FROM course WHERE course.cid=sc.cid) AS 课程名称,
sc.score AS 成绩
FROM student,sc
WHERE student.sid = sc.sid
2、查询都学过2号同学(sid=2)学习过的课程的同学的学号
SELECT sid
FROM sc
WHERE cid IN (SELECT cid FROM sc WHERE sid=2)
GROUP BY sid
HAVING COUNT(*) = (SELECT COUNT(*) FROM sc WHERE sid=2)
3、查询“语文(cid=1)”课程比“数学(cid=2)”课程成绩高的所有学生的学号;
SELECT a.sid
FROM
(SELECT sid, score FROM sc WHERE cid=1) a,
(SELECT sid, score FROM sc WHERE cid=2) b
WHERE a.sid=b.sid AND a.score > b.score;
4、查询平均成绩大于60分的同学的学号和平均成绩;
SELECT sid, AVG(score)
FROM `sc`
GROUP BY sid
HAVING AVG(score) > 60;
5、查询所有同学的学号、姓名、选课数、总成绩;
SELECT a.sid, a.sname, COUNT(a.cid), SUM(a.score)
FROM
(SELECT student.sid, student.sname, sc.cid, sc.score FROM student JOIN sc WHERE student.sid=sc.sid) a
GROUP BY sid;
6、查询姓“周”的老师的个数;
SELECT COUNT(*) FROM `teacher` WHERE tname LIKE '周%';
7、查询没学过“叶平”老师课的同学的学号、姓名;
SELECT student.sid,student.sname
FROM student
WHERE sid NOT IN
(
SELECT DISTINCT(sc.sid)
FROM sc, course, teacher
WHERE sc.cid=course.cid AND teacher.tid=course.tid AND teacher.tname='叶平'
);
8、查询学过“语文(cid=1)”并且也学过“数学(cid=2)”课程的同学的学号、姓名;
SELECT student.sid,student.sname
FROM student, sc
WHERE student.sid=sc.sid AND sc.cid=1
AND
EXISTS
( SELECT * FROM sc AS sc_2 WHERE sc_2.sid=sc.sid AND sc_2.cid=2);
9、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
FROM student
WHERE sid IN
(
SELECT sid
FROM sc ,course ,teacher
WHERE sc.cid=course.cid AND teacher.tid=course.tid AND teacher.tname='叶平'
GROUP BY sid
HAVING COUNT(sc.cid)=
(
SELECT COUNT(cid)
FROM course,teacher
WHERE teacher.tid=course.tid AND teacher.tname='叶平'
)
);
10、查询课程编号“数学(cid=2)”的成绩比课程编号“语文(cid=1)”课程低的所有同学的学号、姓名;
SELECT sid,sname
FROM student
WHERE sid=
(
SELECT a.sid
FROM
(SELECT sid, score FROM sc WHERE cid=1) a,
(SELECT sid, score FROM sc WHERE cid=2) b
WHERE a.sid=b.sid AND a.score > b.score
)
11、查询所有课程成绩小于60分的同学的学号、姓名;
SELECT sid,sname
FROM student
WHERE sid NOT IN
(
SELECT student.sid FROM student AS s,sc WHERE s.sid=sc.sid AND score>60
);
12、查询没有学全所有课的同学的学号、姓名;
SELECT student.sid, student.sname
FROM student,sc
WHERE student.sid=sc.sid
GROUP BY sid
HAVING COUNT(cid) < (SELECT COUNT(cid) FROM course)
13、按平均成绩从高到低显示所有学生的“语文“、“数学”、“英语”三门的课程成绩,按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分
SELECT sid AS 学生ID,
(SELECT score FROM sc WHERE sc.sid=sc_2.sid AND cid=1) AS 语文,
(SELECT score FROM sc WHERE sc.sid=sc_2.sid AND cid=2) AS 数学,
(SELECT score FROM sc WHERE sc.sid=sc_2.sid AND cid=3) AS 英语,
COUNT(*) AS 有效课程数,
AVG(score)
FROM sc AS sc_2
GROUP BY sid
ORDER BY AVG(sc_2.score)
14、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
SELECT cid AS 课程ID, MAX(score) AS 最高分, MIN(score) AS 最低分
FROM sc
GROUP BY cid
15、查询男生、女生人数 :以如下形式显示:男生人数,女生人数
SELECT
(SELECT COUNT(ssex) FROM student GROUP BY ssex HAVING ssex='男') AS 男生人数,
(SELECT COUNT(ssex) FROM student GROUP BY ssex HAVING ssex='女') AS 女生人数;
16、查询课程名称为“数学”,且分数低于60的学生姓名和分数
SELECT student.sid, student.sname
FROM student,sc
WHERE student.sid=sc.sid AND cid=(SELECT cid FROM course WHERE cname='数学') AND score > 60
17、查询两门及两门以上不及格课程的同学的学号及其平均成绩
SELECT sid, AVG(score) FROM sc WHERE sid IN (SELECT sid FROM sc WHERE score < 60 GROUP BY sid HAVING COUNT(*)>1) GROUP BY sid;
18、检索“cid=4”课程分数小于60,按分数降序排列的同学学号
SELECT sid FROM sc WHERE cid=4 AND score < 60 ORDER BY score DESC
19.创建课程情况汇总表,汇总表栏目包括课程ID,课程名称,选学人数,合格人数,不合格人数,平均成绩,通过率,各分数段人数。
select
sc.cid as `课程ID`,
course.cname as `课程名称`,
count(1) as `选学人数`,
count(case when score < 60 then null else 1 end) as `合格人数`,
count(case when score < 60 then 1 else null end) as `不合格人数`,
round(avg(score)) as `平均成绩`,
round(count(case when score < 60 then null else 1 end) / count(1), 2) as `通过率`,
sum(case when score>= 85 then 1 else 0 end) as `100 - 85`,
sum(case when score>= 70 and score< 85 then 1 else 0 end) as `85 - 70`,
sum(case when score>= 60 and score<70 then 1 else 0 end) as `70 - 60`,
sum(case when score< 60 then 1 else 0 end) as `60 - 0`
from sc, course
where sc.Cid = course.Cid
group by sc.Cid;
20.查询结果包含的栏目是:学号、姓名、平均成绩、排名。
select t2.sid,
student.sname,
t2.average,
1+(select count(average) from
(
select sid, avg(score) as average
from sc
group by sid
) as t1
where average > t2.average
) as ·rank·
from
student,
(
select sid, avg(score) as average
from sc
group by sid
) as t2
where t2.sid = student.sid
order by average desc;