南县做网站多少钱,沈阳网站建设电话,小说系统 wordpress,开发公司个人总结文章目录 一 CreateTable二 练习题1 查询01课程比02课程成绩高的学生的信息及课程分数2 查询01课程比02课程成绩低的学生的信息及课程分数3 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩4 查询平均成绩小于… 文章目录 一 CreateTable二 练习题1 查询01课程比02课程成绩高的学生的信息及课程分数2 查询01课程比02课程成绩低的学生的信息及课程分数3 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩4 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)5 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩6 查询李姓老师的数量7 查询学过张三老师授课的同学的信息8 查询没学过张三老师授课的同学的信息9 查询学过编号为01并且也学过编号为02的课程的同学的信息10 查询学过编号为01但是没有学过编号为02的课程的同学的信息11 查询没有学全所有课程的同学的信息12 查询至少有一门课与学号为01的同学所学相同的同学的信息13 查询和01号的同学学习的课程完全相同的其他同学的信息14 查询没学过张三老师讲授的任一门课程的学生姓名15 查询两门及其以上不及格课程的同学的学号姓名及其平均成绩16 检索01课程分数小于60按分数降序排列的学生信息17 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩18 查询各科成绩最高分、最低分和平均分以如下形式显示19 按各科成绩进行排序并显示排名20 查询学生的总成绩并进行排名21 查询不同老师所教不同课程平均分从高到低显示22 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩23 统计各科成绩各分数段人数课程编号,课程名称,[100-85),[85-70),[70-60),[0-60)及所占百分比24 查询学生平均成绩及其名次25 查询各科成绩前三名的记录26 查询每门课程被选修的学生数27 查询出只有两门课程的全部学生的学号和姓名28 查询男生、女生人数29 查询名字中含有风字的学生信息30 统计同姓的人员名单打印 姓 人数 姓名31 查询1990年出生的学生名单32 查询每门课程的平均成绩结果按平均成绩降序排列平均成绩相同时按课程编号升序排列33 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩34 查询课程名称为数学且分数低于60的学生姓名和分数35 查询所有学生的课程及分数情况Result1 groupResult2 pivot 36 查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数37 查询课程不及格的学生38 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名39 查询每门课程的人数40 查询选修张三老师所授课程的学生中成绩最高的学生信息及其成绩41 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩42 统计每门课程的前几名43 统计课程的选课人数 5 才统计44 查询选修了2门课的sid45 查询选修了全部课程的学生信息46 求学生周岁47 本周过生日的同学48 下周过生日的同学49 查询本月过生日的同学50 查询12月份过生日的同学 先用sys创建一个用户防止其他表带来干扰
CREATE USER c##baseMyf IDENTIFIED BY 123456GRANT CONNECT, RESOURCE, DBA TO c##baseMyf;alter user c##ifeng identified by 123456;一 CreateTable --Studentcreate table student (s_id int,s_name varchar(8),s_birth date,s_sex varchar(4)
);
go
insert into student values
(1,赵雷,to_date(1990-01-01,yyyy-MM-dd),男);insert into student values
(2,钱电,to_date(1990-12-21,yyyy-MM-dd),男);insert into student values
(3,孙风,to_date(1990-05-20,yyyy-MM-dd),男);insert into student values
(4,李云,to_date(1990-08-06,yyyy-MM-dd),男);insert into student values
(5,周梅,to_date(1991-12-01,yyyy-MM-dd),女);insert into student values
(6,吴兰,to_date(1992-03-01,yyyy-MM-dd),女);insert into student values
(7,郑竹,to_date(1989-07-01,yyyy-MM-dd),女);insert into student values
(8,王菊,to_date(1990-01-20,yyyy-MM-dd),女);--course
create table course (c_id int,c_name varchar(8),t_id int
);insert into course values
(1,语文,2);
insert into course values
(2,数学,1);
insert into course values
(3,英语,3);-- teachercreate table teacher (t_id int,t_name varchar(8)
);insert into teacher values
(1,张三);
insert into teacher values
(2,李四);
insert into teacher values
(3,王五);--score
create table score (s_id int,c_id int,s_score int
);insert into score values
(1,1,80);
insert into score values
(1,2,90);
insert into score values
(1,3,99);
insert into score values
(2,1,70);
insert into score values
(2,2,60);
insert into score values
(2,3,65);
insert into score values
(3,1,80);
insert into score values
(3,2,80);
insert into score values
(3,3,80);
insert into score values
(4,1,50);
insert into score values
(4,2,30);
insert into score values
(4,3,40);
insert into score values
(5,1,76);
insert into score values
(5,2,87);
insert into score values
(6,1,31);
insert into score values
(6,3,34);
insert into score values
(7,2,89);
insert into score values
(7,3,98);
二 练习题
1 查询01课程比02课程成绩高的学生的信息及课程分数
--查询01课程比02课程成绩高的学生的信息及课程分数
select distinct stu.s_id, s_name, s_birth, s_sex ,s_score_1,s_score_2
from student stu
join score s on s.s_id stu.s_id
join (select s_id ,max(case when c_id 1 then s_score end) as s_score_1,max(case when c_id 2 then s_score end) as s_score_2from scoregroup by s_idhaving max(case when c_id 1 then s_score end) max(case when c_id 2 then s_score end)
)a on stu.s_id a.s_id2 查询01课程比02课程成绩低的学生的信息及课程分数
--查询01课程比02课程成绩低的学生的信息及课程分数(查询了全部的课程分数)select distinct stu.s_id, s_name, s_birth, s.c_id,s.s_score
from student stu
join score s on stu.s_id s.s_id
and s.s_id in (select s_id--,max(case when c_id 1 then s_score end) as score_1--,max(case when c_id 2 then s_score end) as score_2from scoregroup by s_idhaving max(case when c_id 1 then s_score end) max(case when c_id 2 then s_score end)
)3 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
--查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩select stu.s_id, s_name, s_birth, s_sex ,a.avg_score
from student stu
join (
select s_id,round(avg(s_score),2) as avg_score
from score
group by s_id
having avg(s_score) 60) a on a.s_id stu.s_id4 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
--查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
select stu.s_id, s_name, s_birth, s_sex ,a.avg_score,a.avg_score_2
from student stu
left join (select s_id--, c_id, s_score ,round(sum(s_score) / count(coalesce(c_id,1)),2) as avg_score,avg(s_score) as avg_score_2from scoregroup by s_id
) a on a.s_id stu.s_id
where (avg_score 60 or avg_score is null)5 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
--查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩select stu.s_id, s_name,coalesce(count_c,0),coalesce(sum_score,0)
from student stu
left join (select s_id--, c_id, s_score ,count(c_id) as count_c,sum(s_score) as sum_scorefrom scoregroup by s_id
)a on stu.s_id a.s_id6 查询李姓老师的数量
--查询李姓老师的数量
select count(t_id) as count_li from teacher
where t_name like 李%7 查询学过张三老师授课的同学的信息
--查询学过张三老师授课的同学的信息select s_id, s_name, s_birth, s_sex
from student where s_id in(select s_id from scorewhere c_id in (select c.c_id from teacher t join course c on c.c_id t.t_id and t_name 张三)
)
8 查询没学过张三老师授课的同学的信息
--查询没学过张三老师授课的同学的信息select s_id, s_name, s_birth, s_sex
from student
where s_id not in (select s_idfrom score where c_id in (--select c.c_id from teacher t,course c where t_name 张三 and t.t_id c.c_idselect c.c_id from teacher t join course c on t.t_id c.c_id and t_name 张三)
)9 查询学过编号为01并且也学过编号为02的课程的同学的信息
--查询学过编号为01并且也学过编号为02的课程的同学的信息select s_id, s_name, s_birth, s_sex
from student
where s_id in (select s_idfrom scorewhere c_id 01and s_id in (select s_id from score where c_id 02)
)10 查询学过编号为01但是没有学过编号为02的课程的同学的信息
select s_id, s_name, s_birth, s_sex
from student where s_id in(select s_idfrom scorewhere c_id 1--where s_id in (-- select s_id from score where c_id 1--)and s_id not in (select s_id from score where c_id 2)
)11 查询没有学全所有课程的同学的信息
--查询没有学全所有课程的同学的信息select s_id, s_name, s_birth, s_sex
from student
where s_id in (select s_idfrom scoregroup by s_id having count(c_id) ! (select count(*) from course)
)12 查询至少有一门课与学号为01的同学所学相同的同学的信息
--查询至少有一门课与学号为01的同学所学相同的同学的信息select s_id, s_name, s_birth, s_sex
from student
where s_id in (select distinct s_idfrom scorewhere c_id in(select c_id from score where s_id 1)
) and s_id ! 113 查询和01号的同学学习的课程完全相同的其他同学的信息
--查询和01号的同学学习的课程完全相同的其他同学的信息
with data as (select distinct s_id,listagg(c_id,,) within group(order by c_id) over(partition by s_id) as cid_list
from score)select s_id, s_name, s_birth, s_sex
from student
where s_id in (select s_id from datawhere cid_list in (select cid_list from data where s_id 1) and s_id ! 1
)--查询和01号的同学学习的课程完全相同的其他同学的信息
select s_id, s_name, s_birth, s_sex
from student
where s_id in(select s_id--, c_id, s_scorefrom score sinner join (select c_id from score where s_id 1)a on a.c_id s.c_idwhere s_id ! 1group by s_idhaving count(*) (select count(*) from score where s_id 1)
)14 查询没学过张三老师讲授的任一门课程的学生姓名
--查询没学过张三老师讲授的任一门课程的学生姓名select stu.s_id, stu.s_name, a.c_id
from student stu
join (select s_id, c_id, s_score from scorewhere c_id not in (select c.c_idfrom teacher tjoin course con t.t_id c.c_id and t.t_name 张三)
)a on a.s_id stu.s_id
-- 没学过 - 首先想到 排除学过的select * from student where s_id not in(select distinct s_id from score where c_id in(select c_id from course where t_id in(select t_id from teacher where t_name 张三 ))
)15 查询两门及其以上不及格课程的同学的学号姓名及其平均成绩
--查询两门及其以上不及格课程的同学的学号姓名及其平均成绩with data as (
select s_id, c_id, s_score ,avg(s_score) over(partition by s_id) as avg_score
from score)select stu.s_id, stu.s_name, avg_score
from student stu
join (
select s_id,avg_score
from data
group by s_id,avg_score
having sum(case when s_score 60 then 1 else 0 end) 2
)a on stu.s_id a.s_id16 检索01课程分数小于60按分数降序排列的学生信息
select stu.s_id, s_name, s_birth, s_sex ,a.s_score
from student stu
join (
select s_id,s_score from score
where c_id 1 and s_score 60
)a on stu.s_id a.s_id
order by a.s_score desc17 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
--按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
with data as (
select *
from (select s_id, c_id, s_score,avg(s_score) over(partition by s_id) as avg_score from score)
pivot(max(s_score)for c_id in(1 as 数学,2 as 语文,3 as 英语)
)
)select d.*,stu.s_name
from data d
join student stu on stu.s_id d.s_id18 查询各科成绩最高分、最低分和平均分以如下形式显示
--查询各科成绩最高分、最低分和平均分以如下形式显示
--课程ID课程name最高分最低分平均分及格率中等率优良率优秀率
--– 及格为60中等为70-80优良为80-90优秀为90select c_id, max(s_score ) as max_score, min(s_score ) as min_score, round(avg(s_score ),2) as avg_score, concat(round((sum(case when s_score 60 then 1 else 0 end) / count(*)) * 100,2),%) as jg, concat(round((sum(case when s_score 70 and s_score 80 then 1 else 0 end) / count(*)) * 100,2),%) as zd , concat(round((sum(case when s_score 80 and s_score 90 then 1 else 0 end) / count(*)) * 100,2),%) as yl , concat(round((sum(case when s_score 90 then 1 else 0 end) / count(*)) * 100,2),%) as yx
from score
group by c_id 19 按各科成绩进行排序并显示排名
select s.s_id, s.c_id,c.c_name, s.s_score ,rank() over(partition by s.c_id order by s.s_score desc) as rank
from score s
join student stu on s.s_id stu.s_id
join course c on s.c_id c.c_id
order by s.s_id,c.c_name,rank20 查询学生的总成绩并进行排名
--查询学生的总成绩并进行排名
with data as (
select s_id, c_id, s_score ,sum(s_score) over(partition by s_id) as sum_score
from score
order by sum_score desc
)
select data.*,stu.s_name,rank() over(order by sum_score desc) as rank
from data
join student stu on stu.s_id data.s_id
order by rank21 查询不同老师所教不同课程平均分从高到低显示
--查询不同老师所教不同课程平均分从高到低显示select c.t_id,s.c_id,round(avg(s_score ),2) as avg_score
from course c
join score s on c.c_id s.c_id
group by c.t_id,s.c_id
order by avg_score desc22 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
--查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
with data as (
select s_id, c_id, s_score,rank() over(partition by c_id order by s_score desc) as rankfrom score)select stu.*,data.c_id,data.s_score
from data
join student stu on stu.s_id data.s_id and rank between 2 and 323 统计各科成绩各分数段人数课程编号,课程名称,[100-85),[85-70),[70-60),[0-60)及所占百分比
--统计各科成绩各分数段人数课程编号,课程名称,[100-85),[85-70),[70-60),[0-60)及所占百分比
with socre_s as (
select s_id, c_id, s_score,count(s_id) over(partition by c_id) as c_s_count,case when s_score 85 and s_score 100 then [100-85)when s_score 70 and s_score 85 then [85-70)when s_score 60 and s_score 70 then [70-60)when s_score 0 and s_score 60 then [0-60)end as score_djfrom score)select s_id, c_id,score_dj,concat(round((count(s_id) / c_s_count),2) * 100,%) as pre_score
from socre_s
group by s_id, c_id,score_dj,c_s_count24 查询学生平均成绩及其名次
select s.s_id, c_id, s_score ,s_name,avg(s_score) over(partition by s.s_id) as avg_score,rank() over(partition by c_id order by s_score desc)
from score s
join student stu
on stu.s_id s.s_id25 查询各科成绩前三名的记录
with data1 as (
select s_id, c_id, s_score ,rank() over(partition by c_id order by s_score desc) as rank
from score
)select s.s_id, s.s_name,c.c_id, c.c_name, c.t_id ,d.s_score
from course c
join data1 d on c.c_id d.c_id and d.rank 3
join student s on s.s_id d.s_id--感觉写的很奇怪平常都不这么用select c.c_id,c.c_name,s.s_id,s.s_name,s_score
from (select *from score scwhere (select count(*)from score sc1where sc.c_id sc1.c_idand sc.s_score sc1.s_score) 3
)
t1
inner join student s on t1.s_id s.s_id
inner join course c on t1.c_id c.c_id
order by c.c_id,s_score desc26 查询每门课程被选修的学生数
select c.c_id, c_name, t_id ,count_s
from course c
join (
select count(s_id) as count_s, c_id
from score
group by c_id
) a
on c.c_id a.c_id27 查询出只有两门课程的全部学生的学号和姓名
select s_id, s_name, s_birth, s_sex
from student
where s_id in (
select s_id
from score
group by s_id
having count(c_id) 2
)28 查询男生、女生人数
select s_sex ,count(s_id ) as count
from student
group by s_sex29 查询名字中含有风字的学生信息
select s_id, s_name, s_birth, s_sex
from student
where s_name like %风%30 统计同姓的人员名单打印 姓 人数 姓名
--统计同姓的人员名单打印 姓 人数 姓名
select substr(s_name,0,1) as first_name, s_name,count(s_name) over(partition by substr(s_name,0,1)) as first_name_count
from student31 查询1990年出生的学生名单
select s_id, s_name, s_birth, s_sex
from student
--where to_char(s_birth,yyyy) 1990
where extract(year from s_birth) 1990
32 查询每门课程的平均成绩结果按平均成绩降序排列平均成绩相同时按课程编号升序排列
--select c_id, avg(s_score) as avg_score
from score
group by c_id
order by avg(s_score) desc,c_id33 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select stu.s_id, s_name, avg_score
from student stu
join
(select s_id, avg(s_score) as avg_score
from score
group by s_id
having avg(s_score) 85) s
on s.s_id stu.s_id
34 查询课程名称为数学且分数低于60的学生姓名和分数
select stu.s_name, s.s_score
from score s
join student stu
on s.s_id stu.s_id
where c_id in (select c_id from course where c_name 数学
)and s.s_score 6035 查询所有学生的课程及分数情况
Result1 group
select stu.s_id, s_name, s_birth, s_sex ,sum(case when s.c_id 1 then s.s_score end) as 数学,sum(case when s.c_id 2 then s.s_score end) as 语文,sum(case when s.c_id 3 then s.s_score end) as 英语
from student stu
join score s on stu.s_id s.s_id
join course c on s.c_id c.c_id
group by stu.s_id, s_name, s_birth, s_sex Result2 pivot
with data as (
SELECT *
FROM scorePIVOT (MAX(s_score)FOR c_id IN (1 as 数学, 2 as 语文, 3 as 英语))
)select s.s_name, s.s_birth, s.s_sex ,d.*
from student s
join data d
on s.s_id d.s_id select stu.s_id, s_name, s_birth, s_sex , coalesce(a.s_score,0) as 数学, coalesce(b.s_score,1) as 语文, coalesce(c.s_score,2) as 英语
from student stu
left join (select s_id, c_id, s_score from score where c_id 1) a on a.s_id stu.s_id
left join (select s_id, c_id, s_score from score where c_id 2) b on b.s_id stu.s_id
left join (select s_id, c_id, s_score from score where c_id 3) c on c.s_id stu.s_id36 查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数
--查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数(任何的理解不同)select s_name, c.c_name ,s.s_score
from student stu
join (select s_id, c_id, s_score ,max(s_score) over(partition by s_id) as max_scorefrom score) s
on stu.s_id s.s_id
and s.max_score 70
join course c
on s.c_id c.c_id37 查询课程不及格的学生
--
select stu.s_id, s_name, s_birth, s_sex ,s.s_score
from student stu
join score s
on stu.s_id s.s_id
and s.s_score 60 38 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名 select s_id, s_name, s_birth, s_sex
from student s
where s_id in(select s_idfrom scorewhere c_id 1 and s_score 80
)
39 查询每门课程的人数
select c.c_id, c_name, t_id ,count_s
from course c
join(select c_id,count(s_id) as count_sfrom scoregroup by c_id
)a
on c.c_id a.c_id
40 查询选修张三老师所授课程的学生中成绩最高的学生信息及其成绩
with cid as (
select c.c_id
from course c
join teacher t
on c.t_id t.t_id
and t.t_name 张三)
select * from (
select s.*,stu.s_name,rank() over(order by s_score desc) as rank
from score s
join cid on cid.c_id s.c_id
join student stu on stu.s_id s.s_id
) where rank 1 41 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select s.s_id,stu.s_name,s.c_id,s.s_score
from score s
join (select s_idfrom scoregroup by s_id,s_score having count(c_id ) 1
) a
on s.s_id a.s_id
join student stu
on stu.s_id s.s_id
order by s.s_id,s.c_id--严谨一点
select a.s_id,s.s_name,a.c_id,a.s_score
from (selects_id,c_id,s_score,count(c_id) over(partition by s_id,s_score) as count_scorefrom score
)a
join student s
on s.s_id a.s_id
and count_score 1select * from score where s_score in(select s_scorefrom score group by s_score having count(1) 1
)42 统计每门课程的前几名
select a.c_id ,c_name ,a.s_id ,s_name ,s_score
from (
selects_id ,c_id ,s_score ,rank() over(partition by c_id order by s_score desc) as rank,row_number() over(partition by c_id order by s_score desc) as rn
from score) a
join student s
on a.rank 3
and s.s_id a.s_id
join course c
on c.c_id a.c_id
order by a.c_id ,c_name ,a.s_id ,s_name ,s_score
43 统计课程的选课人数 5 才统计
--要求输出课程号和选修人数查询结果按人数降序排列若人数相同按课程号升序排列
selectc_id,count(s_id ) as count_s
from score
group by c_id
having count(s_id ) 5
order by count(s_id ) desc,c_id44 查询选修了2门课的sid
selects_id
from score
group by s_id
having count(c_id ) 2
45 查询选修了全部课程的学生信息 select * from student
where s_id in (
selects_id
from score
group by s_id
having count(c_id ) (select count(c_id ) from course)
)46 求学生周岁
selects_name ,s_birth ,trunc(MONTHS_BETWEEN(SYSDATE, s_birth ) / 12)from student 47 本周过生日的同学
selectto_char(trunc(sysdate,IW),yyyy-mm-dd) this_monday,to_char(trunc(sysdate,IW) 6,yyyy-mm-dd) this_sunday,to_char(trunc(next_day((sysdate),1)) ,yyyy-mm-dd) next_fir_day_sun,to_char(trunc(next_day((sysdate),星期日)),yyyy-mm-dd) next_sunday
from dual48 下周过生日的同学
select*
from student
where s_birth between (trunc(sysdate,IW) 7) and (trunc(sysdate,IW) 13)49 查询本月过生日的同学
select * from student
where extract(month from s_birth) extract(month from sysdate)50 查询12月份过生日的同学
select * from student
where to_char(s_birth ,mm) 12
