---------- 禁止修改 ----------dropdatabaseifexists mydb cascade;set hive.auto.convert.join=false;set hive.ignore.mapjoin.hint=false;---------- 禁止修改 -------------------- begin -------------创建mydb数据库createdatabaseifnotexists mydb;---使用mydb数据库use mydb;---创建表scorecreatetableifnotexists score( name string comment'姓名',
chinese string comment'语文成绩', maths string comment'数学成绩')row format delimited fieldsterminatedby',' stored as textfile;---导入数据:/root/data/step1_files/score.txtloaddatalocal inpath '/root/data/step1_files/score.txt'intotable score;--创建表classcreatetableifnotexists class( stuname string comment'姓名',
classname string comment'所在班级')row format delimited fieldsterminatedby',' stored as textfile;---导入数据:/root/data/step1_files/class.txtloaddatalocal inpath '/root/data/step1_files/class.txt'intotable class;---计算每个班的语文总成绩和数学总成绩,要求有哪科低于60分,该名学生成绩不计入计算。select t1.classname,t1.chinese,t2.maths from(select c.classname classname,sum(s.chinese) chinese from class c,score s
where c.stuname=s.name and s.chinese>=60groupby c.classname) t1,(select c.classname classname,sum(s.maths) maths from class c