山东大学数据库实验答案2—8

合集下载

山大数据库简答题整理

1、事务的定义及其特性答：事务是由一系列操作序列构成的程序执行单元，这些操作要么都做，要么都不做，是一个不可分割的工作单位。

事务的ACID特性：原子性(Atomicity) 事务中包含的所有操作要么全做，要么全不做一致性(Consistency) 事务的隔离执行必须保证数据库的一致性隔离性(Isolation) 系统必须保证事务不受其它并发执行事务的影响持久性(Durability) 一个事务一旦提交之后，它对数据库的影响必须是永久的。

2、完整性约束：数据库完整性（Database Integrity）是指数据库中数据的正确性、有效性和相容性。

数据库完整性由各种各样的完整性约束来保证，因此可以说数据库完整性设计就是数据库完整性约束的设计。

1、实体完整性：要求每个关系模式有且仅有一个主码，每个主码的值必须唯一，而且不能为空。

2、域完整性：数据库表中的列必须满足某种特定的数据类型或约束。

其中约束又包括取值范围、精度等规定。

表中的CHECK、FOREIGN KEY 约束和DEFAULT、 NOT NULL定义都属于域完整性的范畴。

3、参照完整性：参照的完整性要求关系中不允许引用不存在的实体。

当更新、删除、插入一个表中的数据时，通过参照引用相互关联的另一个表中的数据，来检查对表的数据操作是否正确。

3.DBMS数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。

它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。

用户通过DBMS访问数据库中的数据，数据库管理员也通过dbms进行数据库的维护工作。

它可使多个应用程序和用户用不同的方法在同时或不同时刻去建立，修改和询问数据库。

大部分DBMS提供数据定义语言DDL（Data Definition Language）和数据操作语言DML（Data Manipulation Language），供用户定义数据库的模式结构与权限约束，实现对数据的追加、删除等操作。

文献检索实验报告

实验1：各种类型中外文文献检索目的：1．学会电子图书检索。

2．学会中文学位论文检索。

3．学会中文会议论文检索。

4．学会中文期刊论文检索。

5．学会国内重要专业人士、重要研究机构、重要文献、重要期刊等信息检索。

6．学会标准文献检索。

7. 学会科技成果检索。

8. 学会外文期刊论文检索。

内容：1．自选检索课题。

2．分析检索课题，确定检索标识，编写提问式。

3．利用超星电子图书(免费阅览室)、、国家科技图书文献中心等相关数据库。

4. 在相关检索提问框中输入提问式实施检索，筛选命中文献，选择检索结果输出格式并输出结果。

如有必要可反复修改检索提问式优化检索结果。

5．记录检索过程，完成检索报告并提交。

要求：1.电子图书选择或其他电子书全文数据库中的1种数据库进行电子图书检索：数据库名称___超星电子图书(免费阅览室)_________检索策略（表达式）___TI=悲惨世界__________命中文献数____128________选取其中1条文献题录：书名：悲惨世界作者姓名：雨果出版时间：2006年12月第2版总页数：2552.中文学位论文选择中国知网数字出版平台——中国优秀硕士学位论文全文数据库、中国博士学位论文全文数据库、国家科技图书文献中心——中文学位论文等数据库中的1种数据库进行学位论文检索：数据库名称______中国优秀硕士学位论文全文数据库______________检索年限____________2010-2012_____________________检索策略（表达式）______su=电路原理___________________命中文献数_____42_______选取其中1条文献题录：论文题目：电力电子电路中快标分叉及混沌控制研究作者姓名：胡乃红导师姓名：陈军宁；周宇飞授予学位级别：博士作者专业名称：电路与系统学位授予单位：安徽大学授予学位时间：2012论文总页数：1013.会议论文选择中国知网数字出版平台——中国重要会议论文全文数据库、国家科技图书文献中心——中文会议论文等数据库中的1种数据库进行会议论文检索：数据库名称____中国重要会议论文全文数据库____________检索年限_____2010-2013_____________________检索策略（表达式）____ KV_=老年文学___________命中文献数____54________选取其中1条文献题录：论文题目：不断创新学会工作思路合力助推老龄事业发展作者姓名：陈跃第一作者单位：陕西省老年学学会会议名称：陕西老年学通讯会议地点：陕西省老年学学会主办单位：陕西省老年学学会∙出处（会议录名称、出版时间、论文在会议录中的页码范围）：【会议时间】2013-01-01∙【分类号】D669.6-264.中文期刊论文选择中国知网数字出版平台——中国学术期刊网络出版总库、国家科技图书文献中心——中文期刊等数据库中的1种数据库进行中文电子期刊检索：数据库名称__中国学术期刊网络出版总库______检索年限_______201_________________检索策略（表达式）__SU_=高校建设______命中文献数____8________选取其中1条文献题录：论文题目：高校学科建设项目目标集成管理研究作者姓名：贾晓霞第一作者单位：上海海事大学经济管理学院出处（出版物名称、年、卷、期、页码范围）：现代教育科学2012年11期5.国内重要专业人士、重要研究机构、重要文献、重要期刊等信息利用中国知网数字出版平台——中国学术期刊网络出版总库进行相关信息检索：数据库名称_中国知网数字出版平台——中国学术期刊网络出版总库_____检索年限_________2010-2012_________________________检索策略（表达式）____kv=文学________________________命中文献数_____980_______选取其中1条文献题录：（1）该领域重要作者（收录文献总数位于前三名）：第一名：收录文献数量：8作者姓名：吴建民作者单位：徐州师范大学文学院第二名：收录文献数量：8作者姓名：金里作者单位：复旦大学第三名：收录文献数量：8作者姓名：王永平作者单位：扬州大学社会发展学院（2）该领域重要机构（收录文献总数位于前三名）：第一名：收录文献数量：106单位名称：南京大学第二名：收录文献数量：106单位名称：四川大学第三名：收录文献数量：102单位名称：山东大学（3）该领域重要文献（文献被引频次位于前三名）：第一名：文献题目：躲避崇高作者姓名：王蒙文献来源：读书发表时间：1993被引频次：208作者单位名称：第二名：文献题目：美国生态文学批评述略作者姓名：朱新福文献来源：当代外国文学发表时间：2003被引频次：145作者单位名称：苏州大学外国语学院第三名：文献题目：审美意识形态论作为文艺学的第一原理作者姓名：童庆炳文献来源：学术研究发表时间：2000被引频次：105作者单位名称：北京师范大学中文系（4）该领域重要中文期刊（收录文献总数位于前三名）：第一名：收录文献数量：8632期刊名称：萌芽第二名：收录文献数量：6574期刊名称：小说月报第三名：收录文献数量：6340期刊名称：当代6.标准文献选择国家科技图书文献中心——标准检索、标准网——标准目录高级查询等数据库中的1种数据库进行标准检索：数据库名称___标准网——标准目录高级查询_________________检索年限___________2008—2012____________________检索策略（表达式）___SU=生物学_____________________命中文献数____124________选取其中1条文献题录：标准名称：淀粉水分测定烘箱法标准编号：GB/T 12087-2008起草单位或起草人：国家粮食局科学研究院实施日期：2008-10-01发布日期：2008-05-27分类号：X11标准文献页数：137.科技成果选择国家科技成果数据库国家科技成果网——成果高级检索、中国知网数字出版平台——国家科技成果数据库、国家科技图书文献中心——中国科技成果等数据库中的1种数据库进行科技成果检索：数据库名称_____国家科技成果数据库_______________检索年限_______2012________________________检索策略（表达式）_______KV_=通信技术_________命中文献数____59________选取其中1条文献题录：成果名称：高精度遥感卫星图像定位分析关键技术与应用分类号：【中图分类号】TP75【学科分类号】420.20完成单位：中国科学院研究生院成果公布日期：20128.外文期刊论文选择国家科技图书文献中心——西文期刊检索、中国知网数字出版平台——德国SPRINGER公司期刊数据库、Emerald管理学、经济学、工程学数据库等数据库中的1种数据库进行外文电子期刊检索：数据库名称_中国知网数字出版平台——德国SPRINGER公司期刊数据库__检索年限____2010——2012_____________检索策略（表达式）____su=生物医学_______命中文献数______52_____选取其中1条文献题录：论文题目：Spectral-domain optical coherence tomography in healed ocular toxoplasmosis作者姓名：(1) Sandeep Saxena(2) Ramit A. K. Rastogi(3) Kuldeep Vishvkarma(4) Sanjiv Hansraj(5) Carsten H. Meyer出处（出版物名称、年、卷、期、页码范围）：Springer-Verlag Received: 2011-03-31;Published online: 2011-08-10; 109～111实验2：利用搜索引擎及网上免费资源检索相关课题的文献目的：1.学会搜索引擎高级检索的技巧2.学会网上免费资源的使用方法内容：1．自选检索课题。

山东大学数据库实验答案2—8

⼭东⼤学数据库实验答案2—8⼭东⼤学数据库实验答案2—8 CREATE TABLE test2_01 ASSELECT SID, NAMEFROM pub.STUDENTWHERE sid NOT IN(SELECT sid FROM pub.STUDENT_COURSE)CREATE TABLE test2_02 ASSELECT SID,NAMEFROM PUB.STUDENTWHERE SID IN(SELECT DISTINCT SIDFROM PUB.STUDENT_COURSEWHERE CID IN(SELECT CID FROM PUB.STUDENT_COURSE WHERESID='200900130417'))CREATE TABLE test2_03 ASselect SID,NAME from PUB.STUDENT where SID in ( select distinct SID from PUB.STUDENT_COURSE where CID in (select CID from PUB.COURSE where FCID='300002') )CREATE TABLE test2_04 ASselect SID,NAME from PUB.STUDENT where SID in ( select distinct SID from PUB.STUDENT_COURSE where CID in (select CID from PUB.COURSE where NAME='操作系统')intersectselect distinct SID from PUB.STUDENT_COURSE where CID in (select CID from PUB.COURSE where NAME='数据结构') )create table test2_05 aswith valid_stu(sid,name) as(select SID,NAME from PUB.STUDENT where AGE=20 and SID in (select SID from PUB.STUDENT_COURSE))select sid,name as name,ROUND(avg(score)) as avg_score,sum(score) as sum_score fromPUB.STUDENT_COURSE natural join valid_stu where SID in (select SID from valid_stu)group by SID,NAMEcreate table test2_06 asselect CID,(max(SCORE))max_score from PUB.STUDENT_COURSE group byCIDcreate table test2_07 asselect SID,NAME from PUB.STUDENT where(NAME not like '张%' and NAME not like '李%' and NAME not like '王%') create table test2_08 aswith xing(value) as (select substr(NAME,1,1) from PUB.STUDENT)select value as second_name,count(value) as p_count from xing group by valuecreate table test2_09 asselect distinct SID,NAME,SCORE from PUB.STUDENT_COURSE natural join PUB.STUDENTwhere CID ='300003';create table test2_10 asselect distinct SID,CID from PUB.STUDENT_COURSE where SID in (select SID from PUB.STUDENT_COURSE) /* 1 */create table test3_01 asselect * from pub.student_31 where regexp_like(SID,'^[0-9]+$')/* 2 */create table test3_02 asselect * from pub.student_31where to_number(substr(BIRTHDAY,8,2))+AGE=112/* 3 */create table test3_03 asselect * from pub.student_31where SEX is null or SEX='男' or SEX='⼥'/* 4 */create table test3_04 asselect * from pub.student_31where DNAME is not null and length(DNAME)>=3 and instr(DNAME,' ')<=0 /* 5 */create table test3_05 asselect * from pub.student_31where regexp_like(CLASS,'^[0-9]{4}$')/* 6 */create table test3_06 asselect * from pub.student_31where regexp_like(SID,'^[0-9]+$')and to_number(substr(BIRTHDAY,8,2))+AGE=112 and (SEX is null orSEX='男' or SEX='⼥')and (DNAME is not null and length(DNAME)>=3 and instr(DNAME,' ')<=0) and regexp_like(CLASS,'^[0-9]{4}$') and (instr(NAME,' ')<=0 and length(NAME)>=2)/* 7 */create table test3_07 asselect * from pub.student_course_32 where SID in (select SID from pub.student)/* 8 */create table test3_08 asselect * from pub.student_course_32 natural join pub.teacher_course /* 9 */create table test3_09 asselect * from pub.student_course_32 where SCORE>=0 and score <=100 /* 10 */create table test3_10 asselect * from pub.student_course_32 natural join pub.teacher_course where SID in (select SID from pub.student) and CID in (select CID from pub.course) and TID in (select TID from pub.teacher) and SCORE>=0 and score <=100/*-------------- test4_01 --------------*/ create table test4_01 as select * from pub.student_41;ALTER TABLE test4_01 ADD sum_score number;update test4_01 set sum_score=(select sum(score) from pub.student_course a wheretest4_01.sid=a.sid group by sid);/*-------------- test4_02 --------------*/create table test4_02 as select * from pub.student_41;ALTER TABLE test4_02 ADD avg_score number;update test4_02 set avg_score =(select round(avg(score),1) from pub.student_course a wheretest4_02.sid=a.sidgroup by sid);/*-------------- test4_03 --------------*/create table test4_03 as select * from pub.student_41;ALTER TABLE test4_03 ADD sum_credit number;update test4_03 set sum_credit =(with a as (select * from pub.student_course natural join pub.course) (select sum(credit) from awhere SCORE>=60 and test4_03.sid=a.sid group by sid) )/* 4 */create table test4_04 as select * from pub.student_41;update test4_04 set dname=(select did from pub.department wheretest4_04.dname=pub.department.dname)where dname in (select dname from pub.department);/* 5 */create table test4_05 as select * from pub.student_41;ALTER TABLE test4_05 add did varchar(2);ALTER TABLE test4_05 ADD sum_score number;ALTER TABLE test4_05 ADD avg_score number;ALTER TABLE test4_05 ADD sum_credit number;update test4_05 set sum_score=(select sum(score) from pub.student_course a wheretest4_05.sid=a.sid group by sid);update test4_05 set avg_score =(select round(avg(score),1) from pub.student_course a wheretest4_05.sid=a.sidgroup by sid);update test4_05 set sum_credit =(with a as (select * from pub.student_course natural join pub.course) (select sum(credit) from awhere SCORE>=60 and test4_05.sid=a.sid group by sid));update test4_05 set did =(select did from pub.department wherepub.department.dname=test4_05.dname) where dname in (select dname from pub.department);update test4_05 set did =(select did from pub.department_41 where pub.department_41.dname=test4_05.dname) where dname in (select dname from pub.department_41);update test4_05 set did ='00' where did is null;/* 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(trim(SEX),1,1) where length(SEX)<>1;/* 8 */create table test4_08 as select * from pub.student_42;update test4_08 set CLASS=substr(CLASS,1,4) where length(CLASS)<>4;/* 9 */create table test4_09 as select * from pub.student_42;update test4_09 set AGE=112-to_number(substr(BIRTHDAY,8,2)) where AGE is null;/* 10 */create table test4_10 as select * from pub.student_42;update test4_10 set name=replace(name,' ',''),DNAME=replace(DNAME,' ','');update test4_10 set SEX=substr(trim(SEX),1,1) where length(SEX)<>1;update test4_10 set CLASS=substr(CLASS,1,4) where length(CLASS)<>4;update test4_10 set AGE=112-to_number(substr(BIRTHDAY,8,2)) where AGE is null;create table test5_10(test varchar(20),age numeric (3));insert into test5_10 values('结果1',88);insert into test5_10 values('结果2',90);insert into test5_10 values('结果3',90);insert into test5_10 values('结果4',86);insert into test5_10 values('结果5',90);insert into test5_10 values('结果6',90);insert into test5_10 values('结果7',86);insert into test5_10 values('结果8',86);insert into test5_10 values('结果9',76);insert into test5_10 values('结果10',86);/* 1 */create view test6_01 asselect sid,name,dname from pub.studentwhere age<20 and dname='物理学院' order by sid/* 2 */create view test6_02 aswith temp_table(sid,ss) as(select sid,sum(score) from pub.student_course group by sid) select sid,name,dname,ss as sum_score from pub.student natural join temp_table where class='2009' and dname='软件学院'/* 3 */create view test6_03 asselect * from pub.student_course natural join pub.student whereclass='2010' and dname='计算机科学与技术学院' and cid =(select cid from pub.course where name='操作系统')/* 4 */create view test6_04 asselect sid,/doc/06a78e778662caaedd3383c4bb4cf7ec4bfeb671.html from pub.student_course natural join pub.studentwhere score>90 and cid =(select cid from pub.course where name='数据库系统')/* 5 */create view test6_05 asselect sid,cid,name,score from pub.student_course natural joinpub.coursewhere sid in (select sid from pub.student where name='李龙')/* 6 */create view test6_06 aswith a as (select sid,count(*) as totc from pub.student_course group by sid)select sid,name from pub.student where sid in (select sid from a where totc>=(selectcount(*) from pub.course))/* 7 */create view test6_07 asselect * from test6_06 where sid not in (select distinct sid from pub.student_course where sid in(select sid from test6_06)and score<60)/* 8 */create view test6_08 asselect cid,name from pub.coursewhere fcid in (select cid from pub.course where credit=2)/* 9 */create view test6_09 aswith a(sid,sum_credit) as(select sid,sum(credit) as sum_creditfrom pub.student_course natural join pub.course where SCORE>=60 group by sid)select sid,name,sum_credit from pub.student natural join a where class='2010' and dname='化学与化⼯学院'/* 10 */create view test6_10 asselect cid,name from pub.coursewhere fcid in(select cid from pub.course where fcid is not null)/* 1 */create table test7_01 asselect First_name,count(*) as frequency from( select substr(NAME,2) as First_name from pub.student ) group by First_name /* 2 */create table test7_02 asselect letter,count(*) as frequency from(select substr(NAME,2,1) as letter from pub.studentunion allselect substr(NAME,3) as letter from pub.student wherelength(Name)=3 ) group by letter/* 3 */create table test7_03 aswith a as (select sid,sum(credit) as tot from pub.student_course natural join pub.course where SCORE>=60 group by sid),b as (select * from pub.student natural left join a),c as (select dname,class,count(sid) as p_count from b group by (dname,class)),d as (select dname,class,count(sid) as p_count1 from b where tot>=10 group by (dname,class)),e as (select dname,class,count(sid) as p_count2 from b where tot<10 or tot is null group by (dname,class))select * from c natural left join d natural join eupdate test7_03 set p_count1=0 where p_count1 is null;/* 4 */create view test7_04_v1 asselect * from pub.student natural left join(select sid,sum(credit) as tot from pub.student_course natural join pub.course where SCORE>=60 group by sid);create table test7_04 aswith b as (select * from test7_04_v1),c as (select dname,class,count(sid) as p_count from b group by (dname,class)),d as (select dname,class,count(sid) as p_count1 from b where class<=2008 and tot>=8 group by (dname,class)unionselect dname,class,count(sid) as p_count1 from b where class>2008 and tot>=10 group by (dname,class))select * from c natural left join d where dname is not null;alter table test7_04 add p_count2 NUMBER;update test7_04 set p_count2 = p_count - p_count1;/* 1 */create table test8_01 aswith A as(select DNAME,SCORE,NAME from(select CID,DNAME,SCORE from pub.student_course natural joinpub.student where dnameis not null) natural join pub.course),B as(select dname,round(avg(score)) as avg_ds_score from A where name='数据结构' groupby dname),C as(select dname,round(avg(score)) as avg_os_score from A where name='操作系统' groupby dname)select * from B natural join C/* 2 */create table test8_02 aswith a as(select sid from pub.student_course where cid=(select cid frompub.course where name='操作系统') intersectselect sid from pub.student_course where cid=(select cid frompub.course where name='数据结构')),b as (select a.sid,score as ds_score from pub.student_course,a where pub.student_course.sid=a.sid and cid=(select cid from pub.course where name='数据结构')),c as (select a.sid,score as os_score from pub.student_course,a where pub.student_course.sid=a.sid and cid=(select cid from pub.course where name='操作系统'))select sid,name,dname,ds_score,os_score from b natural join cnatural join pub.student where dname='计算机科学与技术学院' /* 3 */create table test8_03 aswith a as(select sid from pub.student_course where cid=(select cid frompub.course where name='操作系统') unionselect sid from pub.student_course where cid=(select cid frompub.course where name='数据结构')),b as (select a.sid,score as ds_score from pub.student_course,a where pub.student_course.sid=a.sid and cid=(select cid from pub.course where name='数据结构')),c as (select a.sid,score as os_score from pub.student_course,a wherepub.student_course.sid=a.sid and cid=(select cid from pub.course where name='操作系统'))select sid,name,dname,ds_score,os_score from b natural full join c natural join pub.student where dname='计算机科学与技术学院' /* 4 */create table test8_04 aswith a as(select sid from pub.student_course where cid=(select cid frompub.course where name='操作系统') unionselect sid from pub.student_course where cid=(select cid frompub.course where name='数据结构')),b as (select a.sid,score as ds_score from pub.student_course,a where pub.student_course.sid=a.sid and cid=(select cid from pub.course where name='数据结构')),c as (select a.sid,score as os_score from pub.student_course,a where pub.student_course.sid=a.sid and cid=(select cid from pub.course where name='操作系统'))select sid,name,dname,ds_score,os_score from b natural full join c natural full join pub.student where dname='计算机科学与技术学院'。

山东大学数据库实验答案

数据库实验（一）1、create table test1_teacher(tid char(6) primary key,name varchar(10) not null,sex char(2),age 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 int, birthday date, dname varchar(10),class varchar(10))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 varchar(10) not null,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(sid char(12) , cid char(6) , score numeric(5,1), tid 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_coursevalues('200800020101','300001',91.5,'100101');insert into test1_student_coursevalues('200800020101','300002',92.6,'100102');insert into test1_student_coursevalues('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 ，namefrom pub.studentwhere sid not in(select sid from pub.student_course)2、create table test2_02 as select distinct student.sid，namefrom pub.student, pub.student_coursewhere student_course.sid = student.sid and student_course.cid in (select cid from pub.student_course where sid='200900130417')3、create table test2_03 as select distinct student.sid，namefrom pub.student, pub.student_coursewhere student_course.sid = student.sid and student_course.cid in (select cid from pub.course where fcid='300002')4、create table test2_04 as select sid,namefrom pub.studentwhere sid in(select sidfrom pub.student_course,pub.coursewhere student_course.cid=course.cid and name ='操作系统')and sid in(select sidfrom pub.student_course,pub.coursewhere student_course.cid=course.cidand name ='数据结构')5.create table test2_05 as select student.sid,name，cast(avg(score) as numeric(5,0)) avg_score,sum（score） sum_scorefrom pub.student,pub.student_coursewhere student.sid = student_course.sid and age ='20'group by student.sid，name使用CAST： CAST ( expression AS data_type )使用CONVERT： CONVERT (data_type[(length)], expression [, style])6、create table test2_06 as select sid，max(score） max_scorefrom pub.student_coursegroup by cid7.create table test2_07 as select sid，namefrom pub.studentwhere name not in（select namefrom pub.studentwhere name like '张%' or name like '李%' or name like '王%')8、create table test2_08 as select substr(name,1,1) second_name,count(*) p_countfrom pub.studentgroup by substr(name,1,1)SUBSTR(string,start,count)取子字符串，从start开始（如果start是负数，从尾部开始），取count个.上述就是PL/SQL函数的解释，从中可以看出，是1开始从左开始取数；如果是负值，那么就从右开始取数。

山东大学数据库实验4

根据院系名称到pub.department或者pub.department_41中，找到对应编号，填写到院系编号中，如果都没有对应的院系，则填写为00。
create table test4_04 as select * from pub.student_41
alter table test4_04
add avg_score numeric(4,1)
alter table test4_03
add sum_credit int
alter table test4_03
add did varchar(2)
create table use as
select sid,sum(credit) sumcredit
8．将pub用户下的Student_42及数据复制到主用户的表test4_08中,对表中的数据进行整理，修复那些不规范的数据：
对班级列进行规范（需要先确定哪些班级不规范）。
9．将pub用户下的Student_42及数据复制到主用户的表test4_09中,对表中的数据进行整理，修复那些不规范的数据：
年龄为空值的根据出生日期设置学生年龄（年龄=2012-出生年份），年龄不为空值的不要改变。
10．将pub用户下的Student_42及数据复制到主用户的表test4_10中,对表中的数据进行整理，修复那些不规范的数据：
（1）剔除姓名列中的所有空格；
（2）剔除院系名称列中的所有空格；
（3）对性别列进行规范（需要先确定哪些性别数据不规范，也就是那些和大多数不一样的就是不规范的）；
where test4_03.sid=use.sid)
4．将pub用户下表student_41及数据复制到主用户的表test4_04中，使用alter table语句为表增加五个列：“总成绩:sum_score”、 “平均成绩:avg_score”、“总学分:sum_credit”、“院系编号:did varchar(2) ”。

2022年山东大学数据科学与大数据技术专业《数据库系统原理》科目期末试卷B(有答案)

2022年山东大学数据科学与大数据技术专业《数据库系统原理》科目期末试卷B（有答案）一、填空题1、在SELECT命令中进行查询，若希望查询的结果不出现重复元组，应在SEL ECT语句中使用______保留字。

2、在SELECT命令中，______子句用于选择满足给定条件的元组，使用______子句可按指定列的值分组，同时使用______子句可提取满足条件的组。

3、对于非规范化的模式，经过转变为1NF，______，将1NF经过转变为2NF，______，将2NF经过转变为3NF______。

4、如图所示的关系R的候选码为；R中的函数依赖有；R属于范式。

一个关系R5、在设计局部E-R图时，由于各个子系统分别有不同的应用，而且往往是由不同的设计人员设计，所以各个局部E-R图之间难免有不一致的地方，称为冲突。

这些冲突主要有______、______和______3类。

6、某在SQL Server 2000数据库中有两张表：商品表（商品号，商品名，商品类别，成本价）和销售表（商品号，销售时间，销售数量，销售单价）。

用户需统计指定年份每类商品的销售总数量和销售总利润，要求只列出销售总利润最多的前三类商品的商品类别、销售总数量和销售总利润。

为了完成该统计操作，请按要求将下面的存储过程补充完整。

7、数据库系统是利用存储在外存上其他地方的______来重建被破坏的数据库。

方法主要有两种：______和______。

8、数据仓库创建后，首先从______中抽取所需要的数据到数据准备区，在数据准备区中经过净化处理______，再加载到数据仓库中，最后根据用户的需求将数据发布到______。

9、数据库管理系统的主要功能有______________、______________、数据库的运行管理以及数据库的建立和维护等4个方面。

10、数据仓库主要是供决策分析用的______，所涉及的数据操作主要是______，一般情况下不进行。

山大数据库简答题整理汇编

1、事务的定义及其特性答：事务是由一系列操作序列构成的程序执行单元，这些操作要么都做，要么都不做，是一个不可分割的工作单位。

2、完整性约束：数据库完整性（Database Integrity）是指数据库中数据的正确性、有效性和相容性。

数据库完整性由各种各样的完整性约束来保证，因此可以说数据库完整性设计就是数据库完整性约束的设计。

1、实体完整性：要求每个关系模式有且仅有一个主码，每个主码的值必须唯一，而且不能为空。

2、域完整性：数据库表中的列必须满足某种特定的数据类型或约束。

其中约束又包括取值范围、精度等规定。

表中的CHECK、FOREIGN KEY 约束和DEFAULT、 NOT NULL定义都属于域完整性的范畴。

3、参照完整性：参照的完整性要求关系中不允许引用不存在的实体。

当更新、删除、插入一个表中的数据时，通过参照引用相互关联的另一个表中的数据，来检查对表的数据操作是否正确。

3.DBMS数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。

它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。

用户通过DBMS访问数据库中的数据，数据库管理员也通过dbms进行数据库的维护工作。

它可使多个应用程序和用户用不同的方法在同时或不同时刻去建立，修改和询问数据库。

山东大学数据库DB实验8

1.create table tmp as select dname,round(avg(score),0) as avg_ds_score from pub.student,pub.student_course,pub.course where pub.student.sid=pub.student_course.sid and pub.course.cid=pub.student_course.cid and dname is not null and ='数据结构' group by dname
create table test8_01 as select tmp.dname,tmp.avg_ds_score,tmp1.avg_os_score from tmp,tmp1 where tmp.dname=tmp1.dname
drop table tmp
drop table tmp1
2.create table tmp as select pub.student.sid,,dname,score as ds_score from pub.student,pub.student_course,pub.course where pub.student.sid=pub.student_course.sid and pub.course.cid=pub.student_course.cid and dname='计算机科学与技术学院' and ='数据结构' and in (select from pub.student,pub.student_course,pub.course where pub.student.sid=pub.student_course.sid and pub.course.cid=pub.student_course.cid and dname='计算机科学与技术学院' and ='操作系统')

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

山东大学数据库实验答案2—8 CREATE TABLE test2_01 ASSELECT SID, NAMEFROM pub.STUDENTWHERE sid NOT IN(SELECT sid FROM pub.STUDENT_COURSE)CREATE TABLE test2_02 ASSELECT SID,NAMEFROM PUB.STUDENTWHERE SID IN(SELECT DISTINCT SIDFROM PUB.STUDENT_COURSEWHERE CID IN(SELECT CID FROM PUB.STUDENT_COURSE WHERESID='200900130417'))CREATE TABLE test2_03 ASselect SID,NAME from PUB.STUDENT where SID in ( select distinct SID from PUB.STUDENT_COURSE where CID in (select CID from PUB.COURSE where FCID='300002') )CREATE TABLE test2_04 ASselect SID,NAME from PUB.STUDENT where SID in ( select distinct SID from PUB.STUDENT_COURSE where CID in (select CID from PUB.COURSE where NAME='操作系统')intersectselect distinct SID from PUB.STUDENT_COURSE where CID in (select CID from PUB.COURSE where NAME='数据结构'))create table test2_05 aswith valid_stu(sid,name) as(select SID,NAME from PUB.STUDENT where AGE=20 and SID in (select SID from PUB.STUDENT_COURSE))select sid,name as name,ROUND(avg(score)) as avg_score,sum(score) as sum_score fromPUB.STUDENT_COURSE natural join valid_stu where SID in (select SID from valid_stu)group by SID,NAMEcreate table test2_06 asselect CID,(max(SCORE))max_score from PUB.STUDENT_COURSE group byCIDcreate table test2_07 asselect SID,NAME from PUB.STUDENT where(NAME not like '张%' and NAME not like '李%' and NAME not like '王%') create table test2_08 aswith xing(value) as (select substr(NAME,1,1) from PUB.STUDENT)select value as second_name,count(value) as p_count from xing group by valuecreate table test2_09 asselect distinct SID,NAME,SCORE from PUB.STUDENT_COURSE natural join PUB.STUDENTwhere CID ='300003';create table test2_10 asselect distinct SID,CID from PUB.STUDENT_COURSE where SID in (select SID from PUB.STUDENT_COURSE)/* 1 */create table test3_01 asselect * from pub.student_31 where regexp_like(SID,'^[0-9]+$')/* 2 */create table test3_02 asselect * from pub.student_31where to_number(substr(BIRTHDAY,8,2))+AGE=112/* 3 */create table test3_03 asselect * from pub.student_31where SEX is null or SEX='男' or SEX='女'/* 4 */create table test3_04 asselect * from pub.student_31where DNAME is not null and length(DNAME)>=3 and instr(DNAME,' ')<=0 /* 5 */create table test3_05 asselect * from pub.student_31where regexp_like(CLASS,'^[0-9]{4}$')/* 6 */create table test3_06 asselect * from pub.student_31where regexp_like(SID,'^[0-9]+$')and to_number(substr(BIRTHDAY,8,2))+AGE=112 and (SEX is null orSEX='男' or SEX='女')and (DNAME is not null and length(DNAME)>=3 and instr(DNAME,' ')<=0) and regexp_like(CLASS,'^[0-9]{4}$') and (instr(NAME,' ')<=0 and length(NAME)>=2)/* 7 */create table test3_07 asselect * from pub.student_course_32 where SID in (select SID from pub.student)/* 8 */create table test3_08 asselect * from pub.student_course_32 natural join pub.teacher_course /* 9 */create table test3_09 asselect * from pub.student_course_32 where SCORE>=0 and score <=100 /* 10 */create table test3_10 asselect * from pub.student_course_32 natural join pub.teacher_course where SID in (select SID from pub.student) and CID in (select CID from pub.course) and TID in (select TID from pub.teacher) and SCORE>=0 and score <=100/*-------------- test4_01 --------------*/ create table test4_01 as select * from pub.student_41;ALTER TABLE test4_01 ADD sum_score number;update test4_01 set sum_score=(select sum(score) from pub.student_course a wheretest4_01.sid=a.sid group by sid);/*-------------- test4_02 --------------*/create table test4_02 as select * from pub.student_41;ALTER TABLE test4_02 ADD avg_score number;update test4_02 set avg_score =(select round(avg(score),1) from pub.student_course a wheretest4_02.sid=a.sidgroup by sid);/*-------------- test4_03 --------------*/create table test4_03 as select * from pub.student_41;ALTER TABLE test4_03 ADD sum_credit number;update test4_03 set sum_credit =(with a as (select * from pub.student_course natural join pub.course) (select sum(credit) from awhere SCORE>=60 and test4_03.sid=a.sid group by sid) )/* 4 */create table test4_04 as select * from pub.student_41;update test4_04 set dname=(select did from pub.department wheretest4_04.dname=pub.department.dname)where dname in (select dname from pub.department);/* 5 */create table test4_05 as select * from pub.student_41;ALTER TABLE test4_05 add did varchar(2);ALTER TABLE test4_05 ADD sum_score number;ALTER TABLE test4_05 ADD avg_score number;ALTER TABLE test4_05 ADD sum_credit number;update test4_05 set sum_score=(select sum(score) from pub.student_course a wheretest4_05.sid=a.sid group by sid);update test4_05 set avg_score =(select round(avg(score),1) from pub.student_course a wheretest4_05.sid=a.sidgroup by sid);update test4_05 set sum_credit =(with a as (select * from pub.student_course natural join pub.course) (select sum(credit) from awhere SCORE>=60 and test4_05.sid=a.sid group by sid));update test4_05 set did =(select did from pub.department wherepub.department.dname=test4_05.dname) where dname in (select dname from pub.department);update test4_05 set did =(select did from pub.department_41 where pub.department_41.dname=test4_05.dname) where dname in (select dname from pub.department_41);update test4_05 set did ='00' where did is null;/* 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(trim(SEX),1,1) where length(SEX)<>1;/* 8 */create table test4_08 as select * from pub.student_42;update test4_08 set CLASS=substr(CLASS,1,4) where length(CLASS)<>4;/* 9 */create table test4_09 as select * from pub.student_42;update test4_09 set AGE=112-to_number(substr(BIRTHDAY,8,2)) where AGE is null;/* 10 */create table test4_10 as select * from pub.student_42;update test4_10 set name=replace(name,' ',''),DNAME=replace(DNAME,' ','');update test4_10 set SEX=substr(trim(SEX),1,1) where length(SEX)<>1;update test4_10 set CLASS=substr(CLASS,1,4) where length(CLASS)<>4;update test4_10 set AGE=112-to_number(substr(BIRTHDAY,8,2)) where AGE is null;create table test5_10(test varchar(20),age numeric (3));insert into test5_10 values('结果1',88);insert into test5_10 values('结果2',90);insert into test5_10 values('结果3',90);insert into test5_10 values('结果4',86);insert into test5_10 values('结果5',90);insert into test5_10 values('结果6',90);insert into test5_10 values('结果7',86);insert into test5_10 values('结果8',86);insert into test5_10 values('结果9',76);insert into test5_10 values('结果10',86);/* 1 */create view test6_01 asselect sid,name,dname from pub.studentwhere age<20 and dname='物理学院' order by sid/* 2 */create view test6_02 aswith temp_table(sid,ss) as(select sid,sum(score) from pub.student_course group by sid) select sid,name,dname,ss as sum_score from pub.student natural join temp_table where class='2009' and dname='软件学院'/* 3 */create view test6_03 asselect * from pub.student_course natural join pub.student whereclass='2010' and dname='计算机科学与技术学院' and cid =(select cid from pub.course where name='操作系统')/* 4 */create view test6_04 asselect sid, from pub.student_course natural join pub.studentwhere score>90 and cid =(select cid from pub.course where name='数据库系统')/* 5 */create view test6_05 asselect sid,cid,name,score from pub.student_course natural joinpub.coursewhere sid in (select sid from pub.student where name='李龙')/* 6 */create view test6_06 aswith a as (select sid,count(*) as totc from pub.student_course group by sid)select sid,name from pub.student where sid in (select sid from a where totc>=(selectcount(*) from pub.course))/* 7 */create view test6_07 asselect * from test6_06 where sid not in (select distinct sid from pub.student_course where sid in(select sid from test6_06)and score<60)/* 8 */create view test6_08 asselect cid,name from pub.coursewhere fcid in (select cid from pub.course where credit=2)/* 9 */create view test6_09 aswith a(sid,sum_credit) as(select sid,sum(credit) as sum_creditfrom pub.student_course natural join pub.course where SCORE>=60 group by sid)select sid,name,sum_credit from pub.student natural join a where class='2010' and dname='化学与化工学院'/* 10 */create view test6_10 asselect cid,name from pub.coursewhere fcid in(select cid from pub.course where fcid is not null)/* 1 */create table test7_01 asselect First_name,count(*) as frequency from( select substr(NAME,2) as First_name from pub.student ) group by First_name/* 2 */create table test7_02 asselect letter,count(*) as frequency from(select substr(NAME,2,1) as letter from pub.studentunion allselect substr(NAME,3) as letter from pub.student wherelength(Name)=3 ) group by letter/* 3 */create table test7_03 aswith a as (select sid,sum(credit) as tot from pub.student_course natural join pub.course where SCORE>=60 group by sid),b as (select * from pub.student natural left join a),c as (select dname,class,count(sid) as p_count from b group by (dname,class)),d as (select dname,class,count(sid) as p_count1 from b where tot>=10 group by (dname,class)),e as (select dname,class,count(sid) as p_count2 from b where tot<10 or tot is null group by (dname,class))select * from c natural left join d natural join eupdate test7_03 set p_count1=0 where p_count1 is null;/* 4 */create view test7_04_v1 asselect * from pub.student natural left join(select sid,sum(credit) as tot from pub.student_course natural join pub.course where SCORE>=60 group by sid);create table test7_04 aswith b as (select * from test7_04_v1),c as (select dname,class,count(sid) as p_count from b group by (dname,class)),d as (select dname,class,count(sid) as p_count1 from b where class<=2008 and tot>=8 group by (dname,class)unionselect dname,class,count(sid) as p_count1 from b where class>2008 and tot>=10 group by (dname,class))select * from c natural left join d where dname is not null;alter table test7_04 add p_count2 NUMBER;update test7_04 set p_count2 = p_count - p_count1;/* 1 */create table test8_01 aswith A as(select DNAME,SCORE,NAME from(select CID,DNAME,SCORE from pub.student_course natural joinpub.student where dnameis not null) natural join pub.course),B as(select dname,round(avg(score)) as avg_ds_score from A where name='数据结构' groupby dname),C as(select dname,round(avg(score)) as avg_os_score from A where name='操作系统' groupby dname)select * from B natural join C/* 2 */create table test8_02 aswith a as(select sid from pub.student_course where cid=(select cid frompub.course where name='操作系统') intersectselect sid from pub.student_course where cid=(select cid frompub.course where name='数据结构')),b as (select a.sid,score as ds_score from pub.student_course,a where pub.student_course.sid=a.sid and cid=(select cid from pub.course where name='数据结构')),c as (select a.sid,score as os_score from pub.student_course,a where pub.student_course.sid=a.sid and cid=(select cid from pub.course where name='操作系统'))select sid,name,dname,ds_score,os_score from b natural join cnatural join pub.student where dname='计算机科学与技术学院' /* 3 */create table test8_03 aswith a as(select sid from pub.student_course where cid=(select cid frompub.course where name='操作系统') unionselect sid from pub.student_course where cid=(select cid frompub.course where name='数据结构')),b as (select a.sid,score as ds_score from pub.student_course,a where pub.student_course.sid=a.sid and cid=(select cid from pub.course where name='数据结构')),c as (select a.sid,score as os_score from pub.student_course,a wherepub.student_course.sid=a.sid and cid=(select cid from pub.course where name='操作系统'))select sid,name,dname,ds_score,os_score from b natural full join c natural join pub.student where dname='计算机科学与技术学院' /* 4 */create table test8_04 aswith a as(select sid from pub.student_course where cid=(select cid frompub.course where name='操作系统') unionselect sid from pub.student_course where cid=(select cid frompub.course where name='数据结构')),b as (select a.sid,score as ds_score from pub.student_course,a where pub.student_course.sid=a.sid and cid=(select cid from pub.course where name='数据结构')),c as (select a.sid,score as os_score from pub.student_course,a where pub.student_course.sid=a.sid and cid=(select cid from pub.course where name='操作系统'))select sid,name,dname,ds_score,os_score from b natural full join c natural full join pub.student where dname='计算机科学与技术学院'。