oracle 练习50题

** 以下五行是 Oracle 创建用户的操作，若为 mysql 则可忽略

create user zxq identified by zxq;       #-- 创建用户

grant create session to zxq;              # -- 创建会话权限

grant create table to zxq;                 #-- 创建表权限

grant unlimited tablespace to zxq;         #-- 分配表空间使用权限

select tname from tab;                    #-- 查用户中的表

Student(S#,Sname,Sage,Ssex) 学生表

Course(C#,Cname,T#) 课程表

SC(S#,C#,score) 成绩表

Teacher(T#,Tname) 教师表

Student(S_id,Sname,Sage,Ssex) 学生表

学生编号： S_id

学生姓名： Sname

出生年月： Sage

学生性别： Ssex

Course(C_id,Cname,T_id) 课程表

课程编号： C_id

课程名称： Cname

教师编号： T_id

Teacher(T_id,Tname) 教师表

教师编号： T_id

教师姓名： Tname

SC(S_id,C_id,score) 成绩表

学生编号：S_id

课程编号：C_id

分数：    score

教师表

create table Teacher (T_id varchar(10),Tname varchar(20));          

insert into Teacher values('01','Li Pengfei');

insert into Teacher values('02','Wang Wen');

insert into Teacher values('03','Zhang Zhichao');

select * from Teacher;

学生表 

create table Student (S_id varchar(10),Sname varchar(20),Sage date,Ssex varchar(10));

insert into Student values('01','Zhao Lei',to_date('1990-01-01','YYYY-MM-DD'),'MAN');   

insert into Student values('02','Qian Dian',to_date('1990-12-21','YYYY-MM-DD'),'MAN');

insert into Student values('03','Sun Feng',to_date('1990-05-20','YYYY-MM-DD'),'MAN');

insert into Student values('04','Li Yun',to_date('1990-08-06','YYYY-MM-DD'),'MAN');

insert into Student values('05','Zhou Mei',to_date('1991-12-01','YYYY-MM-DD'),'WOMAN');

insert into Student values('06','Wu Lan',to_date('1992-03-01','YYYY-MM-DD'),'WOMAN');

insert into Student values('07','Zheng Zhu',to_date('1989-07-01','YYYY-MM-DD'),'WOMAN');

insert into Student values('08','Wang Ju',to_date('1990-01-20','YYYY-MM-DD'),'WOMAN');

select * from Student;

课程表

create table Course (C_id varchar(10),Cname varchar(10),T_id varchar(10));

insert into Course values('01','CHINESE','02');

insert into Course values('02','MATH','01');

insert into Course values('03','ENGLISH','03');

select * from Course;

成绩表

create table SC (S_id varchar(10),C_id varchar(10),score number(3,0));      -- Mysql 支持字符型 float,

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');

commit;

select * from SC;

01、 查询'01'课程比'02'课程成绩高的所有学生的学号；

 (一)select a.S_id from SC a,SC b

     where a.C_id = 01 and b.C_id = 02 and a.SCORE > b.SCORE and a.S_id = b.S_id;

 (二)select a.S_id from

     (select S_id,C_id,score from sc where C_id = '01') a

     join (select S_id,C_id,score from sc where C_id = '02') b

     on a.S_id = b.S_id where a.score > b.score;  

 

02、 查询平均成绩大于60分的同学的学号和平均成绩； 

select S_id,avg(score) from sc

 group by S_id

 having avg(score) > 60;

 

03、 查询所有同学的学号、姓名、选课数、总成绩； 

select s.S_id,s.Sname,nvl(count(sc.C_id),0),nvl(sum(sc.score),0) from student s

 left join sc on s.S_id = sc.S_id

 group by s.S_id,s.Sname;

 

04、 查询姓“Li” 的老师个数；

 (一)select count(*) from teacher where Tname like 'Li%';

 (二)select count(*) from teacher where instr(tname,'Li') = 1;

 

05、 查询没学过“Li Pengfei”老师课的同学的学号、姓名；

(一)select s.S_id,s.Sname from student s where s.S_id not in

   (select S_id from sc where C_id  in

   (select C_id from course where T_id  in

   (select T_id from teacher where Tname = 'Li Pengfei')));

(二)select s.S_id,s.Sname from student s where s.S_id not in

   (select S_id from sc

   join course c on sc.C_id = c.C_id

   join teacher t on c.T_id = t.T_id

   and t.Tname = 'Li Pengfei')；

      

06、 查询学过“01”并且也学过“02”课程的同学的学号、姓名； 

select a.S_id,(select s.Sname from student s where a.S_id = s.S_id) S_name from sc a,sc b

 where a.C_id = 01 and b.C_id = 02 and a.S_id = b.S_id

 group by a.S_id;

 

07、 查询学过“Li Pengfei”老师所教的所有课的同学的学号、姓名； 

select s.S_id,s.Sname from student s where s.S_id in

 (select S_id from sc where C_id in

 (select C_id from course where T_id in

 (select T_id from teacher where Tname = 'Li Pengfei')));

08、 查询课程编号“02”的成绩比课程编号“01”课程低的所有同学的学号、姓名；

(一)select s.S_id,s.Sname from student s,sc a,sc b

    where a.C_id = 02 and b.C_id = 01 and s.S_id = a.S_id and s.S_id = b.S_id and a.score < b.score;

(二)select s.S_id,s.Sname from student s,(select * from sc where C_id = '01') a,(select * from sc where C_id = '02') b

    where s.S_id = a.S_id and s.S_id = b.S_id and b.score < a.score;

  

09、 查询所有课程成绩小于80分的同学的学号、姓名；(查看两段sql的区别)

(一)select s.S_id,s.Sname from student s,sc where s.S_id = sc.S_id

    group by s.S_id,s.Sname having max(score) < 80;-----------------没有8号同学

  

(二) select S_id,Sname from student where S_id not in (select S_id from sc where score >= 80);

 

10、 查询没有学全所有课的同学的学号、姓名；

 select s.S_id,s.Sname from student s where s.S_id not in

 (select sc.S_id from sc group by sc.S_id having count(sc.C_id) = (select count(c.C_id) from course c));

 

11、 查询至少有一门课与学号为“07”的同学所学相同的同学的学号和姓名；         -- 至少，代表最少一门，就都包含 

select distinct a.S_id,(select Sname from student s where s.S_id = a.S_id) S_name from sc a

    where not exists (select * from sc b where b.S_id = '07'

       and not exists (select * from sc c where c.C_id = b.C_id and c.S_id = a.S_id));

 

12、 查询学过学号为“07”的同学所有门课的其他同学学号和姓名；       -- 注意：所有门课

 select distinct S_id,(select Sname from student s where s.S_id = sc.S_id) S_name from sc where C_id in

 (select distinct C_id from sc where S_id = '07')

 and S_id != '07'

 group by S_id

 having count(*) = (select count(1) from sc where S_id = '07');

 

14、 查询和“07”号的同学学习的课程完全相同的其他同学的学号和姓名； -- 注意：完全相同  - update sc set c_id = '02' where s_id = '06' and score = '31'; 

select S_id,(select Sname from student s where s.S_id = sc.S_id) S_name from sc where C_id not in

  (select C_id from sc where S_id = '07')

  group by sc.S_id

  having count(*) = (select count(*) from sc where S_id = '07');

15、 删除学习“Li Pengfei”老师课的SC表记录; 

delete from sc where C_id in

 (select C_id from course where T_id in

 (select T_id from teacher where T_name = 'Li Pengfei'))

 select * from sc;

16、 向SC表插入一些记录，这些记录要求符合条件：没有上过编号“03”课程的同学学号，“02”，以及“02”课的平均成绩；  

 (一)  insert into sc(S_id,C_id,score)

     select distinct s.S_id,'02',(select avg(score) from sc where C_id = '02')

     from student s

     left join sc on sc.S_id = s.S_id

     where s.S_id not in (select S_id from sc where C_id = '03');

    

     select * from sc;

      

（二） insert into sc(S_id,C_id,score)

     select distinct s.S_id,sc.C_id,(select avg(score) from sc where C_id = '02')

     from student s,sc

     where not exists (select * from sc where C_id = '03' and sc.S_id = s.S_id) and sc.C_id = '02';

    

     select * from sc;

 

17、 按平均成绩从高到低显示所有学生的“数学”，“语文”，“英语”三门的课程成绩，

按如下形式显示：student_id,math,chinese,english,course_sum,avg_score;

 select

        tt.S_id student_id,tt.math math,tt.chinese chinese,tt.english english,tt1.course_num,tt1.avg_score

 from

        (select t.S_id,

           sum(decode(t.Cname,'MATH',t.score,0)) as math,

           sum(decode(t.Cname,'CHINESE',t.score,0)) as chinese,

           sum(decode(t.Cname,'ENGLISH',t.score,0)) as english

        from (select c.S_id,c.C_id,c.score,d.Cname from sc c left join course d on c.C_id = d.C_id) t

        group by t.S_id

        ) tt

 inner join

        (select S_id,sum(score) course_num,round(avg(score),1) avg_score from sc group by S_id) tt1

 on tt.S_id = tt1.S_id

 order by tt1.avg_score desc;

   

18、 查询各科成绩最高和最低分，以如下形式显示：cours_id,max,min

 select C_id course_id,max(score) max,min(score) min from sc group by C_id;

     

19、 按各科平均成绩从低到高和及格率的百分数从高到低顺序

 select sc.C_id,avg(sc.score),

  100*sum(case when sc.score >= 60 then 1 else 0 end)/count(1)||'%'

 from sc

 group by sc.C_id

 order by avg(sc.score),

 sum(case when sc.score >= 60 then 1 else 0 end)/count(1) desc;

 

20、 查询如下课程平均成绩和及格率的百分数（用“1行”显示）：math(01),chinese(02),english(03); 

select

   sum

    (case when c_id='01' then score else 0 end)/

    sum(case when c_id='01' then 1 else 0 end) "math_avg",

  (sum

    (case when c_id='01' and score>=60 then 1 else 0 end)/

    sum(case when c_id='01' then 1 else 0 end))*100||'%'"math_rate",

   sum

     (case when c_id='02' then score else 0 end)/

     sum(case when c_id='02' then 1 else 0 end) "chinese_avg",

  (round

   (sum(case when c_id='02' and score>=60 then 1 else 0 end)/

     sum(case when c_id='02' then 1 else 0 end),2)

  )*100||'%'"chinese_rate",

  sum(case when c_id='03' then score else 0 end)/

  sum(case when c_id='03' then 1 else 0 end) "english_avg",

  (round

   (sum(case when c_id='03' and score>=60 then 1 else 0 end)/

   sum(case when c_id='03' then 1 else 0 end),2)

  )*100||'%'"english_rate"

 from sc;

21、 查询不同老师所教不同课程平均分从高到低显示； 

select tname,sc.c_id,cname,avg(score)

  from sc,course c,teacher t where t.t_id=c.t_id and sc.c_id=c.c_id

  group by sc.c_id,tname,cname

  order by avg(score) desc;

22、 查询如下课程成绩从第3名到第6名的学生成绩单：math(01),chinese(02),english(03)-student_id,student_name,math,chinese,english,avg_score;    (本题有争议)   

select * from

  (select s.s_id student_id,s.sname student_name,

       sum(case when c_id = '01' then score else 0 end) math,

       sum(case when c_id = '02' then score else 0 end) chinese,

       sum(case when c_id = '03' then score else 0 end) english,

       avg(sc.score) avg_score,

       row_number() over(order by avg(sc.score) desc) r

  from student s,sc

  where s.s_id = sc.s_id

  group by s.s_id,s.sname)

  where r >= 3 and r <= 6;

23、 统计列印各科成绩，各分数段人数：课程ID，课程名称，[100-85],[85-70],[70-60],[-60]

 select

  a.C_id,

  a.Cname,

  sum(case when score > 85 and score <= 100 then 1 else 0 end) as yi,

  sum(case when score > 70 and score <= 85 then 1 else 0 end) as er,

  sum(case when score > 60 and score <= 70 then 1 else 0 end) as san,

  sum(case when score > 0 and score <= 60 then 1 else 0 end) as si

 from course a,sc b

 where b.C_id = a.C_id

 group by a.C_id,a.Cname;

  

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

 

(一)select S_id ,avg(score) avg,rank() over(order by avg(score) desc) paiming from sc group by S_id;

 

（二）select S_id,round(avg(score),1) avg,rank() over(order by avg(score) desc) paiming from sc group by S_id;--------------------保留小数一位

 

25、 查询各科成绩前三名的记录：（不考虑成绩并列情况）

 select * from

 (select S_id,C_id,score,

 row_number() over(partition by C_id order by score desc) rn

 from sc)

 t

 where t.rn < 4;

   

26、 查询每门课程被选修的学生数；

 select C_id,count(S_id) from sc group by C_id;

   

27、 查询出只选修了2门课程的全部学生的学号和姓名；

 select S_id,(select Sname from student s where a.S_id = s.S_id) S_name

 from sc a

 group by a.S_id having count(C_id) = 2;

 

28、 查询男生、女生人数

select ssex,count(*) from student group by ssex;

 

29、 查询姓“张”的学生名单；

 select Sname from student where Sname like 'Zhang%'

   

30、 查询同名同姓学生名单，并统计同名人数；

 select sname,count(*) renshu from student group by sname having count(*) > 1;

 

31、 1990年出生的学生名单（注：student表中的sage列的类型是datetime）； 

 select * from student where to_char(sage,'YYYY') = '1990';

 

32、 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；

 select C_id,avg(score) from sc group by C_id order by avg(score) asc,C_id desc;

  

34、 查询课程名称为“MATH”，且分数低于60的学生姓名和分数；

 select s.Sname,sc.score from student s,sc,course c

 where s.S_id = sc.S_id and sc.C_id = c.C_id and c.Cname = 'MATH' and sc.score < 60;

 

35、 查询所有学生的选课情况；

 select s.S_id,s.Sname,c.Cname from student s,sc,course c where s.S_id = sc.S_id and sc.C_id = c.C_id;

    

36、 查询每门课程成绩在70分以上的姓名、课程名称和分数；

 select s.Sname,c.Cname,sc.score from student s,sc,course c

 where s.S_id = sc.S_id and sc.C_id = c.C_id and sc.score > 70;

 

37、 查询不及格的课程，显示学号、姓名、课程号、成绩；

 select sc.S_id,(select Sname from student s where s.S_id = sc.S_id) S_name,C_id,score from sc where score < 60;

 

38、 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名；

 select S_id,(select Sname from student s where s.S_id = sc.S_id) S_name from sc where C_id = 03 and score > 80;

 

39、 求选了课程的学生人数；

 select count(distinct S_id) from sc;

  

40、 查询选修“Li Pengfei”老师所授课程的学生中，成绩最高的学生姓名及其成绩；

 select s.Sname,sc.score from student s,sc,course c,teacher t

 where s.S_id = sc.S_id and sc.C_id = c.C_id and c.T_id = t.T_id and t.Tname = 'Li Pengfei'

 and sc.score = (select max(score) from sc where sc.C_id = c.C_id);

 

41、 查询各个课程及相应的选修人数；

 select C_id,count(C_id) from sc group by C_id;

 

42、 查询不同课程成绩相同的学生的学号、课程号、学生成绩；

select distinct a.S_id, a.C_id,a.score from sc a,sc b

where a.S_id = b.S_id and a.C_id != b.C_id

and a.score = b.score;

  

43、 查询每门课程成绩最好的前两名； 

（一)select * from

     (select C_id,S_id,score,

     row_number() over(partition by C_id order by score desc) rn

     from sc)

     t

     where t.rn < 3;

    

 (二)select a.C_id,a.S_id,a.score from sc a

     left join sc b

     on a.C_id = b.C_id

     and a.score <b.score

     group by a.C_id,a.S_id,a.score

     having count(a.C_id) < 2;---------------------无法排序

    

（三）select a.C_id,a.score,a.s_id

      from sc a where

      (select count(*) from sc b where a.C_id = b.C_id and a.score < b.score) < 2

      order by a.C_id asc,a.score desc;

  

44、 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列；

 select C_id,count(S_id)

 from sc

 group by C_id

 having count(S_id) > 5

 order by count(S_id) desc,C_id asc;

45、 检索至少选修3门课程的学生学号；

 select S_id from sc group by S_id having count(C_id) >= 3;

  

46、 查询全部学生都选修的课程的课程号和课程名；

 select c.C_id,c.Cname from course c

 where c.C_id in

 (select C_id from sc group by C_id having count(S_id) = (select count(S_id) from student));

 

47、 查询没学过“Li Pengfei”老师所授的任一门课程的学生姓名； 

select Sname from student s where s.S_id not in

 (select sc.S_id from sc where sc.C_id in

 (select c.C_id from course c where c.T_id in

 (select t.T_id from teacher t where Tname = 'Li Pengfei')));

 

48、 查询两门以上不及格课程的同学的学号及其平均成绩；

 select S_id,avg(score) from sc where S_id in

 (select S_id from sc where score < 60 group by S_id having count(C_id) > 1) group by S_id;

 

49、 检索“04”课程分数小于60，按分数降序排列的同学学号； 

select S_id from sc where C_id = '04' and score < 60 order by score desc;

  

50、 删除“02”同学的“01”课程的成绩；

 delete from sc where S_id = '02' and C_id = '01';

 select * from sc;