SQL實(shí)驗(yàn)與練習(xí)題參考答案

1、數(shù)據(jù)庫(kù)原理與應(yīng)用（sql server）練習(xí)題 - 13 - 實(shí)驗(yàn)1 數(shù)據(jù)庫(kù)操作1創(chuàng)建數(shù)據(jù)庫(kù)：操作1.1：創(chuàng)建一個(gè)test數(shù)據(jù)庫(kù)，其主數(shù)據(jù)文件邏輯名test_data，物理文件名test_data.mdf，初始大小10mb，最大尺寸為無限大，增長(zhǎng)速度1mb；數(shù)據(jù)庫(kù)日志文件邏輯名稱為test_log，物理文件名為test_log.ldf，初始大小為1mb，最大尺寸為5mb，增長(zhǎng)速度為10%。參考答案：create database teston primary(name = test_data,filename = d:testtest_data.mdf,size = 5mb,maxsize =

2、 unlimited,filegrowth = 1mb)log on(name = test_log,filename = d:testtest_log.ldf,size = 1mb,maxsize = 5mb,filegrowth = 10%)go2查看數(shù)據(jù)庫(kù)屬性：操作1.2：使用t-sql語(yǔ)句查看數(shù)據(jù)庫(kù)test屬性參考答案：exec sp_helpdb test3刪除數(shù)據(jù)庫(kù)：操作1.3：使用t-sql語(yǔ)句刪除數(shù)據(jù)庫(kù)test參考答案：drop database test實(shí)驗(yàn)2 表操作1創(chuàng)建表：操作2.1：創(chuàng)建學(xué)生表：表名：student說明：學(xué)生基本信息表屬性列數(shù)據(jù)類型長(zhǎng)度空值列約束說明st

3、_idnvarchar9not nullpk學(xué)生學(xué)號(hào)st_nmnvarchar8not null學(xué)生姓名st_sexnvarchar2null學(xué)生性別st_birthdatetimenull出生日期st_scoreintnull入學(xué)成績(jī)st_datedatetimenull入學(xué)日期st_fromnchar20null學(xué)生來源st_dpidnvarchar2null所在系編號(hào)st_mnttinyintnull學(xué)生職務(wù)參考答案：use testgocreate table student(st_id nvarchar(9) primary key not null ,st_nm nvarchar(

4、8) not null ,st_sex nvarchar(2) null ,st_birth datetime null ,st_score int null ,st_date datetime null ,st_ from nvarchar(20)null ,st_dpid nvarchar(2) null ,st_ mnt tinyint null)go操作2.2：創(chuàng)建課程信息表：表名：couse說明：課程信息表屬性列數(shù)據(jù)類型長(zhǎng)度空值列約束說明cs_idnvarchar4not nullpk課程編號(hào)cs_nmnvarchar20not null課程名稱cs_tmintnull課程學(xué)時(shí)cs_

5、scintnull課程學(xué)分參考答案：use testgocreate table couse(cs_id nvarchar(4) primary key not null ,cs_nm nvarchar(20) not null ,cs_tm int null ,cs_sc int null)go操作2.3：創(chuàng)建選課表：表名：slt_couse說明：選課表屬性列數(shù)據(jù)類型長(zhǎng)度空值列約束說明cs_idnvarchar4not nullfk課程編號(hào)st_idnvarchar9not nullfk學(xué)生編號(hào)scoreintnull課程成績(jī)sltdatedatetimenull選課日期參考答案：use t

6、estgocreate table couse(cs_id nvarchar(4) not null ,st_id nvarchar(9) not null ,score int null ,sltdate datetime null)go操作2.4：創(chuàng)建院系信息表：表名：dept說明：院系信息表屬性列數(shù)據(jù)類型長(zhǎng)度空值列約束說明dp_idnvarchar2not null系編號(hào)dp_nmnvarchar20not null院系名稱dp_drtnvarchar8null院系主任dt_telnvarchar12null聯(lián)系電話參考答案：use testgocreate table dept(dp_

7、id nvarchar(2) not null ,dp_nm nvarchar(20)not null ,dp_drtnvarchar(8) null ,dp_telnvarchar(12)null)go2修改表結(jié)構(gòu)：（1）向表中添加列：操作2.5：為“dept”表添加“dp_count”列（數(shù)據(jù)類型為nvarchar，長(zhǎng)度為3，允許為空）參考答案：alter table dept add dp_count nvarchar(3) null（2）修改列數(shù)據(jù)類型：操作2.6：修改“dept”表的“dp_count”列數(shù)據(jù)類型為int參考答案：alter table dept alter colu

8、mn dp_count int null（3）刪除表中指定列：操作2.7：刪除“dept”表的“dp_count”列參考答案：alter table dept drop column dp_count3刪除表操作2.8：刪除“dept”表參考答案：drop table student4向表中輸入數(shù)據(jù)記錄操作2.9：分別向“student”表、“couse”表、“slt_couse”表、“dept”表中輸入數(shù)據(jù)記錄實(shí)驗(yàn)3 數(shù)據(jù)完整性1空值約束( null )操作3.1：將student表中的st_sex列屬性更改為not null參考答案：alter table student alter co

9、lume st_nm nvarchar(8) not null2默認(rèn)值約束( default )操作3.2：將student表中的st_from列默認(rèn)值設(shè)置為“陜西省”參考答案：alter table student add default 陜西省 for st_from3默認(rèn)值對(duì)象操作3.3：創(chuàng)建默認(rèn)值對(duì)象df_today為當(dāng)前日期，并將其綁定到slt_couse表中的sltdate列，然后取消綁定，最后刪除默認(rèn)值對(duì)象df_today。參考答案：create default df_today as getdate( )goexec sp_bindefault df_today, slt_co

10、use.sltdategoexec sp_unbindefault slt_couse.sltdategodrop default df_todaygo4檢查約束( check )操作3.4：將slt_couse表中的score列的檢查約束設(shè)置為=0且=0 and score= 2008操作6.4：在查詢student表080808班學(xué)生的學(xué)號(hào)、姓名、性別和入學(xué)成績(jī)select st_id, st_nm, st_sex, st_score from studentwhere left(st_id,6)=0808082使用邏輯表達(dá)式表示查詢條件操作6.5：查詢student表中非11系的學(xué)生信息

11、select * from student where not (st_dpid = 11)操作6.6：查詢選修了1002號(hào)課程且成績(jī)?cè)?0以下的學(xué)生學(xué)號(hào)select st_id from slt_cousewhere (cs_id=1002) and (score75操作8.11：查詢選修了2門以上課程的學(xué)生學(xué)號(hào)select st_id from slt_cousegroup by st_id having count(*)2操作8.12：明細(xì)匯總年齡20的學(xué)生，并匯總學(xué)生數(shù)量、平均年齡select st_nm,datepart(yy,getdate( )-datepart(yy,st_bi

12、rth) as 年齡from studentwhere datepart(yy,getdate()-datepart(yy,st_birth)20compute count(st_nm),avg(datepart(yy,getdate()-datepart(yy,st_birth)操作8.13：按班級(jí)明細(xì)匯總成績(jī)85分的學(xué)生，匯總學(xué)生數(shù)、均分select st_nm, left(st_id,6) as 班級(jí), st_scorefrom studentwhere st_score85order by 班級(jí)compute count(st_nm), avg(st_score) by 班級(jí)實(shí)驗(yàn)9 數(shù)

13、據(jù)查詢（5）連接查詢操作9.1：用sql server形式連接查詢學(xué)生學(xué)號(hào)、姓名、性別及其所選課程編號(hào)select a.st_id, st_nm, st_sex, cs_idfrom student a, slt_couse bwhere a.st_id = b.st_idorder by a.st_id操作9.2：用ansi形式連接查詢學(xué)生學(xué)號(hào)、姓名、性別及其所選課程編號(hào)select a.st_id, st_nm, st_sex, cs_idfrom student a inner join slt_couse bon a.st_id = b.st_idorder by a.st_id操作9

14、.3：用sql server形式連接查詢學(xué)生學(xué)號(hào)、姓名及其所選課程名稱及成績(jī)select a.st_id, st_nm, cs_nm, scorefrom student a, slt_couse b, couse cwhere a.st_id = b.st_id and b.cs_id = c.cs_idorder by a.st_id操作9.4：用ansi形式連接查詢學(xué)生學(xué)號(hào)、姓名及其所選課程名稱及成績(jī)select a.st_id, st_nm, cs_nm, scorefrom slt_couse a inner join student b on a.st_id = b.st_idin

15、ner join couse c on a.cs_id = c.cs_idorder by b.st_id操作9.5：查詢選修了1002課程的學(xué)生學(xué)號(hào)、姓名及1001課程成績(jī)select a.st_id, st_nm, scorefrom student a，slt_couse bwhere a.st_id = b.st_id and b.cs_id = 1002order by b.st_id操作9.6：查詢選修了“數(shù)據(jù)結(jié)構(gòu)”課程的學(xué)生學(xué)號(hào)、姓名及課程成績(jī)select a.st_id, st_nm, scorefrom student a, slt_couse b, couse cwhere

16、 a.st_id=b.st_id and b.cs_id=c.cs_id and c.cs_nm=數(shù)據(jù)結(jié)構(gòu)order by a.st_id操作9.7：用左外連接查詢沒有選修任何課程的學(xué)生學(xué)號(hào)、姓名select a.st_id, st_nm, scorefrom student a left outer join slt_couse b on a.st_id = b.st_idwhere b.cs_id is nullorder by b.st_id操作9.8：用右外連接查詢選修各個(gè)課程的學(xué)生學(xué)號(hào)select b.cs_id, a.st_idfrom slt_couse a right oute

17、r join couse b on a.cs_id = b.cs_idorder by b.cs_id實(shí)驗(yàn)10 數(shù)據(jù)查詢（6）子查詢操作10.1：用子查詢對(duì)各班人數(shù)進(jìn)行查詢（新增列）select distinct left(a.st_id,6) as 班級(jí), 人數(shù) = ( select count(st_id) from student b where left(a.st_id,6) = left(b.st_id,6)from student a order by left(a.st_id,6) asc操作10.2：用子查詢對(duì)各課程的選課人數(shù)進(jìn)行查詢（新增列）select distinct a

18、.cs_id, 人數(shù) = ( select count(st_id) from slt_couse b where a.cs_id = b.cs_id)from slt_couse a order by a.cs_id asc操作10.3：查詢選修了1002課程成績(jī)不及格的學(xué)生的學(xué)號(hào)、姓名和性別，并按姓名升序排序通過子查詢實(shí)現(xiàn)：使用in關(guān)鍵字select st_id, st_nm, st_sex from studentwhere st_id in( select st_id from slt_couse where cs_id=1002 and score 60)order by st_nm

19、通過子查詢實(shí)現(xiàn)：使用比較運(yùn)算符select st_id, st_nm, st_sexfrom student awhere ( select score from slt_couse b where a.st_id = b.st_id and cs_id = 1002 ) any(select score from slt_couse where cs_id = 1002 and left(st_id,6)=070511)and left(st_id,6) 070511 and cs_id = 1002操作10.6：查詢其它班比070511班任一學(xué)生的1002號(hào)課程成績(jī)高的學(xué)生信息（any/a

20、ll）select * from slt_cousewhere score all(select score from slt_couse where cs_id = 1002 and left(st_id,6)=070511)and left(st_id,6) 070511 and cs_id = 1002操作10.7：查詢大于等于60分且且比1003課程平均成績(jī)低的學(xué)生課程信息（betweenand）select * from slt_couse awhere a.score between 60 and ( select avg(b.score) from slt_couse bwhere b.cs_id=1003 )操作10.8：查詢系主任為“趙虎”的系的所有學(xué)生信息通過子查詢實(shí)現(xiàn)：in運(yùn)算符select * from student where exists( select * from dept where st_dpid = dp_id and dp_drt=趙虎 )通過子查詢實(shí)現(xiàn)：=運(yùn)算符select * from student where st_dpid =( select dp_id from dept where dp_drt=趙虎 )實(shí)驗(yàn)11 數(shù)據(jù)查詢（7）數(shù)據(jù)更新與子查詢操作11.1：將070511班所有學(xué)生信息插入到表student0