SQL編程練習(xí)題

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

2、 是否是周六或周日0表示不是1表示是編寫存儲過程實現(xiàn)根據(jù)參數(shù)值傳入的年份向jobday表中寫入該年對應(yīng) 的每一天的日期及各日期所對應(yīng)的是星期幾并在isjobday列中使用0 或1進行標(biāo)識。6、編寫存儲過程根據(jù)參數(shù)值傳入的年份統(tǒng)計該年份以后被 雇傭的各項工作每年的雇傭人數(shù)。顯示效果如下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.。程序設(shè)計題假設(shè)在factory數(shù)據(jù)庫中已創(chuàng)建了如下3個表，（1）職工表worker，其結(jié)構(gòu)為：職工號：int，姓名：char(8)，性別：char(2)，出生日期：datetime，黨員否：bit，參加工作：datetime，部門號：int。（2）部門表depart，其結(jié)構(gòu)為：部門號：int，部門名：char(10)。（3）職工工資表salary，其結(jié)構(gòu)為：職工號：int，姓名：char(8)，日

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

5、AR(出生日期) AS 年齡 FROM worker（2）SELECT depart.部門名,count(*) AS 黨員人數(shù) 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.姓名CAS

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

9、 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數(shù)據(jù)庫上，用Tr

10、ansact-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 SELECT

11、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 INFORMATION_SC

12、HEMA.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_SCHEMA.VI

13、EWS 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數(shù)據(jù)庫上，用Tr

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

15、EFAULT 男 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 sex AS 性別=男 OR 性別=女 EXEC sp_bindru

16、le sex,worker.性別（6） ALTER TABLE worker DROP CONSTRAINT default_sex（7）EXEC sp_unbindrule worker.性別 DROP RULE sex5在前面建立的factory數(shù)據(jù)庫上，用Transact-SQL語句完成下列各題：(1) 創(chuàng)建一個為worker表添加職工記錄的存儲過程Addworker。(2) 創(chuàng)建一個存儲過程Delworker刪除worker表中指定職工號的記錄。(3) 顯示存儲過程Delworker。(4) 刪除存儲過程Addworker和Delworker。答案： （1）USE factory GO

17、 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 請重新輸入該職工信息! PRINT 你必須提供職工號、姓名、性別、出生日期、部門號 RETURN EN

18、D BEGIN TRANSACTION INSERT INTO worker VALUES(no,name,sex,birthday,na,wtime,depno) IF error0 BEGIN ROLLBACK TRAN RETURN END COMMIT TRANSACTION PRINT 職工+name+的信息成功添加到表worker中 執(zhí)行下列語句，可驗證存儲過程的正確性： USE factory GO Addwoeker 20,陳立,女,55/03/08,1,75/10/10,4 GO SELECT * FROM worker GO （2）USE factory GO CREATE

19、 PROCEDURE Delworker no int=NULL AS IF no IS NULL BEGIN PRINT 必須輸入職工號！ RETURN END BEGIN TRANSACTION DELETE FROM worker WHERE 職工號=no IF error0 BEGIN ROLLBACK TRAN RETURN END COMMIT TRANSACTION PRINT 成功刪除職工號為+CAST(no AS CHAR(2)+的職工記錄 執(zhí)行下列語句，可驗證存儲過程的正確性： USE factory GO Delwoeker 20 GO SELECT * FROM wor

20、ker 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數(shù)據(jù)庫上，用Transact-SQL語句完成下列各題：(1) 在表depart上創(chuàng)建一個觸發(fā)器depart_update，當(dāng)更改部門號時同步更改worker表中對應(yīng)的部門號。(2) 在表worker上創(chuàng)建一個觸發(fā)器worker_delete，當(dāng)刪除職工記錄時同