1、試卷總分:20 得分:0綜合應用題1 圖書銷售（本題10分）寫出創建如下三張數據表的SQL語句。（本題4分）“圖書”表結構：書號：普通編碼定長字符類型，長度為10，主鍵。書名：普通編碼可變長字符類型，長度為20，非空。類別：統一字符編碼定長字符類型，長度為4，取值為：“高等數學”、“量子力學”、“國學基礎”。出版日期：日期類型。單價：整型。?“書店”表結構：書店編號：普通編碼定長字符類型，長度為20，主鍵。書店名：普通編碼可變長字符類型，長度為30，非空。地址：統一字符編碼可變長字符類型，長度為30。?“銷售”表結構：書號：普通編碼定長字符類型，長度為10，引用圖書表的外鍵。書店編號：普通編碼

2、定長字符類型，長度為20，引用書店表的外鍵。銷售時間：日期時間類型。銷售數量：整型。主鍵：（書號，書店編號，銷售時間）。答案:答： CREATE TABLE 圖書(書號 char(10) primary key,書名 varchar(20) not null,類別 nchar(4) CHECK (類別 IN(高等數學,量子力學, 國學基礎),出版日期 date,單價 int )CREATE TABLE書店( 書店編號 char(20) primary key,書店名 varchar(30) not null,地址 nvarchar(30) ) CREATE TABLE銷售( 書號 char(1

3、0),書店編號 char(20),銷售時間 datetime,銷售數量 int, Primary key(書號,書店編號,銷售時間), Foreign key(書號) references 圖書(書號), Foreign key(書店編號) references書店(書店編號)依據第1題所創建的三張表，寫出完成下列操作的SQL語句。（本題4分）1）? 查詢“量子力學”類圖書的書名和出版日期。2）? 查詢單價大于等于60的圖書的銷售情況，列出書名、類別、銷售時間和銷售數量。3）? 將“量子力學”類圖書的單價降低10。4）? 在書店表中插入一行數據，書店編號：SD100，書店名：新華書店，地址：西

4、單。答案:1）SELECT 書名,出版日期 FROM 圖書 WHERE 類別 = 量子力學2）SELECT 書名, 類別, 銷售時間, 銷售數量FROM 圖書 a JOIN 銷售 b ON a.書號 = b.書號WHERE 單價 = 603）UPDATE 圖書 SET 單價 = 單價 - 10 WHERE 類別 = 量子力學4）INSERT INTO 書店VALUES(SD100,新華書店,西單)依據第1題所創建的三張表，寫出創建滿足如下要求的視圖的SQL語句：查詢銷售了“高等數學”類圖書的書店名和地址。（本題2分）答案:答：CREATE VIEW V1AS SELECT 書店名,地址 FRO

5、M 書店 a JOIN 銷售 b ON a.書店編號 = b.書店編號 JOIN 圖書 c ON c.書號 = b.書號 WHERE 類別 = 高等數學綜合應用題2 教師授課（本題10分）寫出創建如下三張數據表的SQL語句。（本題4分）“教師”表結構：教師號：普通編碼定長字符類型，長度為10，主鍵。教師名：普通編碼可變長字符類型，長度為20，非空。所在部門：普通編碼可變長字符類型，長度為30。職稱：普通編碼定長字符類型，長度為6，取值為：“教授”、“副教授”、“其他”。?“課程”表結構：課程號：普通編碼定長字符類型，長度為20，主鍵。課程名：普通編碼可變長字符類型，長度為40，非空。學時數：微

6、整型。開課學期：微整型。?“授課”表結構：教師號：普通編碼定長字符類型，長度為10，引用教師表的外鍵。課程號：普通編碼定長字符類型，長度為20，引用課程表的外鍵。授課時數：整型。授課年份：整型。主鍵：（教師號，課程號，授課年份）。答案:CREATE TABLE 教師(教師號 char(10) primary key,教師名 varchar(20) not null,所在部門 varchar(30),職稱 char(6) CHECK (職稱 IN(教授,副教授, 其他)CREATE TABLE 課程(課程號 char(20) primary key,課程名 varchar(40) not nul

7、l,學時數 tinyint,開課學期 tinyint依據第1題所創建的三張表，寫出完成下列操作的SQL語句。（本題4分）1）? 查詢“教授”職稱的教師姓名和所在部門。2）? 查詢每學期開設的學時數大于40的課程門數。3）? 查詢2016年全部課程的授課情況，列出課程名、學時數、教師名和授課時數。刪除沒人講授的課程。答案:答： 1） SELECT 教師名, 所在部門FROM 教師WHERE 職稱 = 教授2） SELECT 開課學期,COUNT(*) 課程門數FROM 課程WHERE 學時數 40GROUP BY 開課學期3） SELECT 課程名, 學時數, 教師名, 授課時數 FROM 課程JOIN 授課 ON 課程.課程號 = 授課.課程號 JOIN 教師 ON 教師.教師號 = 授課.教師號 WHERE 授課年份 = 20164） DELETE FROM 課程WHERE 課程號 NOT IN (SELECT 課程號 FROM 授課)依據第1題所創建的三張表，寫出創建滿足如下要求的視圖的SQL語句：查詢2016授課年份全體授課教師的教師名、所授的課程名和授課時數。（本題2分