Oracle简单查询练习题（附数据库及答案）

72 阅读 0 评论 48 点赞

我是靠谱客的博主怕孤独月亮，最近开发中收集的这篇文章主要介绍Oracle简单查询练习题（附数据库及答案），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

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

建立STUDENT表

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表

CREATE TABLE COURSE
(CNO VARCHAR2(10) NOT NULL,
CNAME VARCHAR2(20) NOT NULL,
TNO VARCHAR2(20) NOT NULL);

建立SCORE表

CREATE TABLE SCORE
(SNO VARCHAR2(10) NOT NULL,
CNO VARCHAR2(10) NOT NULL,
DEGREE NUMBER NOT NULL);

建立TEACHER表

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数据

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数据

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数据

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数据

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列。

select sname ,ssex ,class	
	from student;

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

select distinct depart
	from teacher;

–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=85 or degree=88;

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

select *
	from student
	where class='95301' or ssex='女';

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

select *
	from student
	order by class desc;

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

select *
	from score
	order by cno asc,degree desc;

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

select count(*)
	from student
	where class ='95031';

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

select sno,cno
	from score
	where degree = (
		select max(degree) 
		from score);  //虚表   //单值

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

select avg(degree) 
	from score
	where cno='3-105';

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

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列。

select sno
	from score
	group by sno
	having min(degree)>70 and max(degree)<90;

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

select sname , s.cno ,s.degree
	from student , score s
	where student.sno = s.sno;

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

select sno , cname , degree
	from course , score
	where course.cno = score.cno;

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

select sname , cname ,degree
	from student,course,score
	where student.sno = score.sno and score.cno=course.cno;

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

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

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

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”号同学成绩的所有同学的记录。

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”的成绩的所有记录。

select sno,cno
from score
where degree>(select degree 
from score 
where sno='109' and cno='3-105')

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

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

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

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班全体学生的记录。

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

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

select distinct cno
from score
where degree>'85';

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

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,并按

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.

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.

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.

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、查询成绩比该课程平均成绩低的同学的成绩表。

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.

select tname , depart
from teacher
where tno in(select tno
from course)

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

select tname , depart
from teacher
where tno not in(select tno
from course)

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

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

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

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

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

select sname ,trunc(months_between(sysdate,sbirthday)/12)
from student;

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

select max(sbirthday),min(sbirthday)
from student;

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

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、查询“男”教师及其所上的课程。

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列。

select sno,cno,degree 
from score 
where degree=(select max(degree)
from score)

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

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;