北大自考上机数据库系统原理上机.docx

北大自考上机数据库系统原理上机.docx

《北大自考上机数据库系统原理上机.docx》由会员分享，可在线阅读，更多相关《北大自考上机数据库系统原理上机.docx（44页珍藏版）》请在冰点文库上搜索。

北大自考上机数据库系统原理上机

1.某系统有如下数据库，其中存放了三个表：

　　Project（PID，Pname，city，principal）

　　注：

工程（工程号，工程名，所在城市，负责人）

　　要求：

工程号为主码，所有字段都不能为空

　　CreatetableProject9527（PIDvarchar（8），Pnamevarchar（8）notnull，cityvarchar（8）notnull，principalvarchar（8）notnull，primarykey（PID））

　　Bank（BID，Bname，city）

　　注：

银行（银行号，银行名，所在城市）

　　要求：

银行号为主码，银行名唯一，所有字段不为空。

　　createtableBank9527（BIDvarchar（4），Bnamevarchar（8）uniquenotnull，cityvarchar（8）notnull，primarykey（BID））

　　Loan（PID，BID，money1，Bdate，RDate）

　　注：

贷款（工程号，银行号，贷款金额，贷款日期，偿还日期）

　　要求：

工程号和银行号作为主码，工程号为参照工程表的主码的外码，银行号为参照银行表的主码的外码，所有字段不为空，偿还日期必须迟于贷款日期或者为空。

　　createtableLoan9527（PIDvarchar（8）notnull，BIDvarchar（4）notnull，money1intnotnull，Bdatedatetimenotnull，

　　Rdatedatetime，primarykey（PID，BID），foreignkey（PID）referencesproject9527（PID），foreignkey（BID）referencesBank9527（BID），check（Bdate

　　试用SQL语言完成下列功能：

　　1.在数据库中建立上述三个表，请使用英文表名和属性名（满足上述表的每个要求）。

　　2.往表中插入数据

　　工程（101，'地铁'，'北京'，'刘阳'）

　　（102，'教学楼'，'济南'，'张虎'）

　　insertintoproject9527values（'101'，'地铁'，'北京'，'刘阳'）

　　insertintoproject9527values（'102'，'教学楼'，'济南'，'张虎'）

　　银行（201，'工商银行'，'北京'）

　　（202，'农业银行'，'济南'）

　　（203，'建设银行'，'济南'）

　　insertintoBank9527values（'201'，'工商银行'，'北京'）

　　insertintoBank9527values（'202'，'农业银行'，'济南'）

　　insertintoBank9527values（'203'，'建设银行'，'济南'）

　　贷款（101，201，300000，2005-11-10，null）

　　（101，202，3000000，2005-11-15，2006-11-28）

　　（102，202，200000，2006-10-15，null）

　　（102，203，500000，2005-5-6，null）

　　insertintoLoan9527values（'101'，'201'，300000，'2005-11-10'，null）

　　insertintoLoan9527values（'101'，'202'，3000000，'2005-11-15'，'2006-11-28'）

　　insertintoLoan9527values（'102'，'202'，200000，'2006-10-15'，null）

　　insertintoLoan9527values（'102'，'203'，500000，'2005-5-6'，null）

　　3.SQL语句完成下列查询：

a）列出所有工程和银行在同一城市的贷款记录，包括工程名、银行名、贷款金额、贷款日期。

　　selectproject9527.PID，Bank9527.BID，Bdate，money1fromproject9527，Bank9527，Loan9527where

　　project9527.PID=Loan9527.PIDandBank9527.BID=Loan9527.BIDandproject9527.city=Bank9527.cityb）找出贷款记录大于1次的工程名。

　　selectPname，count（*）as贷款记录fromproject9527，Loan9527whereproject95

project9527.PID=Loan9527.PID

　　groupbyPnamehavingCOUNT（Bdate）>1

　　c）列出2005年贷款总额超过300万的工程的工程号、工程名和贷款总金额。

　　selectProject9527.PID，Pname，sum（money1）as贷款总金额fromProject9527，Loan9527

　　whereProject9527.PID=Loan9527.PIDand（'2005-1-1'<=BdateandBdate<'2006-1-1'）groupby

　　Project9527.PID，Pnamehavingsum（money1）>3000000

　　4.完成如下更新（15分）：

　　今天是2006年10月18日，“教学楼”工程还清了其所有贷款，请在数据库中更新相应记录。

　　updateLoan9527setRdate='2006-10-18'wherePIDin（selectPIDfromproject9527wherepname='教学楼'）

　　5.使用游标完成如下操作：

　　经过调查发现，“地铁”工程所有未偿还的贷款记录的贷款时间应该在2006-11-11日，做出以上更新。

　　declarec1cursorfor

　　selectBdatefromproject9527，Loan9527whereproject9527.PID=Loan9527.PIDand

　　Rdateisnull

　　openc1

　　declare@xdatetime

　　fetchnextfromc1into@x

　　while@@fetch_status=0

　　begin

　　updateLoan9527setBdate='2006-11-11'

　　whereBdate=@x

　　fetchnextfromc1into@x

　　end

　　closec1

　　deallocatec1

　　6.列出同时满足如下条件的银行的银行号和银行名（20分）：

　　a）该银行在济南。

　　b）贷出款的总金额大于与其在同一地区的其他任何银行的贷出款的总金额。

　　selectBank9527.BID，BnamefromBank9527，Loan9527whereBank9527.city='济南'andBank9527.BID=Loan9527.BID

　　groupbyBank9527.BID，Bname

　　havingsum（money1）>=all（selectsum（money1）fromLoan9527

　　groupbyLoan9527.BID）

2.某公司产品的分销管理系统有如下四个表项：

　　Agent（AID，ANAME，SALARY）

　　注：

对应含义为：

代理商（代理商编号，姓名，薪水）

　　要求：

AID为主码，所有字段不为空

　　createtableAgent9527（AIDvarchar（8），ANAMEvarchar（8）notnull，SALARYINTNOTNULL，primarykey（AID））

　　Customer（CID，CNAME）

　　注：

对应含义为：

顾客（顾客编号，姓名）

　　要求：

编号为主码，所有字段不为空

　　createtableCustomer9527（CIDvarchar（8），CNAMEvarchar（8）notnull，primarykey（CID））

　　Product（PID，PNAME，PRICE）

　　注：

对应含义为：

产品信息（编号，名称，价格）

　　要求：

编号为主码，所有字段不为空

　　CREATEtableProduct9527（PIDvarchar（8），PNAMEvarchar（8）notnull，PRICErealnotnull，primarykey（PID））

　　Orders（OID，BUY_DATE，CID，AID，PID，QTY，DOLLARS）

　　注：

对应含义为：

订单（订单号，购买日期，顾客号，产品号，代理商号，订购数量，订金）

　　要求：

订单号为主码，顾客号、产品号、代理商号为外码，分别参照Customer中的CID，Agent中的AID，和Product中的PID，订购数量大于0，所有字段不为空。

题目：

　　1按要求完成该四个表的创建，请使用原英文表名和属性名（满足上述表的每个约束要求）。

　　2完成每个表中的数据的插入

　　Agent（01，'Smith'，10000）；

　　（02，'Jones'，7000）；

　　（03，'Brown'，5000）；

　　（04，'Gray'，7200）；

　　（05，'Otasi'，4800）；

　　（06，'Jack'，5500）；

　　insertintoAgent9527values（'01'，'Smith'，10000）

　　insertintoAgent9527values（'02'，'Jones'，7000）

　　insertintoAgent9527values（'03'，'Brown'，5000）

　　insertintoAgent9527values（'04'，'Gray'，7200）

　　insertintoAgent9527values（'05'，'Otasi'，4800）

　　insertintoAgent9527values（'06'，'Jack'，5500）

　　Customer（001，'TipTop'）；

　　（002，'Basics'）；

　　（003，'Allied'）；

　　（004，'ACME'）；

　　（005，'ACME'）；

　　insertintoCustomer9527values（'001'，'TipTop'）

　　insertintoCustomer9527values（'002'，'Basics'）

　　insertintoCustomer9527values（'003'，'Allied'）

　　insertintoCustomer9527values（'004'，'ACME'）

　　insertintoCustomer9527values（'005'，'ACME'）

　　Product（01，'comb'，0.5）

　　（02，'brush'，0.5）；

　　（03，'razor'，1）

　　（04，'pen'，1）

　　（05，'pencil'，1）

　　insertintoProduct9527values（'01'，'comb'，0.5）

　　insertintoProduct9527values（'02'，'brush'，0.5）

　　insertintoProduct9527values（'03'，'razor'，1）

　　insertintoProduct9527values（'04'，'pen'，1）

　　insertintoProduct9527values（'05'，'pencil'，1）

　　Orders（1011，'2002-4-8'，001，01，01，1000，450）

　　（1012，'2001-4-1'，001，02，02，400，180）

　　（1013，'2002-1-1'，002，03，03，1000，880）

　　（1014，'2001-5-1'，002，05，03，800，704）

　　（1015，'2002-1-1'，003，03，05，1200，1104）

　　（1016，'2001-8-1'，004，06，01，1000，460）

　　（1017，'2002-9-1'，005，01，04，1000，500）

　　（1018，'2001-3-6'，005，01，01，800，400）

　　insertintoOrders9527values（'1011'，'2002-4-8'，'001'，'01'，'01'，1000，450）

　　insertintoOrders9527values（'1012'，'2001-4-1'，'001'，'02'，'02'，400，180）

　　insertintoOrders9527values（'1013'，'2002-1-1'，'002'，'03'，'03'，1000，880）

　　insertintoOrders9527values（'1014'，'2001-5-1'，'002'，'05'，'03'，800，704）

　　insertintoOrders9527values（'1015'，'2002-1-1'，'003'，'03'，'05'，1200，1104）

　　insertintoOrders9527values（'1016'，'2001-8-1'，'004'，'06'，'01'，1000，460）

　　insertintoOrders9527values（'1017'，'2002-9-1'，'005'，'01'，'04'，1000，500）

　　insertintoOrders9527values（'1018'，'2001-3-6'，'005'，'01'，'01'，800，400）

3用SQL语句完成下列查询：

（1）列出产品订购数量超过1000的订单号。

　　selectOIDfromOrders9527whereQTY>1000

（2）列出每个代理商经手的订单数和总的订金以及该代理商的编号，姓名

　　selectAgent9527.AID，ANAME，count（OID）as订单数，sum（DOLLARS）as总订金fromAgent9527，

　　Orders9527whereAgent9527.AID=Orders9527.AIDgroupbyAgent9527.AID，ANAME

　　（3）找出同时通过代理商01和02购买产品的顾客编号，姓名。

　　4完成如下更新

　　将薪水不高于5000的代理商的薪水提高10%，高于5000的代理商的薪水提高5%.

　　updateAgent9527setSALARY=SALARY*1.05whereSALARY>5000

　　updateAgent9527setSALARY=SALARY*1.1whereSALARY<=5000

　　5使用游标完成如下操作：

　　使用游标找出经手办理的订单订金总额大于1000的代理商，将其薪水提高5%.

　　declarec1cursorfor

　　selectAgent9527.AID，ANAMEfromAgent9527，Orders9527whereAgent9527.AID=Orders9527.AID

　　groupbyAgent9527.AID，ANAMEhavingsum（DOLLARS）>1000

　　openc1

　　declare@xint

　　declare@yvarchar（8）

　　fetchnextfromc1into@x，@y

　　while@@fetch_status=0

　　begin

　　updateAgent9527setSALARY=SALARY*1.05whereSALARY=@xandANAME=@y

　　fetchnextfromc1into@x，@y

　　end

　　closec1

　　deallocatec1

　　6列出在通过代理商01购买产品数量最多的顾客的编号。

　　selectCustomer9527.CIDfromCustomer9527，Orders9527whereCustomer9527.CID=Orders9527.CID

　　andPID=01groupbyCustomer9527.CIDhavingcount（QTY）>=all（selectcount（QTY）fromOrders9527wherePID=01

　　groupbyOrders9527.CID）

一、现有关于学生成绩管理数据库的模式如下：

　　学生（学号，姓名，性别，出生日期，系号）；

　　课程（课号，课程名，学分）；

　　选修（学号，课号，成绩）

　　学生与课程的联系是M：

N联系。

　　1.按题目要求建立表结构，各表各个字段的名字、数据类型、长度等根据语义和查询的需要自行决定。

可根据以下题目的需要建立索引。

　　有些题目则可能需要建立中间表或临时表。

（15分）

　　2.利用你所熟悉的方法向各表录入适量的、满足题目需要的数据。

各数据项的取值范围应该合理、有效，并与查询要求相呼应。

（10分）

　　3.编写一个对选修表的数据修改程序。

用学号和课号定位记录，找到后，显示该记录，并提示用户确认“真的要修改吗？

”，

　　根据用户回答决定程序流程。

　　要求只允许修改成绩信息，不退出本程序能继续进行修改操作。

（20分）

　　4.用FoxPro语言或SQL语言编写程序完成以下查询。

（以下四题依次为10分、10分、15分、20分）

（1）列出学生名单，要包含所有学生的所有属性，且在最后一行显示：

　　女生的平均年龄为xx岁。

（2）按照学分对课程进行分类，列出每类学分的课程数，且在最后一行显示

　　课程总数为xx门。

　　（3）检索选修课号为C2的学生的姓名、课程名和成绩，检索结果按成绩的降序排列。

　　（4）检索与ZANG同学同龄的学生的学号、姓名、课程名和成绩，并列表显示。

　　答案：

3

　　settalkoff

　　clear

　　usesc

　　indexonsno+cnoto11

　　dimensiond（3）

　　action=.t.

　　answer=.t.

　　dowhileaction

　　a=space（6）

　　b=space（4）

　　@1，1say“sno：

”getapicture“xxxxxx”

　　@2，1say“cno”getbpicture“xxxx”

　　read

　　c=a+b

　　seekc

　　iffound（）

　　scattertod

　　@5，2say“sno”getd

（1）picture“xxxxxx”

　　@7，2say“cno”getd

（2）picture“xxxx”

　　cleargets

　　@9，2say“grade”getd（3）picture“999.9”

　　read

　　@11，2say“真的修改吗？

”getanswerpicture“l”

　　read

　　ifanswer

　　clear

　　gatherfromd

　　@1，1say“修改后的数据已存入！

”

　　endif

　　if.not.answer

　　clear

　　endif

　　endif

　　if.not.found（）

　　clear

　　@1，1say“查无此人”

　　endif

　　@4，1say“继续查吗？

”getactionpicture“l”

　　read

　　clear

　　enddo

答案：

4_1

　　clear

　　settalkoff

　　uses

　　listoff

　　？

　　？

　　averageageforsex=“w”toa

　　？

“女生平均年龄：

”

　　？

？

str（a）

　　？

？

“岁”

　　settalkon

　　return

　　答案：

4_2（需另建一个临时表：

cc.dbf属性为：

cname，sum，cxi）

　　clear例如：

高数1物理

　　settalkoff

　　usecc

　　indexonteachertoliu

　　totalonteachertoyufields课程数

　　useyu

　　listoff

　　？

　　usecc

　　sum课程数toa

　　？

“课程总数：

”

　　？

？

str（a）

　　答案：

4_3

　　clear

　　settalkoff

　　selectsname，cname，grade；

　　froms，sc，c1；

　　wheres.sno=sc.snoando=oando='1000'；

　　orderbysc.gradedesc

　　settalkon

　　答案：

4_4

　　clear

　　settalkoff

　　selects.sno，s.sname，ame，sc.grade；

　　froms，c1，sc；

　　wheres.sno=sc.snoando=o；

　　ands.agein（selectage；

　　froms；

　　wheresname='古天乐'）

　　settalkon

二、3.编写一个课程信息的录入程序。

要求在录入一门课程后屏幕询问：

“继续录入吗？

”，根据用户回答决定程序流程。

　　要求不退出本程序能继续进行录入操作。

（20分）

　　4.用FoxPro语言或SQL语言编写程序完成以下查询。

（以下四题依次为10分、10分、15分、20分）

（1）列出学生名单，要包含所有学生的所有属性，且在最后一行显示：

　　年龄小于18岁的男生有xx人。

（2）列出成绩不及格的学生的信息，要包含选修表的所有属性，且在最后一行显示：

　　他们的平均成绩为