數(shù)據(jù)庫技術(shù)與應(yīng)用SQL習(xí)題答案

1、實驗2 SQL Server 數(shù)據(jù)庫的管理4. 啟動查詢分析器，在查詢分析器中使用Transact-SQL語句CREATE DATABASE創(chuàng)建studb數(shù)據(jù)庫。然后通過系統(tǒng)存儲過程sp_helpdb查看系統(tǒng)中的數(shù)據(jù)庫信息CREATE DATABASE studbsp_helpdb5. 在查詢分析器中使用Transact-SQL語句ALTER DATABASE修改studb數(shù)據(jù)庫的設(shè)置，指定數(shù)據(jù)文件大小為5MB，最大文件大小為20MB，自動遞增大小文1MB。ALTER DATABASE studbMODIFY FILE(NAME=studb,SIZE=5MB,MAXSIZE=20MB,FILE

2、GROWTH=1MB)7. 使用企業(yè)管理器將studb數(shù)據(jù)庫的名稱更改為student_db。ALTER DATABASE studbMODIFY NAME=student_db8. 使用Transact-SQL語句DROP DATABASE刪除student_db數(shù)據(jù)庫。DROP DATABASE student_db實驗3 SQL Server 數(shù)據(jù)表的管理5. 使用Transact-SQL語句CREATE TABLE在studentsdb數(shù)據(jù)庫中創(chuàng)建grade表。CREATE TABLE grade(學(xué)號 char(4),課程編號 char(4),分?jǐn)?shù) decimal(5)8. 使用Tr

3、ansact_SQL語句INSERT INTO.VALUES向studentsdb數(shù)據(jù)庫的grade表插入以下數(shù)據(jù)：學(xué)號 課程編號 分?jǐn)?shù)0004 0001 80USE studentsdbGOINSERT INTO grade VALUES(0004,0001,80)9. 使用Transact_SQL語句ALTER TABLE修改curriculum表的“課程編號”列，使之為非空。ALTER TABLE curriculumALTER COLUMN 課程編號 char(4)NOT NULLGO10. 使用Transact_SQL語句ALTER TABLE修改grade表的“分?jǐn)?shù)”列，使其數(shù)據(jù)類

4、型為real。ALTER TABLE gradeALTER COLUMN 分?jǐn)?shù) real11. 分別使用企業(yè)管理器和Transact_SQL語句DELETE刪除studentsdb數(shù)據(jù)庫的grade表中學(xué)號為0004的成績記錄。DELETE grade WHERE 學(xué)號=000413 使用Transact_SQL語句UPDATE修改studentsdb數(shù)據(jù)庫的grade表中學(xué)號為0003、課程編號為0005、分?jǐn)?shù)為90的成績記錄。UPDATE grade SET 分?jǐn)?shù)=90WHERE 學(xué)號=0003 and 課程編號=000514. 使用Transact_SQL語句ALTER.ADD為stud

5、entsdb數(shù)據(jù)庫的grade表添加一個名為“備注”的數(shù)據(jù)列，其數(shù)據(jù)類型為VARCHAR(20)。ALTER TABLE grade ADD 備注 VARCHAR(20) NULL15. 分別使用企業(yè)管理器和Transact_SQL語句DROP TABLE刪除studentsdb數(shù)據(jù)庫中的grade表。DROP TABLE grade實驗4 數(shù)據(jù)查詢1. 在studentsdb數(shù)據(jù)庫中，使用下列SQL語句將輸出什么？（1）15（2）劉衛(wèi)張衛(wèi)馬東錢達(dá)東方郭文肖海張明 （3）KELLY （4）kellykellykelly （5）9.48683298050513819.32737905308881

6、579.27361849549570399.4339811320566032 （6）238 （7）201110102. 在studentsdb數(shù)據(jù)庫中使用SELECT語句進(jìn)行基本查詢。 (1) SELECT 姓名,學(xué)號,姓名,出生日期 from student_info (2) SELECT 姓名,家庭住址 from student_info where 學(xué)號=0002 (3) 劉衛(wèi)平 0001張衛(wèi)民 0002馬東 0003錢達(dá)理 0004東方牧 00053.使用SELECT語句進(jìn)行條件查詢(1) SELECT 學(xué)號,分?jǐn)?shù) from gradewhere 分?jǐn)?shù)80(2) SELECT avg(

7、分?jǐn)?shù)) from grade where 學(xué)號=0003(3) SELECT 課程編號, count(課程編號) from grade group by 課程編號(4) SELECT 姓名,出生日期 from student_info order by 出生日期 asc(5) SELECT 學(xué)號,姓名 FROM student_info WHERE 姓名 LIKE 張%4. 嵌套查詢 (4) SELECT 課程編號,分?jǐn)?shù) FROM gradewhere 學(xué)號=0001 and 分?jǐn)?shù)(SELECT max(分?jǐn)?shù)) from grade where 學(xué)號=0002)5. 多表查詢 (3) SELE

8、CT s.學(xué)號,s.姓名,c.課程名稱,g.分?jǐn)?shù) FROM student_info s,grade g,curriculum cwhere s.學(xué)號=g.學(xué)號 and s.性別=男 and c.課程編號=g.課程編號(4) select 學(xué)號,max(分?jǐn)?shù))from grade group by 學(xué)號(5) SELECT s.學(xué)號,姓名,sum(g.分?jǐn)?shù)) FROM student_info s left outer join grade g on s.學(xué)號=g.學(xué)號 group by s.學(xué)號,姓名(6) insert into grade(學(xué)號,課程編號,分?jǐn)?shù))values(0004,0

9、006,76) SELECT c.課程編號,課程名稱,count(g.學(xué)號) FROM curriculum c right outer join grade g on g.課程編號=c.課程編號 group by c.課程編號,課程名稱6. 使用UNION運算符將student_info表中姓“張”的學(xué)生的學(xué)號、姓名與curriculum表的課程編號、課程名稱返回在一個表中，且列名為u_編號、u_名稱，如圖1-8所示。select 學(xué)號 u_編號,姓名 u_名稱 from student_info where 姓名 like 張%unionselect 課程編號,課程名稱 from curr

10、iculum7. 數(shù)據(jù)更新 (4) delete from totalgradewhere 總成績=null實驗5 索引和視圖1. 分別使用企業(yè)管理器和Transact-SQL語句為studentsdb數(shù)據(jù)庫的student_info表格和curriculum表創(chuàng)建主鍵索引。ALTER table student_infoALTER column 學(xué)號 char(4) not nullgoALTER table student_info add constraint PK_student_info primary key(學(xué)號) ALTER table curriculumALTER colu

11、mn 課程編號 char(4) not nullgoALTER table curriculum add constraint PK_curriculum primary key(課程編號)5. .分別使用企業(yè)管理器和系統(tǒng)存儲過程sp_helpindex查看grade表和student_info表上的索引信息。use studentsdbgoexec sp_helpindex gradegouse studentsdbgoexec sp_helpindex student_infogo16. 在studentsdb數(shù)據(jù)庫中，使用Transact-SQL語句 CREATE VIEW 建立一個名為

12、v_stu_c的視圖，顯示學(xué)生的學(xué)號、姓名、所學(xué)課程的課程編號，并利用視圖查詢學(xué)號為0003的學(xué)生情況 create view v_stu_casselect student_info.學(xué)號,student_info.姓名,curriculum.課程名稱,grade.課程編號from student_info inner join grade on student_info.學(xué)號=grade.學(xué)號 inner join curriculum on grade.課程編號=curriculum.課程編號 select * from v_stu_cwhere 學(xué)號=000317. 基于student

13、_info表、curriculum表和grade表，建立一個名為v_stu_g的視圖，視圖中具有所有學(xué)生的學(xué)號、姓名、課程名稱、分?jǐn)?shù)。使用視圖v_stu_g查詢學(xué)號為0001的學(xué)生的所有課程和成績create view v_stu_gasselect student_info.學(xué)號,student_info.姓名,curriculum.課程名稱,grade.分?jǐn)?shù)from student_info inner join grade on student_info.學(xué)號=grade.學(xué)號 inner join curriculum on grade.課程編號=curriculum.課程編號 sel

14、ect *from v_stu_gwhere 學(xué)號=000118. 分別使用企業(yè)管理器和Transact-SQL語句修改視圖v_stu_c,使之顯示學(xué)號、姓名、每個學(xué)生所學(xué)課程數(shù)目。 alter view v_stu_c(學(xué)號,姓名,課程數(shù)目)as select student_info.學(xué)號,student_info.姓名,count(grade.課程編號) as 課程數(shù)目from student_info,gradewhere student_info.學(xué)號=grade.學(xué)號group by student_info.學(xué)號,student_info.姓名21. 利用視圖v_stu_i為st

15、udent_info表添加一行數(shù)據(jù)：學(xué)號為0015、姓名為陳婷、性別為女。 insert into v_stu_infovalues(0015,陳婷,女)22. 利用視圖v_stu_i刪除學(xué)號為0015的學(xué)生記錄。delete from v_stu_infowhere 學(xué)號=001523. 利用視圖v_stu_g修改姓名為劉衛(wèi)平的學(xué)生的高等數(shù)學(xué)的分?jǐn)?shù)為84。update v_stu_gset 分?jǐn)?shù)=84where 姓名=劉衛(wèi)平 and 課程名稱=高等數(shù)學(xué)24. 使用Transact-SQL語句DROP VIEW 刪除視圖v_stu_c和v_stu_g。 drop view v_stu_c,v_

16、stu_g 實驗6 數(shù)據(jù)完整性1為sutdentsdb數(shù)據(jù)庫創(chuàng)建一個規(guī)則，限制所輸入的數(shù)據(jù)為7位0-9的數(shù)字。（1）SELECT*INTO stu_phone FROM student_infoALTER TABLE stu_phone ADD 電話號碼 CHAR(7)NULL（2）CREATE RULE phone_ruleASphone LIKE 0-9 0-9 0-9 0-9 0-9 0-9 0-9（3）sp_bindrule phone_rule,stu_phone.電話號碼（4）出錯原因：與該列所綁定的規(guī)則發(fā)生沖突； 需要修改：改1234yyyy為1234567 phone_rule

17、規(guī)則不能對其他操作（如DELETE）進(jìn)行規(guī)則檢查3創(chuàng)建一個規(guī)則stusex_rule，將其綁定到stu_phone表的“性別”列上，保證輸入的性別值只能是“男”或“女”。CREATE RULE stusex_ruleAS sex in(男,女)sp_bindrule stusex_rule,stu_phone.性別4使用系統(tǒng)存儲過程sp_help查詢stusex_rule規(guī)則列表，使用sp_helptext查詢stusex_rule規(guī)則的文本，使用sp_rename將stusex_rule規(guī)則更名為stu_s_rule。sp_help stusex_rule sp_helptext stuse

18、x_rulesp_rename stusex_rule,stu_s_rule5刪除stu_s_rule規(guī)則。Sp_unbindrule stu_phone.性別DROP RULE stu_s_rule6在stuedentdb數(shù)據(jù)庫中，建立日期、貨幣和字符等數(shù)據(jù)類型的默認(rèn)對象。(1) CREATE DEFAULT df_dateAS 2006-4-12GOCREATE DEFAULT df_charAS unknownGOCREATE DEFAULT df_moneyAS $100GO（2）CREATE TABLE stu_fee(學(xué)號 char(10)NOT NULL,姓名 char(8)NO

19、T NULL,學(xué)費 money,交費日期 datetime,電話號碼 char(7)（3）Sp_bindefault df_money,stu_fee.學(xué)費GOSp_bindefault df_date,stu_fee.交費日期GOSp_bindefault df_char,stu_fee.電話號碼GO（4）INSERT INTO stu_fee(學(xué)號,姓名) values(0001,劉衛(wèi)平)INSERT INTO stu_fee(學(xué)號,姓名,學(xué)費) values(0001,張衛(wèi)民,$120)INSERT INTO stu_fee(學(xué)號,姓名,學(xué)費,交費日期)VALUES(0001,馬東,$1

20、10,2006-5-12)（5）sp_unbindefault stu_fee.電話號碼DROP DEFAULT df_char GO sp_unbindefault stu_fee.交費日期DROP DEFAULT df_dateGOsp_unbindefault stu_fee.學(xué)費DROP DEFAULT df_moneyGO8.為student_info表添加一列，命名為“院系”，創(chuàng)建一個默認(rèn)對象stu_d_df，將其綁定到student_info表的“院系”列上，時期默認(rèn)值為“信息院”，對student_info表進(jìn)行插入操作，操作完成后，刪除該默認(rèn)對象。分別使用企業(yè)管理器和查詢分析

21、器實現(xiàn)。ALTER TABLE student_info ADD 院系 CHAR(12)NULLCREATE DEFAULT stu_d_dfAS 信息院sp_bindefault stu_d_df,student_info.院系INSERT student_info(學(xué)號,姓名,院系) values(0001,劉衛(wèi)平, 土木工程)sp_unbindefault student_info.院系DROP DEFAULT stu_d_df9.在studentsdb數(shù)據(jù)庫中用CREATE TABLE語句創(chuàng)建表stu_con，并同時創(chuàng)建約束。(1) CREATE TABLE stu_con(學(xué)號 ch

22、ar(4) CONSTRAINT pk_sid PRIMARY KEY(學(xué)號),姓名 char(8) CONSTRAINT uk_name UNIQUE,性別 char(2) CONSTRAINT df_sex DEFAULT 男,出生日期 datetime CONSTRAINT ck_beday CHECK(出生日期1988-1-1),家庭住址 varchar(50)(2) INSERT stu_con(學(xué)號,姓名,出生日期) VALUES(0009,張小東,1989-4-6)INSERT stu_con(學(xué)號,姓名,性別,出生日期) VALUES(0010,李梅,女,1983-8-5)IN

23、SERT stu_con(學(xué)號,姓名,出生日期) VALUES(0011,王強,1988-9-10)INSERT stu_con(學(xué)號,姓名,出生日期) VALUES(0012,王強,1989-6-3)結(jié)果分析：第一、三條命令順利執(zhí)行，第二、四條命令不能執(zhí)行。第二行語句INSERT 語句與 COLUMN CHECK 約束 ck_beday 沖突。該沖突發(fā)生于數(shù)據(jù)庫 studentsdb，表 stu_con, column 出生日期。第四條語句違反了 UNIQUE KEY 約束 uk_name。不能在對象 stu_con 中插入重復(fù)鍵。（3）ALTER TABLE stu_conDROP CON

24、STRAINT pk_sid,uk_name,df_sex,ck_beday11. 在查詢分析器中，為studentsdb數(shù)據(jù)庫的grade表添加外鍵約束（FOREIGN KEY），要求將“學(xué)號”設(shè)置為外鍵，參照表為student_info，外鍵名為ufk_sid。使用系統(tǒng)存儲過程sp_help查看grade表的外鍵信息。ALTER TABLE student_infoADD PRIMARY KEY(學(xué)號)GOALTER TABLE gradeADD CONSTRAINT ufk_sid FOREIGN KEY(學(xué)號) REFERENCES student_info(學(xué)號)GOsp_help

25、grade在grade表中插入表1-2所示記錄，觀察SQL Server會做何處理，為什么？如何解決所產(chǎn)生的問題?答：學(xué)號0100顯示為100；課程編號0001顯示為1.ALTER TABLE gradeDROP CONSTRAINT ufk_sid實驗7 Transact-SQL程序設(shè)計1.結(jié)果顯示：張明華 顯示的僅為第二個姓張的記錄2. DECLARE grademax int, grademin int, gradesum intSELECT grademax=max(分?jǐn)?shù)),grademin=min(分?jǐn)?shù)),gradesum=sum(分?jǐn)?shù))FROM gradeSELECT gradem

26、ax, grademin, gradesum3. DECLARE row intSET row=(SELECT COUNT(*)FROM grade)SELECT row4. DECLARE intCId int, intErrorCode intINSERT INTO curriculum(課程編號,課程名稱,學(xué)分)VALUES(0006,VB程序設(shè)計,2)SELECT intCId= identity,intErrorCode= errorSELECT intCId, intErrorCode第一次顯示：NULL0 第二次顯示：NULL0curriculum表中數(shù)據(jù)的變化：第一次：0006

27、 VB程序設(shè)計 2第二次：0006 VB程序設(shè)計 25. 在studentsdb數(shù)據(jù)庫的student info表中，以“性別”為分組條件，分別統(tǒng)計男生和女生人數(shù)。SELECT COUNT(性別) FROM student_infoGROUP BY 性別6. 在grade表中，使用適當(dāng)函數(shù)找出“高等數(shù)學(xué)”課程的最高分、最低分和平均分。SELECT MAX(分?jǐn)?shù))AS 最高分,MIN(分?jǐn)?shù))AS 最低分,AVG(分?jǐn)?shù))AS 平均分 FROM grade WHERE 課程編號=(SELECT 課程編號 FROM curriculum WHERE 課程名稱=高等數(shù)學(xué))7. 定義一個datetime型

28、局部變量student，以存儲當(dāng)前日期。計算student info表中的學(xué)生的年齡，并顯示學(xué)生的姓名、年齡。在以下代碼的劃線部分填入適當(dāng)內(nèi)容，以實現(xiàn)上述功能。DECLARE student datetime SET student=getdate() SELECT 姓名,year(student)-year(出生日期) AS 年齡FROM student_info8.運行代碼，寫出運行結(jié)果。運行結(jié)果為： 8 233 2259. 在局部變量stu id中存儲了學(xué)號值。編寫代碼查詢學(xué)號為0001的學(xué)生的各科平均成績，如果平均分=60則顯示“你的成績及格了，恭喜你！”，否則顯示“你的成績不及格”。

29、 IF (SELECT AVG(分?jǐn)?shù)) FROM grade where 學(xué)號=0001)=90 THEN AWHEN 分?jǐn)?shù)=80 AND 分?jǐn)?shù)=70 AND 分?jǐn)?shù)=60 AND 分?jǐn)?shù)70 THEN D ELSE EENDFROM grade12. 計算grade表的分?jǐn)?shù)列的平均值。如果小于80，則分?jǐn)?shù)增加其值的5%；如果分?jǐn)?shù)的最高值超過95，則終止該操作。在以下代碼劃線處填入適當(dāng)?shù)膬?nèi)容以完成上述功能。WHILE (SELECT AVG(分?jǐn)?shù)) FROM grade)95 BREAKELSE BREAKEND13. 編寫代碼計算并顯示 n = 1+2+3+.+20。DECLARE n int,

30、a intSELECT n=1,a=1WHILE a=20 BEGIN SET n=n+a SET a=a+1ENDPRINT n = 1+2+3+.+20PRINT n14. 編寫代碼計算并顯示1100之間的所有完全平方數(shù)。例如，81 = 92，則稱81為完全平方數(shù)。DECLARE a intSELECT a=1WHILE a=a*a PRINT CONVERT(char(3),a*a)+是完全平方數(shù) SET a=a+1END15. 計算1100以內(nèi)的所有的素數(shù)。DECLARE n int,i intSET n=2PRINT 素數(shù)是：WHILE n=100BEGIN SET i=2 WHIL

31、E iSQRT(n)PRINT nSET n=n+1END16. 在studentsdb數(shù)據(jù)庫中，使用游標(biāo)查詢數(shù)據(jù)。（1）DECLARE stu_cursor CURSORGLOBAL SCROLL DYNAMICFORSELECT * FROM student_infoWHERE 性別=男（2）OPEN stu_cursor（3）FETCH NEXT FROM stu_cursor WHILE fetch_status=0BEGIN FETCH NEXT FROM stu_cursorEND（4）CLOSE stu_cursor17. 使用游標(biāo)修改數(shù)據(jù)。(1)OPEN stu_cursor(

32、2)UPDATE student_infoSET 出生日期=Dateadd(Year,1,出生日期)WHERE 姓名 Like 馬% AND 性別=男(3)CLOSE stu_cursor18. 聲明游標(biāo)變量stu c，使之關(guān)聯(lián)stu cursor游標(biāo)，利用stu c查詢年齡在69月份出生的學(xué)生信息。SELECT * FROM student_infoWHERE DATEPART(MONTH,出生日期)=619. 使用系統(tǒng)存儲過程sp cursor list顯示在當(dāng)前作用域內(nèi)的游標(biāo)及其屬性。OPEN stu_cursorDECLARE report CURSOREXEC sp_cursor_l

33、ist cursor_return=report OUTPUT,cursor_scope=2CLOSE stu_cursorFETCH NEXT FROM report實驗8 存儲過程和觸發(fā)器1執(zhí)行代碼之后，studentsdb數(shù)據(jù)庫的存儲過程中出現(xiàn)lletters_print，表明命令已經(jīng)成功執(zhí)行。EXECUTE letter_print2. EXECUTE stu_info 馬東修改為CREATE PROCEDURE stu_info name varchar(40)=劉衛(wèi)平ASSELECT a.學(xué)號,姓名,課程編號,分?jǐn)?shù)FROM student_info a INNER JOIN grade