一碗机智的糖浆的博客

–46、查询各学生的年龄(周岁):– 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一– 47、查询本周过生日的学生:– 48、查询下周过生日的学生:– 49、查询本月过生日的学生:– 50、查询12月份过生日的学生:...

– 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩:SELECT sc1.s_id,sc1.c_id,sc2.s_id,sc2.c_id,sc1.s_scoreFROM score sc1 JOIN score sc2 ON sc1.s_id!=sc2.s_id AND sc1.c_id!=sc2.c_idWHERE sc1.s_score=sc2.s_scoreORDER BY sc1.s_id报错：Error: Error while compiling stateme

– 36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数:SELECT s.s_id,s.s_name,c.c_name,tmp2.s_scoreFROM student sJOIN (SELECT sc.*FROM score sc LEFT JOIN (SELECT s_id FROM score WHERE s_score<70 GROUP BY s_id) tmpON sc.s_id=tmp.s_idWHERE tmp.s_id is NULL)tmp2ON

– 31、查询1990年出生的学生名单:SELECT *FROM student WHERE YEAR(s_birth)=1990– 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列:SELECT c.c_id,c.c_name,ROUND(AVG(s_score),2) avgFROM score sc JOIN course c ON sc.c_id=c.c_idGROUP BY c.c_id,c.c_nameORDER BY avg

– 26、查询每门课程被选修的学生数:SELECT c.c_id,c.c_name,COUNT(s_id)FROM score sc JOIN course c ON sc.c_id=c.c_idGROUP BY c.c_id,c.c_name– 27、查询出只有两门课程的全部学生的学号和姓名:SELECT s.s_id,s.s_nameFROM student s JOIN score sc ON s.s_id=sc.s_idGROUP BY s.s_id,s.s_name H

– 21、查询不同老师所教不同课程平均分从高到低显示:SELECT t.t_name,c.c_name,round(AVG(s_score),2) avgFROM teacher t JOIN course c ON t.t_id=c.t_idJOIN score sc ON c.c_id=sc.c_idGROUP BY sc.c_id,t.t_name,c.c_nameORDER BY avg DESC– 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩– 2.

– 16、检索"01"课程分数小于60，按分数降序排列的学生信息:SELECT s.*,sc.s_scoreFROM student sJOIN score sc ON s.s_id=sc.s_id AND sc.c_id='01' AND sc.s_score<60ORDER BY sc.s_score DESC– 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩:最开始的sql（获取的数据格式不好，无法清晰的知道各科的成绩）SELECT s.*,tmp

– 11、查询没有学全所有课程的同学的信息:方式1：先查询课程总数 select count(1) from course;根据得到的课程数，进行查询SELECT s.*FROM student s LEFT JOIN score sc on s.s_id=sc.s_idGROUP BY s.s_id,s_name,s_birth,s_sex HAVING COUNT(s.s_id)<3思路：进行student和score表的连接后，选3课的sid必然出现3.

– 6、查询"李"姓老师的数量:SELECT COUNT(*)FROM teacherWHERE t_name LIKE '李%'加group和不加的区别：在数据中加入一列，进行查询select t_name,count(1) from teacher where t_name like '李%' group by t_name;SELECT t_name,COUNT(*)FROM teacherWHERE t_name LIKE '李%'..

– 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数:SELECT s.*,s1.c_id,s1.s_score,s2.c_id,s2.s_scoreFROM student sjoin score s1 on s.s_id=s1.s_id and s1.c_id='01' join score s2 on s.s_id=s2.s_id and s2.c_id='02'where s1.s_score > s2.s_score思路：先构建表，join一个合适的大表后

建表语句create table student(s_id string,s_name string,s_birth string,s_sex string) row format delimited fields terminated by ' ';create table course(c_id string,c_name string,t_id string) row format delimited fields terminated by ' ';create table teach