第1关：计算每个班的语文总成绩和数学总成绩
---------- 禁止修改 ----------
drop database if exists mydb cascade;
---------- 禁止修改 ----------
---------- begin ----------
---创建mydb数据库
create database if not exists mydb;
---使用mydb数据库
use mydb;
---创建表score
create table if not exists score(
name string comment '姓名',
chinese string comment '语文成绩',
maths string comment '数学成绩'
)
row format delimited fields terminated by ','
stored as textfile;
---导入数据：/root/data/step1_files/
load data local inpath '/root/data/step1_files/' into table score;
--创建表class
create table if not exists class(
stuname string comment '姓名',
classname string comment '所在班级'
)
row format delimited fields terminated by ','
stored as textfile;
---导入数据：/root/data/step1_files/
load data local inpath '/root/data/step1_files/' into table class;
--计算每个班的语文总成绩和数学总成绩，要求有哪科低于60分，该名学生成绩不计入计算。
select ,,
from(
select  classname,sum chinese
from class c,score s
where = and >=60
group by  t1,(
select  classname,sum maths
from class c,score s
where = and >=60
group by  t2
where =;
---------- end ----------
第2关：查询选修了3门以上的课程的学生姓名
---------- 禁止修改 ----------
drop database if exists mydb cascade;
---------- 禁止修改 ----------
---------- begin ----------
---创建mydb数据库
create database if not exists mydb;
---使用mydb数据库
use mydb;
---创建表my_stu
create table if not exists my_stu(
id string comment '学生id',
name string comment '学生姓名',
sex string comment '性别',
age string comment '年龄',
col string comment '所选的系'
)
row format delimited fields terminated by ','
stored as textfile;
---导入数据：/root/data/step2_files/
load data local inpath '/root/data/step2_files/' into table my_stu;
--创建表my_score
create table if not exists my_score(
id string comment '学生id',
courseid string comment '课程id',
score string comment '成绩'
)
row format delimited fields terminated by ','
stored as textfile;
---导入数据：/root/data/step2_files/
load data local inpath '/root/data/step2_files/' into table my_score;
--创建表my_course
create table if not exists my_course(
courseid string comment '课程id',
coursename string comment '课程名称'
)
row format delimited fields terminated by ','
stored as textfile;
---导入数据：/root/data/step2_files/
load data local inpath '/root/data/step2_files/' into table my_course;
---查询选修了3门以上的课程的学生姓名。
select ,
from(
select id,count(courseid) coursenum
from my_score
group by id) t,my_stu stu
where >=3 and =;
---------- end ----------
第3关：课程选修人数
---------- 禁止修改 ----------
drop database if exists mydb cascade;
---------- 禁止修改 ----------
---------- begin ----------
---创建mydb数据库
create database if not exists mydb;
---使用mydb数据库
use mydb;
---创建表my_stu
create table if not exists my_stu(
id string comment '学生id',
name string comment '学生姓名',
sex string comment '性别',
age string comment '年龄',
col string comment '所选的系'
)
row format delimited fields terminated by ','
stored as textfile;
---导入数据：/root/data/step2_files/
load data local inpath '/root/data/step2_files/' into table my_stu;
--创建表my_score
create table if not exists my_score(
id string comment '学生id',
courseid string comment '课程id',
score string comment '成绩'
)
row format delimited fields terminated by ','
stored as textfile;
---导入数据：/root/data/step2_files/
load data local inpath '/root/data/step2_files/' into table my_score;
--创建表my_course
create table if not exists my_course(
courseid string comment '课程id',
coursename string comment '课程名称'
)
row format delimited fields terminated by ','
数据库应用案例
stored as textfile;
---导入数据：/root/data/step2_files/
load data local inpath '/root/data/step2_files/' into table my_course;
---查询每个课程有多少人选修。
select ,count(*)
from(
select  name, coursename
from(
select  name, courseid
from my_score score,my_stu stu
where = t1,my_course course
where = t2
group by ;
---------- end ----------
第4关：shujuku课程的平均成绩
---------- 禁止修改 ----------
drop database if exists mydb cascade;
---------- 禁止修改 ----------
---------- begin ----------
---创建mydb数据库
create database if not exists mydb;
---使用mydb数据库
use mydb;
---创建表my_stu
create table if not exists my_stu(
id string comment '学生id',
name string comment '学生姓名',
sex string comment '性别',
age string comment '年龄',
col string comment '所选的系'
)
row format delimited fields terminated by ','
stored as textfile;
---导入数据：/root/data/step2_files/
load data local inpath '/root/data/step2_files/' into table my_stu;