环境是mysql
11、查询‘3-105’号课程的平均分
SELECT cno,AVG(degree)
FROM score
WHERE cno='3-105'
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT AVG(degree),cno
FROM score
WHERE cno LIKE'3%'
GROUP BY cno
HAVING COUNT(*)>=5
SELECT AVG(degree),cno
FROM score
GROUP BY cno
HAVING (COUNT(1)>=5 AND cno LIKE '3%')
13、查询最低分大于70，最高分小于90的Sno列。
SELECT sno
FROM score
GROUP BY sno
HAVING MIN(degree)>=70 AND MAX(degree)<=90
14、查询所有学生的Sname、Cno和Degree列。
-- 集合思维（优选）
SELECT a.sname,b.cno,b.degree
FROM student a
JOIN score b
ON a.sno=b.sno
-- 过程思维
SELECT (SELECT sname FROM student WHERE sno = sc.sno) sname ,cno ,degree
FROM score sc
15、查询所有学生的Sno、Cname和Degree列。
-- 集合思维（优选）
SELECT b.sno,a.cname,b.degree
FROM course a
JOIN score b
ON a.cno=b.cno
-- 过程思维
SELECT sno,(SELECT cname FROM course WHERE course.cno=score.cno),degree
FROM score
16、查询所有学生的Sname、Cname和Degree列。
-- 集合思维（优选）
SELECT b.sname,a.degree,c.cname
FROM score a
JOIN student b ON a.sno=b.sno
JOIN course c ON a.cno=c.cno
-- 过程思维
SELECT (SELECT Sname FROM Student WHERE student.Sno=Score.Sno) sname
,(SELECT Cname FROM course WHERE course.Cno=score.Cno) cname
,Degree
FROM score
17、 查询“95033”班学生的平均分。
-- PS :课程号是95033，并不是班级
-- 集合思维（优选）
SELECT AVG(b.degree),b.cno
FROM student a
JOIN score b
ON a.sno=b.sno
WHERE a.class='95033'
GROUP BY cno
-- 过程思维
SELECT AVG(degree),Cno
FROM score
JOIN student
ON student.sno=score.sno
WHERE cno IN (SELECT cno FROM score WHERE class="95033")
GROUP BY cno
--18、假设使用如下命令建立了一个grade表：
SELECT *FROM grade
CREATE TABLE grade(low INT(3),upp INT(3),rank CHAR(1))
INSERT INTO grade VALUES(90,100,'A')
INSERT INTO grade VALUES(80,89,'B')
INSERT INTO grade VALUES(70,79,'C')
INSERT INTO grade VALUES(60,69,'D')
INSERT INTO grade VALUES(0,59,'E')
现查询所有同学的Sno、Cno和rank列。
-- 集合思维（优选）
SELECT a.Sno,a.Cno,b.rank
FROM score a
JOIN grade b
ON a.degree>=b.low AND a.degree<=b.upp
-- 过程思维
SELECT s.sno,s.cno
,(SELECT rank FROM grade AS g WHERE s.degree BETWEEN g.low AND g.upp)AS rank
FROM SCORE AS s
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT *
FROM score
WHERE cno = '3-105'
AND degree > (SELECT degree FROM score WHERE sno = '109' AND cno = '3-105')
20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。score(sno,cno,degree)
-- EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，
因为IN不走索引，但要看实际情况具体使用：
-- IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况
--集合思维（优选）
SELECT a.sno,a.degree,a.cno
FROM score a
LEFT JOIN (SELECT MAX(degree) degree,cno
FROM score
GROUP BY cno)b
ON a.cno = b.cno
WHERE a.degree != b.degree
AND a.sno IN(SELECT sno
FROM score
GROUP BY sno
HAVING COUNT(sno)>1)
-- lcy 过程思维
SELECT sno,degree,cno
FROM score
WHERE sno IN(SELECT sno
FROM score
GROUP BY sno
HAVING COUNT(sno)>1)
AND CONCAT(degree,sno) NOT IN(SELECT CONCAT(MAX(degree),sno)
FROM score
GROUP BY sno)
<错误写法>：原因在于当有个最高分的A同学的分数88，是B同学的最低分88分，88分会被过滤掉。
解决方法有2个（其一是拼接在一起，其二是用集合的方式：join、except）
SELECT sno,cno,degree,CONCAT(degree,sno)
FROM score
WHERE degree NOT IN (SELECT MAX(degree)
FROM score
GROUP BY sno)
AND sno IN (SELECT sno FROM score GROUP BY sno HAVING COUNT(cno)>1)
欢迎留言进行讨论 : )
