mySql随堂练习(3)

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mySql随堂练习(3)，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3165字，纯文字阅读大概需要5分钟。

内容图文

insert into student
values("9512101","李1","男",19,"计算机系"),("9512102","刘晨","男",20,"计算机系"),
("9512103","王2","女",20,"计算机系"),("9512103","王敏","女",20,"计算机系"),
("9521101","张3","男",22,"信息系"),("9521102","吴宾","女",21,"信息系"),
("9521103","张4","男",20,"信息系"),("9531101","钱小力","女",18,"数学系"),
("9531102","王大","男",19,"数学系");

insert into course
values("c01","计算机文化学",70),("c02","VB",90),
("c03","计算机网络",80),("c04","数据库基础",108),
("c05","高等数学",180),("c06","数据结构",72);

insert into sc
values("9512101","c01",90),("9512101","c02",86),
("9512101","c06",null),("9512102","c02",78),
("9512102","c04",66),("9521102","c01",82),
("9521102","c02",75),("9521102","c04",92),
("9521102","c05",50),("9521103","c02",68),
("9521103","c06",null),("9531101","c01",80),
("9531101","c05",95),("9531102","c05",85);

#查询C01号课程成绩最高的分数

select max(grade) from sc where cno="c01";

#查询学生都选修了哪些课程,要求列出课程号

SELECT Cname AS 学生选修的课程,Cno AS 课程号
FROM course
WHERE Cno IN(SELECT DISTINCT Cno FROM SC); #DISTINCT用来去除重复

#统计每门课程的修课人数和考试最高分

SELECT course.cno,cname,COUNT(sno) AS 选课人数,MAX(grade) AS 最高分
FROM course
LEFT JOIN sc ON course.cno = sc.cno
GROUP BY sc.cno
ORDER BY course.cno;

#统计每个学生的选课门数,并按选课门数的递增顺序显示结果

select student.sno,sname,count(cno) as 选课门数 
from student 
left join sc on student.sno=sc.sno 
group by student.sno
order by 选课门数;

#查询选课门数超过2门的学生的平均成绩和选课门数

select student.sno,sname,avg(grade) as 平均成绩,count(cno) as 选课门数 
from student 
left join sc on student.sno=sc.sno
group by student.sno
having 选课门数>2;

#查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果

SELECT Sname 学生姓名,SC.Cno 课程号,SC.Grade 成绩
FROM student
left join SC ON student.Sno=SC.Sno #左(内)连接表SC查询
WHERE SC.Grade>80
ORDER BY SC.Grade DESC;

#分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,
#并要求将这两个查询结果合并成一个结果集,
#并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列

SELECT Sdept 系名,Sname 姓名,Ssex 性别,course.Cname 修课名称,SC.Grade 修课成绩
FROM student 
inner join SC ON student.Sno=SC.Sno
inner join course ON course.Cno=SC.Cno
WHERE Sdept=‘信息系‘
UNION 
SELECT Sdept 系名,Sname 姓名,Ssex 性别,course.Cname 修课名称,SC.Grade 修课成绩
FROM student 
inner join SC ON student.Sno=SC.Sno
inner join course ON course.Cno=SC.Cno
WHERE Sdept=‘计算机系‘;

select sdept,sname,ssex,course.cname,sc.grade from student
left join sc on student.sno=sc.sno
left join course on sc.cno=course.cno
where sdept in ("信息系","计算机系")
order by sdept;

 

mySql随堂练习(3)

标签：cname   insert   cno   arch   合并   数据库   union   max   base   

本文系统来源：https://www.cnblogs.com/Koi504330/p/11901760.html

内容总结

以上是互联网集市为您收集整理的mySql随堂练习(3)全部内容，希望文章能够帮你解决mySql随堂练习(3)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端