数据库实验四

实验项目名称：T-SQL程序设计实验学时：4同组学生姓名：实验地点：b513实验日期：2012.11.272012.12.04实验成绩：批改教师：批改时间：一、实验目的和要求1、掌握T－SQL中运算符和表达式的使用；2、通过对Select的使用，掌握Select语句的结构及其应用；3、掌握T－SQL中几个常用流程控制语句的使用；4、掌握系统内置函数的概念及其应用；5、通过定义和使用用户自定义函数，掌握自定义函数的概念及其应用。二、实验设备、环境设备：奔腾Ⅳ或奔腾Ⅳ以上计算机；环境：WINDOWS2000SERVER或WINDOWS2003SERVER、SQLServer2005中文版。三、实验步骤1、根据题目要求熟悉SQLServer2005的各种管理工具；2、分析题意，重点分析题目要求并给出解决方法；3、按题目要求完成实际操作任务，并将相关文档资料保存在以自己学号命名的文件夹中；4、提交完成的实验结果。四、实验内容一、SQL查询（*使用SQLServer样例数据库pubs完成）1、简单查询（1）查询所有作者的姓名和作者号信息，并在每个作者的作者号前面显示字符串“身份证号：”表明显示信息是身份证信息；（authors表）（2）改变显示列名。显示所有作者的姓名信息和作者号信息，要求用“名”和“姓”来区别fname和lname，“作者编号”来区分作者号；（authors表）（3）查询所有书在价格提高10%后的价格和书名信息；（titles表）（4）查询所有书的书号和税后价格。（titles表，royalty列表示税率）；（5）查询所有作者的姓和“名的第一个字符”以及作者号；（authors表，SUBSTRING函数）（6）查询邮政编码大于9000的作者姓名和电话信息；（authors表）（7）查询出版日期在1/1/1991到12/31/1991之间的书名（书名限制为38个字符）和出版日期；（titles表，SUBSTRING函数）（8）查询书的类型是mod_cook或trad_cook的书名和它的类型；（titles表）（9）查询店名中包含Book的店的信息；(stores表)；（10）查询书名以T开头或者出版号为0877，且价格大于16美元的书的信息；（titles表）金陵科技学院实验报告1（11）查询所有作者的所在城市和州名，要求没有重复信息；（authors表）（12）按照类型的升序和价格的降序显示书的信息；（titles表）2、生成汇总数据（1）计算多少种书已被定价；（titles表）（2）计算每本书的书号及它的售书总量；（sales表）（3）求销售量大于30的书号及销售数量；（sales表）（4）显示在1994年1月1日到1994年10月31日间，每本书的销售总额；（sales表，titles表）3、连接查询（1）求每本杂志上刊登的文章；（titles,publishers表）（2）求某书店销售某书的数量；（titles,stores,sales表）（3）查询所有合著的书及其作者。（4）显示所有已销售的书名。4、子查询（1）查询有销售记录的所有书信息，包括书的编号、书名、类型和价格；（2）求已销售的书的信息；二、函数1、自定义一个名为Sage_func函数，按出生年月计算年龄。然后从Student表中检索出含有年龄的学生信息。2、定义一个名为grade_func的自定义函数，将成绩从百分制转化为五级记分制。将该用户定义函数用在查询每个学生的成绩中，给出五级记分制的成绩。三、流程控制1、Student表中若存在学号为“19920101”的学生，则显示已存在的信息，否则插入该学生的记录。然后从student表中删除学号为“19920101”的学生记录，重新执行该程序，观察与上次有何不同。2、使用While语句求1到100之间的累加和，输出结果。五、问题解答及实验结果1、简单查询（1）查询所有作者的姓名和作者号信息，并在每个作者的作者号前面显示字符串“身份证号：”表明显示信息是身份证信息；（authors表）1.select'身份证号：'+au_id,au_fname,au_lnamefromauthors;金陵科技学院实验报告2（2）改变显示列名。显示所有作者的姓名信息和作者号信息，要求用“名”和“姓”来区别fname和lname，“作者编号”来区分作者号；（authors表）2.selectau_id作者编号,au_fname姓,au_lname名fromauthors;（3）查询所有书在价格提高10%后的价格和书名信息；（titles表）3.selecttitle,price*1.1fromtitles;金陵科技学院实验报告3（4）查询所有书的书号和税后价格。（titles表，royalty列表示税率）；selecttitle_id,price*(1+royalty)pricefromtitles;（5）查询所有作者的姓和“名的第一个字符”以及作者号；（authors表，SUBSTRING函数）selectau_id,au_lname,SUBSTRING(au_fname,1,1)fromauthors;（6）查询邮政编码大于9000的作者姓名和电话信息；（authors表）selectau_fname,au_lname,phonefromauthorswherezip9000;（7）查询出版日期在1/1/1991到12/31/1991之间的书名（书名限制为38个字符）和出版日期；（titles表，SUBSTRING函数）selectSUBSTRING(title,1,38),pubdatefromtitleswherepubdate'1/1/1991'andpubdate'12/31/1991';金陵科技学院实验报告4（8）查询书的类型是mod_cook或trad_cook的书名和它的类型；（titles表）selecttitle,typefromtitleswheretype='mod_cook'ortype='trad_cook';（9）查询店名中包含Book的店的信息；(stores表)；4.select*fromstoreswherestor_namelike'%Book%';金陵科技学院实验报告5（10）查询书名以T开头或者出版号为0877，且价格大于16美元的书的信息；（titles表）select*fromtitleswhereSUBSTRING(title,1,1)='T'orpub_id=0877andprice16;（11）查询所有作者的所在城市和州名，要求没有重复信息；（authors表）selectDISTINCTcity,statefromauthors;（12）按照类型的升序和价格的降序显示书的信息；（titles表）select*fromtitlesorderbytypeasc,pricedesc;金陵科技学院实验报告62、生成汇总数据（1）计算多少种书已被定价；（titles表）selectcount(title_id)fromtitleswherepriceisnotnull;（2）计算每本书的书号及它的售书总量；（sales表）selecttitle_id,sum(qty)qtyfromsalesgroupbytitle_id;金陵科技学院实验报告7（3）求销售量大于30的书号及销售数量；（sales表）selecttitle_id,sum(qty)qtyfromsalesgroupbytitle_idhaving(sum(qty)30);（4）显示在1994年1月1日到1994年10月31日间，每本书的销售总额；（sales表，titles表）selecttitles.title_id,sum(sales.qty)*titles.price销售总额fromsales,titles金陵科技学院实验报告8wheretitles.pubdatebetween'1/1/1991'and'12/31/1991'groupbysales.title_id,titles.price,titles.title_idhaving(sales.title_id=titles.title_id)3、连接查询（1）求每本杂志上刊登的文章；（titles,publishers表）selecttitles.title,publishers.pub_namefromtitles,publisherswheretitles.pub_id=publishers.pub_id金陵科技学院实验报告9（2）求某书店销售某书的数量；（titles,stores,sales表）selecta.title,b.stor_name,c.qtyfromtitlesa,storesb,salescwherea.title_id=c.title_idandb.stor_id=c.stor_id（3）查询所有合著的书及其作者。selectdistinct(a.title_id),b.au_id,a.au_id金陵科技学院实验报告10fromtitleauthora,titleauthorbwherea.title_id=b.title_id（4）显示所有已销售的书名。selecttitle,sum(qty)qtyfromsales,titleswheresales.title_id=titles.title_idgroupbytitle4、子查询金陵科技学院实验报告11（1）查询有销售记录的所有书信息，包括书的编号、书名、类型和价格；selectsales.title_id,titles.title,titles.[type],titles.pricefromtitles,saleswheresales.title_id=titles.title_id（2）求已销售的书的信息；金陵科技学院实验报告12二．函数1、自定义一个名为Sage_func函数，按出生年月计算年龄。然后从Student表中检索出含有年龄的学生信息。createfunctionSage_func(@vardatedatetime,@curdatedatetime)returnstinyintasbeginreturndatediff(yy,@vardate,@curdate)endselectSNOas学号,SNameas姓名,dbo.Sage_func(Birthday,getdate())as年龄fromStudent金陵科技学院实验报告132、定义一个名为grade_func的自定义函数，将成绩从百分制转化为五级记分制。将该用户定义函数用在查询每个学生的成绩中，给出五级记分制的成绩。createfunctiongrade_func(@Scorceint)returnstinyintasbeginreturn@Scorce/20endselectSNameas姓名,dbo.grade_func(Scorce)as五级分制fromGrade,StudentwhereGrade.SNO=Student.SNO金陵科技学院实验报告14三、流程控制1.ifexists(select*fromStudentwhereSNO='19920101')beginselect*fromStudentwhereSNO='19920101'endelsebegininsertintoStudentvalues('19920101','王军','男','CS01','下关#','1976-12-2100:00:00',1.82,10)enddeletefromStudentwhereSNO='19920101'金陵科技学院实验报告152、使用While语句求1到100之间的累加和，输出结果。流程控制语句为：Declare@sumint,@startintset@start=1set@sum=0beginwhile(@start101)beginset@sum