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

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

　　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）

　　注：銀行（銀行號，銀行名，所在城市）

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

　　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 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語言完成下列功能：

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

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

　　工程（101，'地鐵'，'北京'，'劉陽'）

　?。?02，'教學樓'，'濟南'，'張虎'）

　　insert into project9527 values（'101'，'地鐵'，'北京'，'劉陽'）

　　insert into project9527 values（'102'，'教學樓'，'濟南'，'張虎'）

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

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

　?。?03，'建設銀行'， '濟南'）

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

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

　　insert into Bank9527 values（'203'，'建設銀行'， '濟南'）

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

　?。?01，202，3000000，2005-11-15， 2006-11-28）

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

　?。?02，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語句完成下列查詢：

　　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年貸款總額超過300萬的工程的工程號、工程名和貸款總金額。

　　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日，“教學樓”工程還清了其所有貸款，請在數(shù)據(jù)庫中更新相應記錄。

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

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

　　經(jīng)過調(diào)查發(fā)現(xiàn)，“地鐵”工程所有未償還的貸款記錄的貸款時間應該在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.列出同時滿足如下條件的銀行的銀行號和銀行名（20分）：

　　a）該銀行在濟南。

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

　　select Bank9527.BID，Bname from Bank9527，Loan9527 where Bank9527.city='濟南' and Bank9527.BID=Loan9527.BID

　　group by Bank9527.BID，Bname

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

　　group by Loan9527.BID ）