sql 练习(五)

环境是mysql

练习数据见SQL：练习的前期准备
sql 练习(一)
sql 练习(二)
sql 练习(三)
sql 练习(四)
*41、查询“1”课程比“2”课程成绩高的所有学生的学号；

SELECT a.sno
  FROM (SELECT * FROM score WHERE cno='1') a
  JOIN (SELECT * FROM score WHERE cno='2') b
    ON a.sno=b.sno
 WHERE a.degree>b.degree

  SELECT a.Sno
    FROM (SELECT sno,degree FROM score WHERE Cno='1') a
        ,(SELECT sno,degree FROM score WHERE Cno='2') b 
   WHERE a.sno=b.sno 
     AND a.degree>b.degree

42、查询平均成绩大于60分的同学的学号和平均成绩；

SELECT sno,AVG(degree)
  FROM score
 GROUP BY sno 
HAVING AVG(degree)>60

43、查询所有同学的学号、姓名、选课数、总成绩；

SELECT a.sname,a.sno,b.coursenum,b.totaldegree
  FROM student a
  LEFT JOIN (SELECT COUNT(*)AS coursenum,SUM(degree) AS totaldegree,sno
               FROM score
              GROUP BY sno) b
    ON a.sno=b.sno
         
SELECT a.sno,a.Sname,COUNT(b.cno),SUM(degree) 
  FROM Student a
  LEFT JOIN score b ON a.sno=b.sno
 GROUP BY a.sno,Sname 

44、查询姓“李”的老师的个数；

SELECT COUNT(*),tname
  FROM teacher 
 WHERE tname LIKE "李%" 

45、查询没学过“叶平”老师课的同学的学号、姓名；

SELECT sno,sname
  FROM student
 WHERE sno NOT IN (
            SELECT sno 
              FROM score 
             WHERE cno IN( SELECT cno 
                             FROM course  
                            WHERE cno IN (SELECT tno
                            FROM teacher
                           WHERE tname='叶平')))

46、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

SELECT a.sno,a.sname
  FROM student a
  JOIN (SELECT score.sno
         FROM score
         JOIN (SELECT sno FROM score WHERE cno=2)c
       ON score.sno=c.sno
        WHERE score.cno=1 )b
    ON a.sno=b.sno
    
SELECT a.sno,a.Sname 
  FROM Student a ,score b 
 WHERE a.sno=b.sno AND b.cno='1'
   AND EXISTS( SELECT sno FROM score c WHERE c.sno=b.sno AND c.Cno='2')

47、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

-- my way
SELECT sno,sname
  FROM student
 WHERE sno IN(SELECT sno 
                FROM score 
               WHERE cno IN(SELECT cno 
                  FROM course 
                 WHERE tno IN (SELECT tno
                         FROM teacher
                        WHERE tname='叶平')))
SELECT sno,Sname 
  FROM Student 
  WHERE sno IN (SELECT sno
                  FROM score ,Course ,Teacher 
                 WHERE score.cno=Course.cno 
                   AND Teacher.tno=Course.tno 
                   AND Teacher.Tname='叶平' 
                 GROUP BY sno 
                HAVING COUNT(score.cno)=(SELECT COUNT(cno) 
                                           FROM Course,Teacher 
                                          WHERE Teacher.tno=Course.tno
                                            AND Tname='叶平')); 

48、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

SELECT sno,sname
 FROM student 
WHERE sno IN (SELECT a.sno
           FROM (SELECT degree,sno FROM score WHERE cno='2')a
           JOIN (SELECT degree,sno FROM score WHERE cno='1')b
             ON a.sno=b.sno
          WHERE a.degree<b.degree)

49、查询[所有]课程成绩小于60分的同学的学号、姓名；

SELECT sno,Sname 
 FROM Student 
WHERE sno NOT IN (SELECT b.sno
                FROM score b 
               WHERE b.degree>60); 

50、查询[没有]学全所有课的同学的学号、姓名；

-- 方法一

SELECT a.sno,a.sname
  FROM student a
  JOIN (SELECT sno 
      FROM score
     GROUP BY sno 
    HAVING COUNT(*)<(SELECT COUNT(*) FROM course))b
    ON a.sno=b.sno
    
-- 方法二

SELECT a.sno,a.Sname 
  FROM Student a,score b 
 WHERE a.sno=b.sno
 GROUP BY  a.sno,a.Sname 
HAVING COUNT(b.sno) <(SELECT COUNT(cno) FROM Course)