MySQL查询练习--答案2

本文主要是介绍MySQL查询练习--答案2，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

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

SELECT st.* FROM student st,
(SELECT DISTINCT a.sno FROM
(SELECT * FROM sc) a,
(SELECT * FROM sc WHERE sc.sno='s001') b
WHERE a.cno=b.cno) h
WHERE st.sno=h.sno AND st.sno<>'s001'

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

SELECT * FROM sc
LEFT JOIN student st
ON st.sno=sc.sno
WHERE sc.sno<>'s001'
AND sc.cno IN
(SELECT cno FROM sc
WHERE sno='s001')

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

UPDATE sc c1 SET score=(
SELECT AVG(s.score) score FROM teacher t,course c,sc s WHERE t.tno=c.tno AND t.tname='谌燕' AND s.cno=c.cno GROUP BY c.cno)
WHERE c1.cno IN(
SELECT cno FROM course a,teacher b
WHERE a.tno=b.tno
AND b.tname='谌燕');

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

 SELECT sno FROM SC WHERE sno<>'s001'AND cno IN (SELECT cno FROM SC WHERE sno='s001') 
GROUP BY sno HAVING COUNT(SC.cno)=(SELECT COUNT(*) FROM SC WHERE sno='s001');

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

DELETE FROM sc
WHERE sc.cno IN
(
SELECT cno FROM course c
LEFT JOIN teacher t  on  c.tno=t.tno
WHERE t.tname='谌燕'
)

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

INSERT INTO sc (sno,cno,score)
SELECT DISTINCT st.sno,sc.cno,(SELECT AVG(score)FROM sc WHERE cno='c002')
FROM student st,sc
WHERE NOT EXISTS
(SELECT * FROM sc WHERE cno='c002' AND sc.sno=st.sno) AND sc.cno='c002';

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

SELECT cno 课程ID ,MAX(score) 最高分,MIN(score)最低分 FROM sc GROUP BY cno;

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

SELECT cno,AVG(score),SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END)/COUNT(*)
AS 及格率
FROM sc GROUP BY cno
ORDER BY AVG(score) , 及格率 DESC

19、查询不同老师所教不同课程平均分从高到低显示

SELECT MAX(t.tno),MAX(t.tname),MAX(c.cno),MAX(c.cname),c.cno,AVG(score) FROM sc , course c,teacher t
WHERE sc.cno=c.cno AND c.tno=t.tno
GROUP BY c.cno
ORDER BY AVG(score) DESC

20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

SELECT sc.cno,c.cname,
SUM(case  WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS "[100-85]",
SUM(case  WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS "[85-70]",
SUM(case  WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS "[70-60]",
SUM(case  WHEN score <60 THEN 1 ELSE 0 END) AS "[<60]"
FROM sc, course c
WHERE sc.cno=c.cno
GROUP BY sc.cno ,c.cname;

这篇关于MySQL查询练习--答案2的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！