我是靠谱客的博主 怕孤独月亮,这篇文章主要介绍Oracle简单查询练习题(附数据库及答案),现在分享给大家,希望可以做个参考。

切记插入表时会话要给成英文会话,否则在插入STUDENT和TEACHER表时日期项会报错
(解决办法:百度查修改ORACLE 会话)

建立STUDENT表
复制代码
1
2
3
4
5
6
7
CREATE TABLE STUDENT (SNO VARCHAR2(10) NOT NULL, SNAME VARCHAR2(10) NOT NULL, SSEX VARCHAR2(10) NOT NULL, SBIRTHDAY DATE, CLASS NUMBER NOT NULL);
建立COURSE表
复制代码
1
2
3
4
5
CREATE TABLE COURSE (CNO VARCHAR2(10) NOT NULL, CNAME VARCHAR2(20) NOT NULL, TNO VARCHAR2(20) NOT NULL);
建立SCORE表
复制代码
1
2
3
4
5
CREATE TABLE SCORE (SNO VARCHAR2(10) NOT NULL, CNO VARCHAR2(10) NOT NULL, DEGREE NUMBER NOT NULL);
建立TEACHER表
复制代码
1
2
3
4
5
6
7
8
CREATE TABLE TEACHER (TNO VARCHAR2(10) NOT NULL, TNAME VARCHAR2(10) NOT NULL, TSEX VARCHAR2(10) NOT NULL, TBIRTHDAY DATE NOT NULL, PROF VARCHAR2(10), DEPART VARCHAR2(20) NOT NULL);
插入STUDENT数据
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,to_date('1977-09-01','yyyy-mm-dd'),95033); INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,to_date('1975-10-02','yyyy-mm-dd'),95031); INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,to_date('1976-01-23','yyyy-mm-dd'),95033); INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,to_date('1976-02-20','yyyy-mm-dd'),95033); INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,to_date('1975-02-10','yyyy-mm-dd'),95031); INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,to_date('1974-06-03','yyyy-mm-dd'),95031); commit;
插入COURSE数据
复制代码
1
2
3
4
5
6
7
8
9
10
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825); INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804); INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856); INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100); commit;
插入SCORE数据
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86); INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75); INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68); INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92); INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88); INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76); INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64); INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91); INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78); INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85); INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79); INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81); commit;
插入TEACHER数据
复制代码
1
2
3
4
5
6
7
8
9
10
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'李诚','男',to_date('1958-12-02','yyyy-mm-dd'),'副教授','计算机系'); INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'张旭','男',to_date('1969-03-12','yyyy-mm-dd'),'讲师','电子工程系'); INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'王萍','女',to_date('1972-05-05','yyyy-mm-dd'),'助教','计算机系'); INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'刘冰','女',to_date('1977-08-14','yyyy-mm-dd'),'助教','电子工程系'); commit;

练习题目

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

复制代码
1
2
3
select sname ,ssex ,class from student;

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

复制代码
1
2
3
select distinct depart from teacher;

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

复制代码
1
2
select * from student;

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

复制代码
1
2
3
4
select * from score where degree between 60 and 80;

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

复制代码
1
2
3
4
select * from score where degree=85 or degree=85 or degree=88;

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

复制代码
1
2
3
4
select * from student where class='95301' or ssex='女';

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

复制代码
1
2
3
4
select * from student order by class desc;

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

复制代码
1
2
3
4
select * from score order by cno asc,degree desc;

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

复制代码
1
2
3
4
select count(*) from student where class ='95031';

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

复制代码
1
2
3
4
5
6
select sno,cno from score where degree = ( select max(degree) from score); //虚表 //单值

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

复制代码
1
2
3
4
select avg(degree) from score where cno='3-105';

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

复制代码
1
2
3
4
5
6
7
8
select avg(degree) from score where cno = (select cno from score group by cno having count(cno)>=5 ) and cno like '3%';

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

复制代码
1
2
3
4
5
select sno from score group by sno having min(degree)>70 and max(degree)<90;

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

复制代码
1
2
3
4
select sname , s.cno ,s.degree from student , score s where student.sno = s.sno;

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

复制代码
1
2
3
4
select sno , cname , degree from course , score where course.cno = score.cno;

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

复制代码
1
2
3
4
select sname , cname ,degree from student,course,score where student.sno = score.sno and score.cno=course.cno;

–17、查询“95033”班所选课程的平均分。

复制代码
1
2
3
4
select avg(degree) from score,student where class = '95033' and score.sno=student.sno;

–18、假设使用如下命令建立了一个grade表:
–现查询所有同学的Sno、Cno和rank列。

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table grade (low number,upp number,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 sno,cno,rank from score,grade where score.degree between grade.low and grade.upp;

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

复制代码
1
2
3
4
5
6
7
8
select p.sno from (select degree ,sno from score where cno='3-105'and sno!='109') p where p.degree>(select degree from score where cno='3-105' and sno ='109');

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

复制代码
1
2
3
4
5
6
select sno,cno from score where degree>(select degree from score where sno='109' and cno='3-105')

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

复制代码
1
2
3
4
5
6
7
select sno,sname,sbirthday from student where to_char(sbirthday,'yyyy')=( select to_char(sbirthday,'yyyy') from student where sno='108');

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

复制代码
1
2
3
4
5
6
7
8
9
10
select sname,degree from student ,score where cno=( select cno from course where tno = ( select tno from teacher where tname='张旭')) and student.sno = score.sno;

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

复制代码
1
2
3
4
select * from student where class='95033' or class='95031';

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

复制代码
1
2
3
4
select distinct cno from score where degree>'85';

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

复制代码
1
2
3
4
5
6
7
8
select sname,degree from score,student,(select cno from course s ,(select tno from teacher where depart='计算机系') p where s.tno=p.tno) f where student.sno=score.sno and score.cno = f.cno;

–27、查询选修编号为“3-105“且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按

复制代码
1
2
3
4
5
6
7
8
Degree从高到低次序排序。 select cno,sno,degree from score where degree>(select min(degree) from score where cno='3-245') and cno='3-105'; order by degree desc;

–28、查询选修编号为“3-105”且成绩高于选修编号为“3-245”的同学的Cno、Sno和Degree.

复制代码
1
2
3
4
5
6
7
8
select cno,p.sno,degree from score s, (select s1.sno,s1.degree as d1,s2.degree as d2 from score s1,score s2 where s1.cno='3-105' and s2.cno='3-245' and s1.sno=s2.sno) p where p.d1>p.d2 and p.sno=s.sno;

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

复制代码
1
2
3
4
5
select name,sex,birthday from (select sname as name,ssex as sex ,sbirthday as birthday from student) union all (select tname as name,tsex as sex ,tbirthday as birthday from teacher)

—-30、查询所有“女”教师和“女”同学的name、sex和birthday.

复制代码
1
2
3
4
5
6
7
select name,sex,birthday from (select name,sex,birthday from (select tname as name ,tsex as sex ,tbirthday as birthday from teacher) union all (select sname as name ,ssex as sex ,sbirthday as birthday from student)) where sex='女';

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

复制代码
1
2
3
4
5
6
select degree,a.cno,sno from score s,(select avg(degree) as avgdegree,cno from score group by cno) a where s.degree<a.avgdegree and s.cno=a.cno;

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

复制代码
1
2
3
4
5
select tname , depart from teacher where tno in(select tno from course)

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

复制代码
1
2
3
4
5
select tname , depart from teacher where tno not in(select tno from course)

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

复制代码
1
2
3
4
5
6
select class from student where ssex='男' group by class having count(*)>=2;

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

复制代码
1
2
3
4
select sname from student where sname not like '王%';

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

复制代码
1
2
3
select sname ,trunc(months_between(sysdate,sbirthday)/12) from student;

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

复制代码
1
2
3
select max(sbirthday),min(sbirthday) from student;

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

复制代码
1
2
3
4
5
6
select * from student,(select sno,trunc(months_between(sysdate,sbirthday)/12) as age from student) a where a.sno=student.sno order by class desc , a.age desc;

—-39、查询“男”教师及其所上的课程。

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
select cname from course c1,( select cno from teacher,course where teacher.tno = course.tno and teacher.tsex='男') c2 where c1.cno = c2.cno; select cname from course where tno in(select tno from teacher where tsex='男')

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

复制代码
1
2
3
4
5
select sno,cno,degree from score where degree=(select max(degree) from score)

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

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
select sname degree from student,score ,( select sno from student where ssex='男') p where cno = (select cno from course where cname='计算机导论') and score.sno = p.sno and student.sno=score.sno;

最后

以上就是怕孤独月亮最近收集整理的关于Oracle简单查询练习题(附数据库及答案)的全部内容,更多相关Oracle简单查询练习题(附数据库及答案)内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
点赞(49)

评论列表共有 0 条评论

立即
投稿
返回
顶部