mysql exercise ------ one 数据库练习参考

mysql exercise one

大家可以采用SQLyog、Navicat 数据库可视化工具进行查看

用于以下试题使用的数据表地址:
mysql exercise ------ one 数据库练习参考资源地址

一：

1. 查询" 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
   SELECT * FROM (SELECT * FROM SC WHERE SC.CId=“01”)a,(SELECT * FROM SC WHERE SC.CId=“02”)b,Student s WHERE a.score > b.score AND a.SId = b.SId AND a.SId = s.SId;
   1.1 查询同时存在” 01 “课程和” 02 “课程的情况
   SELECT * FROM (SELECT * FROM SC WHERE SC.CId=“01”) a,(SELECT * FROM SC WHERE SC.CId=“02”)b WHERE a.SId = b.SId;
   1.2 查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )
   SELECT * FROM (SELECT * FROM SC WHERE SC.CId=“02”)a1 LEFT JOIN (SELECT * FROM SC WHERE SC.CId=“01”)b1 ON a1.SId = b1.SId;
   1.3 查询不存在” 01 “课程但存在” 02 "课程的情况
   SELECT * FROM (SELECT * FROM SC WHERE SC.CId=“02”)a2 LEFT JOIN (SELECT * FROM SC WHERE SC.CId=“01”)b2 ON a2.SId = b2.SId WHERE b2.SId IS NULL;

---

此代码查询的是什么参数？（内容已进行实践，可实现复杂查询）
SELECT * FROM(SELECT t.*,SC.CId,SC.Score FROM (SELECT * FROM Student WHERE sid IN 
(SELECT t1.SId FROM (SELECT SC.Sid,SC.score FROM SC WHERE SC.CId = 01) AS t1 JOIN 
(SELECT SC.SId,SC.score FROM SC WHERE SC.CId = 02) AS t2
ON t1.sid=t2.sid WHERE t1.score>t2.score)) AS t , SC WHERE t.Sid=SC.sid)aa,(SELECT t.*,SC.CId,SC.Score FROM (SELECT * FROM Student WHERE sid IN 
(SELECT t1.SId FROM (SELECT SC.Sid,SC.score FROM SC WHERE SC.CId = 01) AS t1 JOIN 
(SELECT SC.SId,SC.score FROM SC WHERE SC.CId = 02) AS t2
ON t1.sid=t2.sid WHERE t1.score>t2.score)) AS t , SC WHERE t.Sid=SC.sid)bb
WHERE aa.SId=bb.SId AND aa.CId="01" AND bb.CId="02";

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT s.SId,s.Sname,a.GPA FROM Student s,(SELECT SId,AVG(score)GPA FROM SC GROUP BY SId HAVING GPA>=60)a WHERE s.SId=a.SId;
3. 查询在 SC 表存在成绩的学生信息
SELECT SC.score,SC.SId,s.* FROM SC INNER JOIN Student s ON s.SId=SC.SId

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT s.SId,s.Sname,a.num,a.total FROM Student s LEFT JOIN (SELECT SId,COUNT(CId)num,SUM(score)total FROM SC GROUP BY SId)a ON s.SId= a.SId;
4.1 查有成绩的学生信息
SELECT s.SId,s.Sname,s.Sage,s.Ssex,a.num,a.total FROM Student s,(SELECT SId,COUNT(CId)num,SUM(score)total FROM SC GROUP BY SId)a WHERE s.SId= a.SId;

5.查询「李」姓老师的数量
SELECT COUNT(*) FROM Teacher WHERE Tname LIKE “李%”;

6.查询学过「张三」老师授课的同学的信息
SELECT * FROM Student s,SC sc,
(SELECT CId FROM (SELECT TId FROM Teacher WHERE Tname = “张三”)a,Course b WHERE a.TId=b.TId)c WHERE s.SId=sc.SId AND c.CId=sc.CId;

7.查询没有学全所有课程的同学的信息
SELECT * FROM Student AS s,(SELECT DISTINCT SId FROM SC WHERE CId IN (SELECT CId FROM SC WHERE SId=1)) AS a WHERE s.SId=a.SId;

8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT * FROM Student WHERE SId NOT IN (SELECT SId FROM SC GROUP BY SId HAVING COUNT(CId)=(SELECT COUNT(CId)AS c FROM Course));

9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
SELECT sid FROM SC GROUP BY sid HAVING COUNT(cid)=(SELECT COUNT(CId) FROM SC WHERE sid=01);

10.查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT * FROM Student WHERE SId NOT IN(SELECT z.SId FROM Student AS z,(SELECT s.SId FROM SC AS s ,
(SELECT c.Cid FROM Course AS c ,(SELECT TId FROM Teacher WHERE Tname=‘张三’)
AS a WHERE c.TId=a.Tid) AS b WHERE s.CId= b.CId) AS v WHERE z.SID=v.SId);

11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT * FROM Student s,(SELECT COUNT(sid) AS b ,sid FROM (SELECT * FROM SC WHERE score<60)a GROUP BY sid HAVING b>=2)q,(SELECT sid,AVG(score)r
FROM(SELECT * FROM SC WHERE score<60)t GROUP BY sid)u WHERE q.sid=s.sid AND u.sid=s.sid

12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT s.*,a.cid,a.score FROM Student s LEFT JOIN (SELECT * FROM SC WHERE SC.Cid=‘01’ AND score<60)a ON s.SID=a.sid WHERE score IS NOT NULL ORDER BY score DESC;

13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT SId,AVG(score)“平均成绩” FROM SC GROUP BY SC.SId ORDER BY 平均成绩 DESC

SELECT * FROM (SELECT SId,AVG(score)cc FROM SC GROUP BY SC.SId)a RIGHT JOIN Student s ON s.SId=a.sid ORDER BY cc DESC;

14.查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，
优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT c.c_id,c.c_name,MAX(sc.s_score) “最高分”,MIN(sc.s_score) “最低分”,AVG(sc.s_score) “平均分”
,((SELECT COUNT(s_id) FROM score WHERE s_score>=60 AND c_id=c.c_id )/(SELECT COUNT(s_id) FROM score WHERE c_id=c.c_id)) “及格率”
,((SELECT COUNT(s_id) FROM score WHERE s_score>=70 AND s_score<80 AND c_id=c.c_id )/(SELECT COUNT(s_id) FROM score WHERE c_id=c.c_id)) “中等率”
,((SELECT COUNT(s_id) FROM score WHERE s_score>=80 AND s_score<90 AND c_id=c.c_id )/(SELECT COUNT(s_id) FROM score WHERE c_id=c.c_id)) “优良率”
,((SELECT COUNT(s_id) FROM score WHERE s_score>=90 AND c_id=c.c_id )/(SELECT COUNT(s_id) FROM score WHERE c_id=c.c_id)) “优秀率”
FROM course c
LEFT JOIN score sc ON sc.c_id=c.c_id
GROUP BY c.c_id

二：

1.检索至少选修两门课程的学生学号
#select SId from SC group by SId having COUNT(CId)>=2;

2.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
#select CId,count(SId) from SC group by CId having count(SId)>5;

3.查询每门功成绩最好的前两名
SELECT CId,MAX(score) FROM (SELECT cid,score FROM SC ORDER BY cid,score DESC)a GROUP BY CId;

SELECT a.* FROM SC AS a LEFT JOIN SC AS b ON a.cid = b.cid AND a.score<b.score
GROUP BY a.cid,a.sid HAVING COUNT(b.cid)<2 ORDER BY a.cid;

4.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT s.*,score FROM Student s RIGHT JOIN
(SELECT * FROM SC WHERE
CId=(SELECT CId FROM Course WHERE
TId=(SELECT TId FROM Teacher WHERE Tname=“张三”)))a
ON a.SId=s.SId
WHERE
score=(SELECT MAX(score) FROM SC WHERE
CId=(SELECT CId FROM Course WHERE
TId=(SELECT TId FROM Teacher WHERE Tname=“张三”)));

5.查询每门课程被选修的学生数
#select CId,count(SId) from SC group by CId;

6.查询出只选修两门课程的学生学号和姓名
#select s.sid,s.sname,a.b from Student s join (SELECT SId,COUNT(*)b FROM SC GROUP BY SId HAVING COUNT(CId)=2)a on a.sid=s.SId;

7.查询男生、女生人数
#select Ssex,count(Ssex) from Student group by Ssex;

8.查询名字中含有「风」字的学生信息
#select * from Student where Sname like “%风%”;

9.查询同名同姓学生名单，并统计同名人数
#select Sname,count(Sname)a from Student group by Sname having a>1;

10.查询 1990 年出生的学生名单
#select * from Student where Sage like “1990%”;

11.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
#select CId,avg(score)a from SC group by CId order by a desc,CId;

12.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT s.SId,s.Sname,AVG(score) FROM Student s JOIN SC ON s.SId=SC.SId GROUP BY sc.SId
HAVING AVG(score)>=85;

13.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT s.Sname,a.score FROM Student s JOIN
(SELECT * FROM SC WHERE
CId=(SELECT CId FROM Course WHERE Cname=“数学”) AND score<60)a
ON s.SId=a.SId;

14.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select b.sid,cid,score from sc a right join student b on a.sid=b.sid;

15.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select distinct a.sname,b.cname,c.score from student a inner join course b join
(select * from sc where score>70)c on a.sid=c.sid and c.cid=b.cid;

16.查询不及格的课程
#SELECT * FROM SC WHERE score<60;

17.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT * FROM SC WHERE CId=“01” AND score>=80;

18.求每门课程的学生人数
#select CId,count(SId) from SC group by CId;

在这里欢迎大家的点赞、关注、评论，以此来促进大家互相学习交流，同时可以让新加入的小伙伴更快的了解新知识！！!

以上内容如有侵权，请联系作者进行删除

≧◠◡◠≦ 1分2分都是爱，感谢已经打赏的老板，和正在打赏的老板们 ≧◠◡◠≦