前言
如果你有不一样实现方式,请留言题号和内容。
环境准备
t_course表
CREATE TABLE public.t_course (
cid varchar(10) NOT NULL,
cname varchar(10) NOT NULL,
tid varchar(10) NOT NULL,
CONSTRAINT t_course_pkey PRIMARY KEY (cid)
);
INSERT INTO public.t_course (cid,cname,tid) VALUES
('01','语文','02'),('02','数学','01'),('03','英语','03');
t_score表
CREATE TABLE public.t_score (
sid varchar(10) NOT NULL,
cid varchar(10) NOT NULL,
score numeric(18, 1) NOT NULL,
CONSTRAINT t_score_pkey PRIMARY KEY (sid, cid)
);
INSERT INTO public.t_score (sid,cid,score) VALUES
('01','02',90.0),('01','01',80.0),('01','03',99.0),
('02','02',60.0),('02','01',70.0),('02','03',80.0),
('03','01',80.0),('03','02',80.0),('03','03',80.0),
('04','01',50.0);
INSERT INTO public.t_score (sid,cid,score) VALUES
('04','02',30.0),('04','03',20.0),('05','01',76.0),
('05','02',87.0),('06','01',31.0),('06','03',34.0),
('07','02',90.0),('07','03',98.0);
t_student
CREATE TABLE public.t_student (
sid varchar(6) NOT NULL,
sname varchar(10) NOT NULL,
sage timestamp NULL,
ssex varchar(10) NOT NULL,
CONSTRAINT t_student_pkey PRIMARY KEY (sid),
CONSTRAINT t_student_ssex_check CHECK ((((ssex)::text = '男'::text) OR ((ssex)::text = '女'::text)))
);
INSERT INTO public.t_student (sid,sname,sage,ssex) VALUES
('01','赵雷','1990-05-18 00:00:00','男'),('02','钱电','1990-05-24 00:00:00','男'),
('03','孙风','1990-05-20 00:00:00','男'),('04','李云','1990-05-25 00:00:00','男'),
('05','周梅','1991-12-01 00:00:00','女'),('06','吴兰','1992-01-01 00:00:00','女'),
('07','郑竹','1989-10-31 00:00:00','女'),('08','张三','2017-12-20 00:00:00','女'),
('09','李四','2017-12-25 00:00:00','女'),('10','王五','2021-09-14 00:00:00','女');
INSERT INTO public.t_student (sid,sname,sage,ssex) VALUES
('11','赵六','2013-09-13 00:00:00','女'),('12','孙七','2014-10-01 00:00:00','女');
t_teacher
CREATE TABLE public.t_teacher (
tid varchar(10) NOT NULL,
tname varchar(10) NOT NULL,
CONSTRAINT t_teacher_pkey PRIMARY KEY (tid)
);
INSERT INTO public.t_teacher (tid,tname) VALUES
('01','张三'),('02','李四'),('03','王五');
注意
MySQL 可以使用单引号(‘)或者双引号(")表示值,但是 PG 只能用单引号(’)表示值。
PG 的双引号(“)是表示系统标识符的,比如表名或者字段名。MySQL可以使用反单引号(`)表示系统标识符,比如表名、字段名,PG 也是不支持的。
MySQL 可以使用单引号(')或者双引号(”)表示值,但是 PG 只能用单引号(')表示值。
PG 的双引号(")是表示系统标识符的,比如表名或者字段名。MySQL可以使用反单引号(`)表示系统标识符,比如表名、字段名,PG 也是不支持的。
开始练习
1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
with data_01 as (select sid,score from t_score ts1 where cid = '01'),
data_02 as (select sid,score from t_score ts2 where cid = '02')
select ts.*,d1.score from t_student ts, data_01 d1 , data_02 d2
where
d1.sid = d2.sid
and d1.score > d2.score
and ts.sid = d1.sid;
2.查询同时存在" 01 “课程和” 02 "课程的情况
with data_01 as (select sid,score from t_score ts1 where cid = '01'),
data_02 as (select sid,score from t_score ts2 where cid = '02')
select ts.* from t_student ts, data_01 d1 , data_02 d2
where
d1.sid = d2.sid
and ts.sid = d1.sid;
3.查询存在" 01 “课程,但可能不存在” 02 "课程的情况(不存在时显示为 null )
with data_01 as (select sid,score from t_score ts1 where cid = '01'),
data_02 as (select sid,score from t_score ts2 where cid = '02')
select
d1.sid as sid,
d1.score as "01",
d2.score as "02"
from
data_01 as d1 left join data_02 as d2 on d1.sid = d2.sid;
4.查询不存在" 01 “课程但存在” 02 "课程的情况
select ts.sid,ts.cid ,ts.score from t_score ts
where
cid = '02'
and sid not in (select sid from t_score ts1 where cid = '01')
5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select st.*,sid_avg.avger as "avg" from
t_student st ,
(select ts.sid,round(sum(score)/3,1) as avger from t_score ts group by ts.sid) sid_avg
where
st.sid = sid_avg.sid and sid_avg.avger >= 60
order by "avg" asc;
6.查询在 SC 表存在成绩的学生信息.
select * from t_student ts
where sid in (select distinct sid from t_score ts)
7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
select
ts2.*,lo.num,lo.avger
from
t_student ts2
left join
(select ts.sid,count(1) as num,round(sum(score)/3,1) as avger
from t_score ts
group by ts.sid
) lo
on
ts2.sid = lo.sid;
8.显示没选课的学生(显示为NULL)
select * from t_student ts where ts.sid not in (select sid from t_score ts group by sid order by sid)
9.查有成绩的学生信息
select *
from t_student
where
sid not in(select distinct sid from t_score)
参照表 (小表in 大表 exists)
10.查询「李」姓老师的数量
select count(*) from (select tid from t_teacher where tname like '李%') as tech;
11.查询学过「张三」老师授课的同学的信息
– 拿到老师tid
– 通过tid拿到相关的cid
– 遍历t_score 通过cid 拿到sid
– 通过sid拿到所有信息
with cid_col as (select cid from t_course tc where tc.tid in
(select tid from t_teacher where tname = '张三'))
select * from t_student ts
inner join (select sid from t_score where t_score.cid in (select cid from cid_col)) as sid_col
on sid_col.sid = ts.sid
12.查询没有学全所有课程的同学的信息
– 获取课程数量
– 获取选课表 选出未全选名单
– 通过名单查询学生信息
select ts2.* from t_student ts2
inner join
( select sid from t_score ts group by sid
having count(cid) < (select count(*) from t_course)
) res_col
on res_col .sid = ts2.sid ;
13.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息()
– 先查看01同学 课程集合 cid_col_01
– 其他同学 看看自己在不在这个集合中
select distinct t_student.sid,t_student.sname,t_student.sage,t_student.ssex from t_student inner join
(
select t_score.sid from t_score
where t_score.cid in (select cid from t_score where sid = '01')
) sid_col
on sid_col.sid = t_student.sid;
14.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
https://www.cnblogs.com/bangbangzoutianya/p/15158490.html
SELECT * FROM t_student WHERE sid <>‘01’ AND NOT EXISTS ( SELECT *
FROM t_score as t1 WHERE sid = ‘01’ AND NOT EXISTS ( SELECT * FROM
t_score as t2 WHERE t2.sid = t_student.sid AND t1.cid = t2.cid ))
15.查询没学过"张三"老师讲授的任一门课程的学生姓名
– 先查张三老师所有课程
–先查老师 tid
–通过tid 查到cid
–通过成绩表找到所有有学过的
–排除这些学过的
with cid_col as
(select cid from t_course where tid in (select tid from t_teacher where tname = '张三'))
select * from t_student
where sid not in(
select sid from t_score where cid in (select cid from cid_col)
);
16.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select t_student.sid,t_student.sname,avg(score) from t_student
inner join t_score
on t_student.sid = t_score.sid
group by t_student.sid having count(score < 60 or score = null) >= 2
select t_student.sid,t_student.sname,avg(score) from t_student
inner join t_score
on t_student.sid = t_score.sid where t_score.score < 60
group by t_student.sid having count(*) >= 2
17.检索" 01 "课程分数小于 60,按分数降序排列的学生信息
– t_course 找到01课程的cid
– 通过cid 找到 t_score 中 sid
select ts.*,cid_score_lo.score as score from t_student ts
inner join
(select sid,score from t_score ts2 where cid
in (select cid from t_course where cid = '01')
)cid_score_lo
on
cid_score_lo.sid = ts.sid
and
cid_score_lo.score < 60
order by score desc
18.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT sid,
MAX(CASE WHEN cid='01' THEN score ELSE 0 END)"语文",
MAX(CASE WHEN cid='02' THEN score ELSE 0 END)"数学",
MAX(CASE WHEN cid='03' THEN score ELSE 0 END)"英语",
round(sum(score)/3,1) "平均成绩"
FROM t_score
GROUP BY sid
ORDER BY "平均成绩" DESC
19.查询各科成绩最高分、最低分和平均分
以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
select
ts.cid,
max(tc.cname) "名称",
max(ts.score) "最高分",
min(ts.score) "最低分",
round(sum(ts.score) / count(ts.sid),1) "平均分",
sum(case when ts.score >= 60 then 1 else 0 end) "及格率",
sum(case when ts.score >= 70 and ts.score <80 then 1 else 0 end) "中等率",
sum(case when ts.score >= 80 and ts.score <90 then 1 else 0 end) "优良率",
sum(case when ts.score >= 90 then 1 else 0 end) "优秀率",
count(sid) "课程人数"
from t_score ts
left join t_course tc
on ts.cid = tc.cid
group by ts.cid
order by "课程人数",cid;
20.按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
select cid, sid, score,(select count(*) from t_score where cid = ts.cid and score > ts.score) + 1 rank
from t_score ts order by cid, score desc;
21.查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
select sid,sum(score),dense_rank() over(order by sum(score) desc) from
t_score group by sid;
22.统计各科成绩各分数段人数:课程编号,课程名称,[100-85],(85-70],(70-60],(60-0] 及所占百分比
select
ts.cid,
max(tc.cname),
sum(case when score >= 85 then 1 else 0 end) "[100-85]",
sum(case when score >= 70 and score < 85 then 1 else 0 end) "(85-70]",
sum(case when score >= 60 and score < 70 then 1 else 0 end) "(70-60]",
sum(case when score < 60 then 1 else 0 end) "(60-0]"
from t_score ts
left join t_course tc
on ts.cid = tc.cid
group by ts.cid
23. 查询没选全课程的学生
with course_num as(select count(*) from t_course)
,sid_col as (select distinct sid from t_score)
,extra_col as (select distinct sid from t_score group by sid having count(sid) = (select * from course_num) )
select * from t_student ts where sid not in (select * from sid_col)
or sid not in (select * from extra_col)
order by sid
24.查询每门课程被选修的学生数
select ts.cid,max(tc.cname),count(ts.sid) from t_score ts left join t_course tc on ts.cid = tc.cid group by ts.cid ;
25.查询出只选修两门课程的学生学号和姓名
select
ts2.sid,
ts2.sname,
ri.num
from t_student ts2
right join
(select ts.sid,count(*) as num from t_score ts group by ts.sid having count(*) = 2) ri
on ri.sid = ts2.sid
26.查询男生、女生人数
select ssex,count(*) as num from t_student group by ssex ;
27.查询名字中含有「风」字的学生信息
select * from t_student where sname like '%风%';
28.查询同名学生名单,并统计同名人数
select sname,count(*) from t_student group by sname having count(*) > 1;
29.查询 1990 年出生的学生名单
select * from t_student where date_part('year',sage) = '1990'
select * from t_student where extract(year from sage) = '1990'
30.查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
select cid,avg(score)av_s from t_score ts group by cid order by av_s desc,cid asc;
31.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select
ts.sid,
ts.sname,
ri.avs
from t_student ts
right join (select sid,avg(score) avs from t_score ts group by sid having avg(score) >=85 ) ri
on ri.sid = ts.sid
32.查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
select
ts.sid,ts2.sname,ts.score
from t_score ts
inner join t_student ts2 on ts2.sid = ts.sid
and ts.score < 60 and ts.cid in (select cid from t_course where cname ='数学')
33.查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况
select
ts.*,
ri."语文",
ri."数学",
ri."英语"
from t_student ts
left join
(select ts2.sid,
max(case when ts2.cid = '01' then score else null end) "语文",
max(case when ts2.cid = '02' then score else null end) "数学",
max(case when ts2.cid = '03' then score else null end) "英语"
from t_score ts2 group by ts2.sid) ri
on ts.sid = ri.sid
34.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select
ts.sid,
ts.sname,
ri."语文",
ri."数学",
ri."英语"
from t_student ts
inner join
(select
ts2.sid,
max(case when ts2.cid = '01' then score else null end) "语文",
max(case when ts2.cid = '02' then score else null end) "数学",
max(case when ts2.cid = '03' then score else null end) "英语"
from t_score ts2 group by ts2.sid) ri
on ri.sid = ts.sid and ri."语文" >=70 and ri."数学" >=70 and ri."英语" >=70
35.查询存在不及格的课程
select cid from t_score ts group by cid having min(score) < 60
36.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
select
ts2.sid,ts2.sname
from
t_student ts2
right join
(select sid from t_score ts where ts.cid = '01' and ts.score >=80) ri
on ri.sid = ts2.sid
37.求每门课程的学生人数
select cid,count(*) from t_score group by cid;
38.成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
with cid_col as (select cid from t_course where tid in
(select tid from t_teacher where tname = '张三'))
select ts.*,ri.score from t_student ts
inner join (select sid,score from t_score where cid in (select * from cid_col))ri
on ri.sid = ts.sid
order by ri.score desc limit 1
select
t_student.*,
t_score.score
from t_course,t_score,t_student,t_teacher
where
t_teacher.tname = '张三'
and t_teacher.tid = t_course.tid
and t_course.cid = t_score.cid
and t_score.sid = t_student.sid
order by t_score.score desc limit 1
39.成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
select lef.sid,lef.sname,lef.score from
(
select
t_student.*,
t_score.score,
t_score.cid
from t_course,t_score,t_student,t_teacher
where
t_teacher.tname = '张三'
and t_teacher.tid = t_course.tid
and t_course.cid = t_score.cid
and t_score.sid = t_student.sid
) lef
inner join
(select cid,max(score) max_sc from t_score group by cid) rig
on
lef.cid = rig.cid
and
lef.score = rig.max_sc
40.查询不同课程,成绩相同的学生的学生编号、课程编号、学生成绩
select ts1.sid,ts1.cid,ts1.score from t_score ts1
inner join t_score ts2
on ts2.cid = ts1.cid and ts1.sid<> ts2.sid and ts1.score = ts2.score
group by ts1.cid,ts1.sid
order by cid
41.查询每门功成绩最好的前两名
(select * from t_score where cid = '01' order by score desc limit 2)
union
(select * from t_score where cid = '02' order by score desc limit 2)
union
(select * from t_score where cid = '03' order by score desc limit 2)
order by cid
select ts1.sid,ts1.cid,ts1.score from t_score ts1
left join t_score ts2
on ts2.cid = ts1.cid and ts1.score < ts2.score
group by ts1.sid,ts1.cid
having count(ts1.sid) < 2
order by cid
42.统计每门课程的学生选修人数(超过 5 人的课程才统计)
select cid,count(*) as num from t_score group by cid having count(*) > 5 order by cid
43.检索至少选修两门课程的学生学号
select sid,count(*) as num from t_score group by sid having count(*) >= 2 order by sid
44.查询选修了全部课程的学生信息
with nu as (select count(*) from t_course tc)
select sid,count(*) as num from t_score group by sid having count(*) >= (select * from nu) order by sid
45.查询各学生的年龄,只按年份来算
select sname,to_number( to_char(current_date,'yyyy'), '9999') - to_number(to_char(sage,'yyyy'), '9999') as "年份" from t_student order by "年份"
46.按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
select sname,extract ("year" from age(sage))as "年龄" from t_student order by "年龄"
select sname,date_part ('year',age(sage))as "年龄" from t_student order by "年龄"
47.查询本月过生日的学生
select sname,to_char(sage,'yyyy-mm-dd') from t_student where to_number( to_char(current_date,'mm'), '99') - to_number(to_char(sage,'mm'), '99') = 0
48.查询下月过生日的学生
select sname,to_char(sage,'yyyy-mm-dd') from t_student where to_number( to_char(current_date,'mm'), '99') - to_number(to_char(sage,'mm'), '99') = -1
49.查询出生日期是1990年5月18号到25号的学生
一般不使用between and语句
select * from t_student ts where
ts.sage >= cast(concat('1990-05-18', ' 00:00:00')as timestamp)
and
ts.sage <= cast(concat('1990-05-25', ' 23:59:59')as timestamp)
如果是xml需要<![CDATA[内容]]> 修饰