HIVE50道经典练习题，带答案以及查询结果.pdfVIP

HIVE50道经典练习题，带答案以及查询结果

建表语句

CREATETABLESTUDENT(

S_IDSTRING,

S_NAMESTRING,

S_BIRTHSTRING,

S_SEXSTRING

)

ROWFORMATDELIMITEDFIELDSTERMINATEDBYt

CREATETABLECOURSE(

C_IDSTRING,

C_NAMESTRING,

T_IDSTRING

)

ROWFORMATDELIMITEDFIELDSTERMINATEDBYt

CREATETABLETEACHER(

T_IDSTRING,

T_NAMESTRING

)

ROWFORMATDELIMITEDFIELDSTERMINATEDBYt

CREATETABLESCORE(

S_IDSTRING,

C_IDSTRING,

S_SCOREINT

)

ROWFORMATDELIMITEDFIELDSTERMINATEDBYt

数据

vi/export/data/hivedatas/student.csv

01赵雷1990-01-01男

02钱电1990-12-21男

03孙风1990-05-20男

04李云1990-08-06男

05周梅1991-12-01⼥

06吴兰1992-03-01⼥

07郑⽵1989-07-01⼥

08王菊1990-01-20⼥

vi/export/data/hivedatas/course.csv

01语⽂02

02数学01

03英语03

vi/export/data/hivedatas/teacher.csv

01张三

02李四

03王五

vi/export/data/hivedatas/score.csv

010180

010290

010399

020170

020260

020380

030180

030280

030380

040150

040230

040320

050176

050287

060131

060334

070289

070398

题

1.查询01课程⽐02课程成绩⾼的学的信息及课程分数:

1.WITHAAS(

SELECT*FROMSCOREWHEREC_ID=01

),

BAS(

SELECT*FROMSCOREWHEREC_ID=02

),

CAS(

SELECTS.*,A.S_SCOREASFIRST_SCORE,B.S_SCOREASSECOND_SCOREFROMSTUDENTS,A,BWHERES.S_ID=A.S_IDANDS.S

_ID=B.S_ID

)

SELECT*FROMCWHEREC.FIRST_SCOREC.SECOND_SCORE

+++++++--+

|c.s_id|c.s_name|c.s_birth|c.s_sex|c.first_score|c.second_score|

+++++++--+

+++++++--+

|02|钱电|1990-12-21|男|70|60|

|04|李云