Oracle练习题

Oracle练习题

---

练习题

---

select  from  where  group by having order by
查询语句执行顺序--oracle
from
where
group by
having
select 
order by
分页--mysql

1.查询emp表中的全部数据
select * from emp;

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

select avg(score),sno from sc group by sno having avg(score)>60 


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



select sname, c.*
  from (select sno, count(cno), sum(score) from sc group by sno) c
  join student
    on student.sno = c.sno

4、查询姓“刘”的老师的个数；

select count(1) from teacher where tname like '刘%'

7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；

select sno, sname
  from student
 where sno in
       (select sno
          from sc
         where cno in
               (select cno
                  from course
                 where tno in (select tno from teacher where tname = '谌燕'))
         group by sno
        having count(cno) = (select count(cno)
                              from course
                             where tno = (select tno
                                            from teacher
                                           where tname = '谌燕')))
8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；


select sno, sname
  from student
 where sno in (select s1.sno
                 from sc s1
                 join sc s2
                   on s1.cno = 'c001'
                  and s2.cno = 'c002'
                  and s1.sno = s2.sno
                  and s1.score > s2.score)


9、查询所有课程成绩小于60 分的同学的学号、姓名；

select sno, sname
  from student
 where sno in (select sno
                 from sc
                where score < 60
                group by sno
               having count(cno) = (select count(1) from course))


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


 
select sno, sname
  from student
 where sno in (select sno
                 from sc
                group by sno
               having count(cno) = (select count(1) from course))

11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名


select sno, sname
  from student
 where sno in (select sno
                 from sc
                where cno in (select cno from sc where sno = 's001')
                  and sno != 's001')




12、查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名；


13、查询和“s002”号的同学学习的课程完全相同的其他同学学号和姓名；
select * from sc 



select sno, sname
  from student
 where sno in
       (select sno
          from sc
         where cno in (select cno from sc where sno = 's002')
         group by sno
        having count(cno) = (select count(cno) from sc where sno = 's002')
        intersect
        select sno
          from sc
         group by sno
        having count(cno) = (select count(cno) from sc where sno = 's002') and sno != 's002')

---

14、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

select cno,max(score),min(score) from sc group by cno


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

select avg(score) 平均成绩,sum(case when score>60 then 1 else 0 end)/count(sno)*100||'%' 及格率 
from sc group by cno order by 平均成绩  asc ,及格率 desc


16、查询不同老师所教不同课程平均分从高到低显示
select avg(score), cname, tname
  from course
  left join teacher
    on course.tno = teacher.tno
  left join sc
    on course.cno = sc.cno
 group by cname, tname
 order by 1

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

select cname,course.cno,
sum(case when score<=100 and score>=85 then 1 else 0 end) A,
sum(case when score<85 and score>=70 then 1 else 0 end) B,
sum(case when score<70 and score>=60 then 1 else 0 end) C,
sum(case when score<60 then 1 else 0 end) D
from course left join sc on course.cno=sc.cno
group by cname,course.cno

18、查询各科成绩前三名的记录:(不考虑成绩并列情况)

SELECT *
  FROM (SELECT SC.*,
               row_number()over(partition by CNO order by SCORE desc) R
          FROM SC)
 WHERE R < 4

19、查询每门课程被选修的学生数
SELECT CNAME,COUNT(SNO)
FROM COURSE
LEFT JOIN SC
ON COURSE.CNO=SC.CNO
GROUP BY CNAME


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

SELECT SNO,SNAME FROM STUDENT WHERE SNO IN(SELECT SNO FROM SC GROUP BY SNO HAVING COUNT(CNO)=1)



21、查询男生、女生人数

SELECT SUM(CASE WHEN SSEX='男' then 1 else 0 end) 男,
sum(case when ssex='女' then 1 else 0 end) 女
from student

select * from student

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

select * from student where sname like '张%'

23、查询同名同姓学生名单，并统计同名人数
select count(sname),sname from student group by sname having count(sname)>1 

24、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select avg(score), cno from sc group by cno order by avg(score), cno desc
25、查询平均成绩大于70 的所有学生的学号、姓名和平均成绩



select sname, s.*
  from student
  join (select sno, avg(score) from sc group by sno having avg(score) > 70) s
    on student.sno = s.sno

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

select sname, score
  from student
  join (select sno, score
          from sc
         where cno = (select cno from course where cname = 'SSH')
           and score < 60) s
  on student.sno=s.sno
27、查询所有学生的选课情况；
select sname, cname
  from student
  left join sc
    on student.sno = sc.sno
  left join course
    on sc.cno = course.cno
    
28、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；
select cname, sname, score
  from sc
  join student
    on sc.sno = student.sno
  join course
    on sc.cno = course.cno
 where score > 70

---

29、查询不及格的课程，并按课程号从大到小排列

select cno from sc where score<60 order by cno desc


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


select sno, sname
  from student
 where sno in (select sno
                 from sc
                where cno = 'c001'
                  and score > 80)






31、求选了课程的学生人数

select count(distinct sno) from sc 

32、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩

select sname,score from student join (select sno, score
  from sc
 where cno in
       (select cno
          from course
         where tno = (select tno from teacher where tname = '谌燕'))
         order by score desc
) s
on student.sno=s.sno
where rownum=1

33、查询各个课程及相应的选修人数
select cname,count(sno) from course left join sc 
on course.cno=sc.cno
group by cname



34、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select s1.*
  from sc s1
  join sc s2
    on s1.score = s2.score
   and s1.cno != s2.cno

35、查询每门功课成绩最好的前两名


select *
  from (select cno,
               score,
               row_number() over(partition by cno order by score desc) r
          from sc)
 where r < 3

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

select cno,count(sno)
from sc 
group by cno
having count(sno)>2
order by count(sno) desc,cno


37、检索至少选修两门课程的学生学号


select sno from sc group by sno having count(cno)>=2 

38、查询>=3学生都选修的课程的课程号和课程名




select cno, cname
  from course
 where cno in (select cno from sc group by cno having count(sno) >= 3)


39、查询没学过“谌燕”老师讲授的任一门课程的学生姓名


select sno, sname
  from student
 where sno not in
       (select sno
          from sc
         where cno in
               (select cno
                  from course
                 where tno in (select tno from teacher where tname = '谌燕')))

40、查询两门以上<80课程的同学的学号及其平均成绩
select sno, avg(score)
  from sc
 where score < 80
 group by sno
having count(cno) >= 2
41、检索“c001”课程分数小于80，按分数降序排列的同学学号

select sno
  from sc
 where score < 80
   and cno = 'c001'
 order by score desc

建表信息

---

--drop table student;
create table student(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
ssex varchar2(5)
);
--drop table teacher;
create table teacher(
tno varchar2(10) primary key,
tname varchar2(20)
);
--drop table course;
create table course(
cno varchar2(10),
cname varchar2(20),
tno varchar2(20),
constraint pk_course primary key (cno,tno)
);
--drop table sc;
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(5,2),
constraint pk_sc primary key (sno,cno)
);
/*******初始化学生表的数据******/
insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');
commit;
/******************初始化教师表***********************/
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');
commit;
/***************初始化课程表****************************/
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');
commit;
/***************初始化成绩表***********************/
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');
commit;