sql 练习(二)

环境是mysql

练习数据见SQL：练习的前期准备
sql 练习(一)

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.cno IN(SELECT cno
            FROM score
           GROUP BY cno
          HAVING COUNT(cno)>1)

-- lcy 过程思维           
SELECT sno,degree,cno
  FROM score 
 WHERE cno IN(SELECT cno
            FROM score
           GROUP BY cno
          HAVING COUNT(cno)>1)
   AND CONCAT(degree,cno) NOT IN(SELECT CONCAT(MAX(degree),cno) 
                                   FROM score
                  GROUP BY cno)
         
<错误写法>：原因在于当有个最高分的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) 

欢迎留言进行讨论 : )