MYSQL 练习题-part2

-- 11、查询‘3-105’号课程的平均分。
SELECT AVG(DEGREE) FROM SCORE
WHERE CNO='3-105'


-- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT CNO,AVG(DEGREE) FROM SCORE
WHERE CNO LIKE '3%'
GROUP BY CNO HAVING COUNT(SNO)>=5


-- 13、查询最低分大于70，最高分小于90的Sno列。
SELECT SNO FROM SCORE
WHERE DEGREE BETWEEN 70 AND 90 
-- 错的，因为这样选出来的只是某门课中成绩符合条件的学生，不是一个每门课都符合条件的


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 AS A
JOIN SCORE AS B
ON A.SNO=B.SNO


-- 15、查询所有学生的Sno、Cname和Degree列。
SELECT A.CNAME,B.SNO,B.DEGREE
FROM COURSE AS A
JOIN SCORE AS B
ON A.CNO=B.CNO


-- 16、查询所有学生的Sname、Cname和Degree列。
SELECT A.Sname,B.Cname,C.Degree
FROM STUDENT AS A
JOIN COURSE AS B
JOIN SCORE AS C
ON A.SNO=C.SNO AND B.CNO=C.CNO


-- 17、查询“95033”班所选课程的平均分。
SElECT avg(A.Degree) 
from SCORE AS A
JOIN STUDENT AS B
ON A.SNO=B.SNO
WHERE B.CLASS=95033


-- 18、假设使用如下命令建立了一个grade表：
USE test1
CREATE TABLE grade
(
low varchar(3),
upp varchar(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');
commit;
SELECT * FROM grade
-- 现查询所有同学的Sno、Cno和rank列。
SELECT A.SNO,A.CNO,B.RANK 
FROM SCORE AS A,GRADE AS B 
WHERE A.DEGREE BETWEEN B.LOW AND B.UPP 


-- 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT A.* 
FROM SCORE AS A 
JOIN SCORE AS B 
WHERE A.CNO='3-105'
AND B.CNO='3-105' 
AND B.SNO='109' 
AND A.DEGREE>B.DEGREE;


-- 20.查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
SELECT SNO FROM SCORE
WHERE DEGREE<(SELECT MAX(DEGREE) FROM SCORE)
GROUP BY SNO HAVING COUNT(CNO)>=2