oracle报表输出练习,oracle数据库练习

CREATE TABLE student(

sno VARCHAR(10) PRIMARY KEY,

sname VARCHAR(20),

sage NUMERIC(2),

ssex VARCHAR(5)

);

CREATE TABLE teacher(

tno VARCHAR(10) PRIMARY KEY,

tname VARCHAR(20)

);

CREATE TABLE course(

cno VARCHAR(10),

cname VARCHAR(20),

tno VARCHAR(20),

CONSTRAINT pk_course PRIMARY KEY (cno,tno)

);

CREATE TABLE sc(

sno VARCHAR(10),

cno VARCHAR(10),

score NUMERIC(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.9);

commit;

练习：

注意：以下练习中的数据是根据初始化到数据库中的数据来写的SQL 语句，请大家务必注意。

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

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

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

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

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

6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名

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

我的思路：1、找到该老师的工号

2、找到该老师所教的课程的号码

3、在分数表中找到有这些课程号码的学生的号码

4、在学生表中找到相应的学生

具体sql如下：

select *

from student

where sno in

(select distinct (s.sno)

from sc s

where s.cno in

(select b.cno

from course b

where b.tno in

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

答案思路：使用连接一环套一环往后走，直至最后的条件是老师名字

8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；

我的思路：1、两个数据c002的成绩和c001的成绩同出一表，所以使用内连接

2、给定两个表不同的条件然后查询即可

具体sql如下：select *

from sc a

join sc b

on a.sno = b.sno

join student s

on a.sno = s.sno

where a.cno = 'c001'

and b.cno = 'c002'

and a.score > b.score

答案思路：即我的思路

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

我的思路：1、查询出分数小小于60的学生号码并且使用内连接查询出学生姓名

具体sql如下：

select distinct (a.sno), s.sname

from sc a

join student s

on a.sno = s.sno

where a.score < 60

答案思路：即我的思路

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

我的思路：1、在分数表中使用groupby查询每个学生所学习的科目的数量

2、找到学习数量等于总数量的学生学号

3、查询student表，排除第二步得到的学号即可

具体sql如下：select *

from student d

where d.sno not in

(select sno

from (select a.sno, count(a.cno) cnum from sc a group by a.sno) b

where b.cnum = (select count(*) from course))

答案思路：第一种思路：使用左连接把学生表和成绩表连接起来，然后使用分组统计出每个学生所学的科目数量，

分组统计完成之后，再选出所学科目数小于总数量的学生

第二种思路：使用笛卡尔成绩和minus取差集，这个集合里面的数据就是某位学生和他没有学过的课程，妙哉

回过头看，我写的sql是什么狗屁东西

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

我的思路：使用内连接拼接sc表，然后选择a表中是s001而b表中不是s001的即可得到选修了与s001同样课程之人

具体sql如下：select s.sno, s.sname

from student s

where s.sno in (select distinct (b.sno)

from sc a

join sc b

on a.cno = b.cno

where a.sno = 's001'

and b.sno != 's001')

and s.sno != 's001'

答案思路： 整体思路与我一致，只是采用的不同的连接方式

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

我的思路：没读懂题目，这题跟上一个有什么区别吗？

答案思路：将sc表与student表进行左连接，其中一条筛选条件为连接表的课程编号要在s001同学学过的课程编号中

13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；

我的思路：首先找到该老师所教的课程的编号，然后进行更改

具体sql如下：update sc c

set c.score =

(select avg(e.score) from sc e where c.cno = e.cno group by e.cno)

where c.cno in (select a.cno

from course a

join teacher b

on a.tno = b.tno

where b.tname = '谌燕')

答案思路：

14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名；

我的思路：不会做

答案思路：答案是错的

可能的方向:

select t.sno, WMSYS.WM_CONCAT(t.cno) TIME

From (select * from sc order by cno desc) t

GROUP BY t.sNO

15、删除学习“谌燕”老师课的SC 表记录；

我的思路：找到该老师所教授课程的编号，然后去sc表中删除即可

具体sql如下：delete from sc c

where c.cno in (select a.cno

from course a

join teacher b

on a.tno = b.tno

where b.tname = '谌燕')

答案思路：跟我一样

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

我的思路：不会写

答案思路：需要知道向sc表中插入的数据都是从哪里来的，然后再考虑怎么去组合这些数据

学号是从学生表中来(排除那些有成绩的)，cno是常量，平均分是求出来的，也是常量

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

我的思路：使用聚合函数直接查询

具体sql如下：select a.cno, max(score), min(score) from sc a group by a.cno

答案思路：跟我一样

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

我的思路：找到这三个数据然后排序

具体sql如下：select a.cno,

round(avg(a.score), 2) avgsc,

(select count(b.score) from sc b where b.score>60 and b.cno = a.cno)/ count(a.score) bili

from sc a

group by a.cno

order by avgsc,bili desc

答案思路：基本跟我相同，但是其找及格率的时候使用casewhen

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

我的思路：最开始题意理解错误，因为我想到了一种情况，就是一门课可以有不同的老师去教，

但是在本练习中没有存在这样的情况，所以本题就相当于是简单地按科目分类，按平均分排序而已

具体sql如下：select a.cno, avg(a.score), max(b.tno), max(c.tname)

from sc a

join course b

on a.cno = b.cno

join teacher c

on b.tno = c.tno

group by a.cno

order by avg(a.score) desc

答案思路：只是采用的连接方式不同而已

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

我的思路：本来以为这是一个很简单的casewhen应用，但是我之前没有使用过casewhen计数，所以出错了，

百度了一下，有两种写法，可以体会一下这两种写法的不同。

具体sql如下：select a.cno,

max(a.cname),

count(case when b.score>85 then 1 else null end),

count(case when b.score <= 85 and b.score>70 then 1 else null end) ,

count(case when b.score <= 70 and b.score>60 then 1 else null end) ,

count(case when b.score <60 then 1 else null end)

from course a

join sc b

on a.cno = b.cno

group by a.cno;

select a.cno,

max(a.cname),

sum(case when b.score>85 then 1 else 0 end),

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

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

sum(case when b.score <60 then 1 else 0 end)

from course a

join sc b

on a.cno = b.cno

group by a.cno;

答案思路：使用的是sum，也是这么个思路

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

我的思路：不会做，按班级和成绩排序之后不知道该怎么办了。

答案思路：使用分区函数partition by和row_number函数。其中row_number函数和rank()函数的区别在于

是否考虑了相同分数学生的排名。

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

我的思路：很简单的一个sql，关联聚合就行了

具体sql如下：select a.cno, count(b.score)

from course a

left join sc b

on a.cno = b.cno

group by a.cno

order by a.cno

答案思路：答案比我还简单，直接从sc里面去查

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

我的思路：简单的sql，关联聚合即可

具体sql如下：select a.sno, a.sname ,count(b.score)

from student a

left join sc b

on a.sno = b.sno

group by a.sno ,a.sname

having count(b.score) = 1

答案思路：差不多跟我一样，但是写的比我好

24、查询男生、女生人数

我的思路：分两种写法，横着写和竖着写。

具体sql如下：select count(case a.ssex

when '男' then

1

else

null

end) as 男,

count(case a.ssex

when '女' then

1

else

null

end) as 女

from student a;

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

答案思路：是我的第二种写法

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

select * from student a where a.sname like '张%'

26、查询同名同性学生名单，并统计同名人数

我的思路：也是两种写法，一种直接按名字分组，找大于1的；另外一种是使用连接找名字相同但是学号不同的

具体sql如下：

select a.sname, count(a.sno)/2

from student a

join student b

on a.sno != b.sno

and a.sname = b.sname

group by a.sname;

select a.sname,count(a.sno) from student a group by a.sname having count(a.sno) >1

答案思路：是我下面这个sql的思路

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

我的思路：这个题主要考察对日期的获取

具体sql如下：

select * from student a where a.sage=(to_number(to_char(sysdate,'yyyy'))-1981)

答案思路：跟我差不多(oracle中应该有自动类型转换机制，因为就算我上面的sql中没有to_number那一步，也能执行且正确)

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

我的思路：使用课程表左连接成绩表，然后按课程号分组，按成绩和课程号排序即可。

具体sql：select a.cno, avg(b.score)

from course a

left join sc b

on a.cno = b.cno

group by a.cno

order by avg(b.score), a.cno desc

答案思路：只使用了成绩表

29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩

我的思路：连接聚合即可

具体sql如下：select a.sno, max(b.sname), avg(a.score)

from sc a

join student b

on a.sno = b.sno

group by a.sno

having avg(a.score) > 85

答案思路：跟我差不多

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

我的思路：简单sql

具体sql如下：select b.cname, a.score

from sc a

join course b

on a.cno = b.cno

where b.cname = 'SSH'

and a.score < 60

答案思路：差不多一样

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

我的思路：以分数表为中介，连接学生表和课程表进行查询

具体sql如下；select a.sno, a.sname, b.cno, c.cname

from student a

left join sc b

on a.sno = b.sno

left join course c

on b.cno = c.cno

order by a.sno

答案思路：采用的连接方式跟我不一样，使用sc表作为中介的方式跟我一样。(现在我怎么老是想用左连接啊)

32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；

我的思路：简单的三表联查

具体sql如下：select c.sname, b.cname, a.score

from sc a

join course b

on a.cno = b.cno

join student c

on a.sno = c.sno

where a.score > 70

答案思路：跟我的连接方式不同而已

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

我的思路：这个题目没说清楚

具体sql如下：select * from sc a where a.score<60 order by a.cno desc

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

我的思路：简单查询

具体sql如下：select a.sno, b.sname, a.score

from sc a

join student b

on a.sno = b.sno

where a.score > 80

and a.cno = 'c001'

答案思路： 跟我一样

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

sql：select count(distinct sno) from sc

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

我的思路：这个题目需要到四个表中去取数据，要找最高的成绩，就需要用到排列，就是之前所学的rank或者row_number

按学科分集合，按分数排列，取第一个

具体sql如下：select *

from (select e.sname,

a.score,

a.cno,

row_number() over(partition by a.cno order by a.score desc) seq

from sc a

join course b

on a.cno = b.cno

join teacher c

on b.tno = c.tno

join student e

on a.sno = e.sno

where c.tname = '谌燕')

where seq = 1

答案思路：找到最高分数，以最高分数作为条件去找学生

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

我的思路：简单的左连接查询

具体sql如下：select a.cno, count(b.sno)

from course a

left join sc b

on a.cno = b.cno

group by a.cno

order by a.cno

答案思路：简单查询

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

我的思路：自连接加条件即可

具体sql如下：select a.*

from sc a, sc b

where a.sno = b.sno

and a.cno != b.cno

and a.score = b.score

答案思路：跟我一样

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

我的思路：使用partitionby分组

具体sql：select *

from (select a.*,

row_number() over(partition by a.cno order by a.score desc) seq

from sc a) b

where b.seq <= 2

答案思路：跟我一样

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

我的思路：连接分组排序

具体sql：select a.cno, count(b.sno)

from course a

left join sc b

on a.cno = b.cno

group by a.cno

having count(b.sno) > 0

order by count(b.sno) desc, a.cno

答案思路：给我一样

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

我的思路：简单分组

具体sql如下：select a.sno from sc a group by a.sno having count(a.cno) >=2

答案思路：跟我一样

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

我的思路：简单连接查询

具体sql如下：select a.cno, max(b.cname)

from sc a

join course b

on a.cno = b.cno

group by a.cno

having count(a.sno) = (select count(*) from student)

答案思路：答案错了

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

我的思路：采用反证，将所有学过该老师课的学生都排除

具体sql如下：select *

from student a

where a.sno not in (select distinct b.sno

from sc b

where b.cno in (select c.cno

from course c

join teacher e

on e.tno = c.tno

where e.tname = '谌燕'))

答案思路：思路一样，细节略有出入

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

我的思路：简单查询

具体sql如下：select a.sno, avg(a.score)

from sc a

group by a.sno

having count(case when a.score < 60 then 1 else null end) > 2

答案思路：答案没我写的好，使用了两次分组

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

我的思路；简单查询

具体sql如下：select *

from sc a

where a.score < 90

and a.cno = 'c004'

order by a.sno desc

46、删除“s002”同学的“c001”课程的成绩

我的思路：直接删除，简单sql

具体sql如下；delete from sc a where a.sno='s002' and a.cno='c001'

答案:

select a.* from

(select * from sc a where a.cno='c001') a,

(select * from sc b where b.cno='c002') b

where a.sno=b.sno and a.score > b.score;

select * from sc a

where a.cno='c001'

and exists(select * from sc b where b.cno='c002' and a.score>b.score

and a.sno = b.sno)

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

select a.*,s.sname from (select sno,sum(score),count(cno) from sc group by sno) a ,student s where a.sno=s.sno

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

select a.sno,a.sname from student a

where a.sno

not in

(select distinct s.sno

from sc s,

(select c.*

from course c ,

(select tno

from teacher t

where tname='谌燕')t

where c.tno=t.tno) b

where s.cno = b.cno )

select * from student st where st.sno not in

(select distinct sno from sc s join course c on s.cno=c.cno

join teacher t on c.tno=t.tno where tname='谌燕')

select st.* from sc a

join sc b on a.sno=b.sno

join student st

on st.sno=a.sno

where a.cno='c001' and b.cno='c002' and st.sno=a.sno;

select st.* from student st join sc s on st.sno=s.sno

join course c on s.cno=c.cno

join teacher t on c.tno=t.tno

where t.tname='谌燕'

select * from student st

join sc a on st.sno=a.sno

join sc b on st.sno=b.sno

where a.cno='c002' and b.cno='c001' and a.score < b.score

select st.*,s.score from student st

join sc s on st.sno=s.sno

join course c on s.cno=c.cno

where s.score <60

select stu.sno,stu.sname,count(sc.cno) from student stu

left join sc on stu.sno=sc.sno

group by stu.sno,stu.sname

having count(sc.cno)

===================================

select * from student where sno in

(select sno from

(select stu.sno,c.cno from student stu

cross join course c

minus

select sno,cno from sc)

)

===================================

select st.* from student st,

(select distinct a.sno from

(select * from sc) a,

(select * from sc where sc.sno='s001') b

where a.cno=b.cno) h

where st.sno=h.sno and st.sno<>'s001'

select * from sc

left join student st

on st.sno=sc.sno

where sc.sno<>'s001'

and sc.cno in

(select cno from sc

where sno='s001')

update sc c set score=(select avg(c.score) from course a,teacher b

where a.tno=b.tno

and b.tname='谌燕'

and a.cno=c.cno

group by c.cno)

where cno in(

select cno from course a,teacher b

where a.tno=b.tno

and b.tname='谌燕')

select* from sc where sno<>'s001'

minus

(

select* from sc

minus

select * from sc where sno='s001'

)

delete from sc

where sc.cno in

(

select cno from course c

left join teacher t on c.tno=t.tno

where t.tname='谌燕'

)

insert into sc (sno,cno,score)

select distinct st.sno,sc.cno,(select avg(score)from sc where cno='c002')

from student st,sc

where not exists

(select * from sc where cno='c002' and sc.sno=st.sno) and sc.cno='c002';

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

select cno,avg(score),sum(case when score>=60 then 1 else 0 end)/count(*)

as 及格率

from sc group by cno

order by avg(score) , 及格率desc

select max(t.tno),max(t.tname),max(c.cno),max(c.cname),c.cno,avg(score) from sc , course c,teacher t

where sc.cno=c.cno and c.tno=t.tno

group by c.cno

order by avg(score) desc

select sc.cno,c.cname,

sum(case when score between 85 and 100 then 1 else 0 end) AS "[100-85]",

sum(case when score between 70 and 85 then 1 else 0 end) AS "[85-70]",

sum(case when score between 60 and 70 then 1 else 0 end) AS "[70-60]",

sum(case when score <60 then 1 else 0 end) AS "[<60]"

from sc, course c

where sc.cno=c.cno

group by sc.cno ,c.cname;

select * from

(select sno,cno,score,row_number()over(partition by cno order by score desc) rn from sc)

where rn<4

select cno,count(sno)from sc group by cno;

select sc.sno,st.sname,count(cno) from student st

left join sc

on sc.sno=st.sno

group by st.sname,sc.sno having count(cno)=1;

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

select * from student where sname like '张%';

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

select sno,sname,sage,ssex from student t where to_char(sysdate,'yyyy')-sage =1988

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

select st.sno,st.sname,avg(score) from student st

left join sc

on sc.sno=st.sno

group by st.sno,st.sname having avg(score)>85;

select sname,score from student st,sc,course c

where st.sno=sc.sno and sc.cno=c.cno and c.cname='Oracle' and sc.score<60

select st.sno,st.sname,c.cname from student st,sc,course c

where sc.sno=st.sno and sc.cno=c.cno;

select st.sname,c.cname,sc.score from student st,sc,course c

where sc.sno=st.sno and sc.cno=c.cno and sc.score>70

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

where sc.cno=c.cno and sc.score<60 order by sc.cno desc;

select st.sno,st.sname,sc.score from sc,student st

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

select count(distinct sno) from sc;

select st.sname,score from student st,sc ,course c,teacher t

where

st.sno=sc.sno and sc.cno=c.cno and c.tno=t.tno

and t.tname='谌燕' and sc.score=

(select max(score)from sc where sc.cno=c.cno)

select cno,count(sno) from sc group by cno;

select a.* from sc a ,sc b where a.score=b.score and a.cno<>b.cno

select * from (

select sno,cno,score,row_number()over(partition by cno order by score desc) my_rn from sc t

)

where my_rn<=2

select cno,count(sno) from sc group by cno

having count(sno)>10

order by count(sno) desc,cno asc;

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

||

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

select distinct(c.cno),c.cname from course c ,sc

where sc.cno=c.cno

||

select cno,cname from course c

where c.cno in

(select cno from sc group by cno)

select st.sname from student st

where st.sno not in

(select distinct sc.sno from sc,course c,teacher t

where sc.cno=c.cno and c.tno=t.tno and t.tname='谌燕')

select sno,avg(score)from sc

where sno in

(select sno from sc where sc.score<60

group by sno having count(sno)>1

) group by sno

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

delete from sc where sno='s002' and cno='c001';