sql 练习（hive，spqrk）

数据准备

表1 课程表（course）

*字段名*	*数据类型*
课程编号（CNO）	string
课程名称（CNAME）	string
教室编号（TNO）	string

表2 成绩表（score）

*字段名*	*数据类型*
学生编号（SNO）	string
课程标号（CNO）	string
分数（DEGREE）	int

表3 学生表 （student）

*字段名*	*数据类型*
学生编号（SNO）	string
学生姓名（SNAME）	string
学生性别（SSEX）	string
出生年月（SBIRTHDAY）	string
班级（CLASS）	string

表4 教师表（teacher）

*字段名*	*数据类型*
教师编号（TNO）	string
教师姓名（TNAME）	string
性别（TSEX）	string
出生年月（TBIRTHDAY）	string
职称（PROF）	string
系（DEPART）	string

数据

course

3-105,计算机导论,825
3-245,操作系统,804
6-166,数据电路,856
9-888,高等数学,100

score

103,3-245,86
105,3-245,75
109,3-245,68
103,3-105,92
105,3-105,88
109,3-105,76
101,3-105,64
107,3-105,91
108,3-105,78
101,6-166,85
107,6-106,79
108,6-166,81

student

108,曾华,男,1977-09-01,95033
105,匡明,男,1975-10-02,95031
107,王丽,女,1976-01-23,95033
101,李军,男,1976-02-20,95033
109,王芳,女,1975-02-10,95031
103,陆君,男,1974-06-03,95031

teacher

804,李诚,男,1958-12-02,副教授,计算机系
856,张旭,男,1969-03-12,讲师,电子工程系
825,王萍,女,1972-05-05,助教,计算机系
831,刘冰,女,1977-08-14,助教,电子工程系

题目

1.在hive数据库里面建立一个名为school的数据库

create database school;

2.在school数据库中建立上面我们需要的表，数据是以‘，’分割的

课程表（course）

create table course(cno string,cname string,ton string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ','STORED AS textfilelocation '/user/hive/warehouse/school.db/course/';

成绩表（score）

create table score(sno string,con string,degree int)ROW FORMAT DELIMITED FIELDS TERMINATED BY ','STORED AS textfilelocation '/user/hive/warehouse/school.db/score/';

学生表 （student）

create table student(sno string,sname string,tsex string,tbirthday string,clazz string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ','STORED AS textfilelocation '/user/hive/warehouse/school.db/student/';

教师表（teacher）

create table teacher(tno string,tname string,ssex string,sbirthday string,prof string,Depart string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ','STORED AS textfilelocation '/user/hive/warehouse/school.db/teacher/';

3.请将数据导入到相应的表里面

load data inpath '/user/course.txt' into table course;load data inpath '/user/score.txt' into table score;load data inpath '/user/student.txt' into table student;load data inpath '/user/teacher.txt' into table teacher;

4.查询Score表中成绩在60到80之间的所有记录

select * from score where degree > 60 and degree < 80;

5.查询Score表中成绩为85，86或88的记录。

select * from score where degree == 85 or degree == 88 or degree ==86;

6.以cno升序、degree降序查询score表的所有记录。

select * from score order by con,degree desc;

7.查询score表中的每门最高分的学生学号和课程号。

select con,sno from score where degree == (select max(degree) from score);

8.查询score表中至少有5名学生选修的并以3开头的课程的平均分数。

select avg(degree) as avg from score where con like '3%' group by con having count(*)>=5;

9.查询最低分大于70，最高分小于90的sno列。

select sno from score group by sno having max(degree)<90 and min(degree)>70;

10.查询所有学生的sname、cno和degree列

select sname,con,degree from score as a join student as b on a.sno=b.sno;

11.查询“95033”班所选课程的平均分。

select avg(degree) from score as a join student as b on a.sno=b.sno group by b.clazz having b.clazz == '95033';

12.查询 score 中选学一门以上课程的同学中分数为非最高分成绩的记录。

select * from (select sno,degree from score where sno in (select sno from (select sno,count(sno) as w from score group by sno having w>1) a)) wjc where degree not in (select max(degree) from score);

13.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

select * from score where degree > (select degree from score where sno'109' and con'3-105');

14.查询“张旭“教师任课的学生成绩。

select * from score where con in (select cno from course where ton in (select tno from teacher where tname == '张旭')) ;

15.查询选修 某 课程的同学人数多于5人的 教师姓名。

//错 select tname from teacher where tno in (select ton from course where cno in (select con from (select con,count(con) as s from score group by con having s>5) s));
//对（麻烦）select t.tname from teacher as t right join (select ton from course where cno in (select con from (select con,count(con) as s from score group by con having s>5) s)) e on t.tno= e.ton;

16.查询存在有85分以上成绩的课程 cno

select con from score where degree > 85;

17.查询各科成绩第一的学生信息

select * from student where sno in (select sno from score where degree in (select max(degree) from score group by con));

18.查询score里面以‘6’开头的所有信息

select * from score where con like '6%';

19.在教师表里面查询职称第二个字是教的信息

select * from teacher where prof like '_教%';

20.在教师表里面查询系这个字段里面含有程的信息

select * from teacher where depart like '%程%';