# 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

• 序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 115,262评论 1 230
• 序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 49,625评论 1 197
• 文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 70,059评论 0 162
• 文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
开封第一讲书人阅读 34,527评论 0 123
• 正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 41,293评论 0 204
• 文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 34,196评论 1 121
• 那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
沈念sama阅读 26,219评论 2 204
• 文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 25,388评论 0 116
• 想象着我的养父在大火中拼命挣扎，窒息，最后皮肤化为焦炭。我心中就已经是抑制不住地欢快，这就叫做以其人之道，还治其人...
爱写小说的胖达阅读 24,343评论 5 168
• 序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 28,349评论 0 176
• 正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
茶点故事阅读 25,648评论 1 165
• 正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 26,900评论 1 173
• 白月光回国，霸总把我这个替身辞退。还一脸阴沉的警告我。[不要出现在思思面前， 不然我有一百种方法让你生不如死。]我...
爱写小说的胖达阅读 21,257评论 0 24
• 序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 23,894评论 2 162
• 正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 27,611评论 3 168
• 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 22,604评论 0 4
• 文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
开封第一讲书人阅读 22,784评论 0 111
• 我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
沈念sama阅读 28,799评论 2 183
• 正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 29,218评论 2 184