经典SQL50练习题Hive版

建表与数据导入

create database if not exists sql50;
use sql50;
//创建外部表(否则默认为内部表) 指定分隔符
create external table teacher(tid int,tname string) row format delimited fields terminated by '\t';
create external table student(sid int,sname string,sage string,ssex string)row format delimited fields terminated by '\t';
create external table sc(sid int,cid int,score int)partitioned by (month string)row format delimited fields terminated by '\t';
create external table course(cid int,cname string,tid int)row format delimited fields terminated by '\t';
 
load data local inpath '/export/sql50_data/student.csv' into table student;
load data local inpath '/export/sql50_data/techer.csv' into table teacher;
load data local inpath '/export/sql50_data/course.csv' into table course;
load data local inpath '/export/sql50_data/score.csv' into table sc;

设置hive本地模式运行
set hive.exec.mode.local.auto=true;

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 女

techer.csv

 

01  张三

02  李四

03  王五

course.csv

01  语文     02

02  数学     01

03  英语     03

score.csv

 
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

04  01  50

04  02  30

04  03  20

05  01  76

05  02  87

06  01  31

06  03  34

07  02  89

07  03  98

SQL

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

SELECT s.*,sc1.score ,sc2.score
FROM student s,sc sc1,sc sc2
WHERE s.sid=sc1.sid AND s.sid=sc2.sid
AND sc1.cid='01' AND sc2.cid='02'
AND sc1.score>sc2.score

需要考虑01与02课程不同时存在,适合用外连接,不适合使用内连接,因为内连接会去掉空值

SELECT * FROM student s 
LEFT JOIN sc sc1 ON s.sid=sc1.sid AND sc1.cid='01'
LEFT JOIN sc sc2 ON s.sid=sc2.sid AND sc2.cid='02'
WHERE nvl(sc1.score,0) > nvl(sc2.score,0)

-- nvl(sc1.score,0)  如果 sc1.score 值为NULL就返回 0,否则返回 sc1.score

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
方式一
select sc.sid,avg(sc.score) avg from sc  group by sc.sid having avg>=60

select s.sid,s.sname,a.avg from student s join (select sid,avg(sc.score) avg from sc  group by sc.sid having avg>=60) a on s.sid = a.sid

方式二
select *,avg(score) over(partition by sid) avg from sc

select sid,round(avg(score) over(partition by sid),2) avg from sc

select distinct sid,round(avg(score) over(partition by sid),2) avg from sc 

select s.sid,s.sname,a.avg from student s,() a where s.sid=a.sid and a.avg>=60 

select s.sid,s.sname,a.avg from student s,(select distinct sid,round(avg(score) over(partition by sid),2) avg from sc) a where s.sid=a.sid and a.avg>=60 

select s.sid,s.sname,a.avg from student s,(select sid,avg from (select sid,round(avg(score) over(partition by sid),2) avg from sc )t group by t.sid,t.avg) a where s.sid=a.sid and a.avg>=60

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.sid,s.sname,count(sc.cid),sum(sc.score) from student s join sc sc on s.sid=sc.sid group by s.sid ,s.sname

select distinct sc.sid,count(sc.sid) over(partition by sc.sid),sum(sc.score) over(partition by sc.sid) from student s join sc sc on s.sid=sc.sid

6、查询"李"姓老师的数量
select count(*) from teacher t where t.tname like "李%"

7、查询学过"张三"老师授课的同学的信息
select * from sc sc join course c on sc.cid=c.cid join teacher t on c.tid=t.tid and t.tname="张三"

8、查询没学过"张三"老师授课的同学的信息
select sc.sid sid from sc sc join course c on sc.cid=c.cid join teacher t on c.tid=t.tid and t.tname="张三"

select * from sc where sc.sid not in ()

select * from sc where sc.sid not in (select sc.sid sid from sc sc join course c on sc.cid=c.cid join teacher t on c.tid=t.tid and t.tname="张三")

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
方式一
并行:利用学生ID相等关联两张表 然后分别给不同的条件
select * from sc sc1,sc sc2 where sc1.sid = sc2.sid and sc1.cid="01" and sc2.cid="02"

select sc1.sid from sc sc1,sc sc2 where sc1.sid = sc2.sid and sc1.cid="01" and sc2.cid="02"

select * from student s where s.sid in ()

select * from student s where s.sid in (select sc1.sid from sc sc1,sc sc2 where sc1.sid = sc2.sid and sc1.cid="01" and sc2.cid="02")

方式二
串行:先查出学过01课程的学生 然后到这个结果集中查学过02课程的学生
select sid from sc where sc.cid ="01"

select * from sc sc,() a where sc.sid = a.sid and sc.cid="02" 

select * from sc sc,(select sid from sc where sc.cid ="01") a where sc.sid = a.sid and sc.cid="02"

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select sid from sc where sc.cid ="01"
select sid from sc where sc.cid ="02"

select sid from sc where sc.cid ="01" and sc.sid not in()

select sid from sc where sc.cid ="01" and sc.sid not in(select sid from sc s where s.cid ="02")

select sid from sc sc where sc.cid ="01" and not exists(select 1 from sc s where s.sid=sc.sid and s.cid ="02")


hive
11、查询没有学全所有课程的同学的信息
在成绩表中查询课程总数小于总课程的同学
select sid from sc group by sc.sid having count(cid) <3 

关联学生表 显示相关信息
方式一:in子查询
Total jobs = 3
number of mappers: 1; number of reducers: 1
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 0

select * from student s where s.sid in ()
select * from student s where s.sid in (select sid from sc group by sc.sid having count(cid) <3)

方式二:join连接查询
Total jobs = 2
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 0

select * from student s join () a on s.sid=a.sid
select * from student s join (select sid from sc group by sc.sid having count(*) <3) a on s.sid=a.sid

方式三:where 提前
Total jobs = 1
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1

select s.sid from student s join sc sc where s.sid = sc.sid group by s.sid having count(sc.cid)<3

explain select s.sid from student s join sc sc where s.sid = sc.sid group by s.sid having count(sc.cid)<3

select * from (select sid,count(cid) count from sc group by sid) a , (select count(cid) count from course) b where a.count<b.count

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
思路
1查询01号学生所学课程ID
2在成绩表中查找课程ID与学号为"01"的同学所学相同的同学的信息

1查询01号学生所学课程ID
select cid from sc where sid='01'
2在成绩表中查找课程ID至少有一门课与学号为"01"的同学所学相同的同学的信息


select b.sid from sc b where cid in (select a.cid from sc a where a.sid='01') group by b.sid

select * from student s join () c where s.sid=c.sid 

select * from student s join (select b.sid sid from sc b where b.cid in (select a.cid from sc a where a.sid='01') group by b.sid) c on s.sid=c.sid

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select sid,cid from sc where sid='01'		
select count(cid) count from sc where sid='01'


select sid,cid from sc where sid!='01'
select sid,count(cid) count from sc where sid!='01' group by sid

select * from ()a join ()b on join ()c on join() d on

select a.sid from 
(select sid,count(cid) count from sc where sid!='01' group by sid) a 
join (select count(cid) count from sc where sid='01') b on a.count =b.count
join (select sid,cid from sc where sid!='01') c on a.sid=c.sid 
join(select sid,cid from sc where sid='01') d on c.cid=d.cid
group by a.sid


13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select sid,cid from sc where sid='01'		
select count(cid) count from sc where sid='01'


select sid,cid from sc where sid!='01'
select sid,count(cid) count from sc where sid!='01' group by sid

select * from ()a join ()b on join ()c on join() d on

select a.sid from 
(select sid,count(cid) count from sc where sid!='01' group by sid) a 
join (select count(cid) count from sc where sid='01') b on a.count =b.count
join (select sid,cid from sc where sid!='01') c on a.sid=c.sid 
join(select sid,cid from sc where sid='01') d on c.cid=d.cid

14、查询没学过"张三"老师讲授的任一门课程的学生姓名
查询学过张三老师课程的学生ID
select sc.sid from sc sc,course c ,teacher t where sc.cid = c.cid and c.tid = t.tid and t.tname ='张三'
查询没学过张三老师课程的学生
select s.* from student s where s.sid not in ()

select s.* from student s where s.sid not in (select sc.sid from sc sc,course c ,teacher t where sc.cid = c.cid and c.tid = t.tid and t.tname ='张三')



15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
查询两门及其以上不及格课程的同学
查询满足上不条件的学生学号，姓名及其平均成绩
select s.sname,sc.sid,avg(sc.score) avg from student s,sc sc,(select sid from sc sc where sc.score<'60' group by sc.sid having count(sid)  >= 2) a where s.sid =sc.sid and sc.sid=a.sid
group by sc.sid,s.sname

16、检索"01"课程分数小于60，按分数降序排列的学生信息
检索"01"课程分数小于60的学生
显示相关学生信息

检索"01"课程分数小于60的学生
select *  from sc s where s.cid ='01' and s.score < 60 order by s.score desc
显示相关学生信息
select * from student a join () b on a.sid = b.sid order by b.score desc

select a.sid sid,a.sname name,b.score score from student a join (select s.sid sid,s.score score from sc s where s.cid ='01' and s.score < 60) b on a.sid = b.sid order by score desc\

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select * from sc s join course c on s.cid = c.cid
列转行 语文 数学 英语

select s.sid sid ,
max(case when c.cname='语文' then s.score else null end) `语文`,
max(case when c.cname='数学' then s.score else null end) `数学`,
max(case when c.cname='英语' then s.score else null end) `英语`,
avg(s.score) `平均成绩`
from sc s join course c on s.cid = c.cid group by s.sid

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select * from sc s join course c on s.cid=c.cid group by s.cid

select c.cid cid,c.cname name,
max(s.score) max,
min(s.score) min,
avg(s.score) avg, 
sum(case when s.score>=60 then 1 else 0 end)/count(1) `及格率`,
sum(case when s.score>=70 and s.score<80 then 1 else 0 end)/count(1) `中等率`,
sum(case when s.score>=80 and s.score<90 then 1 else 0 end)/count(1) `优良率`,
sum(case when s.score>=90 then 1 else 0 end)/count(1) `优秀率`
from sc s join course c on s.cid=c.cid group by c.cid,c.cname

select c.cid cid,c.cname name,
max(s.score) max,
min(s.score) min,
bround(avg(s.score),2) avg, 
bround(sum(case when s.score>=60 then 1 else 0 end)/count(1)*100,2) `及格率`,
bround(sum(case when s.score>=70 and s.score<80 then 1 else 0 end)/count(1)*100,2) `中等率`,
bround(sum(case when s.score>=80 and s.score<90 then 1 else 0 end)/count(1)*100,2) `优良率`,
bround(sum(case when s.score>=90 then 1 else 0 end)/count(1)*100,2) `优秀率`
from sc s join course c on s.cid=c.cid group by c.cid,c.cname

19、按各科成绩进行排序，并显示排名
select b.cid id,b.cname name,a.score score,
row_number() over(partition by a.cid order by a.score desc) rank
from sc a join course b on a.cid = b.cid

select b.cid id,b.cname name,a.score score,
rank() over(partition by a.cid order by a.score desc) rank
from sc a join course b on a.cid = b.cid

select b.cid id,b.cname name,a.score score,
dense_rank() over(partition by a.cid order by a.score desc) rank
from sc a join course b on a.cid = b.cid


20、查询学生的总成绩并进行排名
计算总成绩
排名

计算总成绩
select a.sid sid,sum(a.score) tatal from sc a group by a.sid order by tatal desc

排名
select *,row_number() over(order by b.tatal desc) rank from () b

select *,row_number() over(order by b.tatal desc) rank from (select a.sid sid,sum(a.score) tatal from sc a group by a.sid) b

21、查询不同老师所教不同课程平均分从高到低显示
select c.tname tname,b.cname cname,bround(avg(a.score) ,2) avg
from sc a join course b on a.cid=b.cid join teacher c on b.tid = c.tid 
group by c.tname,b.cname



22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
1产生排名 ==>t1
2查询第二名到第三名的成绩 ==>t2
3关联学生表,课程表与t2显示相关学生信息

1产生排名 使用窗口函数 ==>t1
select *,(row_number() over(partition by cid order by score desc)) rank from sc
2查询第二名到第三名的成绩 ==>t2
select * from () t1 where rank between 2 and 3
SELECT
    * 
FROM
    ( SELECT *,( row_number () over ( PARTITION BY cid ORDER BY score DESC )) rank FROM sc ) t1 
WHERE
    rank BETWEEN 2 
AND 3
3关联学生表,课程表与t2显示相关学生信息
SELECT
    s.sname,
    c.cname,
    t2.* 
FROM
    student s,
    course c,(
    SELECT
        * 
    FROM
        ( SELECT *,( row_number () over ( PARTITION BY cid ORDER BY score DESC )) rank FROM sc ) t1 
    WHERE
        rank BETWEEN 2 
        AND 3 
    ) t2 
WHERE
    s.sid = t2.sid 
    AND c.cid = t2.cid 
ORDER BY
    t2.cid,
    t2.rank


23、统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-60 , 0-60 及所占百分比
思路
1.观察表结构需要新增多列,先统计各分段人数
2.再算出所占百分比

方式二(横向)

1.先统计各分段人数
select sc.* from sc group by sc.cid

select *,
sum(case when sc.score >= 85 then 1 else 0 end) `100-85`
from sc group by sc.cid

select sc.cid,
sum(case when sc.score >= 85 then 1 else 0 end) `85-100`,
sum(case when sc.score >= 70 and sc.score < 85 then 1 else 0 end) `70-85`,
sum(case when sc.score >= 60 and sc.score < 70 then 1 else 0 end) `60-70`,
sum(case when sc.score < 60 then 1 else 0 end) `0-60`
from sc 
group by sc.cid


select sc.cid `课程编号`,course.cname `课程名称`,
sum(case when sc.score >= 85 then 1 else 0 end) `85-100人数`,
sum(case when sc.score >= 70 and sc.score < 85 then 1 else 0 end) `70-85人数`,
sum(case when sc.score >= 60 and sc.score < 70 then 1 else 0 end) `60-70人数`,
sum(case when sc.score < 60 then 1 else 0 end) `0-60人数`
from sc left join course on sc.cid = course.cid 
group by sc.cid,course.cname

2.再算出所占百分比
bround(DOUBLE a, INT d)	Returns a rounded to d decimal places using HALF_EVEN rounding mode (as of Hive 1.3.0, 2.0.0). Example: bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4.
银行家舍入法,保留d位小数
bround(, 2)
bround(sum(case when sc.score >= 70 and sc.score <= 85 then 1 else 0 end), 2)


select sc.cid `课程编号`,course.cname `课程名称`,
sum(case when sc.score >= 85 then 1 else 0 end) `85-100人数`,
bround(sum(case when sc.score >= 85 then 1 else 0 end)/count(sc.score)*100, 2) `85-100百分比`,
bround(sum(case when sc.score >= 70 and sc.score < 85 then 1 else 0 end), 2) `70-85人数`,
bround(sum(case when sc.score >= 70 and sc.score < 85 then 1 else 0 end)/count(sc.score)*100, 2) `70-85百分比`,
sum(case when sc.score >= 60 and sc.score < 70 then 1 else 0 end) `60-70人数`,
bround(sum(case when sc.score >= 60 and sc.score < 70 then 1 else 0 end)/count(sc.score)*100, 2) `60-70百分比`,
sum(case when sc.score < 60 then 1 else 0 end) `0-60人数`,
bround(sum(case when sc.score < 60 then 1 else 0 end)/count(sc.score)*100, 2) `0-60百分比`
from sc left join course on sc.cid = course.cid 
group by sc.cid,course.cname

24、查询学生平均成绩及其名次

1.统计平均成绩=>t1
2.排名
3.关联学生表,显示相关信息

1.统计平均成绩=>t1
select *,(avg(sc.score) over(partition by sc.sid)) avg from sc

select sid,bround((avg(sc.score) over(partition by sc.sid)), 2) avg from sc

2.排名

select *,() rank from () t1
SELECT
    *,(row_number () over ( ORDER BY avg DESC )) rank 
FROM
    ( SELECT sid, bround (( avg( sc.score ) over ( PARTITION BY sc.sid )), 2 ) avg FROM sc ) t1


3.关联学生表,显示相关信息

SELECT
    s.sname NAME,
    t1.sid id,
    t1.avg,
    (row_number () over ( ORDER BY avg DESC )) rank 
FROM
    ( SELECT sid, bround (( avg( sc.score ) over ( PARTITION BY sc.sid )), 2 ) avg FROM sc ) t1
    LEFT JOIN student s ON t1.sid = s.sid
		
25、查询各科成绩前三名的记录
思路
1排名 =>t1
2选出前三名

1排名 =>t1
select *,() rank from sc 

select *,(row_number() over(partition by sc.cid order by sc.score desc)) rank from sc 
2选出前三名
select * from () t1 where t1.rank<=3

26、查询每门课程被选修的学生数
select *,count(sc.sid) over(partition by sc.cid) from sc

27、查询出只有两门课程的全部学生的学号和姓名
思路
1统计学生选课数量=>t1
2找出只有两门课的学生
3关联

1统计学生选课数量=>t1
select *,count(sc.sid) over(partition by sc.sid) amount from sc
2找出只有两门课的学生
select * from () t1 where t1.amount = 2

select * from (select *,count(sc.sid) over(partition by sc.sid) amount from sc) t1 where t1.amount = 2

3关联
select s.sid,s.sname from (select *,count(sc.sid) over(partition by sc.sid) amount from sc) t1 left join student s 
on t1.sid = s.sid and t1.amount = 2
-- left join会产生null值 改进

select s.sid,s.sname from (select *,count(sc.sid) over(partition by sc.sid) amount from sc) t1 inner join student s 
on t1.sid = s.sid and t1.amount = 2
28、查询男生、女生人数
-- 28、查询男生、女生人数
-- 横向
select () `男生人数`,() `女生人数` from student

select sum(case when s.ssex='男' then 1 else 0 end) `男生人数`,sum(case when s.ssex='女' then 1 else 0 end) `女生人数` from student s

-- 纵向
select () `性别`,() `人数` from student s

select (case when s.ssex = '男' then '男' else '女' end) `性别`,count(s.ssex) `人数` from student s
group by (case when s.ssex = '男' then '男' else '女' end)

29、查询名字中含有"风"字的学生信息 
select * from student s where s.sname like '%风%'

30、查询同名同性学生名单，并统计同名人数
-- 这里的1代表第一列,2代表第二列 直接写列名效果也是一样的
-- GROUP BY 1,2 的意思是先按第一列分组 第一列相同再按第二列分组 两列都相同就在同一组
-- 同名同姓人数
SELECT sname,ssex,count(sname)
FROM Student GROUP BY sname,ssex 
HAVING count(sname)>1;
-- 同名人数
SELECT sname,count(sname)
FROM Student GROUP BY sname
HAVING count(sname)>1;
31、查询1990年出生的学生名单(注：Student表中Sage列的类型是string)


SELECT Student.* FROM Student WHERE YEAR(sage)=1990;

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
统计每门课的平均成绩 按平均成绩排序
select cid,avg(sc.score) avg from sc group by sc.cid order by avg desc,cid

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
1统计每个学生的平均成绩 =>t1
2选出大于85的平均成绩 =>t2
3关联学生表,显示相关信息

1统计每个学生的平均成绩 =>t1
select sid,avg(sc.score) avg from sc group by sc.sid	
2选出大于85的平均成绩 =>t2
select * from () t1 where t1.avg>=85

select * from (select sid,avg(sc.score) avg from sc group by sc.sid) t1 where t1.avg>=85
3关联学生表,显示相关信息
select * from () t2,student s where t2.sid = s.sid

select s.sid,s.sname,t2.avg from (select * from (select sid,avg(sc.score) avg from sc group by sc.sid) t1 where t1.avg>=85) t2,student s where t2.sid = s.sid

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select s.sname,sc.score from course c,sc sc,student s where c.cid=sc.cid and sc.sid=s.sid and c.cname='数学' and sc.score < 60

35、查询所有学生的课程及分数情况；
select s.sname,c.cname,sc.score from course c,sc sc,student s where c.cid=sc.cid and sc.sid=s.sid

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
-- 如果将任何一门成绩在70分以上,理解为所有成绩在70分以上的话
思路
1.查出每个学生的最小的成绩 => t1
2.选出最小的成绩比70大的学生,那么此学生的所有成绩都大于70 => t2
3.关联其它表,显示相关信息

1.查出每个学生的最小的成绩
select sid,min(score) min_score from sc group by sid

2.选出最小的成绩比70大的学生,那么此学生的所有成绩都大于70
select sid from () t1 where t1.min_score>70
select sid from (select sid,min(score) min_score from sc group by sid) t1 where t1.min_score>70

3.关联其它表,显示相关信息
select * from student s,sc sc,course c,() t2 where s.sid =sc.sid and sc.cid =c.cid and sc.sid = t2.sid

select 
    s.sname `学生姓名`,c.cname `课程名`,sc.score `分数` 
from 
    student s,sc sc,course c,(select sid from (select sid,min(score) min_score from sc group by sid) t1 where t1.min_score>70) t2 
where 
    s.sid =sc.sid and sc.cid =c.cid and sc.sid = t2.sid
	
	
	
-- 如果将任何一门成绩在70分以上,理解为其中一门成绩在70分以上的话
思路
1.查出每个学生的最大的成绩 => t1
2.选出最大的成绩比70大的学生,那么此学生其中一门成绩大于70 => t2
3.关联其它表,显示相关信息

1.查出每个学生的最大的成绩
select sid,max(score) max_score from sc group by sid

2.选出最大的成绩比70大的学生,那么此学生其中一门成绩大于70 => t2
select sid from () t1 where t1.max_score>70
select sid from (select sid,max(score) max_score from sc group by sid) t1 where t1.max_score>70

3.关联其它表,显示相关信息
select * from student s,sc sc,course c,() t2 where s.sid =sc.sid and sc.cid =c.cid and sc.sid = t2.sid

select 
    s.sname `学生姓名`,c.cname `课程名`,sc.score `分数` 
from 
    student s,sc sc,course c,(select sid from (select sid,max(score) max_score from sc group by sid) t1 where t1.max_score>70) t2 
where 
    s.sid =sc.sid and sc.cid =c.cid and sc.sid = t2.sid

37、查询不及格的课程
select * from sc where sc.score<60

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select * from sc sc,student s where sc.sid=s.sid and sc.cid='01' and sc.score >= 80

39、求每门课程的学生人数
select count(sc.sid) from  sc group by sc.cid

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select *,max(sc.score) from sc sc,course c,teacher t where sc.cid=c.cid and c.tid=t.tid and t.tname='张三' group by t.tname
-- Expression not in GROUP BY key sid 
select max(sc.score) from sc sc,course c,teacher t where sc.cid=c.cid and c.tid=t.tid and t.tname='张三' group by t.tname

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
课程成绩去重后的课程数小于总课程数则不同课程存在相同成绩
select * from (select *,count(sc.score) count ,count(distinct sc.score) distinct_count from sc group by sc.sid) t1 where distinct_count<count
Error: Error while compiling statement: FAILED: SemanticException [Error 10025]: Expression not in GROUP BY key cid

select * from (select sc.sid,count(sc.score) count ,count(distinct sc.score) distinct_count from sc group by sc.sid) t1 where distinct_count<count

42、查询每门功成绩最好的前两名
select *,() rank from sc

select *,(row_number() over(partition by sc.cid order by sc.score desc)) rank from sc

select * from () t1 where t1.rank<3

select * from (select *,(row_number() over(partition by sc.cid order by sc.score desc)) rank from sc) t1 where t1.rank<3

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select sc.cid,count(sc.sid) count from sc group by sc.cid order by count desc,sc.cid

select * from ()t1 where t1.count>5
select * from (select sc.cid,count(sc.sid) count from sc group by sc.cid order by count desc,sc.cid)t1 where t1.count>5

-- ref 
SELECT cid,count(1) count FROM SC GROUP BY cid HAVING count>=5 ORDER BY count desc,cid ;

44、检索至少选修两门课程的学生学号
select *,count(sc.cid) count from sc group by sc.sid

select * from () t1 where t1.count>=2

select * from (select sc.sid,count(sc.cid) count from sc group by sc.sid) t1 where t1.count>=2

45、查询选修了全部课程的学生信息
select *,count(sc.cid) count from sc group by sc.sid 

select * from () t2,() t1 where t2.count=t1.count

select * from (select *,count(sc.cid) count from sc group by sc.sid ) t2,(select count(c.cid) count from course c) t1 where t2.count=t1.count

select * from (select sc.sid,count(sc.cid) count from sc group by sc.sid ) t2,(select count(c.cid) count from course c) t1 where t2.count=t1.count

46、查询各学生的年龄
SELECT Student.*,YEAR(current_date)-YEAR(Student.sage) FROM Student;

47、查询本周过生日的学生
SELECT * FROM Student WHERE WEEKOFYEAR(current_date)-WEEKOFYEAR(sage)=0;

48、查询下周过生日的学生
SELECT * FROM Student WHERE WEEKOFYEAR(current_date)-WEEKOFYEAR(sage)=-1;

49、查询本月过生日的学生
select * from student where month(current_date)-month(sage)=0;

50、查询下月过生日的学生
select * from student where month(current_date)-month(sage)=-1;