sql 练习(六)

11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；

SELECT DISTINCT a.sno,b.sname
  FROM score a 
  JOIN student b
    ON a.sno=b.sno
 WHERE  a.cno IN (SELECT cno
             FROM score
            WHERE sno='1001'
           )

SELECT DISTINCT a.sno,a.sname 
  FROM Student a ,score b 
 WHERE a.sno=b.sno AND b.cno 
    IN (SELECT cno FROM score WHERE sno='1001'); 

12、查询至少学过学号为“1”同学所有一门课的其他同学学号和姓名；

SELECT DISTINCT sno,cno
  FROM score 
 WHERE cno IN (SELECT cno
             FROM score
            WHERE sno='1'
           )

[wrong]13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；

 UPDATE score 
 SET degree=(SELECT AVG(degree) degree
          FROM score 
         WHERE cno IN(SELECT cno
                   FROM course
                  WHERE tno = (SELECT tno
                         FROM teacher
                        WHERE tname='叶平' )))
 WHERE sno IN( SELECT c.sno
        FROM course a
        JOIN teacher b ON a.tno=b.tno
        JOIN score c ON a.cno=c.cno
           WHERE b.tname='叶平')

[hard]14、查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名；

-- 晓彤的方法
SELECT a.sno,b.sname
  FROM 
    (SELECT sno 
      FROM score 
     WHERE cno IN(SELECT cno FROM score WHERE sno='2' )  AND sno <>'2'
     GROUP BY sno
    HAVING COUNT(*)=(SELECT COUNT(*) FROM score WHERE sno='2'))a
  JOIN student b ON a.sno=b.sno

-- 请教前辈的方法
SELECT d.sno,e.sname
  FROM (
    SELECT c.sno,COUNT(*) total 
      FROM (SELECT a.cno cno1,b.sno,b.cno cno2
          FROM (SELECT cno FROM score WHERE sno='2')a 
          LEFT JOIN score b  
                ON a.cno=b.cno
         WHERE b.sno<>'2' 
         ORDER BY sno) c
     GROUP BY c.sno)d
  JOIN student e ON d.sno=e.sno
 WHERE d.total=(SELECT COUNT(*) FROM score WHERE sno='2')
-- 原本作者的方法
SELECT a.sno,a.sname
  FROM score b ,student a
 WHERE b.sno = a.sno
   AND a.sno NOT IN (
        SELECT c.sno
          FROM score c
         WHERE c.cno NOT IN (SELECT c.cno FROM score d WHERE d.sno = 2)
        )
   AND b.sno != 2
 GROUP BY a.sno,a.sname
HAVING COUNT(*) = (SELECT COUNT(*)
             FROM score
            WHERE sno = 2
          );
-- 晓彤的方法            
SELECT DISTINCT sno 
  FROM score 
 WHERE sno NOT IN(
         SELECT t.sno 
           FROM (SELECT * 
               FROM (SELECT cno  FROM score WHERE sno IN ('2')) AS t 
               JOIN  (SELECT DISTINCT sno FROM score) AS a
                 ON 1=1
                  )t
           LEFT JOIN score AS a 
             ON t.cno=a.cno
            AND t.sno=a.sno
          WHERE a.sno IS NULL
          )
   AND sno<>'2' 

15、删除学习“叶平”老师课的SC表记录；

DELETE
SELECT *
  FROM score 
 WHERE cno IN (SELECT cno 
                 FROM course 
                WHERE tno IN (SELECT tno FROM teacher WHERE tname ='叶平' ))

16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“3”课程的同学学号、课程的平均成绩；

SELECT AVG(degree)degree,cno
  FROM score 
 WHERE cno !='3' 
 GROUP BY cno

17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,数据库,企业管理,英语,有效课程数,有效平均分

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT cno,MAX(degree)maxdegree,MIN(degree) mingree
FROM score
GROUP BY cno

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序

-- 及格率=及格人数/参考总人数
SELECT c.cno,c.passrate,d.avgdegree
FROM
(SELECT (b.num2/a.num)AS passrate,a.cno
FROM (SELECT COUNT()num,cno
FROM score
GROUP BY cno) a
JOIN (SELECT COUNT()num2,cno
FROM score
WHERE degree >='60'
GROUP BY cno) b
ON a.cno=b.cno)c
JOIN (SELECT AVG(degree)avgdegree,cno
FROM score
GROUP BY cno)d
ON c.cno=d.cno
GROUP BY c.passrate DESC,d.avgdegree ASC

20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）

SELECT c.cname,c.degree,d.passrate
FROM (
SELECT b.cname,AVG(degree)degree,b.cno
FROM score a
JOIN course b
ON a.cno=b.cno
GROUP BY b.cname,b.cno)c
JOIN (SELECT (b.num2/a.num)AS passrate,a.cno
FROM (SELECT COUNT()num,cno
FROM score
GROUP BY cno) a
JOIN (SELECT COUNT()num2,cno
FROM score
WHERE degree >='60'
GROUP BY cno) b
ON a.cno=b.cno)d
ON c.cno=d.cno