Hive综合应用案例-—-学生成绩查询

第1关：计算每个班的语文总成绩和数学总成绩----------禁止修改----------dropdatabaseifexistsmydbcascade;----------禁止修改--------------------begin-------------创建mydb数据库createdatabaseifnotexistsmydb;---使用mydb数据库usemydb;---创建表scorecreatetableifnotexistsscore(namestringcomment'姓名',chinesestringcomment'语文成绩',mathsstringcomment'数学成绩')rowformatdelimitedfieldsterminatedby','storedastextfile;---导入数据：/root/data/step1_files/score.txtloaddatalocalinpath'/root/data/step1_files/score.txt'intotablescore;--创建表classcreatetableifnotexistsclass(stunamestringcomment'姓名',classnamestringcomment'所在班级')rowformatdelimitedfieldsterminatedby','storedastextfile;---导入数据：/root/data/step1_files/class.txtloaddatalocalinpath'/root/data/step1_files/class.txt'intotableclass;--计算每个班的语文总成绩和数学总成绩，要求有哪科低于60分，该名学生成绩不计入计算。selectt1.classname,t1.chinese,t2.mathsfrom(selectc.classnameclassname,sum(s.chinese)chinesefromclassc,scoreswherec.stuname=s.nameands.chinese=60groupbyc.classname)t1,(selectc.classnameclassname,sum(s.maths)mathsfromclassc,scoreswherec.stuname=s.nameands.maths=60groupbyc.classname)t2wheret1.classname=t2.classname;----------end----------第2关：查询选修了3门以上的课程的学生姓名----------禁止修改----------dropdatabaseifexistsmydbcascade;----------禁止修改--------------------begin-------------创建mydb数据库createdatabaseifnotexistsmydb;---使用mydb数据库usemydb;---创建表my_stucreatetableifnotexistsmy_stu(idstringcomment'学生id',namestringcomment'学生姓名',sexstringcomment'性别',agestringcomment'年龄',colstringcomment'所选的系')rowformatdelimitedfieldsterminatedby','storedastextfile;---导入数据：/root/data/step2_files/my_student.txtloaddatalocalinpath'/root/data/step2_files/my_student.txt'intotablemy_stu;--创建表my_scorecreatetableifnotexistsmy_score(idstringcomment'学生id',courseidstringcomment'课程id',scorestringcomment'成绩')rowformatdelimitedfieldsterminatedby','storedastextfile;---导入数据：/root/data/step2_files/my_score.txtloaddatalocalinpath'/root/data/step2_files/my_score.txt'intotablemy_score;--创建表my_coursecreatetableifnotexistsmy_course(courseidstringcomment'课程id',coursenamestringcomment'课程名称')rowformatdelimitedfieldsterminatedby','storedastextfile;---导入数据：/root/data/step2_files/my_course.txtloaddatalocalinpath'/root/data/step2_files/my_course.txt'intotablemy_course;---查询选修了3门以上的课程的学生姓名。selectstu.name,t.coursenumfrom(selectid,count(courseid)coursenumfrommy_scoregroupbyid)t,my_stustuwheret.coursenum=3andstu.id=t.id;----------end----------第3关：课程选修人数----------禁止修改----------dropdatabaseifexistsmydbcascade;----------禁止修改--------------------begin-------------创建mydb数据库createdatabaseifnotexistsmydb;---使用mydb数据库usemydb;---创建表my_stucreatetableifnotexistsmy_stu(idstringcomment'学生id',namestringcomment'学生姓名',sexstringcomment'性别',agestringcomment'年龄',colstringcomment'所选的系')rowformatdelimitedfieldsterminatedby','storedastextfile;---导入数据：/root/data/step2_files/my_student.txtloaddatalocalinpath'/root/data/step2_files/my_student.txt'intotablemy_stu;--创建表my_scorecreatetableifnotexistsmy_score(idstringcomment'学生id',courseidstringcomment'课程id',scorestringcomment'成绩')rowformatdelimitedfieldsterminatedby','storedastextfile;---导入数据：/root/data/step2_files/my_score.txtloaddatalocalinpath'/root/data/step2_files/my_score.txt'intotablemy_score;--创建表my_coursecreatetableifnotexistsmy_course(courseidstringcomment'课程id',coursenamestringcomment'课程名称')rowformatdelimitedfieldsterminatedby','storedastextfile;---导入数据：/root/data/step2_files/my_course.txtloaddatalocalinpath'/root/data/step2_files/my_course.txt'intotablemy_course;---查询每个课程有多少人选修。selectt2.coursename,count(*)from(selectt1.namename,course.coursenamecoursenamefrom(selectstu.namename,score.courseidcourseidfrommy_scorescore,my_stustuwherescore.id=stu.id)t1,my_coursecoursewheret1.courseid=course.courseid)t2groupbyt2.coursename;----------end----------第4关：shujuku课程的平均成绩----------禁止修改----------dropdatabaseifexistsmydbcascade;----------禁止修改--------------------begin-------------创建mydb数据库createdatabaseifnotexistsmydb;---使用mydb数据库usemydb;---创建表my_stucreatetableifnotexistsmy_stu(idstringcomment'学生id',namestringcomment'学生姓名',sexstringcomment'性别',agestringcomment'年龄',colstringcomment'所选的系')rowformatdelimitedfieldsterminatedby','storedastextfile;---导入数据：/root/data/step2_files/my_student.txtloaddatalocalinpath'/root/data/step2_files/my_student.txt'intotablemy_stu;--创建表my_scorecreatetableifnotexistsmy_score(idstringcomment'学生id',courseidstringcomment'课程id',scorestringcomment'成绩')rowformatdelimitedfieldsterminatedby','storedastextfile;---导入数据：/root/data/step2_files/my_score.txtloaddatalocalinpath'/root/data/step2_files/my_score.txt'intotablemy_score;--创建表my_coursecreatetableifnotexistsmy_course(courseidstringcomment'课程id',coursenamestringcomment'课程名称')rowformatdelimitedfieldsterminatedby','storedastextfile;---导入数据：/root/data/step2_files/my_course.txtloaddatalocalinpath'/root/data/step2_files/my_course.txt'intotablemy_course;---计算shujuku课程的平均成绩。selectt3.coursename,t2.avg_scorefrom(selectt1.courseidcourseid,avg(score.score)avg_scorefrom(selectcourseidfrommy_coursewheremy_course.coursename='shujuku')t1,my_scorescorewher