经典的SparkSQL/Hive-SQL/MySQL面试-练习题（二）

第十题

存在如下表：

table student(s_id string, s_name string, s_birth string, s_sex string) 
table course(c_id string, c_name string, t_id string) 
table teacher(t_id string, t_name string) 
table score(s_id string, c_id string, s_score int)

示例数据：

student：
01 赵雷 1993-01-01 男
02 钱电 1989-12-21 男
03 孙雷 2000-05-20 男
04 李云 1990-08-06 男
05 周天 1978-12-01 女
06 吴兰 1992-03-01 女
07 郑竹 1989-07-01 男
08 王霞 1993-01-20 女

course：
01  语文  02
02  数学  01
03  英语  03

teacher：
01  张三
02  李四
03  王五

score：
01  01  80
01  02  90
01  03  99
02  01  70
02  02  60
02  03  80
03  01  80
03  02  80
03  03  80

分别实现以下需求：

1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT student.*, 
       a.s_score as s1_score, 
       b.s_score as s2_score
FROM student
JOIN score a ON  a.c_id='01'
JOIN score b ON  b.c_id='02'
WHERE  a.s_id = student.s_id 
  AND b.s_id  =student.s_id AND a.s_score > b.s_score;

2. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数

SELECT student.*,
        a.s_score as s1_score,
        b.s_score as s2_score
FROM student
JOIN score a ON  a.c_id='01'
JOIN score b ON  b.c_id='02'
WHERE  a.s_id = student.s_id AND b.s_id = student.s_id AND a.s_score < b.s_score;

3. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT  student.s_id,
          student.s_name,
round(avg (score.s_score),1) as 平均成绩
FROM student
JOIN score ON student.s_id = score.s_id
GROUP BY student.s_id,student.s_name
HAVING avg(score.s_score) >= 60;

4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

--  包括有成绩的和无成绩的
SELECT  student.s_id,
         student.s_name,
         tmp.avgScore
FROM student
JOIN (
select score.s_id,round(avg(score.s_score),1)as avgScore FROM score group by s_id
     ) tmp ON tmp.avgScore < 60
WHERE student.s_id=tmp.s_id
UNION ALL
SELECT  s2.s_id,s2.s_name,0 as avgScore FROM student s2
WHERE s2.s_id NOT IN (select distinct sc2.s_id FROM score sc2);

5. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT student.s_id,
         student.s_name,
         (count(score.c_id) )as total_count,
sum(score.s_score)as total_score
FROM student
LEFT JOIN score ON student.s_id = score.s_id
GROUP BY student.s_id,
            student.s_name;

6. 查询"李"姓老师的数量

SELECT t_name,count(1) FROM teacher WHERE t_name LIKE '李%' GROUP BY t_name;

7. 查询学过"张三"老师授课的同学的信息

SELECT student.* FROM student
JOIN  score ON student.s_id = score.s_id
JOIN  course ON course.c_id = score.c_id
JOIN  teacher ON course.t_id = teacher.t_id AND t_name = '张三';

8. 查询没学过"张三"老师授课的同学的信息

SELECT student.*
FROM student
LEFT JOIN (select s_id FROM score
JOIN  course ON course.c_id = score.c_id
JOIN  teacher ON course.t_id = teacher.t_id and t_name = '张三') tmp
ON  student.s_id = tmp.s_id
WHERE tmp.s_id is null;

9. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT * from student
JOIN (select s_id FROM score WHERE c_id =1 ) t1
ON student.s_id=t1.s_id
JOIN (select s_id FROM score WHERE c_id =2 ) t2
ON student.s_id=t2.s_id;