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
