“數(shù)據(jù)庫(kù)原理”上機(jī)題目匯總（1）

　　某系統(tǒng)有如下數(shù)據(jù)庫(kù)，其中存放了三個(gè)表：

　　Project （PID， Pname， city， principal）

　　注：工程（工程號(hào)，工程名，所在城市，負(fù)責(zé)人）

　　要求：工程號(hào)為主碼， 所有字段都不能為空

　　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）

　　注：銀行（銀行號(hào)，銀行名，所在城市）

　　要求：銀行號(hào)為主碼， 銀行名唯一，所有字段不為空。

　　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）

　　注：貸款（工程號(hào)，銀行號(hào)，貸款金額， 貸款日期， 償還日期）

　　要求：工程號(hào)和銀行號(hào)作為主碼，工程號(hào)為參照工程表的主碼的外碼，銀行號(hào)為參照銀行表的主碼的外碼， 所有字段不為空，償還日期必須遲于貸款日期或者為空。

　　create table Loan9527（PID varchar（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<Rdate or Rdate is null））

　　試用SQL語(yǔ)言完成下列功能：

　　1.在數(shù)據(jù)庫(kù)中建立上述三個(gè)表，請(qǐng)使用英文表名和屬性名（滿足上述表的每個(gè)要求）。

　　2.往表中插入數(shù)據(jù)

　　工程（101，'地鐵'，'北京'，'劉陽(yáng)'）

　　（102，'教學(xué)樓'，'濟(jì)南'，'張虎'）

　　insert into project9527 values（'101'，'地鐵'，'北京'，'劉陽(yáng)'）

　　insert into project9527 values（'102'，'教學(xué)樓'，'濟(jì)南'，'張虎'）

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

　?。?02，'農(nóng)業(yè)銀行'， '濟(jì)南'）

　?。?03，'建設(shè)銀行'， '濟(jì)南'）

　　insert into Bank9527 values（'201'，'工商銀行'，'北京'）

　　insert into Bank9527 values（'202'，'農(nóng)業(yè)銀行'， '濟(jì)南'）

　　insert into Bank9527 values（'203'，'建設(shè)銀行'， '濟(jì)南'）

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

　?。?01，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 into Loan9527 values（'102'，'203'，500000，'2005-5-6'， null）

　　3.SQL語(yǔ)句完成下列查詢：

　　a）列出所有工程和銀行在同一城市的貸款記錄，包括工程名、銀行名、貸款金額、貸款日期。

　　select project9527.PID ，Bank9527.BID， Bdate，money1 from project9527，Bank9527 ，Loan9527 where

　　project9527.PID=Loan9527.PID and Bank9527.BID=Loan9527.BID and project9527.city=Bank9527.city

　　b）找出貸款記錄大于1次的工程名。

　　select Pname，count（*） as 貸款記錄 from project9527，Loan9527 where project9527.PID=Loan9527.PID

　　group by Pname having COUNT（Bdate）>1

　　c）列出2005年貸款總額超過(guò)300萬(wàn)的工程的工程號(hào)、工程名和貸款總金額。

　　select Project9527.PID ，Pname，sum（money1）as 貸款總金額 from Project9527，Loan9527

　　where Project9527.PID=Loan9527.PID and （'2005-1-1'<=Bdate and Bdate<'2006-1-1'） group by

　　Project9527.PID ，Pname having sum（money1）>3000000

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

　　今天是2006年10月18日，“教學(xué)樓”工程還清了其所有貸款，請(qǐng)?jiān)跀?shù)據(jù)庫(kù)中更新相應(yīng)記錄。

　　update Loan9527 set Rdate='2006-10-18' where PID in （select PID from project9527 where pname='教學(xué)樓'）

　　5.使用游標(biāo)完成如下操作：

　　經(jīng)過(guò)調(diào)查發(fā)現(xiàn)，“地鐵”工程所有未償還的貸款記錄的貸款時(shí)間應(yīng)該在2006-11-11日，做出以上更新。

　　declare c1 cursor for

　　select Bdate from project9527，Loan9527 where project9527.PID=Loan9527.PID and

　　Rdate is null

　　open c1

　　declare @x datetime

　　fetch next from c1 into @x

　　while @@fetch_status=0

　　begin

　　update Loan9527 set Bdate='2006-11-11'

　　where Bdate=@x

　　fetch next from c1 into @x

　　end

　　close c1

　　deallocate c1

　　6.列出同時(shí)滿足如下條件的銀行的銀行號(hào)和銀行名（20分）：

　　a）該銀行在濟(jì)南。

　　b）貸出款的總金額大于與其在同一地區(qū)的其他任何銀行的貸出款的總金額。

　　select Bank9527.BID，Bname from Bank9527，Loan9527 where Bank9527.city='濟(jì)南' and Bank9527.BID=Loan9527.BID

　　group by Bank9527.BID，Bname

　　having sum（money1）>= all（select sum（money1） from Loan9527

　　group by Loan9527.BID ）