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='计算机导论') ;