Hive 入门练习题

1.用一条SQL语句查询出每门课都大于80分的学生姓名

name   kecheng   fenshu
张三    语文    81
张三    数学    75
李四    语文    76
李四    数学     90
王五    语文    81
王五    数学    100
王五    英语    90

select name
from student
group by name
having min(fenshu) > 80;

2. 学生表 如下:

自动编号   学号  姓名 课程编号 课程名称 分数
1     2005001 张三   0001   数学   69
2     2005002 李四   0001   数学   89
3     2005001 张三   0001   数学   69

删除除了自动编号不同, 其他都相同的学生冗余信息

 delete tablename where 自动编号 not in(select min(自动编号) from tablename group by学号, 姓名, 课程编号, 课程名称, 分数)

3.一个叫team的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球队，现在四个球队进行比赛，用一条sql语句显示所有可能的比赛组合.

答：select a.name, b.name
from team a, team b
where a.name < b.name

drop table team;
create table team
(
    name string
) row format delimited
    fields terminated by ',';
load data local inpath '/data/14' into table team;
select * from team;

方法1:
这种方法不能去除自己和自己的结合,cross join会导致m*n中组合

select a.name, b.name
from team a
         cross join team b;
+----+----+
|name|name|
+----+----+
|a   |a   |
|b   |a   |
|c   |a   |
|d   |a   |
|a   |b   |
|b   |b   |
|c   |b   |
|d   |b   |
|a   |c   |
|b   |c   |
|c   |c   |
|d   |c   |
|a   |d   |
|b   |d   |
|c   |d   |
|d   |d   |
+----+----+

加个过滤去掉自己和自己的组合以及重复的组合,组合顺序不同的视为相同组合

select a.name, b.name
from team a
         cross join team b
where a.name<b.name;
+----+----+
|name|name|
+----+----+
|a   |b   |
|a   |c   |
|b   |c   |
|a   |d   |
|b   |d   |
|c   |d   |
+----+----+

4.面试题：怎么把这样一个

year   month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4

查成这样一个结果

year m1  m2  m3   m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4 

答案
select year,
(select amount from aaa m where month=1 and m.year=aaa.year) as m1,
(select amount from aaa m where month=2 and m.year=aaa.year) as m2,
(select amount from aaa m where month=3 and m.year=aaa.year) as m3,
(select amount from aaa m where month=4 and m.year=aaa.year) as m4
from aaa group by year

---

5.说明：复制表(只复制结构,源表名：a新表名：b)

SQL: select * into b from a where 1<>1 (where1=1，拷贝表结构和数据内容)
ORACLE:create table b
As
Select * from a where 1=2

[<>（不等于）(SQL Server Compact)
比较两个表达式。 当使用此运算符比较非空表达式时，如果左操作数不等于右操作数，则结果为 TRUE。 否则，结果为 FALSE。]

6. 改变阅读形式

原表:

courseid coursename score
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80

为了便于阅读,查询此表后的结果显式如下(及格分数为60):

courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass

---

写出此查询语句

select courseid,
       coursename,
       score,
       case when score>=60 then "pass" else "fail" end `mark`
from score

select courseid,
       coursename,
       score,
       if(score >= 60, 'pass', 'fail') `mark`
from score;

7.表名：购物信息

购物人      商品名称     数量
A            甲          2
B            乙          4
C            丙          1
A            丁          2
B            丙          5

……

给出所有购入商品为两种或两种以上的购物人记录

答：select * from 购物信息 where 购物人 in (select 购物人 from 购物信息 group by 购物人 having count(*) >= 2);
8.
info 表

date result
2005-05-09 win
2005-05-09 lose 
2005-05-09 lose 
2005-05-09 lose 
2005-05-10 win 
2005-05-10 lose 
2005-05-10 lose 

如果要生成下列结果, 该如何写sql语句?

   　　    win lose
2005-05-09  2   2 
2005-05-10  1   2 

答案：
(1)

select date,
       sum(if(result = "win", 1, 0))  `win`,
       sum(if(result = "lose", 1, 0)) `lose`
from info
group by date;