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

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，Loan9527whereproject9527.PID=Loan9527.PIDandBank9527.BID=Loan9527.BIDandproject9527.city=Bank9527.cityb）找出贷款记录大于1次的工程名。selectPname，count（*）as贷款记录fromproject9527，Loan9527whereproject95project9527.PID=Loan9527.PIDgroupbyPnamehavingCOUNT（Bdate）1c）列出2005年贷款总额超过300万的工程的工程号、工程名和贷款总金额。selectProject9527.PID，Pname，sum（money1）as贷款总金额fromProject9527，Loan9527whereProject9527.PID=Loan9527.PIDand（'2005-1-1'=BdateandBdate'2006-1-1'）groupbyProject9527.PID，Pnamehavingsum（money1）30000004.完成如下更新（15分）：今天是2006年10月18日，“教学楼”工程还清了其所有贷款，请在数据库中更新相应记录。updateLoan9527setRdate='2006-10-18'wherePIDin（selectPIDfromproject9527wherepname='教学楼'）5.使用游标完成如下操作：经过调查发现，“地铁”工程所有未偿还的贷款记录的贷款时间应该在2006-11-11日，做出以上更新。declarec1cursorforselectBdatefromproject9527，Loan9527whereproject9527.PID=Loan9527.PIDandRdateisnullopenc1declare@xdatetimefetchnextfromc1into@xwhile@@fetch_status=0beginupdateLoan9527setBdate='2006-11-11'whereBdate=@xfetchnextfromc1into@xendclosec1deallocatec16.列出同时满足如下条件的银行的银行号和银行名（20分）：a）该银行在济南。b）贷出款的总金额大于与其在同一地区的其他任何银行的贷出款的总金额。selectBank9527.BID，BnamefromBank9527，Loan9527whereBank9527.city='济南'andBank9527.BID=Loan9527.BIDgroupbyBank9527.BID，Bnamehavingsum（money1）=all（selectsum（money1）fromLoan9527groupbyLoan9527.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'，