学校网站的建设费用吗,建设局平台,织梦网站添加视频, 上的网站app大家好#xff0c;我是anyux。数据库联表查询很重要#xff0c;内联查询更是尤为重要。需要将school.sql文件导入到数据库中数据库多表联合查询#xff0c;school库下表的逻辑结构关系都在下图中。可以通过驱动表加子表配合实现所有查询要求。统计zhang3,学习了几门课select…大家好我是anyux。数据库联表查询很重要内联查询更是尤为重要。需要将school.sql文件导入到数据库中数据库多表联合查询school库下表的逻辑结构关系都在下图中。可以通过驱动表加子表配合实现所有查询要求。统计zhang3,学习了几门课select STU.sname as 姓名, count(SC.sno) as 数量 from student as STU join sc as SC on STU.sno SC.sno where STU.snamezhang3;查询zhang3,学习的课程名称有哪些?方法1select STU.sname as 姓名, COU.cname as 课程名 from student as STU join sc as SC on STU.sno SC.sno join course as COU on SC.cno COU.cno where STU.snamezhang3;方法2(推荐)select STU.sname as 姓名, group_concat(COU.cname) as 课程名 from student as STU join sc as SC on STU.sno SC.sno join course as COU on SC.cno COU.cno where STU.snamezhang3 group by STU.sname;查询KING老师教的学生名.select TEA.tname as 教师名称, group_concat(STU.sname) as 学生名称 from teacher as TEA join course as COU on TEA.tnoCOU.tno join sc as SC on SC.cnoCOU.cno join student as STU on STU.snoSC.sno where TEA.tnameKING group by TEA.tname;查询KING所教课程的平均分数select COU.cname as 课程名称,TEA.tname as 教师名称, avg(SC.score) as 课程成绩 from teacher as TEA join course as COU on TEA.tnoCOU.tno join sc as SC on SC.cno COU.cno where TEA.tnameKING group by COU.cname,TEA.tname;每位老师所教课程的平均分,并按平均分排序select TEA.tname as 教师名称, COU.cname as 课程名称, avg(SC.score) as 平均成绩 from teacher as TEA join course as COU on TEA.tnoCOU.tno join sc as SC on SC.cnoCOU.cno group by TEA.tname, COU.cname order by AVG(SC.score) DESC;查询KING所教的不及格的学生姓名select STU.sname as 学生姓名 from teacher as TEA join course as COU on TEA.tnoCOU.tno join sc as SC on SC.cnoCOU.cno join student as STU on STU.snoSC.sno where TEA.tnameKING and SC.score60;查询所有老师所教学生不及格的信息方法1select TEA.tname as 教师名称,STU.sname as 学生姓名,SC.score as 学生成绩 from teacher as TEA join course as COU on TEA.tnoCOU.tno join sc as SC on SC.cnoCOU.cno join student as STU on STU.snoSC.sno where SC.score60;方法2(推荐,显示优化)select TEA.tname as 教师名称,group_concat(concat(STU.sname,:,SC.score)) as 不及格的 from teacher as TEA join course as COU on TEA.tnoCOU.tno join sc as SC on SC.cnoCOU.cno join student as STU on STU.snoSC.sno where SC.score60 group by TEA.tno;查询平均成绩大于60分的同学的学号和平均成绩select STU.sno as 学号, avg(SC.score) as 平均成绩 from student as STU join sc as SC on STU.snoSC.sno group by STU.sno having avg(SC.score)60;查询所有同学的学号、姓名、选课数、总成绩select STU.sno as 学号, STU.sname as 姓名, count(SC.cno) as 选课数, sum(SC.score) as 总成绩 from student as STU join sc as SC on STU.snoSC.sno group by STU.sno ;查询各科成绩最高和最低的分以如下形式显示课程ID最高分最低分 select sc.cno as 课程ID, max(sc.score) as 最高分,min(sc.score) as 最低分 from sc group by sc.cno;统计各位老师,所教课程的及格率select TEA.tname as 教师名称,COU.cname as 课程名称,concat((select count(sc.cno) from sc where sc.score60)/(select count(sc.cno) from sc)*100,%) as 及格率 from teacher as TEA join course as COU on TEA.tnoCOU.tno join sc as SC on SC.cnoCOU.cno group by TEA.tno,COU.cname;查询每门课程被选修的学生数select COU.cno as 课程名称, count(STU.sno) as 学生数量 from student as STU join sc as SC on STU.snoSC.sno join course as COU on SC.cnoCOU.cno group by COU.cno;查询出只选修了一门课程的全部学生的学号和姓名select STU.sno as 学号,STU.sname as 学生名称,count(SC.cno) as 选修课程数量 from student as STU join sc as SC on STU.snoSC.sno group by SC.sno having count(SC.sno)1;查询选修课程门数超过1门的学生信息select STU.sno as 学号,STU.sname as 学生名称,count(SC.cno) as 选修课程数量 from student as STU join sc as SC on STU.snoSC.sno group by SC.sno having count(SC.sno)!1;统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表select STU.sname as 学生名称,STU.sno as 学号,COU.cname as 课程名称, case when SC.score85 then 优秀 when SC.score70 and SC.score85 then 良好 when SC.score60 and SC.score70 then 一般 when SC.score60 then 不及格 end as 成绩状态 from student as STU join sc as SC on STU.snoSC.sno join course as COU on SC.cnoCOU.cno group by COU.cno,STU.sno,STU.sname,SC.score;查询平均成绩大于85的所有学生的学号、姓名和平均成绩 select STU.sno as 学号,STU.sname as 学生名称, avg(SC.score) as 平均成绩 from student as STU join sc as SC on STU.snoSC.sno group by SC.score,STU.sname,STU.sno having avg(SC.score)85;
