Oracle查询练习1

1.创建用户并且授予权限

create user linklin identified by 123;

grant create session to linklin;
grant create table to linklin;
grant create tablespace to linklin;
grant unlimited tablespace to linklin;

2.创建表空间

create tablespace linklin
datafile 'C:\app\Administrator\oradata\orcl\linklin_space.dbf'
size 10m
autoextend on
next 10m maxsize 100m
extent management local;

alter user linklin default tablespace linklin;
/* 切换用户至linklin
在命令行窗口切换：conn linklin/123@ORCL;
*/

3.创建表格并设置约束

create table student(
       sno varchar2(3) not null primary key,
       sname varchar2(4) not null,
       ssex varchar2(2) not null,
       sbirthday date,
       class varchar2(5) not null
);

create table course(
       cno varchar2(5) not null 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) not null primary key,
       tname varchar2(4) not null,
       tsex varchar2(2) not null,
       tbirthday date not null,
       prof varchar2(6),
       depart varchar2(20)
);

alter table score add constraint pk_score primary key (sno,cno);
alter table course add constraint fk_tno foreign key(tno) references teacher(tno);
alter table score add constraint fk_sno foreign key(sno) references student(sno);
alter table score add constraint fk_cno foreign key(cno) references course(cno);

4.插入数据

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 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'),'助教','电子工程系');

insert into course values('3-105','计算机导论',825);
insert into course values('3-245','操作系统',804);
insert into course values('6-166','数据电路',856);

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-166',79);
insert into score values(108,'6-166',81);

5.各种查询

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 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 asc,degree desc;

9、 查询“95031”班的学生人数。
select class,count(*) 人数 from student where class='95031' group by class;
select class,count(*) 人数 from student group by class having class='95031';

10、查询Score表中的最高分的学生学号和课程号。
select sno,cno,degree from score order by degree desc;--测试
select sno,cno,degree from score where degree=(select max(degree) from score);

11、查询‘3-105’号课程的平均分。
select cno,avg(degree)平均分 from score where cno='3-105' group by cno;

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select cno,count(1) from score where cno like '3%' group by cno having count(1)>4;

13、查询最低分大于70，最高分小于90的Sno列。
select sno,degree from score where degree between 70 and 90;
select sno from score group by sno having max(degree)<90 and min(degree)>70;

14、查询所有学生的Sname、Cno和Degree列。
select sname,course.cno,degree from student,course,score where STUDENT.sno=SCORE.sno and COURSE.cno=SCORE.cno;

15、查询所有学生的Sno、Cname和Degree列。
select STUDENT.sno,cname,degree from student,course,score where STUDENT.sno=SCORE.sno and COURSE.cno=SCORE.cno;

16、查询所有学生的Sname、Cname和Degree列。
select sname,cname,degree from student,course,score where STUDENT.sno=SCORE.sno and COURSE.cno=SCORE.cno;

17、查询“95033”班所选课程的平均分。
select class,avg(degree) 平均分 from score ,student where SCORE.sno=STUDENT.sno and class='95033' group by class;
select avg(degree) from score where sno in(select sno from student where 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;
现查询所有同学的Sno、Cno和rank列。
create table grade(
low number(3,0),
upp number(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');
select sno,cno,degree,rank from score,grade where degree between grade.low and grade.upp;

19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select * from score where degree> all(select degree from score where sno='109' )and cno='3-105'; 大于109同学的所有成绩
select * from score where degree> any(select degree from score where sno='109' )and cno='3-105'; 大于109同学的任意成绩
select * from score where degree> all(select degree from score where sno='109' and cno='3-105')and cno='3-105'; 大于109同学选修3-105的成绩

!√20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
select * from score,(select sno,count(sno) num from score group by sno)b
where degree<(select max(degree) from score) and score.sno=b.sno and b.num>1;

select sno,degree from score where sno in(select sno from score group by sno having count(1)>1)
MINUS
select sno,max(degree) from score group by sno having count(1)>1;

21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select * from score where degree>(select degree from score where sno='109' and cno='3-105');
select * from score where degree>any(select degree from score where sno='109' or cno='3-105');
select * from score where degree>all(select degree from score where sno='109' or cno='3-105');

22、查询和学号为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');

23、查询“张旭“教师任课的学生成绩。
select student.sno 学号,sname 名字,degree 成绩,tname 任课老师 from student,course,teacher,score where tname='张旭' and student.sno=score.sno and course.cno=score.cno and teacher.tno=course.tno;

!√24、查询选修某课程的同学人数多于5人的教师姓名。
select distinct tname from(select tname,score.cno,sno from score,teacher,course
where (score.cno=course.cno and teacher.tno=course.tno ))a,(SELECT cno,count(cno) num from score group by cno)b
where a.cno=b.cno and b.num>5;
select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(1)>5));

25、查询95033班和95031班全体学生的记录。
select * from student where class in('95033','95031');

26、查询存在有85分以上成绩的课程Cno.
select cno from score where degree>85 group by cno;
select cno from score group by cno having max(degree)>85;

!√27、查询出“计算机系“教师所教课程的成绩表。
select score.* ,tname,depart from score,teacher,course where depart='计算机系'
and score.cno=course.cno and teacher.tno=course.tno order by sno,score.cno;
select * from score where cno in(select cno from course where tno in(select tno from teacher where depart='计算机系'));

!?28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
(select prof from teacher where depart='计算机系'
MINUS
select prof from teacher where depart='电子工程系')
UNION
(select prof from teacher where depart='电子工程系'
MINUS
select prof from teacher where depart='计算机系')

29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select sno,cno,degree from score where cno='3-105' and degree > any(select degree from score where cno='3-245') order by degree desc;

30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
select sno,cno,degree from score where cno='3-105' and degree > all(select degree from score where cno='3-245')

31、查询所有教师和同学的name、sex和birthday.
SELECT sname,ssex,sbirthday from student union
select tname,tsex,tbirthday from teacher;

32、查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT sname,ssex,sbirthday from student where ssex='女' union
select tname,tsex,tbirthday from teacher where tsex='女';

!√33、查询成绩比该课程平均成绩低的同学的成绩表。
select * from score,(select cno,avg(degree) avg_score from score group by cno) avgs where score.cno=avgs.cno
and score.degree<avg_score;

34、查询所有任课教师的Tname和Depart.
select distinct tname,depart from teacher,course where teacher.tno in (select tno from course);

35  查询所有未讲课的教师的Tname和Depart. 
select distinct tname,depart from teacher,course where teacher.tno <> all(select tno from course);
select distinct tname,depart from teacher,course where teacher.tno not in(select tno from course);

36、查询至少有2名男生的班号。
select class from student where ssex='男' group by class having count(1)>=2;

37、查询Student表中不姓“王”的同学记录。
select * from student where sname not like '王%';

!√38、查询Student表中每个学生的姓名和年龄。
select sname,floor(MONTHS_BETWEEN(sysdate,sbirthday)/12 )年龄 from student;
select sname,to_number(to_char(sysdate,'yyyy'))-to_number(to_char(sbirthday,'yyyy')) age from student;

39、查询Student表中最大和最小的Sbirthday日期值。
select max(sbirthday),min(sbirthday) from student;

40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select * from student order by class desc,sysdate-sbirthday desc;

41、查询“男”教师及其所上的课程。
select tname,tsex,cname from course,teacher where tsex='男' and teacher.tno=course.tno;
select * from course where tno in (select tno from teacher where tsex='男');

42、查询最高分同学的Sno、Cno和Degree列。
select sno,cno,degree from score where degree in (select max(degree) from score);

43、查询和“李军”同性别的所有同学的Sname.
select sname,ssex,class from student where ssex=(select ssex from student where sname='李军');
!√44、查询和“李军”同性别并同班的同学Sname.
select sname,ssex,class from student where ssex=(select ssex from student where sname='李军')
and class=(select class from student where sname='李军') ;
select a.sname,a.ssex,a.class from student a,(select ssex,class,sname from student where sname='李军') b
where a.ssex=b.ssex and a.class=b.class and a.sname<>b.sname;

45、查询所有选修“计算机导论”课程的“男”同学的成绩表
select sname,cname,ssex,degree from course,score,student where cname='计算机导论' and ssex='男'
and student.sno=score.sno and course.cno=score.cno;
select * from score where cno in (select cno from course where cname='计算机导论') and sno in(select sno from student where ssex='男');