第1页 / 共10页
第2页 / 共10页
第3页 / 共10页
第4页 / 共10页
第5页 / 共10页
第6页 / 共10页
第7页 / 共10页
第8页 / 共10页
山东大学数据库实验答案.doc
实验六
实验七.综合查询
实验八.报表统计
数据库实验（一）
1、create table test1_teacher(
tid
sex
char(6)
char(2),age
primary key,name varchar(10) not null,
int,
dname varchar(10)
)
create index index_table1 on test1_teacher(name);
insert into test1_teacher values('100101','张老师','男',44,'计算机
学院');
insert into test1_teacher values('100102','李老师','女',45,'软件学
院');
insert into test1_teacher values('100103','马老师','男',46,'计算机
学院');
2、create table test1_student(
sid char(12) primary key, name varchar(10) not null,sex
char(2),
age
varchar(10))
int,
birthday
date,
dname
varchar(10),class
create index index_table2 on test1_student(name);
insert into test1_student values('200800020101','王欣','女',19,
to_date('19940202','yyyymmdd'),'
计算机学院','2010');
insert into test1_student values('200800020102','李华','女',20,
to_date('19950303','yyyymmdd'),'
软件学院','2009');
insert into test1_student values('200800020103','赵岩','男',18,
to_date('19960404','yyyymmdd'),'
软件学院','2009');
3、create table test1_course( cid char(6) primary key, name
not null,
varchar(10)
fcid char(6),credit numeric(2,1) )
create index index_table3 on test1_course(name);
insert into test1_course values('300001','数据结构','',2);
insert into test1_course values('300002','数据库','300001',2.5);
insert into test1_course values('300003','操作系统','300001',4);
4、 create table test1_student_course(
, score numeric(5,1), tid char(6),
sid char(12) , cid char(6)
primary key(sid,cid),
FOREIGN KEY (sid) REFERENCES test1_student(sid),
FOREIGN KEY (cid) REFERENCES test1_course(cid),
FOREIGN KEY (tid) REFERENCES test1_teacher(tid) )
insert into test1_student_course
values('200800020101','300001',91.5,'100101');
insert into test1_student_course
values('200800020101','300002',92.6,'100102');
insert into test1_student_course
values('200800020101','300003',93.7,'100103');
5、create table
test1_teacher_course(
tid char(6)
, cid char(6)
,
primary key(tid,cid),
FOREIGN KEY (tid) REFERENCES test1_teacher(tid),
FOREIGN KEY (cid) REFERENCES test1_course(cid) )
insert into test1_teacher_course values('100101','300001');
insert into test1_teacher_course values('100102','300002');
insert into test1_teacher_course values('100103','300003');
数据库实验（二） 检索查询
1、create table test2_01 as select sid ，name
from pub.student
where sid not in(select sid from pub.student_course)
2、create table test2_02 as select distinct student.sid，name
from pub.student, pub.student_course
where
(select cid
from pub.student_course
student_course.sid = student.sid and student_course.cid in
where sid='200900130417')
3、create table test2_03 as select distinct student.sid，name
from pub.student, pub.student_course
where
(select cid
from pub.course
where fcid='300002')
student_course.sid = student.sid and student_course.cid in
4、create table test2_04 as select sid,name
from pub.student
where sid in
(select sid
from pub.student_course,pub.course
where student_course.cid=course.cid
and name ='操作系
统')
and sid in
(select sid
from pub.student_course,pub.course
where student_course.cid=course.cid
and name ='数据结构')
5.create table test2_05 as select student.sid,name，
cast(avg(score)
as
numeric(5,0))
avg_score,sum（score） sum_score
from pub.student,pub.student_course
where student.sid = student_course.sid and age ='20'
group by student.sid，name
使用 CAST：
使用 CONVERT： CONVERT (data_type[(length)], expression [, style])
CAST ( expression AS data_type )
6、create table test2_06 as select sid，max(score） max_score
from pub.student_course
group by cid
7.create table test2_07 as select sid，name
from pub.student
where name not in
（select name
from pub.student
where name like '张%' or name like '李%' or name like
'王%')
8、create table test2_08 as select substr(name,1,1) second_name,count(*)
p_count
from pub.student
group by substr(name,1,1)
SUBSTR(string,start,count)取子字符串，从 start 开始（如果 start 是负数，从尾部开始），
取 count 个.上述就是 PL/SQL 函数的解释，从中可以看出，是 1 开始从左开始取数；如果是
负值，那么就从右开始取数。
9、create table test2_09 as select student.sid，student.name，score
from pub.student,pub.student_course
where student.sid = student_course.sid and cid ='300003'
10、create table test2_10 as select sid，cid
from pub.student_course
where score is not null
实验三
1.create table test3_01 as select * from pub.student_31;delete
regexp_like(sid,'^[[:digit:]]+$')
from test3_01 where not
2.create table test3_02 as select * from pub.student_31;delete from test3_02 where sid not
in(select sid from test3_02 where age=2012-extract(year from birthday))
3.create table test3_03 as select * from pub.student_31;delete from test3_03 where sid not
in(select sid from test3_03 where sex='男'or sex='女'or sex is null)
4.create table test3_04 as select * from pub.student_31;delete from test3_04 where sid in(select
sid from test3_04 where dname is null or length(dname)<3 or dname like'% %')
5.create table test3_05 as select * from pub.student_31;delete from test3_05 where sid not
in(select sid from test3_05 where class
not like'%级' and length(class)=4)
6.
7.create table test3_07 as select * from pub.Student_course_32;delete from test3_07 where sid not
in(select
from
where
pub.student.sid=pub.Student_course_32.sid)
pub.student,pub.Student_course_32
pub.Student.sid
8-.create table test3_08 as select * from pub.Student_course_32;delete from test3_08 where sid
in(select pub.Student_course_32.sid from pub.teacher_course,pub.Student_course_32 where
pub.teacher_course.tid=pub.Student_course_32.tid
and
pub.teacher_course.cid=pub.Student_course_32.cid)
9.create table test3_09 as select * from pub.Student_course_32 where score between 0 and 100
10.create table test3_10 as select sid,pub.Student_course_32.cid,score,pub.Student_course_32.tid
from
=
pub.teacher_course.cid and pub.Student_course_32.tid = pub.teacher_course.tid
pub.Student_course_32,pub.teacher_course
pub.Student_course_32.cid
where
delete from test3_10 where sid not in(select sid from test3_10 where score between 0 and 100)
from
where
sid
delete
pub.student,pub.student_course_32 where pub.student.sid=pub.student_course_32.sid)
pub.student.sid
test3_10
not
in(select
from
delete from test3_10 where sid not in(select sid from pub.course,pub.student_course_32 where
pub.course.cid=pub.student_course_32.cid)
delete from test3_10 where sid not in(select sid from pub.teacher,pub.student_course_32 where
pub.teacher.tid=pub.student_course_32.tid
实验 4.
1.create table test4_01 as select * from pub.student_41
alter table test4_01 add(sum_score int,avg_score numeric(5,1),sum_credit int,did varchar(2))
test4_01
update
pub.student_course.sid=test4_01.sid group by pub.student_course.sid )
sum_score=(select
sum(score)
set
from pub.student_course where
2.create table test4_02 as select * from pub.student_41
alter table test4_02 add(sum_score int,avg_score numeric(5,1),sum_credit int,did varchar(2))
test4_02
update
pub.student_course.sid=test4_02.sid group by pub.student_course.sid )
avg_score=(select
avg(score)
set
from pub.student_course where
3.create table test4_03 as select * from pub.student_41
alter table test4_03 add(sum_score int,avg_score numeric(5,1),sum_credit int,did varchar(2))
update test4_03 set sum_credit=(select sum(credit) from pub.student_course,pub.course where
pub.student_course.cid=pub.course.cid
by
pub.student_course.sid)
pub.student_course.sid=test4_03.sid
group
and
4.create table test4_04 as select * from pub.student_41
alter table test4_04 add(sum_score int,avg_score numeric(5,1),sum_credit int,did varchar(2))
create table temp as select * from pub.department union select * from pub.department_41
update test4_04 set did=(select did from temp where temp.dname=test4_04.dname)
update test4_04 set did='00' where did is null
drop table temp
5.create table test4_05 as select * from pub.student_41
alter table test4_05 add(sum_score int,avg_score numeric(5,1),sum_credit int,did varchar(2))
set
test4_05
sum_score=(select
update
pub.student_course.sid=test4_05.sid group by pub.student_course.sid )
update
pub.student_course.sid=test4_05.sid group by pub.student_course.sid )
avg_score=(select
sum(score)
avg(score)
test4_05
from pub.student_course where
set
from pub.student_course where
update test4_05 set sum_credit=(select sum(credit) from pub.student_course,pub.course where
pub.student_course.cid=pub.course.cid
by
pub.student_course.sid)
pub.student_course.sid=test4_05.sid
group
and
create table temp as select * from pub.department union select * from pub.department_41
update test4_05 set did=(select did from temp where temp.dname=test4_05.dname)
update test4_05 set did='00' where did is null
drop table temp
6.create table test4_06 as select * from pub.student_42
update test4_06 set name=replace(name,' ','')
7.create table test4_07 as select * from pub.student_42
update test4_07 set sex=substr(sex,1,1) where sex like'_性'
update test4_07 set sex=replace(sex,' ','')
8.create table test4_08 as select * from pub.student_42
update test4_08 set class=substr(class,1,4) where class like '____级'
9.create table test4_09 as select * from pub.student_42
update test4_09 set age=2012-extract(year from birthday) where age is null
10.create table test4_10 as select * from pub.student_42
update test4_10 set name=replace(name,' ','')
update test4_10 set dname=replace(dname,' ','')
update test4_10 set sex=substr(sex,1,1) where sex like'_性'
update test4_10 set sex=replace(sex,' ','')
update test4_10 set class=substr(class,1,4) where class like '____级'
update test4_10 set age=2012-extract(year from birthday) where age is null
实验六
1.create view test6_01 as select sid ， name ， dname
from pub.student where age<20 and
dname='物理学院' order by sid
test6_02 as select pub.student.sid ， name ， sum(score) sum_score
2.create view
from
pub.student,pub.student_course where pub.student_course.sid=pub.student.sid and dname=' 软 件
学院' and class='2009' group by pub.student.sid,pub.student.name
view
test6_03
from
3.create
pub.student,pub.student_course,pub.course where pub.student_course.sid=pub.student.sid and
pub.student_course.cid=pub.course.cid and dname=' 计 算 机 科 学 与 技 术 学 院' and class='2010'
and pub.course.name='操作系统'
pub.student.sid,pub.student.name,score
select
as
view
4.create
from
pub.student,pub.student_course,pub.course where pub.student_course.sid=pub.student.sid and
pub.student_course.cid=pub.course.cid and pub.course.name='数据库系统' and score>90
pub.student.sid,pub.student.name
test6_04
select
as
5.create view
test6_05 as select pub.student.sid,pub.course.cid,pub.course.name,score from
pub.student,pub.student_course,pub.course where pub.student_course.sid=pub.student.sid and
pub.student_course.cid=pub.course.cid and pub.student.name='李龙'
6.create view
=all(select cid from pub.course) group by sid
test6_06 as select sid from pub.student_course where pub.student_course.cid
7.create view
pub.student.sid in(select sid from pub.student_course)
test6_07 as select pub.student.sid,pub.student.name from pub.student where
8.create view test6_08 as select a1.cid,a1.name from pub.course
a1,pub.course a2 where a1.fcid =a2.cid and a2.credit=2
9.create view test6_09 as select pub.student.sid,pub.student.name,sum(credit) sum_credit from
pub.student,pub.student_course,pub.course where pub.student_course.sid=pub.student.sid and
pub.student_course.cid=pub.course.cid and pub.student.class='2010' and pub.student.dname=' 化
学与化工学院' and score>=60 group by pub.student.sid,pub.student.name
10.create view
a1.fcid =a2.cid and a2.fcid is not nul
test6_10 as select a1.cid,a1.name from pub.course a1,pub.course a2 where
实验七.综合查询
1.
create view a as select SUBSTR(name,2,2) as First_name from pub.student
create view b as select First_name,count(First_name) as frequency from a group by First_name
create table test7_01 (First_name varchar(4),frequency numeric(4))
insert into test7_01(First_name,frequency) select * from b
2.
create view a as select SUBSTR(name,2,1) as letter
SUBSTR(name,3,1) as letter from pub.student where SUBSTR(name,3,1) is not null)
create view b as select letter,count(letter) as frequency from a group by letter
create table test7_02(letter varchar(2),frequency numeric(4))
insert into test7_02(letter,frequency) select * from b
from pub.student union all
(select
3.
create table test7_03 (dname varchar(30), class varchar(10), P_count1 int, P_count2 int, P_count
int)
insert into test7_03
select dname,class,0,0,COUNT(sid)
from pub.student
where dname is not null group by dname,class
create table t as select dname,class,pub.student.sid,sum(credit) sum_credit
from pub.student,pub.course,pub.student_course
where pub.student.sid=pub.student_course.sid and pub.student_course.cid= pub.course.cid
and score >= 60
and dname is not null group by dname,class,pub.student.sid
update test7_03 set p_count1=(select count(sid) num from t where sum_credit>= 10 and
test7_03.dname = t.dname and test7_03.class=t.class group by dname,class)
update test7_03 set p_count1= 0 where p_count1 is null
update test7_03 set p_count2 = p_count-p_count1
4.
create table test7_04 (dname varchar(30), class varchar(10), P_count1 int, P_count2 int, P_count
int)
insert into test7_04
select dname,class,0,0,COUNT(sid)
from pub.student
where dname is not null group by dname,class
create table t as select dname,class,pub.student.sid,sum(credit) sum_credit
from pub.student,pub.course,pub.student_course
where pub.student.sid=pub.student_course.sid and
pub.student_course.cid= pub.course.cid
and score >= 60 and dname is not null group by dname,class,pub.student.sid
update test7_04 set p_count1= (select count(sid) num from t where sum_credit>= 8 and
test7_04.dname
dname,class)where
test7_04.class=t.class
group
by
=
t.dname
and
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
