【正文】 來(lái)統(tǒng)計(jì)并顯示每個(gè)系的系號(hào)、系名以及每個(gè)系學(xué)生的人數(shù)。（1）要求：如果新創(chuàng)建的系還沒(méi)有學(xué)生，則不顯示該系；（2）要求：如果新創(chuàng)建的系還沒(méi)有學(xué)生，則顯示該系的學(xué)生人數(shù)為0。 答: SELECT , , FROM 系信息表 AS a INNER JOIN ( SELECT 系號(hào),count(*) AS 系學(xué)生人數(shù) FROM 學(xué)生信息表 GROUP BY 系號(hào) ) AS b ON = SELECT , , ISNULL(, 0) FROM 系信息表 AS a LEFT OUTER JOIN ( SELECT 系號(hào),count(*) AS 系學(xué)生人數(shù) FROM 學(xué)生信息表 GROUP BY 系號(hào) ) AS b ON = 3. 接上題，請(qǐng)顯示學(xué)生人數(shù)最多的系的系號(hào)、名稱(chēng)和學(xué)生人數(shù)。答: SELECT , , count(學(xué)號(hào)) AS 系學(xué)生人數(shù) FROM 學(xué)生信息表 AS a INNER JOIN 系信息表 AS b ON = GROUP BY , HAVING count(學(xué)號(hào)) = (SELECT max(系學(xué)生人數(shù)) FROM ( SELECT 系號(hào), count(學(xué)號(hào)) AS 系學(xué)生人數(shù) FROM 學(xué)生信息表 GROUP BY 系號(hào)) AS T )4. 在SQL Server 2000數(shù)據(jù)庫(kù)中創(chuàng)建了如下的一些表：CREATE TABLE 部門(mén)表( 部門(mén)號(hào) char(2) not null PRIMARY KEY, 部門(mén)名稱(chēng) varchar(30) not null)CREATE TABLE 雇員表( 雇員代號(hào) char(4) not null PRIMARY KEY, 雇員姓名 varchar(20) not null)CREATE TABLE 任職表( 雇員代號(hào) char(4) not null, 部門(mén)號(hào) char(2) not null, 開(kāi)始時(shí)間 datetime not null, 備注 varchar(1000) null,CONSTRAINT PK_Works PRIMARY KEY(雇員代號(hào),部門(mén)號(hào), 開(kāi)始時(shí)間), CONSTRAINT FK_Employees FOREIGN KEY (雇員代號(hào)) REFERENCES 雇員表(雇員代號(hào)) ON DELETE CASCADE, CONSTRAINT FK_Departments FOREIGN KEY (部門(mén)號(hào)) REFERENCES 部門(mén)表(部門(mén)號(hào)) )現(xiàn)在人事經(jīng)理需要獲得在每個(gè)部門(mén)都工作過(guò)的雇員的名單，請(qǐng)寫(xiě)出帶有子查詢(xún)的SQL語(yǔ)句來(lái)完成改任務(wù)。答:SELECT 雇員姓名FROM 雇員表 AS aWHERE NOT EXISTS( SELECT FROM 部門(mén)表 AS b WHERE NOT IN ( SELECT FROM 任職表 AS c WHERE =) )5. 實(shí)驗(yàn)方案Duluth Mutual Life健康保健組織有一個(gè)用于跟蹤醫(yī)生和他們的病人的信息的數(shù)據(jù)庫(kù)。這個(gè)數(shù)據(jù)庫(kù)有下列數(shù)據(jù)表。l 醫(yī)生數(shù)據(jù)表表 01列數(shù)據(jù)類(lèi)型和約束doc_idchar(9), PRIMARY KEYFnamechar(20)Lnamechar(25)Specialtychar(25)Phonechar(10)l 病人數(shù)據(jù)表表 02列數(shù)據(jù)類(lèi)型和約束pat_idchar(9), PRIMARY KEYFnamechar(20)Lnamechar(25)Insurance_panychar(25)Phonechar(10)l 病歷表表 03列數(shù)據(jù)類(lèi)型和約束Admission_datedatetime, PRIMARY KEY (posite)pat_idchar(9), PRIMARY KEY (posite), FOREIGN KEY to doc_idchar(9), FOREIGN KEY to Diagnosisvarchar(150)根據(jù)這個(gè)表結(jié)構(gòu)，回答下列問(wèn)題。a) 如何用一個(gè)查詢(xún)語(yǔ)句，查詢(xún)出數(shù)據(jù)庫(kù)中最初入院治療的病歷？答:在單值子查詢(xún)中使用函數(shù)MIN來(lái)確定最早入院治療的日期。用WHERE子句把子 查詢(xún)的結(jié)果同每個(gè)病例的入院治療日期進(jìn)行比較。b) 想要知道入院治療的總數(shù)，按病人姓名排序，如何做到這一點(diǎn)？你的方法有哪些優(yōu)點(diǎn)和缺點(diǎn)？答:你可以寫(xiě)一個(gè)含有相關(guān)子查詢(xún)的SELECT語(yǔ)句，用COUNT函數(shù)計(jì)算每個(gè)病人入院治病的總次數(shù)。SELECT pat_id, pat_name ,(SELECT count(*) FROM casefile C WHERE = )FROM patient AS P也可以用含有GROUP BY子句和COUNT函數(shù)的聯(lián)接語(yǔ)句。用子查詢(xún)的方法可能查詢(xún)效率不如用GROUP BY，但邏輯上更清晰。第11章 修改數(shù)據(jù)1. 你在SQL Server 2000數(shù)據(jù)庫(kù)中創(chuàng)建了定單表： CREATE TABLE 定單表 ( 定單號(hào) int IDENTITY(100001,1) PRIMARY KEY, 雇員代號(hào) int NOT NULL, 客戶(hù)代號(hào) int NOT NULL, 訂購(gòu)日期 datetime NOT NULL, 訂購(gòu)金額 money NOT NULL ) 由于業(yè)務(wù)量很大，定單表已經(jīng)存放了大量的數(shù)據(jù)。你想刪除3年以前的訂購(gòu)信息， 下面哪個(gè)語(yǔ)句能夠完成改任務(wù)？A. DELETE FROM 定單表 WHERE 定單日期 DATEADD( YY, 3, GETDATE() )B. DELETE FROM 定單表 WHERE 定單日期 DATEADD( YY, 3, GETDATE() )C. DELETE FROM 定單表 WHERE 定單日期 GETDATE() 3D. DELETE FROM 定單表 WHERE 定單日期 GETDATE() + 3答:A2. 學(xué)生信息系統(tǒng)中有兩個(gè)表：“學(xué)生信息表”和“系信息表”。在學(xué)生信息表中有列“學(xué)號(hào)”、列“姓名”、列“出生日期”、列“系號(hào)”、列“學(xué)生備注”，在系信息表中有列“系號(hào)”、列“系名”、列“系主任”、列“系簡(jiǎn)介”。列“系號(hào)”是學(xué)生信息表引用系信息表的外鍵。假設(shè)成立了一個(gè)新的系，“系號(hào)”為“18”，“系名”為“生命科學(xué)系”，“系主任”和“系簡(jiǎn)介”為空值。請(qǐng)用INSERT語(yǔ)句添加該系到系信息表，要求寫(xiě)出兩種形式的INSERT語(yǔ)句。答:INSERT INTO 系信息表(系號(hào),系名)VALUES(39。1839。,39。生命科學(xué)系39。)或INSERT INTO 系信息表VALUES(39。1839。,39。生命科學(xué)系39。,NULL,NULL)3. 接上題，有一個(gè)新學(xué)生報(bào)到，該學(xué)生的學(xué)號(hào)為“03120001”，姓名為“張三”，出生日期為“19820101”，所在的系名為“計(jì)算機(jī)科學(xué)系”，沒(méi)有“學(xué)生備注”。請(qǐng)用INSERT語(yǔ)句添加該學(xué)生到學(xué)生信息表。提示：使用局部變量‘@系號(hào)’。答:DECLARE @系號(hào) varchar(20)SELECT @系號(hào)=系號(hào) FROM 系信息表 WHERE 系名=39。計(jì)算機(jī)科學(xué)系39。INSERT INTO 學(xué)生信息表VALUES(39。0312000139。,39。張三39。,39。1982010139。,@系號(hào),NULL)說(shuō)明：INSERT 語(yǔ)句中要插入的值不允許使用子查詢(xún)，只允許使用標(biāo)量表達(dá) 式，即下面是錯(cuò)誤的。INSERT INTO 學(xué)生信息表VALUES(39。0312000139。,39。張三39。,39。1982010139。,( SELECT @系號(hào)=系號(hào) FROM 系信息表 WHERE 系名=39。計(jì)算機(jī)科學(xué)系39。 ),NULL) 4. 接上題，請(qǐng)用DELETE語(yǔ)句刪除所在系的系名為“計(jì)算機(jī)科學(xué)系”的所有學(xué)生的紀(jì)錄。答:SELECT * FROM 學(xué)生信息表DELETE 學(xué)生信息表WHERE 系號(hào)= (SELECT 系號(hào) FROM 系信息表 WHERE 系名=39。計(jì)算機(jī)科學(xué)系39。) 5. 接上題，由于學(xué)號(hào)為“03120001”的學(xué)生獲得國(guó)際象棋世界冠軍，要同時(shí)在學(xué)生信息表中該學(xué)生紀(jì)錄的列“學(xué)生備注”和系信息表的列“系簡(jiǎn)介”添加一句話(huà)：“2006年學(xué)生”+該學(xué)生姓名+“榮獲國(guó)際象棋世界冠軍。”。請(qǐng)用一個(gè)事務(wù)完成該任務(wù)。答:DECLARE @系號(hào) varchar(2),@姓名 varchar(20), @新備注 varchar(200)BEGIN TRANSACTION SELECT @系號(hào)=系號(hào),@姓名=姓名 FROM 學(xué)生信息表 WHERE 學(xué)號(hào)=39。0312000139。 SET @新備注=39。2006年學(xué)生39。+@姓名+39。榮獲國(guó)際象棋世界冠軍。39。 UPDATE 學(xué)生信息表 SET 學(xué)生備注= ISNULL(學(xué)生備注,39。39。)+@新備注 WHERE 學(xué)號(hào)=39。0312000139。IF @@ERROR 0 BEGIN RAISERROR (39。錯(cuò)誤，不能更新學(xué)生備注！39。, 16, 1) ROLLBACK TRANSACTION RETURN END UPDATE 系信息表 SET 系簡(jiǎn)介= ISNULL(系簡(jiǎn)介,39。39。)+@新備注 WHERE 系號(hào)=@系號(hào) IF @@ERROR 0 BEGIN RAISERROR (39。錯(cuò)誤，不能更新系簡(jiǎn)介！39。, 16, 1) ROLLBACK TRANSACTION RETURN ENDCOMMIT TRANSACTION 6. 你是一個(gè)關(guān)注健康計(jì)劃的數(shù)據(jù)庫(kù)管理員。使用下面的語(yǔ)句來(lái)創(chuàng)建physicians表：CREATE TABLE ( physician_no int IDENTITY (100, 2) NOT NULL,f_name varchar (25) NOT NULL,l_name varchar (25) NOT NULL,street varchar (50) NULL,city varchar (255) NULL,state varchar (255) NULL,postal_code varchar (7) NULL,co_pay money NOT NULL CONSTRAINT phys_co_pay DEFAULT (10))GOa) 必須提供最少多少個(gè)列值才能把一個(gè)新的行添加到表中？答:必須提供至少兩列的數(shù)據(jù)。至少I(mǎi)NSERT語(yǔ)句要包含f_name和 l_name的值。所有其它列都允許空值或存在為它們產(chǎn)生的默認(rèn)值。b) 已增加了參加活動(dòng)的醫(yī)師的服務(wù)報(bào)酬。你如何為所有醫(yī)生的co_pay字段值增加12%？答:用如下形式的UPDATE語(yǔ)句：UPDATE physicians SET co_pay = (co_pay + co_pay * .12)c) 你如何從 physicians表中刪除所有行？答:使用DELETE語(yǔ)句或TRUNCATE TABLE語(yǔ)句。第12章 全文索引查詢(xún)1. 你在SQL Server 2000數(shù)據(jù)庫(kù)中創(chuàng)建作文表來(lái)記錄學(xué)生的作文和老師的評(píng)語(yǔ)：CREATE TABLE 作文表 （ 作文代號(hào) int IDENTITY(1,1) PRIMARY KEY, 作文內(nèi)容 text NOT NULL, 學(xué)生代號(hào) char(8) NOT NULL, 教師評(píng)語(yǔ) text NOT NULL, 教師代號(hào) char(4) NOT NULL,