oracle数据库练习题

oracle数据库练习题

1、题目前提，创建数据库

3.1 数据表

3.2 插入数据内容：

1 在STUDENT表插入如下数据
108 ,‘曾华’ ,‘男’ ,1977-09-01,95033
105 ,‘匡明’ ,‘男’ ,1975-10-02,95031
107 ,‘王丽’ ,‘女’ ,1976-01-23,95033
101 ,‘李军’ ,‘男’ ,1976-02-20,95033
109 ,‘王芳’ ,‘女’ ,1975-02-10,95031
103 ,‘陆君’ ,‘男’ ,1974-06-03,95031

在COURSE表插入如下数据
‘3-105’ ,‘计算机导论’,825
‘3-245’ ,‘操作系统’ ,804
‘6-166’ ,‘数据电路’ ,856
‘9-888’ ,‘高等数学’ ,831

在SCORE表插入如下数据
103,‘3-245’,86
105,‘3-245’,75
109,‘3-245’,68
103,‘3-105’,92
105,‘3-105’,88
109,‘3-105’,76
101,‘3-105’,64
107,‘3-105’,91
108,‘3-105’,78
101,‘6-166’,85
107,‘6-106’,79
108,‘6-166’,81

在TEACHER表插入如下数据
804,‘李诚’,‘男’,‘1958-12-02’,‘副教授’,‘计算机系’
856,‘张旭’,‘男’,‘1969-03-12’,‘讲师’,‘电子工程系’
825,‘王萍’,‘女’,‘1972-05-05’,‘助教’,‘计算机系’
831,‘刘冰’,‘女’,‘1977-08-14’,‘助教’,‘电子工程系’

3.3 插入数据代码：

CREATE TABLE STUDENT
( SNO VARCHAR2(3) PRIMARY KEY,
  SNAME VARCHAR2(4) NOT NULL,
  SSEX VARCHAR(2) NOT NULL,
  SBIRTHDAY DATE,
  CLASS VARCHAR2(5) NOT NULL
)

CREATE TABLE COURSE
( CNO VARCHAR2(5) PRIMARY KEY,
  CNAME VARCHAR2(10) NOT NULL,
  TNO VARCHAR2(10) NOT NULL
)

CREATE TABLE SCORE
( SNO VARCHAR2(3) NOT NULL,
  CNO VARCHAR2(5) NOT NULL,
  DEGREE NUMBER(10,1) NOT NULL
)

CREATE TABLE TEACHER
( TNO VARCHAR2(3) PRIMARY KEY,
  TNAME VARCHAR2(4) NOT NULL,
  TSEX VARCHAR(2) NOT NULL,
  TBIRTHDAY DATE,
  PROF VARCHAR2(6),
  DEPART VARCHAR2(10)
)

INSERT INTO STUDENT VALUES('108','曾华','男',to_date('1977-09-01','yyyy-mm-dd'),'95033');
INSERT INTO STUDENT VALUES('105','匡明','男',to_date('1975-10-02','yyyy-mm-dd'),'95031');
INSERT INTO STUDENT VALUES('107','王丽','女',to_date('1976-01-23','yyyy-mm-dd'),'95033');
INSERT INTO STUDENT VALUES('101','李军','男',to_date('1976-02-20','yyyy-mm-dd'),'95033');
INSERT INTO STUDENT VALUES('109','王芳','女',to_date('1975-02-10','yyyy-mm-dd'),'95031');
INSERT INTO STUDENT VALUES('103','陆君','男',to_date('1974-06-03','yyyy-mm-dd'),'95031');
INSERT INTO STUDENT VALUES('110','王大锤','男',to_date('1974-06-03','yyyy-mm-dd'),'95031');
INSERT INTO STUDENT VALUES('112','老王','男',null,'95031');

INSERT INTO COURSE VALUES('3-105','计算机导论','825');
INSERT INTO COURSE VALUES('3-245','操作系统','804');
INSERT INTO COURSE VALUES('6-166','数据电路','856');
INSERT INTO COURSE VALUES('9-888','高等数学','831');

INSERT INTO SCORE VALUES('103','3-245','86');
INSERT INTO SCORE VALUES('105','3-245','75');
INSERT INTO SCORE VALUES('109','3-245','68');
INSERT INTO SCORE VALUES('103','3-105','92');
INSERT INTO SCORE VALUES('105','3-105','88');
INSERT INTO SCORE VALUES('109','3-105','76');
INSERT INTO SCORE VALUES('101','3-105','64');
INSERT INTO SCORE VALUES('107','3-105','91');
INSERT INTO SCORE VALUES('108','3-105','78');
INSERT INTO SCORE VALUES('101','6-166','85');
INSERT INTO SCORE VALUES('107','6-106','79');
INSERT INTO SCORE VALUES('108','6-166','81');
INSERT INTO SCORE VALUES('999','6-166','81');
INSERT INTO SCORE VALUES('999','6-166','81');

INSERT INTO TEACHER VALUES('804','李诚','男',to_date('1958-12-02','yyyy-mm-dd'),'副教授','计算机系');
INSERT INTO TEACHER VALUES('856','张旭','男',to_date('1969-03-12','yyyy-mm-dd'),'讲师','电子工程系');
INSERT INTO TEACHER VALUES('825','王萍','女',to_date('1972-05-05','yyyy-mm-dd'),'助教','计算机系');
INSERT INTO TEACHER VALUES('831','刘冰','女',to_date('1977-08-14','yyyy-mm-dd'),'助教','电子工程系');

题目

1、 查询Student表中的所有记录的Sname、Ssex和Class列。
2、 查询教师所有的单位即不重复的Depart列。
3、 查询Student表的所有记录。
4、 查询Score表中成绩在60到80之间的所有记录。
5、 查询Score表中成绩为85，86或88的记录。
6、 查询Student表中“95031”班或性别为“女”的同学记录。
7、 以Class降序查询Student表的所有记录。
8、 以Cno升序、Degree降序查询Score表的所有记录。
9、 查询“95031”班的学生人数。
10、查询Score表中的最高分的学生学号和课程号。
11、查询‘3-105’号课程的平均分。
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
13、查询最低分大于70，最高分小于90的Sno列。
14、查询所有学生的Sname、Cno和Degree列。
15、查询所有学生的Sno、Cname和Degree列。
16、查询所有学生的Sname、Cname和Degree列。
17、查询“95033”班所选课程的平均分。
18、假设使用如下命令建立了一个grade表：
create table grade(low NUMERIC(3,0),upp NUMERIC(3,0),rank char(1));
insert into grade values(90,100,’A’);
insert into grade values(80,89,’B’);
insert into grade values(70,79,’C’);
insert into grade values(60,69,’D’);
insert into grade values(0,59,’E’);
commit;
现查询所有同学的Sno、Cno和rank列。
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
23、查询“张旭“教师任课的学生成绩。
24、查询选修某课程的同学人数多于5人的教师姓名。
25、查询95033班和95031班全体学生的记录。
26、查询存在有85分以上成绩的课程Cno.
27、查询出“计算机系“教师所教课程的成绩表。
28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
31、查询所有教师和同学的name、sex和birthday.
32、查询所有“女”教师和“女”同学的name、sex和birthday.
33、查询成绩比该课程平均成绩低的同学的成绩表。
34、查询所有任课教师的Tname和Depart.
35 查询所有未讲课的教师的Tname和Depart.
36、查询至少有2名男生的班号。
37、查询Student表中不姓“王”的同学记录。
38、查询Student表中每个学生的姓名和年龄。
39、查询Student表中最大和最小的Sbirthday日期值。
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
41、查询“男”教师及其所上的课程。
42、查询最高分同学的Sno、Cno和Degree列。
43、查询和“李军”同性别的所有同学的Sname.
44、查询和“李军”同性别并同班的同学Sname.
45、查询所有选修“计算机导论”课程的“男”同学的成绩表

答案

–1、 查询Student表中的所有记录的Sname、Ssex和Class列

select SNAME,SSEX,CLASS from STUDENT;

–2、 查询教师所有的单位即不重复的Depart列。

select distinct DEPATRT from TEACHER;
select DEPATRT from TEACHER group by DEPATRT;

–3、 查询Student表的所有记录。

select * from STUDENT;

–4、 查询Score表中成绩在60到80之间的所有记录。

select * from SCORE where DEGREE between 60 and 80;

–5、 查询Score表中成绩为85，86或88的记录。

select * from SCORE where DEGREE='85' or DEGREE='86' or DEGREE='88' ;
select * from score where degree in('85','86','88');

–6、 查询Student表中“95031”班或性别为“女”的同学记录。

select * from STUDENT where CLASS='95031' or SSEX='女';

–7、 以Class降序查询Student表的所有记录。

select * from STUDENT order by CLASS desc;

–8、 以Cno升序、Degree降序查询Score表的所有记录。

select * from SCORE order by CNO ,DEGREE desc;

–9、 查询“95031”班的学生人数。

select count(sno) from STUDENT where CLASS='95031';

–10、查询Score表中的最高分的学生学号和课程号。

select sno,cno from score where degree in(select max(degree) from score);

–11、查询‘3-105’号课程的平均分。

select avg(DEGREE) from SCORE where CNO ='3-105';

–12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

select cno,avg(degree) from score where cno like'3%' group by cno having count(1)>5;

–13、查询最低分大于70，最高分小于90的Sno列

select SNO from SCORE where DEGREE>'70' and DEGREE<'90';            
select sno,max(degree),min(degree) from score group by sno having max(degree)<90 and min(degree)>70;

–14、查询所有学生的Sname、Cno和Degree列。

select sname,cno,degree from student stu inner join score  sco on stu.sno=sco.sno;

–15、查询所有学生的Sno、Cname和Degree列。

select stu2.sno,cou.cname,stu2.degree from (select sco.sno,sco.cno,sco.degree from student stu full join score sco on stu.sno=sco.sno) stu2
inner join course cou on stu2.cno=cou.cno;

select SNO,CNAME,DEGREE from SCORE join COURSE on COURSE.CNO=SCORE.CNO;

–16、查询所有学生的Sname、Cname和Degree列

select stu1.sname,stu2.cname,stu1.Degree from (select sname,degree,cno from student inner join score on student.sno=score.sno) stu1
inner join course stu2 on stu1.cno=stu2.cno;

SELECT STUDENT.SNAME,CNAME,DEGREE FROM STUDENT join SCORE on STUDENT.SNO=SCORE.SNO join COURSE on COURSE.CNO=SCORE.CNO;

–17、查询“95033”班所选课程的平均分。
–理解有歧义：
–理解一：95033班所选课程每课程的平均成绩
–方法一：

select cname,avg(degree) from student inner join score on student.sno=score.sno
inner join course on course.cno=score.cno where class='95033' group by cname;

–方法二：

select cname,avg(degree) from course cou inner join (select * from student stu inner join score sco on stu.sno=sco.sno where class='95033') tab1 
on cou.cno=tab1.cno group by cname;

select cname,avg(degree) from (select * from student stu inner join score sco on stu.sno=sco.sno where class='95033') tab1 
inner join  course cou on cou.cno=tab1.cno group by cname; 

–理解二：95033班所选课程总的平均成绩

select avg(degree) from score where sno in (select sno from student where class='95033');               
select avg(degree) from score,student where student.sno=score.sno and class='95033';

–18、假设使用如下命令建立了一个grade表：
create table grade(low NUMERIC(3,0),upp NUMERIC(3,0),rank char(1));
insert into grade values(90,100,‘A’);
insert into grade values(80,89,‘B’);
insert into grade values(70,79,‘C’);
insert into grade values(60,69,‘D’);
insert into grade values(0,59,‘E’);
commit;
select * from grade;
–现查询所有同学的Sno、Cno和rank列。

select sno,cno,rank from score,grade where degree between low and upp;

–19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

select * from student stu inner join
(select * from score where cno='3-105' and degree>all(select degree from score where sno='109'))
 tab1 on stu.sno=tab1.sno;
 

 select * from student,score where score.cno='3-105' and student.sno=score.sno and score.degree>(
select degree from SCORE where cno='3-105' and sno='109');

select * from score,student where student.sno=score.sno and score.cno='3-105' and score.degree>(select degree from SCORE where cno='3-105' and sno='109');

–20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。

select * from score s1,(select sno,max(degree) ma 
from score group by sno having count(1)>1) s2 where s1.sno=s2.sno and s1.degree!=s2.ma

–21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

select * from student stu full join score sco on stu.sno=sco.sno
where degree>all(select degree from score where sno='109') and sco.cno='3-105';

select * from student,score where student.sno=score.sno 
and score.degree>all(select degree from score where sno='109') and score.cno='3-105';

–22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

select sno,sname,sbirthday from student where sbirthday in(select sbirthday from student where sno='108'); 

select sno,sname,sbirthday from student 
where to_char(sbirthday,'yyyy')=(select to_char(sbirthday,'yyyy') from student where sno='108');

–23、查询“张旭“教师任课的学生成绩。

select tname,sname,degree,cname from  student stu inner join score sco on stu.sno=sco.sno
inner join course cou on cou.cno=sco.cno
inner join teacher tea on tea.tno=cou.tno where tea.tname='张旭';

select degree from score where cno in (select cno from course where tno in(select tno from teacher where tname='张旭'));

–24、查询选修某课程的同学人数多于5人的教师姓名。

select tname  from TEACHER
  where tno in (select tno from COURSE where cno in 
        (select cno from SCORE sc  group by cno having count(sc.sno)>5));

select tname from teacher where teacher.tno=(
select tno from course where course.cno=(
select cno from score group by cno having count(*)>5));
 

–25、查询95033班和95031班全体学生的记录。

select * from student where class='95033' or class='95031';

select * from student where class='95033' or class='95031' order by class;

–26、查询存在有85分以上成绩的课程Cno.

select distinct cno from score where degree>85;

–27、查询出“计算机系“教师所教课程的成绩表。

select score.*,student.* from score inner join student on student.sno=score.sno where cno in(
       select cno from course  where tno in
              (select tno from teacher where depatrt ='计算机系'));

–更优

select * from score join course on course.cno=score.cno join teacher on teacher.tno=course.tno where teacher.depatrt='计算机系';

–28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

select tname,prof from teacher where prof not in (select prof from teacher where depatrt ='电子工程系' and prof in (
select prof from teacher where depatrt='计算机系')) and depatrt in ('计算机系','电子工程系');

–29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学

的Cno、Sno和Degree,并按Degree从高到低次序排序。
select cno,sno,degree from score where cno='3-105' and degree>(select min(degree) from score where cno='3-245') order by degree desc;

–30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的

Cno、Sno和Degree.
select * from score where cno='3-105'and degree>all(select degree from score where cno='3-245');

select * from score where cno='3-105' and degree>(select max(degree) from score where cno='3-245');

–31、查询所有教师和同学的name、sex和birthday.

select sname,ssex,sbirthday from student union select tname,tsex,tbirthay from teacher;

–32、查询所有“女”教师和“女”同学的name、sex和birthday.

select sname,ssex,sbirthday from student where ssex='女' 
 union select tname,tsex,tbirthay from teacher where tsex='女';

–33、查询成绩比该课程平均成绩低的同学的成绩表。

select * from score s1,(select cno,avg(degree)ma from score group by cno) s2 where s1.cno=s2.cno and s1.degree<s2.ma

 select * from score a where a.degree<(select avg(degree) from score b where a.cno=b.cno group by cno);

–34、查询所有任课教师的Tname和Depart.

select tname,depatrt from teacher where tno in(select tno from course where cno in(select cno from score));

–35 查询所有未讲课的教师的Tname和Depart.

select tname,depatrt from teacher where tno not in(select tno from course where cno in(select cno from score));

–36、查询至少有2名男生的班号。

select class from (select class,count(1) from student where ssex ='男' group by class having count(1)>=2);

select class from student where ssex='男'group by class having count(*)>=2;

–37、查询Sudent表中不姓“王”的同学记录。

select * from student where sno not in(select sno from student where sname like'王%');

select * from student where sname not like '王%';

–38、查询Student表中每个学生的姓名和年龄。

select sname,(to_char(sysdate,'yyyy')-to_char(sbirthday,'yyyy')) from student;

–39、查询Student表中最大和最小的Sbirthday日期值。

select  max(sbirthday) 最大的,min(sbirthday) 最小的  from student;

–40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

select * from student order by class,sbirthday desc;

select * from student order by class desc,to_char(sysdate,'yyyy')-to_char(sbirthday,'yyyy') desc;

–41、查询“男”教师及其所上的课程。

select tea.tname,cname,tea.tsex from teacher tea inner join course cou on cou.tno= tea.tno where tsex='男';

–42、查询最高分同学的Sno、Cno和Degree列。

select sno,cno,degree from score where degree in(select max(degree) from score);

–43、查询和“李军”同性别的所有同学的Sname.

select sname from student where ssex in (select ssex from student where sname='李军');

–、44查询和“李军”同性别并同班的同学Sname.

select sname from student where ssex in (select ssex from student where sname='李军') and class in (select class from student where sname='李军');

–45、查询所有选修“计算机导论”课程的“男”同学的成绩表

select * from score  inner join student on student.sno=score.sno
inner join course on course.cno =score.cno where ssex='男' and cname='计算机导论';

select * from score where sno in(select sno from student where ssex='男') and cno in(select cno from course where cname='计算机导论') ;