1、北大自考上机数据库系统原理上机1.某系统有如下数据库，其中存放了三个表：Project （PID， Pname， city， principal）注：工程（工程号，工程名，所在城市，负责人）要求：工程号为主码， 所有字段都不能为空Create table Project9527（PID varchar（8），Pname varchar（8） not null，city varchar（8） not null，principal varchar（8） not null，primary key （PID）Bank （BID，Bname，city）注：银行（银行号，银行名，所在城市）要求：银行号为主
2、码， 银行名唯一，所有字段不为空。create table Bank9527（BID varchar（4），Bname varchar（8） unique not null，city varchar（8） not null，primary key （BID）Loan （PID， BID， money1， Bdate， RDate）注：贷款（工程号，银行号，贷款金额， 贷款日期， 偿还日期）要求：工程号和银行号作为主码，工程号为参照工程表的主码的外码，银行号为参照银行表的主码的外码， 所有字段不为空，偿还日期必须迟于贷款日期或者为空。create table Loan9527（PID varch
3、ar（8） not null，BID varchar（4） not null，money1 int not null，Bdate datetime not null，Rdate datetime，primary key（PID，BID），foreign key（PID） references project9527（PID），foreign key （BID） references Bank9527（BID），check（Bdate试用SQL语言完成下列功能： 1.在数据库中建立上述三个表，请使用英文表名和属性名（满足上述表的每个要求）。2.往表中插入数据工程（101，地铁，北京，刘阳）（10
4、2，教学楼，济南，张虎）insert into project9527 values（101，地铁，北京，刘阳）insert into project9527 values（102，教学楼，济南，张虎）银行（201，工商银行，北京）（202，农业银行， 济南）（203，建设银行， 济南）insert into Bank9527 values（201，工商银行，北京）insert into Bank9527 values（202，农业银行， 济南）insert into Bank9527 values（203，建设银行， 济南）贷款 （101，201，300000，2005-11-10，null
5、）（101，202，3000000，2005-11-15， 2006-11-28）（102，202，200000，2006-10-15， null）（102，203，500000，2005-5-6， null）insert into Loan9527 values（101，201，300000，2005-11-10，null）insert into Loan9527 values（101，202，3000000，2005-11-15， 2006-11-28）insert into Loan9527 values（102，202，200000，2006-10-15， null）insert in
6、to Loan9527 values（102，203，500000，2005-5-6， null）3.SQL语句完成下列查询：a）列出所有工程和银行在同一城市的贷款记录，包括工程名、银行名、贷款金额、贷款日期。select project9527.PID ，Bank9527.BID， Bdate，money1 from project9527，Bank9527 ，Loan9527 whereproject9527.PID=Loan9527.PID and Bank9527.BID=Loan9527.BID and project9527.city=Bank9527.cityb）找出贷款记录大于
7、1次的工程名。select Pname，count（*） as 贷款记录 from project9527，Loan9527 where project95project9527.PID=Loan9527.PIDgroup by Pname having COUNT（Bdate）1 c）列出2005年贷款总额超过300万的工程的工程号、工程名和贷款总金额。select Project9527.PID ，Pname，sum（money1）as 贷款总金额 from Project9527，Loan9527where Project9527.PID=Loan9527.PID and （2005-1
8、-1=Bdate and Bdate30000004.完成如下更新（15分）：今天是2006年10月18日，“教学楼”工程还清了其所有贷款，请在数据库中更新相应记录。update Loan9527 set Rdate=2006-10-18 where PID in （select PID from project9527 where pname=教学楼）5.使用游标完成如下操作：经过调查发现，“地铁”工程所有未偿还的贷款记录的贷款时间应该在2006-11-11日，做出以上更新。declare c1 cursor forselect Bdate from project9527，Loan9527
9、 where project9527.PID=Loan9527.PID andRdate is nullopen c1declare x datetimefetch next from c1 into xwhile fetch_status=0beginupdate Loan9527 set Bdate=2006-11-11where Bdate=xfetch next from c1 into xendclose c1deallocate c16.列出同时满足如下条件的银行的银行号和银行名（20分）：a）该银行在济南。b）贷出款的总金额大于与其在同一地区的其他任何银行的贷出款的总金额。sel
10、ect Bank9527.BID，Bname from Bank9527，Loan9527 where Bank9527.city=济南 and Bank9527.BID=Loan9527.BIDgroup by Bank9527.BID，Bnamehaving sum（money1）= all（select sum（money1） from Loan9527group by Loan9527.BID ）2.某公司产品的分销管理系统有如下四个表项：Agent（AID， ANAME， SALARY）注：对应含义为：代理商（代理商编号，姓名，薪水）要求： AID 为主码，所有字段不为空create
11、 table Agent9527（AID varchar（8），ANAME varchar（8）not null，SALARY INT NOT NULL，primary key（AID）Customer（CID，CNAME）注：对应含义为：顾客（顾客编号，姓名）要求：编号为主码，所有字段不为空create table Customer9527（CID varchar（8），CNAME varchar（8） not null，primary key（CID）Product （ PID，PNAME， PRICE）注：对应含义为：产品信息（编号，名称，价格）要求：编号为主码，所有字段不为空CREAT
12、E table Product9527（PID varchar（8），PNAME varchar（8）not null，PRICE real not null，primary key（PID）Orders（OID，BUY_DATE，CID，AID，PID ，QTY ，DOLLARS）注：对应含义为：订单（订单号，购买日期，顾客号，产品号，代理商号，订购数量，订金）要求：订单号为主码，顾客号、产品号、代理商号为外码，分别参照Customer中的CID， Agent 中的AID，和Product 中的PID，订购数量大于0，所有字段不为空。题目：1 按要求完成该四个表的创建，请使用原英文表名和属性
13、名（满足上述表的每个约束要求）。2 完成每个表中的数据的插入Agent （01，Smith，10000）；（02，Jones，7000）；（03， Brown，5000）；（04， Gray，7200）；（05，Otasi，4800）；（06， Jack，5500）；insert into Agent9527 values（01，Smith，10000）insert into Agent9527 values（02，Jones，7000）insert into Agent9527 values（03， Brown，5000）insert into Agent9527 values（04， Gr
14、ay，7200）insert into Agent9527 values（05，Otasi，4800）insert into Agent9527 values （06， Jack，5500）Customer （001，TipTop）；（002，Basics）；（003，Allied）；（004， ACME）；（005， ACME）；insert into Customer9527 values （001，TipTop）insert into Customer9527 values（002，Basics）insert into Customer9527 values（003，Allied）ins
15、ert into Customer9527 values（004， ACME）insert into Customer9527 values（005， ACME）Product （01，comb，0.5）（02，brush，0.5）；（03，razor，1）（04，pen，1）（05， pencil，1）insert into Product9527 values（01，comb，0.5）insert into Product9527 values（02，brush，0.5）insert into Product9527 values（03，razor，1）insert into Produc
16、t9527 values（04，pen，1）insert into Product9527 values （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， 46
17、0）（1017，2002-9-1，005， 01， 04， 1000， 500）（1018，2001-3-6，005， 01， 01， 800， 400）insert into Orders9527 values （1011， 2002-4-8，001， 01， 01， 1000， 450）insert into Orders9527 values （1012，2001-4-1，001， 02， 02， 400， 180）insert into Orders9527 values （1013，2002-1-1，002， 03， 03， 1000， 880）insert into Orders9
18、527 values （1014，2001-5-1，002， 05， 03， 800， 704）insert into Orders9527 values （1015，2002-1-1，003， 03， 05， 1200， 1104）insert into Orders9527 values （1016，2001-8-1，004， 06， 01， 1000， 460）insert into Orders9527 values （1017，2002-9-1，005， 01， 04， 1000， 500）insert into Orders9527 values （1018，2001-3-6，00
19、5， 01， 01， 800， 400）3用SQL语句完成下列查询：（1）列出产品订购数量超过1000的订单号。select OID from Orders9527 where QTY1000（2）列出每个代理商经手的订单数和总的订金以及该代理商的编号，姓名select Agent9527.AID，ANAME，count（OID）as 订单数， sum（DOLLARS）as 总订金 from Agent9527，Orders9527 where Agent9527.AID=Orders9527.AID group by Agent9527.AID，ANAME（3）找出同时通过代理商01和02购
20、买产品的顾客编号，姓名。4完成如下更新将薪水不高于5000的代理商的薪水提高10%，高于5000的代理商的薪水提高5%.update Agent9527 set SALARY=SALARY*1.05 where SALARY5000update Agent9527 set SALARY=SALARY*1.1 where SALARY1000open c1declare x intdeclare y varchar（8）fetch next from c1 into x，ywhile fetch_status=0beginupdate Agent9527 set SALARY=SALARY*1.
21、05 where SALARY=x and ANAME=yfetch next from c1 into x，yendclose c1deallocate c16列出在通过代理商01购买产品数量最多的顾客的编号。select Customer9527.CID from Customer9527 ，Orders9527 where Customer9527.CID=Orders9527.CIDand PID=01 group by Customer9527.CID having count（QTY）=all（select count（QTY） from Orders9527 where PID=
22、01group by Orders9527.CID）一、现有关于学生成绩管理数据库的模式如下：学生（学号，姓名，性别，出生日期，系号）；课程（课号，课程名，学分）；选修（学号，课号，成绩）学生与课程的联系是M：N联系。1.按题目要求建立表结构，各表各个字段的名字、数据类型、长度等根据语义和查询的需要自行决定。可根据以下题目的需要建立索引。有些题目则可能需要建立中间表或临时表。 （15分）2.利用你所熟悉的方法向各表录入适量的、满足题目需要的数据。各数据项的取值范围应该合理、有效，并与查询要求相呼应。 （10分）3.编写一个对选修表的数据修改程序。用学号和课号定位记录，找到后，显示该记录，并提示
23、用户确认“真的要修改吗？”，根据用户回答决定程序流程。要求只允许修改成绩信息，不退出本程序能继续进行修改操作。 （20分）4.用FoxPro语言或SQL语言编写程序完成以下查询。（以下四题依次为10分、10分、15分、20分）（1）列出学生名单，要包含所有学生的所有属性，且在最后一行显示：女生的平均年龄为 xx 岁。（2）按照学分对课程进行分类，列出每类学分的课程数，且在最后一行显示课程总数为 xx 门。（3）检索选修课号为C2的学生的姓名、课程名和成绩，检索结果按成绩的降序排列。（4）检索与ZANG同学同龄的学生的学号、姓名、课程名和成绩，并列表显示。答案：3set talk offclea
24、ruse scindex on sno +cno to 11dimension d（3）action=.t.answer=.t.do while actiona=space（6）b=space（4） 1，1 say “sno：” get a picture “xxxxxx” 2，1 say “cno” get b picture “xxxx”readc=a+bseek cif found（）scatter to d 5，2 say “sno” get d（1） picture“xxxxxx” 7，2 say “cno” get d（2） picture“xxxx”clear gets 9，2
25、say “grade” get d（3） picture“999.9”read 11，2 say “真的修改吗？” get answer picture “l”readif answercleargather from d 1，1 say “修改后的数据已存入！”endifif .not.answerclearendifendifif .not.found（）clear 1，1 say “查无此人”endif 4，1 say “继续查吗？” get action picture “l”readclearenddo答案：4_1clearset talk offuse slist off？aver
26、age age for sex=“w” to a？“女生平均年龄：”？str（a）？“岁”set talk onreturn答案：4_2（需另建一个临时表：cc.dbf 属性为： cname ，sum，cxi ）clear 例如： 高数 1 物理set talk offuse ccindex on teacher to liutotal on teacher to yu fields 课程数use yulist off？use ccsum 课程数 to a？“课程总数：”？ str（a）答案：4_3clearset talk offselect sname，cname，grade；from s
27、，sc，c1；where s.sno=sc.sno and o=o and o=1000；order by sc.grade descset talk on答案：4_4clearset talk offselect s.sno，s.sname，ame，sc.grade；from s，c1，sc；where s.sno=sc.sno and o=o；and s.age in（select age；from s；where sname=古天乐）set talk on二、3.编写一个课程信息的录入程序。要求在录入一门课程后屏幕询问：“继续录入吗？”，根据用户回答决定程序流程。要求不退出本程序能继续进行录入操作。 （20分）4.用FoxPro语言或SQL语言编写程序完成以下查询。（以下四题依次为10分、10分、15分、20分）（1）列出学生名单，要包含所有学生的所有属性，且在最后一行显示：年龄小于18岁的男生有 xx 人。（2）列出成绩不及格的学生的信息，要包含选修表的所有属性，且在最后一行显示：他们的平均成绩为
