因為上次發了數據庫原理總結，瀏覽快上萬了，所以把我總結的題目?也送給大家

?上次的數據庫原理總結

---

一．根據員工工資計算其個人所得稅，3000元為起征點，超出3000元的部分按照10%的比例征收個人所得稅，例如：

員工工資表

員工編號	工資	個人所得稅
1	3100	?
2	3500	?
3	3800	?
. . .	. . .	. . .

則1號員工個人所得稅為10元，2號員工個人所得稅為50元，3號員工個人所得稅為80元…，請使用游標編寫一段Transact-SQL程序段，計算每個員工的個人所得稅并更新員工工資表中的個人所得稅。

DECLARE @eno int, @salary float, @tax float,

DECLARE etax_cursor CURSOR FOR

SELECT 員工編號,工資,個人所得稅

FROM 員工工資表??????????????????????????????????? ---2分

?

Open?? etax_cursor??????????????????????????????????

?

FETCH NEXT FROM etax_cursor

INTO @eno, @salary, @tax?????????????????????????? ?---1分

?

WHILE ?@@fetch_status = 0?

BEGIN

?? select @tax= (@salary-3000)*0.1??

? ?update 員工工資表

?? set 個人所得稅=@tax????????????????????????????? ?---1分

?

?? FETCH NEXT FROM etax_cursor

?? INTO @eno,@salary, @tax

END

Close etax_cursor

DEALLOCATE etax_cursor???????????????????????????? ?---1分

?

?

二、按要求實現下列操作（每題2分，共20分）

現有關系數據庫如下，完成下面題目：

??? 學生(學號，姓名，性別，專業、獎學金)

??? 課程(課程號，名稱，學分)

??? 學習(學號，課程號，分數)

1.從學生表中查詢“數學”專業的學生的學號，姓名，性別。

?

SELECT 學號，姓名，性別

?? FROM? 學生

?? WHERE? 專業=‘數學’

?

?

2.查詢平均成績大于等于90分的成績信息，輸出列名為學號，平均成績，并按照平均成績的升序排序。

?

SELECT 學號，AVG(分數) AS 平均成績

?? FROM? 學習

?? GROUP BY 學號?

?? Having?? AVG(分數)>=90

ORDER BY AVG(分數)[微軟用戶1]?

?

?

?

3．查詢沒有選修課程的學生的名單。

?select *

from 學生

where 學號 not in (select distinct 學號? from 學習 )

或

select A.*

from 學生 A left outer join 學習 B

on A.sno = B.sno

where B.cno is null

?

?

?

?

4．檢索沒有獲得獎學金、同時至少有一門課程成績在90分以上的學生信息，包括學號、姓名和專業。

?SELECT學號，姓名，專業

?? FROM 學生

?? WHERE獎學金 is? null? AND 學號 in (select學號 from學習 where分數>90)

?

5.檢索沒有任何一門課程成績在85分以下的所有學生的信息，包括學號、姓名和專業。

SELECT 學號，姓名，專業

?? FROM 學生

?? WHERE not exists

?? (SELECT *

?? FROM 學習

?? WHERE學習.學號=學生.學號and 分數<85)

6. 將各門課程的選修人數及平均成績定義為視圖V_AVG，包括課程名稱，選修人數和平均成績。

create view V_AVG(名稱, 選修人數, 平均成績)

as

select 名稱,count(學號),avg(分數)

from 學習,課程

where 學習.課程號=課程.課程號

group by? 學習.課程號,名稱

7．請將學號為'006'，課程號為'C3'，分數為92的記錄插入學習表。

insert into 學習

values('006', 'C3',92)

8．請刪除數學專業學生的成績記錄。

delete

from 學習

where 學號 in (select 學號 from 學生 where 專業= ‘數學’)

9．請將高等數學這門課程的成績加5分。

? update 學習

? set 分數 = 分數 +5

where 課程號 =

(? select?? 課程號? from? 課程? where? 名稱=’高等數學’)

10．請將學生表的查詢權限授予張英，并允許張英傳播此權限。

Grant? select? on? 學生

To? 張英

With ?grant? option

四、應用題（20分）

某網上書店后臺數據庫的部分關系模式如下：

會員（會員編號，用戶名，密碼，姓名，地址，郵編，電話，消費額，積分）

圖書（圖書編號，類型名稱，圖書名稱，作者，出版社，出版日期，ISBN，價格）

訂單（訂單編號，會員編號，銷售額，訂購日期，出貨日期）

訂單明細（訂單明細編號，訂單編號，圖書編號，數量）

?

1. 創建訂單表，訂單編號唯一識別一個訂單，會員編號為外碼。要求銷售額大于0。
2. 在會員表的積分列上建立降序索引Index_point。
3. 查詢名稱中包含“數據庫”的圖書的圖書名稱，作者，出版社和出版日期。
4. 查詢每個會員的訂購圖書的情況，顯示用戶名、圖書名、作者、訂購日期。
5. 查詢提供銷售（圖書表中有）但沒有銷售過（沒在訂單明細表中出現）的圖書名稱和出版社。
6. 查詢已銷售的每種圖書的銷售數量，顯示圖書編號、銷售數量。
7. 查詢訂購圖書數量最多的用戶名及其訂購的數量。

8、求書店總的銷售額。

9、刪除沒有銷售過的圖書。

10、將每位會員的積分增加10分。

創建訂單表，訂單編號唯一識別一個訂單，會員編號為外碼。要求訂購日期不能大于出貨日期。

create? table? order (

訂單編號? char(9)? primary key,

會員編號? char(10),

銷售額?? double? check(銷售額>0),

訂購日期? datetime? ,

出貨日期? datetime,

foreign key(會員編號)? references 會員 (會員編號)

)

在會員表的積分列上建立降序索引Index_point。

create index Index_point on 會員(積分 desc)

?

查詢名稱中包含“數據庫”的圖書的圖書名稱，作者，出版社和出版日期。

select 圖書名稱，作者，出版社，出版日期

from? 圖書

where 圖書名稱 like ‘%數據庫%’

?

查詢每個會員的訂購圖書的情況，顯示用戶名、圖書名、作者、訂購日期。

select 用戶名、圖書名、作者、訂購日期

from會員 A, 圖書 B, 訂單 C, 訂單明細 D

where? A. 會員編號 = C. 會員編號 and

?????? ?? B. 圖書編號 = D. 圖書編號 and

?????? ?? C. 訂單編號 = D. 訂單編號

?

?

查詢提供銷售（圖書表中有）但沒有銷售過（沒在訂單明細表中出現）的圖書名稱和出版社。

select 圖書名稱、出版社

from 圖書

where圖書編號 not in (

?????? select? distinct 圖書編號

?????? from訂單明細

)

?

查詢已銷售的每種圖書的銷售數量，顯示圖書編號、銷售數量。

?

select 圖書編號,sum(數量)

from 訂單明細

group by圖書編號

?

?

查詢訂購圖書數量最多的用戶名及其訂購的數量。

select 用戶名, sum(數量)

from會員 A, 訂單 B, 訂單明細 C

where A. 會員編號 = B. 會員編號 and

?????? ?B. 訂單編號 = C. 訂單編號 and

group by會員編號, 用戶名

having sum(數量) >=all(

???? select? sum(數量)

from會員 A, 訂單 B, 訂單明細 C

where A. 會員編號 = B. 會員編號 and

?????? ?B. 訂單編號 = C. 訂單編號 and

group by會員編號, 用戶名

)

?

求書店總的銷售額。

select sum(銷售額)

from 訂單

?

刪除沒有銷售過的圖書。

delete

from 圖書

where 圖書編號 not in (

?????? select? distinct 圖書編號

?????? from訂單明細

)

將每位會員的積分增加10分

upate 會員

set 積分= 積分+10

?

?

五、應用題（每題2分，共20分）

某書店后臺數據庫的部分關系模式如下：

圖書類別（類別代號，類別名）

圖書（書號，書名，ISBN，作者，單價，類別代號）

訂單（訂單號，顧客編號，訂購日期，出貨日期）

訂單明細（訂單號，書號，數量，總價）

按要求實現下列操作：

1.顯示ISBN為“9787302163305”、“7560922171”或“9787810097987”的圖書的書號、書名和ISBN。

2.顯示單價高于40元的圖書的書號、書名和單價。

3.在圖書表中顯示所有沒有類別代號的圖書的書號和書名。

4.統計有多少圖書的價格高于30元。

5.統計每本圖書的銷售數量總和。

6.顯示所有圖書的書號、書名和單價以及圖書對應的類別代號和類別名。

7.顯示書名中包含字符串“ASP”的圖書的銷售訂單號和銷售總價。

8.創建一個名為ViewBookSale的視圖，該視圖包含所有圖書的銷售信息，顯示圖書的編號、書名以及銷量總冊數和銷售總金額。

9.向圖書表中插入一條圖書記錄：書號為“9”，書名為“SQL Server 2005實現與維護”, ISBN為“9787302163350”, 作者為“Solid”, 單價為79.00, 類別代號為“CO01”。

10.將書號為1的圖書的單價打9折。

答案：

1.顯示ISBN為“9787302163305”、“7560922171”或“9787810097987”的圖書的書號、書名和ISBN。

SELECT 書號, 書名, ISBN

FROM 圖書

WHERE ISBN IN('9787302163305', '7560922171', '9787810097987')

?

2.顯示單價高于40元的圖書的書號、書名和單價。

SELECT 書號, 書名, 單價

FROM 圖書

WHERE 單價>40

?

3.在圖書表中顯示所有沒有類別代號的圖書的書號和書名。

SELECT 書號, 書名

FROM 圖書

WHERE 類別代號 IS NULL

?

4.統計有多少圖書的價格高于30元。

SELECT COUNT(*) AS 圖書冊數

FROM 圖書

WHERE 單價 >30

?

5.統計每本圖書的銷售數量總和。

SELECT SUM(數量) AS 銷售總冊數

FROM 訂單明細

GROUP BY 書號

?

6.顯示所有圖書的書號、書名和單價以及圖書對應的類別代號和類別名。

SELECT b.書號, b.書名, b.單價, c.類別代號, c. 類別名

FROM 圖書 b? LEFT OUTER JOIN 圖書類別 c

ON b.類別代號=c.類別代號

7.顯示書名中包含字符串“ASP”的圖書的銷售訂單號和銷售總價。

SELECT 訂單號, 總價

FROM 訂單明細

WHERE 書號 IN ( SELECT 書號

???????????????? FROM 圖書

???????????????? WHERE 書名 LIKE '%ASP%')

?

8.創建一個名為ViewBookSale的視圖，該視圖包含所有圖書的銷售信息，顯示圖書的編號、書名以及銷量總冊數和銷售總金額。

CREATE VIEW ViewBookSale

AS

SELECT b.書號, b.書名, SUM(數量) AS 銷量總冊數, SUM(總價) AS 銷售總金額

FROM 圖書 b LEFT JOIN訂單明細i ON b.書號=i.書號

GROUP BY b.書號, b.書名

?

9.向圖書表中插入一條圖書記錄：書號為“9”，書名為“SQL Server 2005實現與維護”, ISBN為“9787302163350”, 作者為“Solid”, 單價為79.00, 類別代號為“CO01”。

INSERT INTO 圖書

VALUES(‘9’,'SQL Server 2005實現與維護','9787302163350','Solid',79.00,'CO01')

?

10.將書號為1的圖書的單價打9折。

UPDATE 圖書

SET 單價=單價*0.9

WHERE 書號=‘1’

五、簡答題(每題5分，共20分)

1.（1）創建SQL Server登錄賬戶Sql1，密碼為“123456”；（2分）

（2）將Comments表的查詢、刪除和插入的權限授予數據庫用戶Sql1和Sql2，并且這兩個數據庫用戶還可以將得到的權限再授予其他人。（3分）?

?

2. 參照第三道應用題所給數據庫的部分模式，創建一個函數FunBook，根據用戶提供的圖書類別名查看相應類別圖書的詳細信息。

?

3. 根據第三道應用題所給數據庫的部分模式，創建一個觸發器TrInsUpd，當向圖書表中插入或更新一條記錄的類別代號時，新記錄的類別代號必須在圖書類別表中存在，否則提示類別代號不正確。

答案：

1.

CREATE LOGIN Sql1 WITH PASSWORD='123456'

GRANT SELECT,DELETE,INSERT ON Comments TO Sql1,Sql2? WITH GRANT OPTION

?

2. CREATE FUNCTION FunBook(@categoryName nvarchar(50))

RETURNS TABLE

AS RETURN

(SELECT b.*

FROM 圖書 b JOIN 圖書類別 c ON b.類別代號=c.類別代號

WHERE c.類別名=@categoryName

)

?

?

3. CREATE TRIGGER TrInsUpd ON 圖書 AFTER INSERT,UPDATE

AS

IF NOT EXISTS(SELECT * FROM inserted WHERE 類別代號 IN(

SELECT 類別代號 FROM 圖書類別))

?????? BEGIN

????????????? PRINT '類別代號不正確！'

????????????? ROLLBACK TRANSACTION

?????? END

?

?

六、應用題（每題2分，共20分）

某書店后臺數據庫的部分關系模式如下：

圖書類別（類別代號，類別名）

圖書（書號，書名，ISBN，作者，單價，類別代號）

顧客（顧客編號，姓名，地址，推薦人編號）

推薦人編號表示推薦這名顧客注冊的老顧客的編號

訂單（訂單號，顧客編號，訂購日期，出貨日期）

訂單明細（訂單號，書號，數量，總價）

按要求實現下列操作：

1.在圖書表中查看有哪些類別代號。

?

2.顯示類別代號是LA01或者單價低于20元的圖書的書號、書名、單價和類別代號。

?

3.顯示所有圖書的書號、書名、單價和類別代號，首先按照類別代號升序排序，對于類別代號相同的圖書再并按照圖書單價降序排。

4.統計類別代號是CO01的圖書冊數和單價總和。

5.統計銷售次數超過5次的圖書的書號和銷售總冊數。

6.顯示那些有推薦人的顧客的詳細信息以及其推薦人的姓名。

7.顯示2009年以后購買過圖書的顧客的編號和姓名。

8.創建一個名為ViewComputers的視圖，該視圖包含所有計算機類的圖書詳細信息以及類別名。

9.向表圖書中除了類別代號以外的所有列中插入數據，書號為“11”，書名為“英語閱讀詞匯雙突破”, ISBN為“7560922171”,作者為“楊建榮”，單價為19.00。

10.刪除編號為101的圖書記錄。

答案：

1.在圖書表中查看有哪些類別代號。

SELECT DISTINCT 類別代號

FROM 圖書

?

2.顯示類別代號是LA01或者單價低于20元的圖書的書號、書名、單價和類別代號。

SELECT 書號, 書名, 單價, 類別代號

FROM 圖書

WHERE 類別代號='LA01' OR 單價<20

?

3.顯示所有圖書的書號、書名、單價和類別代號，首先按照類別代號升序排序，對于類別代號相同的圖書再并按照圖書單價降序排。

SELECT 書號, 書名, 單價, 類別代號

FROM 圖書

ORDER BY 類別代號, 單價 DESC

?

4.統計類別代號是CO01的圖書冊數和單價總和。

SELECT COUNT(書號) AS 圖書冊數, SUM(單價) AS 單價總和

FROM 圖書

WHERE 類別代號='CO01'

?

5.統計銷售次數超過5次的圖書的書號和銷售總冊數。

SELECT 書號, SUM(數量) AS 銷售總冊數

FROM 訂單明細

GROUP BY 書號

HAVING COUNT（訂單號）>5

?

6.顯示那些有推薦人的顧客的詳細信息以及其推薦人的姓名。

SELECT c1.*, c2.姓名

FROM 顧客 c1, 顧客 c2

WHERE c1.推薦人編號=c2.顧客編號

?

7.顯示2009年以后購買過圖書的顧客的編號和姓名。

SELECT c.顧客編號, c.姓名

FROM 顧客 c

WHERE EXISTS ( SELECT *

??????????????? FROM 訂單 o

??????????????? WHERE o.訂購日期>='01/01/2009'? AND c.顧客編號=o.顧客編號)

?

8.創建一個名為ViewComputers的視圖，該視圖包含所有計算機類的圖書詳細信息以及類別名。

CREATE VIEW ViewComputers

AS

SELECT b.*, c.類別名

FROM 圖書 b JOIN 圖書類別 c ON b.類別代號=c.類別代號

WHERE c.類別名='計算機'

?

9.向表圖書中除了類別代號以外的所有列中插入數據，書號為“11”，書名為“英語閱讀詞匯雙突破”, ISBN為“7560922171”,作者為“楊建榮”，單價為19.00。

INSERT INTO 圖書(書號,書名,ISBN,作者,單價)

?????? VALUES(‘11’,'英語閱讀詞匯雙突破','7560922171','楊建榮',19.00)

?

10.刪除編號為101的圖書記錄。

DELETE FROM 圖書

WHERE 書號=’101’

?

?

?

七、簡答題(每題5分，共20分)

1.（1）創建基于登錄賬戶Sql2的數據庫用戶Sql2，并為該用戶指定默認架構Sale。（3分）

（2）把用戶U5對SC表的INSERT權限收回。（2分）

?

?

2. 參照第三道應用題所給數據庫的部分模式，創建一個存儲過程PrcSelect，根據用戶提供的圖書類別名查看相應類別圖書的詳細信息。

3. 根據第三道應用題所給數據庫的部分模式，創建一個函數FunBookSale，該函數根據給定的書號返回該圖書銷售的數量。

答案：

1.（1）（3分）

CREATE USER Sql2 FROM LOGIN Sql2

WITH DEFAULT_SCHEMA=Sale

（2）（2分）

REVOKE? INSERT? ON ?TABLE SC ?FROM? U5?

?

2. CREATE PROCEDURE PrcSelect? @categoryName nvarchar(50)

AS

SELECT b.*

FROM 圖書 b JOIN 圖書類別 c ON b.類別代號=c.類別代號

WHERE c.類別名=@categoryName

?

3. CREATE FUNCTION FunBookSale (@bookID int)

RETURNS int

AS

BEGIN

?????? DECLARE @quantity int

?????? SELECT @quantity=SUM(quantity)

?????? FROM OrderItems

?????? WHERE bookID=@bookID

RETURN @quantity

END

?

八、請按要求完成操作（30分）

某校學生-課程數據庫部分關系模式如下：

學生表 Student(Sno,Sname,Sage,Ssex，department)??

課程表Course(Cno,Cname, Ccredit，Tno)???

成績表?SC(Sno,Cno, grade)??

教師表Teacher(Tno,Tname，tsex)?

?

1. 數據查詢（每題2分，共20分）

1. 查詢張姓學生的信息

（2） 查詢計算機學院女學生的信息，并按年齡降序排列

（3） 查詢選修了項天老師課程的學生信息

（4） 查詢至少有一門課程與李燕所選課程相同的學生的學號、姓名

（5） 求各門課程最高成績、最低成績、課程號。

?

?

1. 求選修了全部課程的學生信息
2. 求1995年前出生的學生信息
3. 查詢所有學生的選課情況，包括沒有選課的學生
4. 查詢兩門以上不及格課程的同學的學號及其平均成績
5. 檢索至少選修兩門課程的學生學號

?

2. 請SQL語句完成下列要求（共10分）

（1）創建課程表（2分）

?

（2） 刪除成績表中成績為空的記錄（2分）

?

（3） 在課程表的教師編號列上建立降序索引。（2分）

（4）在學生表上創建一個觸發器trigger_delete，當刪除學生信息時，將刪除的學生保存到oldstudent表中，oldstudent和student表結構相同。（4分）

答案：

數據查詢（每題2分，共20分）

1）. select *? from student where sname like '張%'

?

2） select * from student where department ='IS' and ssex='女' order by sage desc

?

3） select? *

from student a,sc b,course c,teacher d

where a.sno = b.sno and

????? b.cno = c.cno and

????? c.tno = d.tno and

????? tname = '項天'

?

?

4） select a.sno,sname

from student a,sc b

where a.sno = b.sno and sname <>'李燕' and

cno in(

? select distinct cno

? from student c,sc d

? where c.sno = d.sno and sname = '李燕'

)

?

5）? select cno,max(grade),min(grade)

from sc

grop by cno

?

6） select *

from student

where not exists(

?? select *

?? from course

?? where not exists(

????? select *

????? from sc

????? where sno = student.sno and

??????????? cno = course.cno

?? )

)

?

7） select *

from student

where (year(getdate())-sage)<1995

?

8） select*

from student left outer join sc

on student.sno = sc.sno

?

9）? select sno ,avg(grade)

from sc

where sno in (

?select sno

?from sc

?group by sno

?having count(case when grade<60 then 1)>=2

)

?

?

10）?? select sno

from sc

group by sno

having count(*)>=2

?

?

2. 請SQL語句完成下列要求（共10分）

1）create table course(

? cno varchar(5) primary key,

? cname varchar(30),

? ccredit float,

? tno varchar(5),

? foreign key (tno) refrences? teacher(tno)

2) delete

from sc

where grade is null

?

3) create index index_tno on teacher(tno desc)

?

4) create trigger trigger_delete

on student

after delete

as

begin

? insert into oldstudent

? select *? from deleted

end

?

?

九. 已知學生數據庫中存放了這樣的兩張表，一張為畢業生信息表(graduation)，記錄畢業生的一些基本信息，一張為學生繳費表(fee)，記錄了學生的繳費信息。由于學生畢業，我們要從學生信息表中把畢業生的記錄刪除，但是如果這個學生欠費的話，則不允許刪除這個學生的記錄。 (字段名都是中文，可以直接使用) (10分)

graduation (學號 姓名 性別 地址 聯系方式)

fee(學號 姓名 已交費用 欠費)

1. 建立一個存儲過程pro_deletestudent在graduation表中刪除指定畢業學生的信息，輸入參數為學號。
2. 在graduation表上建立一個觸發器tr_checkfee，判斷要刪除的學生是否欠費，欠費則不允許刪除該記錄，否則刪除該記錄

答案：

1) 建立一個存儲過程pro_deletestudent在graduation表中刪除指定畢業學生的信息，輸入參數為學號。

?? create procedure pro_deletestudent

?? @sno? char(9)

?? as

??? begin

????????????? delete? from graduation where 學號 = @sno

??? ? end

?

?

(2) 在graduation表上建立一個觸發器tr_checkfee，判斷要刪除的學生是否欠費，欠費則不允許刪除該記錄，否則刪除該記錄

create? TRIGGER? tr_checkfee

on? graduation

for delete

as

begin

?????? DECLARE @sno char(9);

?????? Select @sno=deleted.sno from ?deleted

?????? if exists(select * from? fee where 學號 = @sno and欠費>0)

????????????? rollback

end

?

十、某書店后臺數據庫的部分關系模式如下：

圖書類別（類別代號，類別名）

圖書（書號，書名，ISBN，作者，單價，類別代號）

顧客（顧客編號，姓名，地址，推薦人編號）

推薦人編號表示推薦這名顧客注冊的老顧客的編號

訂單（訂單號，顧客編號，訂購日期，出貨日期）

訂單明細（訂單號，書號，數量，總價）

按要求實現下列操作：

1.使用數據定義語言建立顧客、訂單明細兩張表的結構（注意添加相應的主外鍵約束）。（6分）

1. 在圖書表中查看有哪些類別代號。（2分）
2. 顯示類別代號是LA01或者單價低于20元的圖書的書號、書名、單價和類別代號。（2分）
3. 顯示所有圖書的書號、書名、單價和類別代號，首先按照類別代號升序排序，對于類別代號相同的圖書再并按照圖書單價降序排。（3分）
4. 統計類別代號是CO01的圖書冊數和單價總和。（3分）
5. 顯示有推薦人的顧客的詳細信息以及其推薦人的姓名。（3分）
6. 刪除編號為101的圖書記錄。（2分）

1. 向表圖書中除了類別代號以外的所有列中插入數據，書號為“11”，書名為“英語閱讀詞匯雙突破”, ISBN為“7560922171”,作者為“楊建榮”，單價為19.00。（2分）
2. 創建一個視圖，要求顯示訂單編號、書名、ISBN、并且要求書的作者為“王珊”。（3分）
3. 創建一個角色ROLE1，將顧客表的查詢、更新、刪除的權限授予該角色，并使用該角色對張明、趙強、李峰授權。（4分）

?

答案：

1.使用數據定義語言建立顧客、訂單明細兩張表的結構（注意添加相應的主外鍵約束）。（6分）顧客（顧客編號，姓名，地址，推薦人編號）

訂單明細（訂單號，書號，數量，總價）

?

CREATE TABLE 顧客

(

? 顧客編號 CHAR(9) PRIMARY KEY,

? 姓名? CHAR(10),

? 地址? VARCHAR(20),

? 推薦人編號 CHAR(9),

? FOREIGN KEY (推薦人編號) REFERENCES 顧客(顧客編號)

);

CREATE TABLE 訂單明細

(

? 訂單號 CHAR(8),

? 書號? CHAR(10),

? 數量? SMALLINT),

? 總價 NUMERIC(8,2),

? PRIMARY (訂單號, 書號),

? FOREIGN KEY (訂單號) REFERENCES 訂單(訂單號),

FOREIGN KEY (書號) REFERENCES 圖書(書號)

);

?

2.在圖書表中查看有哪些類別代號。（2分）

SELECT DISTINCT 類別代號

FROM 圖書

?

3.顯示類別代號是LA01或者單價低于20元的圖書的書號、書名、單價和類別代號。（2分）

SELECT 書號, 書名, 單價, 類別代號

FROM 圖書

WHERE 類別代號='LA01' OR 單價<20

?

4.顯示所有圖書的書號、書名、單價和類別代號，首先按照類別代號升序排序，對于類別代號相同的圖書再并按照圖書單價降序排。（3分）

SELECT 書號, 書名, 單價, 類別代號

FROM 圖書

ORDER BY 類別代號, 單價 DESC

?

5.統計類別代號是CO01的圖書冊數和單價總和。（3分）

SELECT COUNT(書號) AS 圖書冊數, SUM(單價) AS 單價總和

FROM 圖書

WHERE 類別代號='CO01'

?

6.顯示有推薦人的顧客的詳細信息以及其推薦人的姓名。（3分）

SELECT c1.*, c2.姓名

FROM 顧客 c1, 顧客 c2

WHERE c1.推薦人編號=c2.顧客編號

?

7.刪除編號為101的圖書記錄。（3分）

DELETE FROM 圖書

WHERE 書號=’101’

?

8.向表圖書中除了類別代號以外的所有列中插入數據，書號為“11”，書名為“英語閱讀詞匯雙突破”, ISBN為“7560922171”,作者為“楊建榮”，單價為19.00。（3分）

INSERT INTO 圖書(書號,書名,ISBN,作者,單價)

?????? VALUES(‘11’,'英語閱讀詞匯雙突破','7560922171','楊建榮',19.00)

9. CREATE ROLE ROLE1

?

? GRANT SELECT,UPDATE,DELETE

? ON TABLE 顧客

? TO ROLE1

?

? GRANT ROLE1

? TO 張明，趙強，李峰

?

十一、綜合題（每題20分，共40分）

1、某服裝銷售公司擬開發一套服裝采購管理系統，以便對服裝采購和庫存進行管理。經過需求分析和概念設計、邏輯設計階段最后得到的關系模式包括：

庫管員（庫管員編號，姓名，級別）Storekeeper（sno,sname,level）

倉庫信息（倉庫編號，倉庫位置，倉庫容量）Storehouse(stno,stadress,stvolume)

服裝（服裝編碼，服裝描述，服裝類型，尺碼，面料，銷售價格）Dress(dno，ddescribe,dtype,dsize,dplus,dprice)供應商（供應商編碼，供應商名稱，地址，聯系電話，企業法人）Supplier(suno,suname,suaddress,sutel,superson)關系模式之間的關聯關系為：每個倉庫有一個庫管員，一個倉庫管理員可以管理多個倉庫；每種服裝有一個供應商，每個供應商提供多種類型的衣服；每種衣服放在同一個倉庫里，每個倉庫里存放多種類型的衣服。

請完成以下題目：

1）請用把以上四個關系模式用SQL創建到數據中（每個創建表的語句2分，共計8分）

?

?

?

2）假設表中已經存在以下數據，請完成a)~e)中SQL語句的編寫（每個2分，共12分）

庫管表中的數據：

?

倉庫表中的數據：

?

供應商表中的數據：

?

服裝表中的數據：

?

1. 請查詢存放在1號樓201倉庫中中的服裝信息
2. 請查詢河北童泰服裝廠生產的服裝信息存儲的倉庫信息
3. 由于庫存銷售量上升，現在“女士古典旗袍”的尺碼已經不全了，只剩下155~165的號了，請修改該服裝的尺碼信息
4. 庫管員孫某某的離職，現在他的庫管工作全部由新來的員工李爽承擔，請將李爽的信息插入到庫管員表中（李爽的等級是3級），并把原來的孫某某的庫房指定給李爽管理。

?

1. 由于換季，現在女士連衣裙已經下架不再銷售，請將服裝中的女士連衣裙刪除。

2、根據第一題中的需求描述，即某服裝銷售公司擬開發一套服裝采購管理系統，編寫以下數據庫程序。

1）編寫一個自定義函數實現按照某個的庫管員查找其所管轄的倉庫中的服裝的供應商的聯系人。（5分）

?

2）編寫一個存儲過程，將參數指定的服裝信息插入到數據庫dress表中。（5分）

?

3）定義一個觸發器，在插入供應商信息的時候檢查聯系人不能為空值（5分）

4）定義一個游標，實現統計服裝信息中價格在500元以內的服裝數量。（5分）

答案：

1.? 1)創建表的SQL語句：（每個創建表的SQL2分，共8分）

--創建庫管員表

create table Storekeeper(

sno int primary key,

sname varchar(20),

level char(2)

)

--創建倉庫表

create table Storehouse(

stno int primary key,

staddress varchar(100),

stvolume? int,

sno int,

foreign key (sno) references? Storekeeper(sno)

)

--創建供應商表

create table Supplier(

suno int primary key,

suname varchar(20),

suaddress varchar(100),

sutel varchar(20),

superson varchar(20)

)

--創建服裝表

create table Dress(

?dno int primary key,

?ddescribe varchar(100),

?dtype varchar(20),

dsize varchar(20),

?dplus varchar(20),

?dprice int,

?stno int,

?suno int,

?foreign key (stno) references Storehouse(stno),

?foreign key (suno) references Supplier(suno)

)

2)（共計12分）

a)：select dress.* from dress,storehouse

where dress.stno=storehouse.stno

and storehouse.staddress='1號樓'(2分)

?

b)： select distinct storehouse.* from storehouse,dress,supplier

where dress.stno=storehouse.stno and supplier.suno=dress.suno

and supplier.suname='河北童泰服裝'（2分）

?

c)：update dress set dsize='155~165' where ddescribe='女士古典旗袍'（2分）

?

d): insert into storekeeper values(4,'李爽',3)（2分）

update storehouse set sno=4 where sno=3（2分）

e)：delete from dress where ddescribe='女士連衣裙'（2分）

?

2. 1）自定義函數：（5分）

create function fun1(@name varchar(20))

returns varchar(20)

as

begin

return

?(select superson from supplier

?where suno in(

????? select suno from dress,Storehouse

????? where dress.stno=storehouse.stno

????? and storehouse.sno=(select sno from storekeeper

????? ? where sname=@name)

?))

end

?

2）自定義存儲過程：（5分）

create proc myproc1

@dno int,

@ddescribe varchar(100),

@dsize varchar(20),

@dplus varchar(20),

@dprice int,

@dtype varchar(20),

@stno int,

@suno int

as

?begin

?insert into dress values(@dno,@ddescribe,@dsize,

?????????????????????????? @dplus,@dprice,@dtype,@stno,@suno)

?end

?

3）觸發器：（5分）

create trigger trigger1 on supplier

for insert

as

?begin

?declare @name varchar(20)

????? select @name=superson from supplier

????? if(@name is null)

????? RAISERROR ('聯系人的名字不能為null', 16, 10)

????? ?? ROLLBACK TRANSACTION

?End

?

4）聲明游標：（5分）

--聲明游標

declare cursor1 cursor

for select dprice from dress

for read only

--打開游標

open cursor1

--定義變量

declare @num int,@price int

set @num=0

--取游標中的數據

fetch next from cursor1 into @price

--循環

?while(@@FETCH_STATUS=0)

?begin

????? if(@price<500)

???????????? set @num=@num+1;?

????? fetch next from cursor1 into @price

?end

?print @num

--關閉游標

?close cursor1

--釋放游標

?deallocate cursor1