oracle查询的前50名同姓学生,Oracle基础查询

create table student

(

sno varchar2(10) primary key,

sname varchar2(20),

sage number(2),

ssex varchar2(5)

);

create table teacher

(

tno varchar2(10) primary key,

tname varchar2(20)

);

create table course

(

cno varchar2(10),

cname varchar2(20),

tno varchar2(20),

constraint pk_course primary key (cno,tno)

);

create table sc

(

sno varchar2(10),

cno varchar2(10),

score number(4,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

--1、查询姓“李”的学生的详细信息

select * from student where sname like '李%'

--2、查询20-23岁的男同学有多少个

select count(*) from student where ssex='男' and sage between 20 and 23

--3、查询姓“刘”的老师有多少个

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

--4、查询男生、女生人数(一条SQL)

select

sum(case when ssex='男' then 1 else 0 end)男生,

sum(case when ssex='女' then 1 else 0 end)女生

from student

--5、查询'刘阳'老师所教的课程

select cname from course where tno in

(select tno from teacher where tname='刘阳')

--6、查找学员编号为's004','s007','s008'的学生信息

select * from student where sno in('s004','s007','s008')

--7、查找教'Oracle'课程的老师

select tname from teacher where tno in

(select tno from course where cname='Oracle')

--8、查找成绩低于60分的课程名称

select cname from course where cno in

(select cno from sc where score<60)

--9、查找成绩良好(分数>80)的学员名称和课程名称

select cname from course where cno in

(select cno from sc where score>80)

select sname from student where sno in

(select sno from sc where score>80)

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

select student.sno,t1.avgscore from student INNER JOIN

(

select sc.sno ,AVG(sc.score) as avgscore from sc

GROUP BY sc.sno HAVING AVG(sc.score)>60

) t1 on student.sno=t1.sno

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

select distinct A.cno,最高分,最低分 from sc A

left join course on A.cno=course.cno

left join (select cno,MAX(score)最高分,MIN(score)最低分 from sc group by cno) B on A.cno=B.cno

--12、查询同名同姓学生名单，并统计同名人数

select A.*,B.同名人数 from student A

left join (select sname,ssex,count(*)同名人数 from student group by sname,ssex)B

on A.sname=B.sname and A.ssex=B.ssex

where B.同名人数>1

--13、1999 年出生的学生名单(注：Student 表中Sage 列的类型是number)

select * from student where to_char(sage)=1999

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

select sc.cno,avg(sc.score) from sc group by sc.cno order by avg(sc.score) asc,sc.cno desc

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

select student.sname,t1.score from student

inner join

(

select sc.sno,sc.score from sc,course

where sc.cno=course.cno and course.cname='Oracle' and sc.score<60

) t1

on student.sno=t1.sno

--16、查询所有学生的选课情况

select student.sname,course.*

from student,course,sc where student.sno=sc.sno and course.cno=sc.cno

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

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

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

select student.sno,student.Sname from student,sc

where sc.cno='c001' and student.sno=sc.sno and sc.score>80

--19、求选了课程的学生人数

select count(sno) from student where sno in

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

--20、查询各个课程及相应的选修人数

select sc.cno as 课程编号,count(*) as 选修人数 from sc group by sc.cno

having count(*)>0

--思考题：

--21、查询不同课程成绩相同的学生的学号、课程号、学生成绩

select C.sno,max(C.cno)cno,max(C.score)score from sc C

left join(select sno,avg(score)A from sc group by sno)B

on C.sno=B.sno

where C.score=B.A

group by C.sno

having COUNT(0)=(select COUNT(0)from sc where sno=C.sno)

--22、检索“c004”课程分数小于60，按分数降序排列的同学学号

select sno from sc where cno='c004' and score<60 order by score desc

--23、查询“c001”课程比“c002”课程成绩高的所有学生的学号；

select A.sno from (select * from sc where cno='c001')A

left join(select * from sc where cno='c002')B

on A.sno=B.sno

where A.score>B.score

--24、查询没学过“谌燕”老师课的同学的学号、姓名；

select sno,sname from Student where sno in

(select distinct sno from sc where cno in

(select cno from course where tno=

(select tno from teacher where tname='谌燕')))

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

select sc.cno as 课程id,avg(sc.score) as 平均成绩,

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

from sc

group by sc.cno

order by avg(sc.score),

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