hive sql练习1

DROP TABLE student;
create TEMPORARY TABLE student
(
  sid string,
  sname string,
  sage int,
  ssex string
);
INSERT into student values("01","zhaolei",19900101,"M");
INSERT into student values("02","qiandian",19901221,"M");
INSERT into student values("03","sunfeng",19900520,"M");
INSERT into student values("04","liyun",19900806,"M");
INSERT into student values("05","zhoumei",19911201,"F");
INSERT into student values("06","wulan",19920301,"F");
INSERT into student values("07","zhenzhu",19890701,"F");
INSERT into student values("08","wangju",19900120,"F");

DROP TABLE temp_course;
create TEMPORARY table temp_course(
  cid string,
  cname string,
  tid string
);
INSERT into temp_course values("01","china","02");
INSERT into temp_course values("02","math","01");
INSERT into temp_course values("03","english","03");


create TEMPORARY table temp_teacher(
  tid string,
  tname string);

INSERT into temp_teacher values("01","zhangsan");
INSERT into temp_teacher values("02","lisi");
INSERT into temp_teacher values("03","wangwu");

create TEMPORARY table sc(
  sid string,
  cid string,
  score int
);

INSERT into sc values("01","01",80);
INSERT into sc values("01","02",90);
INSERT into sc values("01","03",99);
INSERT into sc values("02","01",70);
INSERT into sc values("02","02",60);
INSERT into sc values("02","03",80);
INSERT into sc values("03","01",80);
INSERT into sc values("03","02",80);
INSERT into sc values("03","03",80);
INSERT into sc values("04","01",50);
INSERT into sc values("04","02",30);
INSERT into sc values("04","03",20);
INSERT into sc values("05","01",76);
INSERT into sc values("05","02",87);
INSERT into sc values("06","01",31);
INSERT into sc values("06","03",34);
INSERT into sc values("07","02",89);
INSERT into sc values("07","03",98);

-- 查询“某1”课程比“某2”课程成绩高的所有学生的学号
SELECT s1.sid FROM sc s1
JOIN sc s2
on s1.sid=s2.sid and s1.cid =1 and s2.cid =2
WHERE  s1.score > s2.score;

-- 查询平均成绩大于60分的同学的学号和平均成绩；
SELECT sc.sid, avg(sc.score) as avg_s FROM sc GROUP BY sc.sid HAVING avg_s > 60;

select sid,round(avg(score),1) from sc group by sid having avg(score)>60;

-- 查询所有同学的学号、姓名、选课数、总成绩
SELECT student.sid,student.sname,count(1), sum(sc.score) FROM student
JOIN sc
on student.sid=sc.sid
GROUP BY student.sid,student.sname;

select student.sid,student.sname,s.num,s.total
from
(
select sid id,count(cid) num,sum(score) total
from sc group by sid
) s
join student
on s.id=student.sid;

-- 查询姓“李”的老师的个数
SELECT count(1) FROM temp_teacher WHERE temp_teacher.tname like "li%";

-- 5.查询没学过“张三”老师课的同学的学号、姓名
SELECT DISTINCT student.sid,student.sname FROM student
JOIN sc
on student.sid=sc.sid
JOIN temp_course
on sc.cid = temp_course.cid
JOIN temp_teacher
on temp_course.tid = temp_teacher.tid and temp_teacher.tname != 'zhangshan';
-- GROUP BY student.sid,student.sname;

-- 6.查询学过数学并且也学过编号语文课程的同学的学号、姓名
-- 两种思路
SELECT student.sid,student.sname FROM student
JOIN sc
on sc.sid=student.sid
JOIN temp_course course1
on course1.cid=sc.cid and (course1.cname in ('math','china'))
GROUP BY student.sid,student.sname HAVING count(1)=2;

select * from (SELECT stu1.sid,stu1.sname FROM student stu1
JOIN sc sc1
on sc1.sid = stu1.sid
JOIN  temp_course course1
on course1.cid = sc1.cid and course1.cname = 'china') s1
join (SELECT stu1.sid,stu1.sname FROM student stu1
JOIN sc sc1
on sc1.sid = stu1.sid
JOIN  temp_course course1
on course1.cid = sc1.cid and course1.cname = 'math') s2
on s1.sid = s2.sid;

-- 7、查询学过“张三”老师所教的所有课的同学的学号、姓名
SELECT student.sid,student.sname FROM student
JOIN sc
on sc.sid = student.sid
JOIN temp_course
on temp_course.cid = sc.cid
JOIN temp_teacher
on temp_course.tid = temp_teacher.tid
WHERE temp_teacher.tname='zhangsan';


-- 8.查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名
SELECT student.sid,student.sname FROM student
JOIN sc sc1
on sc1.sid = student.sid
JOIN sc sc2
on sc2.sid = student.sid
WHERE sc1.cid ='01' and sc2.cid='02' and sc1.score < sc2.score;

-- 9.查询所有课程成绩小于60分的同学的学号、姓名
SELECT student.sid,student.sname FROM student
JOIN sc
on sc.sid=student.sid
where sc.score < 60
GROUP BY student.sid,student.sname;


SELECT student.sid,student.sname FROM student
WHERE  exists(
  -- sc.score 有多个，会分别执行，必须都小于60才返回真
  SELECT * FROM sc WHERE sc.sid = student.sid and sc.score <  60
);

SELECT student.sid, student.Sname FROM sc,student WHERE sc.sid = student.sid and sc.score <  60
GROUP BY student.sid, student.Sname;