SQL语句学习 练习题（三）

有表SC，内容如下：

SId	CId	score
1	01	80
1	02	90
2	01	99
2	02	70
2	03	60
3	01	80
3	02	80
4	02	80
4	03	80
5	01	50

执行语句：

SELECT s1.SId，s1.CId,s2.SId,s2.CId
FROM SC s1 RIGHT JOIN SC s2 ON s1.SId = '1' AND s1.CId = s2.CId;

  得到：

执行语句：

#删除1同学的信息
SELECT SELECT s1.SId，s1.CId,s2.SId,s2.CId
FROM SC s1 RIGHT JOIN SC s2 ON s1.SId = '1' AND s1.CId = s2.CId
WHERE s2.SId != '1';   #存疑，认为应该时s1.SId

得到：

 执行语句：

#删掉与1同学所学科目数目不等的学生信息，即删除学习科目不为2的学生信息
SELECT SELECT s1.SId，s1.CId,s2.SId,s2.CId
FROM SC s1 RIGHT JOIN SC s2 ON s1.SId = '1' AND s1.CId = s2.CId
WHERE s2.SId = '1'
HAVING COUNT(s2.SId) = (SELCET COUNT(1) FROM SC WHERE SId = '1');

 得到：

 执行语句：

#排除值为NULL的信息，COUNT计数时不会计入NULL
SELECT SELECT s1.SId，s1.CId,s2.SId,s2.CId
FROM SC s1
RIGHT JOIN SC s2 ON s1.SId = '1' AND s1.CId = s2.CId AND COUNT(s2.SId) = COUNT(s1.SId)
WHERE s2.SId = '1'
HAVING COUNT(s2.SId) = (SELCET COUNT(1) FROM SC WHERE SId = '1');

 得到：