1、練習題1、分別創建存儲過程和函數實現兩個數相除并且顯示結果如果第二個 數是0則顯示消息“not to DIVIDE BY ZERO”不為0則顯示結果。2、分別編寫存儲過程和函數計算1到100的累加和。3、分別創建存儲過程和函數顯示EMP表中工資最高及工資最低的員工的 姓名、編號并在之后標出“工資最高”或“工資最低”字樣。4、創建一個函數Emp_Avg根據員工號返回員工所在部門的平均工資。5、創建表jobday表結構如下DayID nvarchar (50) 主鍵日期格式yyyyMMddWeek nvarchar (1) NOT NULL 星期幾中文表示IsJobDay int NOT NULL

2、 是否是周六或周日0表示不是1表示是編寫存儲過程實現根據參數值傳入的年份向jobday表中寫入該年對應 的每一天的日期及各日期所對應的是星期幾并在isjobday列中使用0 或1進行標識。6、編寫存儲過程根據參數值傳入的年份統計該年份以后被 雇傭的各項工作每年的雇傭人數。顯示效果如下job_id 1995 1996 1997 1998 1999 2000- - - - - - -AC_ACCOUNT 0 0 0 0 0 0AC_MGR 0 0 0 0 0 0AD_ASST 0 0 0 0 0 0AD_PRES 0 0 0 0 0 0AD_VP 0 0 0 0 0 0FI_ACCOUNT 0 0

3、 2 1 10FI_MGR 0 0 0 0 0 0HR_REP 0 0 0 0 0 0IT_PROG 0 0 1 1 1 0MK_MAN 0 1 0 0 0 0MK_REP 0 0 1 0 0 0.。程序設計題假設在factory數據庫中已創建了如下3個表，（1）職工表worker，其結構為：職工號：int，姓名：char(8)，性別：char(2)，出生日期：datetime，黨員否：bit，參加工作：datetime，部門號：int。（2）部門表depart，其結構為：部門號：int，部門名：char(10)。（3）職工工資表salary，其結構為：職工號：int，姓名：char(8)，日

4、期：datetime，工資：decimal(6,1)。1.使用Transact-SQL語句完成如下各題：（1）顯示所有職工的年齡。（2）求出各部門的黨員人數。（3）顯示所有職工的姓名和2004年1月份工資數。（4）顯示所有職工的職工號、姓名和平均工資。（5）顯示所有職工的職工號、姓名、部門名和2004年2月份工資，并按部門名順序排列。（6）顯示各部門名和該部門的所有職工平均工資。（7）顯示所有平均工資高于1200的部門名和對應的平均工資。（8）顯示所有職工的職工號、姓名和部門類型，其中財務處和人事處屬管理部門，市場部屬市場部門。答案：（1）SELECT 姓名,YEAR(GETDATE()-YE

5、AR(出生日期) AS 年齡 FROM worker（2）SELECT depart.部門名,count(*) AS 黨員人數 FROM worker,depart WHERE worker.黨員否=1 AND worker.部門號=depart.部門號 GROUP BY depart.部門名（3）SELECT worker.姓名,salary.工資 FROM worker,salary WHERE worker.職工號= salary.職工號 AND YEAR(salary.日期)=2004 AND MONTH(salary.日期)=1（4）. SELECT 職工號,AVG(工資) AS 平

6、均工資FROM salaryGROUP BY 職工號（5） SELECT worker.職工號,worker.姓名, depart.部門名, salary.工資 AS 2004年2月工資FROM worker,depart,salaryWHERE worker.部門號=depart.部門號 AND worker.職工號= salary.職工號 AND YEAR(salary.日期)=2004 AND MONTH(salary.日期)=2ORDER BY worker.部門號（6）SELECT depart.部門名, AVG(salary.工資) AS 平均工資FROM worker,depar

7、t,salaryWHERE worker.部門號=depart.部門號 AND worker.職工號= salary.職工號 GROUP BY depart.部門名（7）SELECT depart.部門名, AVG(salary.工資) AS 平均工資FROM worker,depart,salaryWHERE worker.部門號=depart.部門號 AND worker.職工號= salary.職工號 GROUP BY depart.部門名HAVING AVG(salary.工資)>1200（8）. USE factory GO SELECT worker.職工號,worker.姓

8、名CASE depart.部門名 WHEN 財務處 THEN 管理部門WHEN 人事處 THEN 管理部門WHEN 市場部 THEN 市場部門END AS 部門類型FROM worker,depart WHERE worker.部門號=depart.部門號GO2在前面建立的factory數據庫上，用Transact-SQL語句完成下列各題：（1）在worker表中的“部門號”列上創建一個非聚集索引，若該索引已存在，則刪除后重建。（2）在salary表的“職工號”和“日期”列創建聚集索引，并且強制惟一性。答案：（1）SET NOCOUNT OFF USE factory IF EXISTS(SE

9、LECT name FROM sysindexes WHERE name=depno DROP INDEX worker.depno GO CREATE INDEX depno ON worker(部門號) GO(2) SET NOCOUNT OFF USE factory IF EXISTS(SELECT name FROM sysindexes WHERE name=no_date DROP INDEX salary.no_date GO CREATE UNIQUE CLUSTERED INDEX no_date ON salary(職工號,日期) GO3在前面建立的factory數據庫上

10、，用Transact-SQL語句完成下列各題：（1）建立視圖View1，查詢所有職工的職工號、姓名、部門名和2004年2月份工資，并按部門名順序排列。（2）建立視圖View2，查詢所有職工的職工號、姓名和平均工資。（3）建立視圖View3，查詢各部門名和該部門所有職工平均工資。（4）顯示視圖View3的定義答案：（1）USE factory GO IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME=View1) DROP VIEW View1 GO CREATE VIEW View1 AS SEL

11、ECT TOP 15 worker.職工號,worker.姓名,depart.部門名,salary.工資 AS 2004年2月工資 FROM worker,depart,salary WHERE worker.部門號=depart.部門號 AND worker.職工號=salary.職工號 AND YEAR(salary.日期)=2004 AND MONTH(salary.日期)=2 ORDER BY worker.部門號 GO SELECT * FROM View1 GO（2）USE factory GO IF EXISTS(SELECT TABLE_NAME FROM INFORMATIO

12、N_SCHEMA.VIEWS WHERE TABLE_NAME=View2) DROP VIEW View2 GO CREATE VIEW View2 AS SELECT worker.職工號,worker.姓名,AVG(salary.工資) AS 平均工資 FROM worker,salary WHERE worker.職工號=salary.職工號 GROUP BY worker.職工號,worker.姓名 GO SELECT * FROM View2 GO（3）USE factory GO IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEM

13、A.VIEWS WHERE TABLE_NAME=View3) DROP VIEW View3 GO CREATE VIEW View3 AS SELECT depart.部門名, AVG(salary.工資) AS 平均工資 FROM worker,depart,salary WHERE worker.部門號=depart.部門號 AND worker.職工號=salary.職工號 GROUP BY depart.部門名 GO SELECT * FROM View3 GO（4）USE factory GO EXEC sp_helptext View3 GO4在前面建立的factory數據庫上

14、，用Transact-SQL語句完成下列各題：(1) 實施worker表的“性別”字段默認值為“男”的約束。(2) 實施salary表的“工資”字段值限定在09999的約束。(3) 實施depart表的“部門號”字段值惟一的非聚集索引的約束。(4) 為worker表建立外鍵“部門號”，參考表depart的“部門號”列。(5) 建立一個規則sex：性別='男' OR 性別='女'，將其綁定到worker表的“性別”列上。(6) 刪除(1)小題所建立的約束。(7) 解除(5)小題所建立的綁定并刪除規則sex。答案：（1）ALTER TABLE worker ADD

15、CONSTRAINT default_sex DEFAULT 男 FOR 性別（2）ALTER TABLE salary ADD CONSTRAINT check_salary CHECK(工資>0 AND 工資<9999)（3）ALTER TABLE depart ADD CONSTRAINT unique_depart UNIQUE NONCLUSTERED(部門號)（4）ALTER TABLE worker ADD CONSTRAINT FK_worker_no FOREIGN KEY (部門號) REFERENCES depart(部門號)（5）CREATE RULE se

16、x AS 性別='男' OR 性別='女' EXEC sp_bindrule sex,worker.性別（6） ALTER TABLE worker DROP CONSTRAINT default_sex（7）EXEC sp_unbindrule worker.性別 DROP RULE sex5在前面建立的factory數據庫上，用Transact-SQL語句完成下列各題：(1) 創建一個為worker表添加職工記錄的存儲過程Addworker。(2) 創建一個存儲過程Delworker刪除worker表中指定職工號的記錄。(3) 顯示存儲過程Delworker

17、。(4) 刪除存儲過程Addworker和Delworker。答案： （1）USE factory GO CREATE PROCEDURE Addworker no int=NULL, name char(10)=NULL, sex char(2)=NULL, birthday datetime=NULL, na bit=NULL, wtime datetime=NULL, depno int=NULL AS IF no IS NULL OR name IS NULL OR sex IS NULL OR birthday IS NULL OR depno IS NULL BEGIN PRINT

18、 請重新輸入該職工信息! PRINT 你必須提供職工號、姓名、性別、出生日期、部門號 RETURN END BEGIN TRANSACTION INSERT INTO worker VALUES(no,name,sex,birthday,na,wtime,depno) IF error<>0 BEGIN ROLLBACK TRAN RETURN END COMMIT TRANSACTION PRINT 職工+name+的信息成功添加到表worker中 執行下列語句，可驗證存儲過程的正確性： USE factory GO Addwoeker 20,陳立,女,55/03/08,1,75

19、/10/10,4 GO SELECT * FROM worker GO （2）USE factory GO CREATE PROCEDURE Delworker no int=NULL AS IF no IS NULL BEGIN PRINT 必須輸入職工號！ RETURN END BEGIN TRANSACTION DELETE FROM worker WHERE 職工號=no IF error<>0 BEGIN ROLLBACK TRAN RETURN END COMMIT TRANSACTION PRINT 成功刪除職工號為+CAST(no AS CHAR(2)+的職工記錄

20、執行下列語句，可驗證存儲過程的正確性： USE factory GO Delwoeker 20 GO SELECT * FROM worker GO （3）USE factory GO EXEC sp_helptext Delworker GO（4）USE factory GO IF EXISTS (SELECT name FROM sysobjects WHERE name=Addworker AND type=P DROP PROCEDURE Addworker GO IF EXISTS (SELECT name FROM sysobjects WHERE name=Delworker AND type=P DROP PROCEDURE Delworker GO 6在前面建立的factory數據庫上，用Transact-SQL語句完成下列各題：(1) 在表depart上創建一個觸發器depart_update，當更改部門號時同步更改worker表中對應的部門號。(2) 在表worker上創建一個觸發器worker_delete，當刪