山东大学数据库系统SQL上机实验代码test2——test8(2014最新版)

⼭东⼤学数据库习题及答案⼭东⼤学数据库系统课程试卷A卷参考答案⼀、简答（每⼩题5分，共25分）1、如何理解空值（NULL），空值在参与运算时有哪些特点？答：空值null表⽰“不知道”或者“不存在”的含义。

不是指“0”，也不是“false”，也不是’’。

Null参与的关系运算和算术运算结果均为null。

评分细则：Null的含义3分，Null参与的运算特点2分2、简述事务的概念及其相关特性。

答：事物是访问并可能更新各种数据项的⼀个程序执⾏单元。

事物具有ACID四种特性。

A指原⼦性：事物的所有操作在数据库中要么全部正确反映出来，要么全部不反映。

C指⼀致性：事物的隔离执⾏保持数据库的⼀致性。

I指隔离性：尽管多个事物可以并发执⾏，但系统必须保证每⼀个事物都感觉不到系统中有其他事物在并发地执⾏。

D指持久性：⼀个事物成功完成后，它对数据库的改变必须是永久的。

评分细则：事物概念2分，事物的性质3分。

3、关系中的元组有先后顺序吗？为什么？答：没有。

关系是元组的集合，⽽集合中的元素是没有顺序的，因此关系中的元组也就没有先后顺序。

评分细则：第⼀问回答“有”，零分；第⼀问回答“没有”，2分，说明原因3分4、设关系模式R(A，B，C)上有⼀个多值依赖A B。

如果已知R的当前关系中存在着三个元组(a,b1,c1)、(a,b2,c2)、(a,b3,c3)，那么这个关系中⾄少还应该存在哪些元组？答：（a b1 c2）,(a b2 c1),(a b1 c3)，(a b3 c1),(a b2 c3),(a b3 c2)评分细则：每⼀个元组1分。

5、简述时间戳排序协议。

答：时间戳：对于系统中的每⼀个事务Ti，我们把⼀个唯⼀的固定的时间戳和它联系起来，记为TS(Ti)。

每个数据项Q需要与两个时间戳相关联：W-timestamp(Q)表⽰成功执⾏write(Q)的所有失去的最⼤时间戳；R-timestamp(Q)表⽰成功执⾏read(Q)的所有事务的最⼤的时间戳。

设有一学籍管理系统，其数据库名为“EDUC”。

初始大小为 10MB，最大为50MB，数据库自动增长，增长方式是按5％比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。

数据库的逻辑文件名为“student_data”, 物理文件名为“student_data.mdf，存放路径为“E:\sql_data”(注意：此文件名必须已经建立的前提下才可以此操作)。

日志文件的逻辑文件名为“student_log”, 物理文件名为“student_log.ldf”，存放路径为“E:\sql_data”。

四．实验步骤1．使用SQL Server Management Studio（简称SSMS）创建数据库。

（1）启动SSMS在开始菜单中：所有程序－SQL Server 2005 －SQL Server Management Studio单击“连接”按钮，便可以进入【SQL Server Management Studio】窗口。

如果身份验证选择的是“混合模式”，则要输入sa的密码。

（2）建立数据库在“对象资源管理器”窗口，建立上述数据库EDUC。

在数据库节点上右击选择新建。

同时建立一个同样属性的数据库EDUC1。

2. 使用向导删除上面建立的数据库。

用SSMS删除建立的数据库EDUC。

3、数据库的分离将刚建好的数据库分离出来，即点击新建的EDUC——任务——分离，将删除连接和更新打一个钩，然后点击确定。

如图所示：4、数据分离出来之后可以附加进去。

即右击数据库——附加——点击添加按钮，找到数据库文件.mdf所存放的路径，然后点击确定，即可以将我们刚所创建的文件添加回去。

五．实验总结通过本次实验，我熟悉了SQL Server 中SQL Server Management Studio的环境，了解了SQL Server 数据库的逻辑结构和物理结构，掌握使用向导创建和删除数据库的方法。

加深了对数据库的认识和理解。

⼭东⼤学数据库实验答案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='计算机科学与技术学院'。

SQL部分模拟试题(二)一、单项选择题(本大题共12小题，每小题2分，共24分)1、数据库(DB),数据库系统(DBS)和数据库管理系统(DBMS)之间的关系是_______________ 。

( )A.DBMS 包括DB 和DBSB. DBS 包扌舌DB 和DBMSC. DB包括DBS和DBMSD D・BS就是DB,也就是DBMS2、SQL Server的_________ 允许用户输入SQL语句并且迅速查看这些语句的结果。

()A.查询分析器B.服务管理器C.事件探测器D.企业管理器3、以下哪个操作可以创建数据库。

()A.进入查询分析器，选择菜单命令“工具”-> “向导”，打开“选择向导”对话框，在对话框中，展开“注册服务器向导”屮的“数据库”文件夹，选屮“创建数据库向导”项B.进入服务管理器，选择菜单命令“工具”一“向导”，打开“选择向导”对话框，在对话框中，展开“注册服务器向导”中的“数据库”文件夹，选中“创建数据库向导”项C.进入事件探测器，选择菜单命令“工具”一“向导”，打开“选择向导”对话框，在对话框中，展开“注册服务器向导”中的“数据库”文件夹，选中“创建数据库向导”项D.进入企业管理器，选择菜单命令“工具”一“向导”，打开“选择向导”对话框，在对话框屮，展开“注册服务器向导”中的“数据库”文件夹，选中“创建数据库向导”项4、下面所列条目中，哪一条不是标准的SQL语句？()A.ALTERTABLEB. ALTERV1EWC. CREATETABLED. CREATEVIEW5、若要在基本表S中增加一列CN (课程名)，可用____________ o ()A.ADD TABLES (CN CHAR ( 8 ))B. ADD TABLES ALTER (CN CHAR ( 8 ))C. ALTER TABLES ADD (CN CHAR ( 8 )) D・ ALTER TABLES (ADD CN CHAR ( 8 ))6、取出关系屮的某些列，并消去重复的元组的关系运算称为__________ o ()A.取列运算B.投影运算C.连接运算D.选择运算7、在SQL语言中，条件“ BETWEEN20AND30 ”表示年龄在20到30之间，且___________ 。

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、数据仓库主要是供决策分析用的______，所涉及的数据操作主要是______，一般情况下不进行。

数据库上机实验答案2-2学号：姓名：日期：20 年月日实验目的：练习初步的SQL查询语句，包括：字符串比较、字符串函数等。

实验内容：基于提供的诺基亚N73的淘宝数据表，包括：商品列表Goods，完成如下查询，并回答问题。

要求：（1）输出统计答案；（2）输出查询语句。

1 通配符的练习：用通配符%和_，完成下列问题（1）Goods表中，有多少个商品的名称含有“N73”？2004select count(*)from goods where good_name like'%N73%'（2）Goods表中，有多少个商品的名称以“诺基亚”开头？1102select count(*)from goods where good_name like'诺基亚%'（3）根据商品名称，找出以“诺基亚”开头，且“全国联保”的商品列表。

select*from goods where good_name like'诺基亚%'and good_name like'%全国联保%'（4）找出商品名称中有空格的商品列表。

select*from goods where good_name like'% %'（5）找出商品名称中没有空格的商品列表。

方法1：select*from goods where not good_name like'% %'方法2：select*from goods where good_name not like'% %'方法3：select*from goodsexceptselect*from goods where good_name like'% %'（6）找出商品名称长度为7个字符的商品列表。

select*from goods where good_name like'_______'注：下划线_和连字符-不同（7）找出商品名称长度小于7个字符的商品列表。

山东大学数据库实验四答案实验四1) 4 - 1 统计总成绩create table test4_01 as select * from pub.STUDENT_41alter table test4_01 add sum_score intupdate test4_01set sum_score=(select sum(score)from pub.STUDENT_COURSE,pub.COURSEwhere test4_01.sid=student_course.sid and student_course.cid=COURSE.cidgroup by sid)2) 4 - 2 统计平均成绩alter table test4_02 add avg_score float(int不行)update test4_02set avg_score=(select trim(to_char(avg(score),999999.9))from pub.STUDENT_COURSEwhere test4_02.sid=student_course.sidgroup by sid)3) 4 - 3 统计总学分alter table test4_03 add sum_credit intupdate test4_03set sum_credit=(select sum(credit)from pub.STUDENT_COURSE,pub.COURSEwhere test4_03.sid=student_course.sid and student_course.cid=COURSE.cid and score>=60group by sid)4) 4 - 4 设置院系编号update test4_04set dname= casewhen dname in (select dnamefrom pub.DEPARTMENT) then (select didfrom pub.DEPARTMENTwhere test4_04.DNAME=department.dname)else test4_04.DNAMEend5) 4 - 5 几项内容综合前三项参照前面题目第四项update test4_05set did= casewhen test4_05.dname in (select dnamefrom pub.DEPARTMENT) then (select didfrom pub.DEPARTMENTwhere test4_05.DNAME=department.dname)when test4_05.dname in (select dnamefrom pub.DEPARTMENT_41) then (select didfrom pub.DEPARTMENT_41where test4_05.DNAME=department_41.dname)else '00'end6) 4 - 6 剔除姓名中的空格create table test4_06 as select * from pub.STUDENT_42update test4_06set name= replace(name,' ')7) 4 - 7 规范性别update test4_07set sex= replace(sex,' ')update test4_07set sex= casewhen sex is NULL then NULLwhen length(sex)>1 then substr(sex,1,1)else sexend8) 4 - 8 规范班级update test4_08set class= casewhen class is NULL then NULLwhen length(class)>4 then substr(class,1,4)else classend9) 4 - 9 计算年龄update test4_09set age= casewhen age is NULL then (2012-extract(year from birthday)) else ageend10) 4 - 10 几项内容综合update test4_10setname= replace(name,' ')update test4_10set dname= replace(dname,' ')update test4_10set sez= replace(sex,' ')update test4_10set sex= casewhen sex is NULL then NULLwhen length(sex)>1 then substr(sex,1,1)else sexendupdate test4_10set class= casewhen class is NULL then NULLwhen length(class)>4 then substr(class,1,4)else classendupdate test4_10set age= casewhen age is NULL then (2012-extract(year from birthday)) else ageend。

Test2(1)create table test2_01 as select sid,nameFrom pub.student pwhere not exists (select cid from pub.student_course where sid=p.sid)(2)create table test2_02 as select sid,nameFrom pub.student natural join pub.student_courseWhere cid in (select cid from pub.student_course where sid=’200900130417’)(3)create table test2_03 as select sid,nameFrom pub.student natural join pub.student_courseWhere cid in (select cid from pub.course where fcid=’300002’)(4)create table test2_04 as select sid,nameFrom pub.studentWhere sid in(select sid from pub.course ,pub .student_course where student_course.cid=course.cid and name='操作系统' )Andsid in(select sid from pub.course ,pub .student_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_scorefrom pub.student_course,pub.studentWhere pub.student_course.sid=pub.student.sid and age='20'group by student.sid,name(6)create table test2_06 as select cid,max(score)max_scoreFrom pub.student_courseGroup by cid(7)create table test2_07 as select sid,nameFrom pub.studentWhere 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_countFrom pub.studentGroup by substr(name,1,1)(9)create table test2_09 as select pub.student.sid,name,scoreFrom pub.student,pub.student_courseWhere pub.student.sid=pub.student_course.sid and cid='300003'(10)create table test2_10 as select sid,cidFrom pub.student_courseWhere score is not nullTest3（1）create table test3_01 as select * from pub.Student_31delete from test3_01 where length(translate(sid,'\0123456789','\'))>0（2（create table test3_02 as select * from pub.Student_31delete from test3_02 where age<>2012-extract(year from birthday)delete from test3_03 where sex not in (select sex from test3_03 where sex='男' or sex='女' or sex=null)（4（create table test3_04 as select * from pub.Student_31delete from test3_04 where dname is null or length(dname)<3 or dname like '% %'（5（create table test3_05 as select * from pub.Student_31delete from test3_05 where length(class)>4（6（create table test3_06 as select * from pub.Student_31delete from test3_06 where length(translate(sid,'\0123456789','\'))<12Delete from test3_06 where age<>2012-extract(year from birthday)Delete from test3_06 where sex not in (select sex from test3_03 where sex='男' or sex='女' or sex=null) Delete from test3_06 where dname is null or length(dname)<3 or dname like '% %'delete from test3_06 where length(class)>4delete from test3_06 where name like '% %' or length(name)<2(7)create table test3_07 as select * from pub.Student_course_32delete from test3_07 where sid not in (select sid from pub.student)(8)create table test3_08 as select * from pub.Student_course_32delete from test3_08 where (cid,tid) not in (select cid,tid from pub.teacher_course)(9)create table test3_09 as select * from pub.Student_course_32delete from test3_09 where score <0 or score >100(10)create table test3_10 as select * from pub.Student_course_32delete from test3_10 where score <0 or score >100delete from test3_10 where sid not in (select sid from pub.student)delete from test3_10 where cid not in (select cid from pub.course)delete from test3_10 where tid not in (select tid from pub.teacher)delete from test3_10 where (cid,tid) not in (select cid,tid from pub.teacher_course)Test 4(1)create table test4_01 as select * from pub.student_41alter table test4_01 add sum_score numberupdate test4_01 set sum_score = (select sum (score) from pub.student_course where test4_01.sid= pub.student_course.sid)(2)create table test4_02 as select * from pub.student_41alter table test4_02 add avg_score numeric(5,1)update test4_02 set avg_score = (select avg (score) from pub.student_course where test4_02.sid= pub.student_course.sid)(3)create table test4_03 as select * from pub.student_41alter table test4_03 add sum_credit intcreate table t4_031 as select * from pub.course natural join pub.student_courseupdate t4_031 set credit=0 where score<60update test4_03 set sum_credit = (select sum(credit) from t4_031 where test4_03.sid=t4_031.sid) (4)create table test4_04 as select * from pub.student_41update test4_04 set dname=(select did from pub.department where pub.department.dname=test4_04.dname)where dname in (select dname from pub.department)alter table test4_05 add sum_score numberalter table test4_05 add avg_score numeric(5,1)alter table test4_05 add sum_credit intalter table test4_05 add did varchar(2)update test4_05 set sum_score =(select sum (score) from pub.student_course where test4_05.sid= pub.student_course.sid)update test4_05 set avg_score = (select avg (score) from pub.student_course where test4_05.sid= pub.student_course.sid)update test4_05 set sum_credit = (select sum(credit) from t4_031 where test4_05.sid=t4_031.sid) create table a1 as select * from pub.departmentinsert into a1 select * from pub.department_41 where dname not in (select distinct dname from pub.department )（6）create table test4_06 as select * from pub.student_42update test4_06 set name =replace(name,' ','')（7）create table test4_07 as select * from pub.student_42update test4_07 set sex =replace(sex,'性','')update test4_07 set sex =replace(sex,' ','')(8)create table test4_08 as select * from pub.student_42update test4_08 set class=replace(class,'级','')update test4_08 set class=replace(class,' ','')(9)create table test4_09 as select * from pub.student_42update test4_09 set age=2012-extract(year from birthday) where age is null(10)create table test4_10 as select * from pub.student_42update test4_10 set name=replace(name,' ','')update test4_10 set dname=replace(dname,' ','')update test4_10 set sex=replace(sex,'性','')update test4_10 set sex=replace(sex,' ','')update test4_10 set class=replace(class,'级','')update test4_10 set class=replace(class,' ','')update test4_10 set age=2012-extract(year from birthday) where age is nullTest5create table test5_10 (test varchar(20),age numeric (3))insert into test5_10values ('结果1',88),insert into test5_10values ('结果2',90),insert into test5_10values ('结果3',90),insert into test5_10values ('结果4',86),insert into test5_10values ('结果5',90),insert into test5_10values ('结果6',90),insert into test5_10values ('结果7',86),insert into test5_10values ('结果8',86),insert into test5_10values ('结果9',76),insert into test5_10values ('结果10',86)Test6(1) create view test6_01 as select sid,name,dname from pub.student where age<20 and dname='物理学院'order by sid(2)create view test6_02 as select pub.student.sid,name,sum(score)sum_score from pub.student,pub.student_course where pub.student.sid=pub.student_course.sid and class='2009' and dname='软件学院' group by pub.student.sid,(3)create view test6_03 as select pub.student.sid,,pub.student_course.score from pub.student,pub.student_course where pub.student.sid=pub.student_course.sid and class='2010' and dname='计算机科学与技术学院' and pub.student_course.cid=(select cid from pub.course where ='操作系统')(4)create view test6_04 as select pub.student.sid, from pub.student,pub.student_course where pub.student.sid=pub.student_course.sid and score>90 and pub.student_course.cid=(select cid from pub.course where ='数据库系统')(5)create view test6_05 as select pub.student_course.sid,pub.student_course.cid,score, from pub.course,pub.student_course,pub.student where pub.course.cid=pub.student_course.cid and pub.student_course.sid=pub.student.sid and ='李龙'(6)create view test6_06 as select sid,name from pub.student where sid in (select sid from pub.student_course group by sid having count(*) >=(select count(*) from pub.course ))(7)create view test6_07 as select sid,name from pub.student where sid in (select sid from pub.student_course where score>=60 group by sid having count(*) >=(select count(*) from pub.course ))(8)create view test6_08 as select a1.cid, 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, ,sum(credit) sum_credit from pub.student, pub.student_course,pub.course where pub.student.sid = pub.student_course.sid and pub.student_course.cid=pub.course.cid and class='2010' and dname='化学与化工学院' and score>=60 group by pub.student.sid, (10)create view test6_10 as select a1.cid, from pub.course a1,pub.course a2 where a1.fcid =a2.cid and a2.fcid is not nullTest7(1)create table a as select (substr(name,2)) first_name from pub.student create table test7_01 as select first_name,(count(*)) frequency from a group by first_name(2)Union和Union All的区别之一在于对重复结果的处理。

数据库实验（一）熟悉环境、建立/删除表、插入数据Drop table 表名update dbtest set test=1select * from dbscore1．教师信息(教师编号、姓名、性别、年龄、院系名称)test1_teacher：tid char 6 not null、name varchar 10 not null、sex char 2、age int、dname varchar 10。

根据教师名称建立一个索引。

1、create table test1_teacher(tid char(6) primary key,name varchar(10) not null,sex char(2),age int,dname varchar(10))2．学生信息（学生编号、姓名、性别、年龄、出生日期、院系名称、班级）test1_student：sid char 12 not null、name varchar 10 not null、sex char 2、age int、birthday date（oracle的date类型是包含时间信息的，时间信息全部为零）、dname varchar 10、class varchar(10)。

根据姓名建立一个索引。

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))3．课程信息（课程编号、课程名称、先行课编号、学分）test1_course：cid char 6 not null、name varchar 10 not null、fcid char 6、credit numeric 2，1（其中2代表总长度，1代表小数点后面长度）。

云南大学软件学院实验报告课程：数据库原理与实用技术实验任课教师：包崇明，朱艳萍姓名：李子厚学号：20121120009 专业：软件工程成绩：实验8 安全管理1.设置安全认证模式SQL Server 2008有两种登录验证机制：Windows身份验证和混合身份验证。

修改方法：MSSMS中对象资源管理器中－选择数据库服务器-单击右键－选择“属性”，在属性窗口中选择“安全性”，如下两图所示：通过安全性属性页面可进行身份验证方式的修改。

注意：修改只有重新启动数据库服务器后才起效。

如果修改为 Windows身份验证模式，则不能用SQL Server用户登录，建议设为混合模式- SQL Server 和Windows身份验证模式，这种模式下两类用户都能登录。

2、创建和管理登录帐户（创建登录帐户只能由系统管理员完成）（1）用MSSMS创建、查看、删除基于Windows账户或者SQL Server的登录帐户。

通过对象资源管理器-安全性-登录名-右键，可创建登录名（可设置默认数据库），可查看和删除登录名。

创建一个登录名为test,采用SQL Server身份验证，密码为 1，默认数据库为 cap 的截图为：（2）用SQL语句创建、查看、删除SQL Server登录帐户1）使用CREATE LOGIN语句创建一个登录帐户test，密码为1，使用的默认数据库为CAP（简单语法：CREATE LOGIN login_name WITH PASSWORD = 'password' ,DEFAULT_DATABASE = database）,SQL语句为：CREATE LOGIN test WITH PASSWORD='1',DEFAULT_DATABASE=CAP2）登录帐户存放在表sys.sql_logins中，用select 语句查看该表所有内容，截图: select*from sys.sql_logins3）使用DROP LOGIN 语句（语法：DROP LOGIN login_name）删除刚创建的SQL Server 登录帐户testDROP LOGIN test3、创建与管理数据库用户数据库用户只有和登录帐户相关联，也就是加入某些个数据库中，才具有访问数据库的权限。

上机实验七1．声明一个字符串型的局部变量，并对其赋值：‘我的变量’，然后显示出此值。

declare @a char(10)set @a='我的变量'--select @a as 变量的值print @a2．编程实现如下功能：1）声明两个整形的局部变量：@i1和@i2,对@i1赋初值：10，@i2的值为：@i1乘以5，再显示@i2的结果值。

declare @i1 int,@i2 intset @i1=10set @i2=@i1*5print @i22）用While语句实现5000减1，减2，……一直减到50的计算，并显示最终的结果。

declare @sum int,@i intset @sum=5000set @i=1while(@i<=50)beginset @sum=@sum-@iset @i=@i+1endprint @sum3）输出100以内的素数。

declare @i smallint,@j smallint,@k smallintset @i=2while(@i<=100)beginset @k=0set @j=2while(@j<@i)beginif(@i%@j=0)beginset @k=1set @j=@Iendset @j=@j+1endif @k=0print @Iset @i=@i+1end4）将字符数在20以内的字符串变量C的值逆序输出。

要求输出界面为：declare @i varchar(20),@j int,@k varchar(20)set @i='jnbugswi'set @j=1set @k=''while @j<=len(@i)beginset @k=substring(@i,@j,1)+@kset @j=@j+1endprint '字符串C的值:'+@iprint 'C的逆序字符串:'+@k5）从SC表中查询所有学生的选课成绩情况，分别统计各分数段人数，并输出统计结果。