关于Oracle练习

–关于Oracle序列
create sequence 序列名
increment by 1 --序列步长,默认1 如果出现负值,则递减
start with 1 --初始值 默认1
maxvalue 99999 --定义序列生成器能产生的最大值,递增 10的27次方,递减-1
minvalue 1 --定义序列生成器能产生的最小值, 递增1,递减10的26次方
cycle --当序列生成器值达到限值后是否继续循环, cycle(循环),nocycle(不循环)
cache 20 – 缓冲,定义存放序列的内存大小.默认20,nocache代表对序列不进行缓冲,定义缓冲可改善对序列的性能

– Create sequence
create sequence SEQ_SEQUENCE
minvalue 1
maxvalue 9999999
start with 1
increment by 1
cache 50;

–Ssex varchar2(2) check( gender in (‘男’, ‘女’)), 表示此列只包含这两种数据,其他的报错

–Student 学生
create table Student(
Sno varchar2(3) primary key,
Sname varchar2(8) not null,
Ssex varchar2(2) NOT NULL,
Sbirthday varchar2(10) ,
SClass varchar2(10)
);
comment on column student.Sno
is ‘学号’;
comment on column student.Sname
is ‘学生姓名’;
comment on column student.Ssex
is ‘学生性别’;
comment on column student.Sbirthday
is ‘学生出生年月’;
comment on column student.SClass
is ‘学生所在班级’;

–Course 课程
create table Course(
Cno varchar2(5) primary key,–主
Cname varchar2(10)not null,
Tno varchar2(3) not null --外
);
comment on column Course.Cno
is ‘课程号（主码）’;
comment on column Course.Cname
is ‘课程名称’;
comment on column Course.Tno
is ‘教工编号（外码）’;

–Score 分数
create table Score(
Sno varchar2(3) not null,–外
Cno varchar2(5) not null,–外
Degrees varchar2(10) not null
);

comment on column Score.Sno
is ‘学号（外码）’;
comment on column Score.Cno
is ‘课程号（外码）’;
comment on column Score.Degrees
is ‘成绩’;

–Teacher 老师
create table Teacher(
Tno varchar2(5) primary key,–主
Tname varchar2(10) not null,
Tsex varchar2(5) not null,
Tbirthday varchar2(20),
Prof varchar2(6),
Depart varchar2(10) not null
);
comment on column Teacher.Tno
is ‘教工编号（主码）’;
comment on column Teacher.Tname
is ‘教工姓名’;
comment on column Teacher.Tsex
is ‘教工性别’;
comment on column Teacher.Tbirthday
is ‘教工出生年月’;
comment on column Teacher.Prof
is ‘职称’;
comment on column Teacher.Depart
is ‘教工所在部门’;

alter table score add foreign key(Sno) references Student(Sno);
alter table score add foreign key(Cno) references Course(Cno);
alter table Course add foreign key(Tno) references Teacher(Tno);

select * from Student;
select * from Course;
select * from Score for update;
select * from Teacher;

drop table Student;

insert into Student values
(‘105’,‘匡明’,‘男’,‘1975-10-02’, ‘95031’);
insert into Student values
(‘108’,‘曾华’,‘男’,‘1977-09-01’, ‘95033’);
insert into Student values
(‘107’,‘王丽’,‘女’,‘1976-01-23’, ‘95033’);
insert into Student values
(‘101’,‘李军’,‘男’,‘1976-02-20’, ‘95033’);
insert into Student values
(‘109’,‘王芳’,‘女’,‘1975-02-10’, ‘95031’);
insert into Student values
(‘103’,‘陆君’,‘男’,‘1974-06-03’, ‘95031’);

---

insert into Teacher values
(‘804’,‘李诚’,‘男’,‘1958-12-02’, ‘副教授’,‘计算机系’);
insert into Teacher values
(‘856’,‘张旭’,‘男’,‘1969-03-12’, ‘讲师’,‘电子工程系’);
insert into Teacher values
(‘825’,‘王萍’,‘女’,‘1972-05-05’, ‘助教’,‘计算机系’);
insert into Teacher values
(‘831’,‘刘冰’,‘女’,‘1977-08-14’, ‘助教’,‘电子工程系’);

---

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(‘103’,‘3-105’,‘92’);
insert into Score values(‘101’,‘6-166’,‘85’);
insert into Score values(‘107’,‘3-105’,‘91’);
insert into Score values(‘107’,‘6-166’,‘79’);
insert into Score values(‘105’,‘6-166’,‘86’);

---

1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select Sname,Ssex,SClass from Student

2、 查询教师所有的单位即不重复的Depart列。
select distinct(Depart) from Teacher

3、 查询Student表的所有记录。
select Sno,Sname,Ssex,Sbirthday,SClass from Student

4、 查询Score表中成绩在60到80之间的所有记录。
select * from Score where Degrees between ‘60’ and ‘80’

5、 查询Score表中成绩为85，86或88的记录。
select * from Score where Degrees in (85,86,88)

6、 查询Student表中“95031”班或性别为“女”的同学记录。
select * from Student where SCLASS = ‘95031’ and SSex = ‘女’

7、 以Class降序查询Student表的所有记录。
select * from Student order by SCLASS DESC

8、 以Cno升序、Degree降序查询Score表的所有记录。
select * from Score order by Cno asc , Sno desc

9、 查询“95031”班的学生人数。
select count(SClass) from Student where SClass = ‘95031’

10、 查询Score表中的最高分的学生学号和课程号。（子查询或者排序）

select sc.sno, sc.Cno from Score sc where sc.Degrees = (select max(Degrees) from Score)

11、 查询每门课的平均成绩。
select css.Cname, avg(css.Degrees) FROM (
select co.Cname,sc.Degrees
from Course co, Score sc
where co.cno = sc.cno
) css group by css.Cname

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

–以3开头的课程
select co.Cno, Cname from Course co where co.Cno like ‘3-%’

–以3开头的分数
select sc.sno, sc.cno,sc.Degrees from Score sc where sc.cno like ‘3-%’

–以3开头订课大于5的课程 top
select co.Cname
from Course co
where co.Cno = (
select css.cnos
from (select count(sc.sno) as nums, sc.cno as cnos
from Score sc
where sc.cno like ‘3-%’
group by sc.Cno) css
where rownum=1)

13、查询分数大于70，小于90的Sno列。
select * from Course;
select * from Score;
select *from Student;

select * from
(select sc.degrees as cj, sc.sno as xh, sc.cno as kh
from Score sc where sc.degrees > 70 and sc.degrees < 90) ssc
left join Course co on ssc.kh = co.cno
left join Student st on st.sno = ssc.xh
left join Teacher te on te.tno = co.tno
14、查询所有学生的Sname、Cno和Degree列。
select st.sname, sc.cno, sc.degrees from Student st left join Score sc on st.sno = sc.sno

15、查询所有学生的Sno、Cname和Degree列。
select st.Sname,sc.degrees, sc.Cno, co.Cname from Student st left join Score sc on sc.Sno = st.Sno left join Course co on co.Cno = sc.Cno

17、 查询“95033”班学生的平均分。
select st.Sno from Student st where SClass = ‘95033’
select avg(sc.degrees) from Score sc, (select st.Sno as sts from Student st where SClass = ‘95033’) ssc where sc.sno = ssc.sts

---

补充题(未完): 如遇到一题多条为拆分书写过程

18、 假设使用如下命令建立了一个grade表：
create table grade(low int,upp int,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’);

19、 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select * from Student st ,Score sc where sc.Sno = st.Sno and sc.Cno = ‘3-105’ and Sname != ‘王芳’; --选修3-105课程的同学的分数
select * from Student st ,Score sc where sc.Sno = st.Sno and sc.Sno = ‘109’ --109号学生的分数

select DISTINCT t.Sno, t.Sname, t.SSex, t.Sbirthday, t.SClass from (
select
st.Sno as Sno,
st.Sname as Sname,
st.SSex as SSex,
st.Sbirthday as Sbirthday,
st.SClass as SClass,
sc.Degrees as Degrees
from Student st ,Score sc
where sc.Sno = st.Sno and sc.Cno = ‘3-105’ and Sname != ‘王芳’) t,(
select
sc.Degrees as Degrees
from Student st ,Score sc
where sc.Sno = st.Sno and sc.Sno = ‘109’) c
where t.Degrees > c.Degrees

20、查询score中选学多门课程的同学 中分数为非最高分成绩的记录。
select count(sc.sno) as snum , sc.sno, max(sc.Degrees) as Degrees from Score sc group by sc.sno --进行学号分组

select * from (select count(sc.sno) as snum , sc.sno as sno, max(sc.Degrees) as Degrees from Score sc group by sc.sno) s left join Student st on s.sno = st.sno where s.snum > 2 --如果学号数量大于1代表此学生选修了多门课程

select s.sno, s.Degrees from (select count(sc.sno) as snum , sc.sno as sno, max(sc.Degrees) as Degrees from Score sc group by sc.sno) s left join Student st on s.sno = st.sno where s.snum > 2

select
sc.sno, sc.degrees, ww.names, ww.ssex, ww.sbirthday, ww.sclass
from
Score sc, (
select s.Degrees as Degrees, s.sno as sno , st.sname as names, st.ssex as ssex, st.sbirthday as sbirthday, st.sclass as sclass
from (
select count(sc.sno) as snum , sc.sno as sno, max(sc.Degrees) as Degrees from Score sc group by sc.sno) s
left join Student st on s.sno = st.sno where s.snum > 1) ww
where sc.Sno = ww.sno and sc.Degrees!= ww.Degrees

21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select sc.degrees from Score sc where sc.sno = ‘109’ and cno = ‘3-105’; --学号为109 课程号为3-105的成绩
select * from Score sc where sc.degrees > (select sc.degrees from Score sc where sc.sno = ‘109’ and cno = ‘3-105’)

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

select substr(st.Sbirthday,1,4) from Student st where st.sno = ‘108’; --取出学号为108学生生日前4位(年份)
select * from Student st where substr(st.sbirthday,1,4) = (select substr(st.Sbirthday,1,4) from Student st where st.sno = ‘108’)

23、查询“张旭“教师任课的学生成绩。
select * from Teacher te
left join Course co on te.tno = co.tno
left join Score sc on sc.cno = co.cno
left join Student st on st.sno = sc.sno
where te.tname =‘张旭’;

24、查询选修某课程的同学人数多于5人的教师姓名。
select * from grade;
select * from Course
select * from Score;
select * from Student;
select * from Teacher;

select count(sc.cno) as cnoum, sc.cno from Score sc group by sc.cno;
select * from
(select count(sc.cno) as cnoum, sc.cno from Score sc group by sc.cno) s
left join Course co on s.cno = co.cno
left join Teacher te on te.tno = co.tno
where s.cnoum > 5

25、查询95033班和95031班全体学生的记录。
select * from Student st where st.sclass= ‘95033’
union all
select * from Student st where st.sclass= ‘95031’

纯属个人练习,恢复熟悉查询写法用,未完成,后续有时间就练习更新