2查询20070102号学生所选秀修课程的课程号和成绩

SELECT couid,score
FROM scores WHERE stuid=“20070102”;

3查询20070102号学生的学号、姓名、性别、以及所选修的课程名和成绩

SELECT stu.stuid,stuname,sex,couname,score
FROM scores s join stu on stu.stuid=s.stuid
join course c on s.couid=c.couid
WHERE stu.stuid=“20070102”;

4查询选修了"英语"课程的学生

SELECT stu.stuid,stuname
FROM course join scores on scores.couid=course.couid
join stu on scores.stuid=stu.stuid
WHERE couname=“英语”;

5查询出“电子信息系”所有学生的学号、姓名以及选修的课程名和成绩

SELECT stu.stuid,stuname,couname,score
FROM course join scores on scores.couid=course.couid
join stu on scores.stuid=stu.stuid
join dep on stu.depid=dep.depid
WHERE depname=“电子信息系”;

5查询出所有学生的学号,姓名以及选修的课程名和成绩

SELECT stu.stuid,stuname,couname,score
FROM course join scores on scores.couid=course.couid
join stu on scores.stuid=stu.stuid;

6查询“电子信息系”且选修了“高等数学”课程的学生的学号、姓名及成绩

SELECT stu.stuid,stuname,score
FROM scores join stu on scores.stuid=stu.stuid
join course on course.couid=scores.couid
WHERE depid=“1001” and couname=“高等数学”;

7统计各系的学生人数

SELECT depname,COUNT(stuname)
FROM dep join stu on dep.depid=stu.depid
GROUP BY depname
HAVING COUNT(stuname);

8统计各门课程的平均分

SELECT couname,AVG(score)
FROM course JOIN scores ON course.couid=scores.couid
GROUP BY couname;

9查询各个系各门课程的平均成绩

SELECT depname,couname,AVG(score)
FROM course JOIN scores ON course.couid=scores.couid
JOIN stu ON scores.stuid=stu.stuid
JOIN dep ON stu.depid=dep.depid
GROUP BY depname,couname;

10 查找选修的课程中超过2门成绩在80分以上的学生的学号和成绩高于80分的门数

SELECT stu.stuid,COUNT(score>80)
FROM stu JOIN scores on stu.stuid=scores.stuid
WHERE score>80
GROUP BY stu.stuid
HAVING count(couid)>2;

11请查询出三好生的候选人学号，三好生候选人需要满足的条件是：其各科平均成绩不低于85分且没有不及格的科目

SELECT stu.stuid
FROM stu join scores s on s.stuid=stu.stuid
WHERE score>60
GROUP BY couid
HAVING AVG(score)>=85;

12求英语课程的平均成绩

SELECT AVG(score)
FROM course JOIN scores ON course.couid=scores.couid
WHERE couname=“英语”
GROUP BY couname;

13 请查询出一门课程都没有选修的学生学号和姓名

SELECT stu.stuid,stuname
FROM stu join scores s on stu.stuid=s.stuid
WHERE couid NOT IN
(SELECT DISTINCT couid from scores);

14请查询出没有学生选修的课程号和课程名

SELECT c.couid,couname
FROM course c join scores s on c.couid=s.couid
WHERE c.couid NOT IN
(SELECT couid from course);