广东轻工职业技术学院 试卷习题 上机考试题（B）_数据库应用技术

上机考试题(B) (I)在选择价格位于$11和S20之间的书名时，使用LOWER函数、UPPER函数且把UPPER 函数嵌套在LOWER函数内。另外，该程序还使用SUBSTRING函数从字符串中返回指定的字符 串。 USEpubs GO SELECTLOWER(SUBSTRING(title,1,20))ASLower, UPPER(SUBSTRING(title,1,20))ASUpper, LOWER(UPPER(SUBSTRING(title,1,20)))AsLowerUpper FROMtitles WHEREpricebetween11.00and20.00 运行结果为： LowerUpperLowerUpper thebusyexecutive'sTHEBUSYEXECUTIVE'Sthebusyexecutive's cookingwithcomputeCOOKINGWITHCOMPUTEcookingwithcompute straighttalkaboutSTRAIGHTTALKABOUTstraighttalkabout (2)USEpubs GO IFEXISTS(SELECTnameFROMsysobjectsWHEREname='Oakland_authors'ANDtype='P') DROPPROCEDUREOakland authors GO 创建一个存储过程，该存储过程包含所有来自加利福尼亚州奥克兰市的作者的信息。 USEpubs GO CREATEPROCEDUREOakland_authors AS SELECTau_fname,au_lname,address,city,zip FROMpubs..authors

上机考试题(B) ⑴在选择价格位于$11 和$20 之间的书名时，使用 LOWER 函数、UPPER 函数且把 UPPER 函数嵌套在 LOWER 函数内。另外，该程序还使用 SUBSTRING 函数从字符串中返回指定的字符 串。 USEpubs GO SELECTLOWER(SUBSTRING(title,1,20))ASLower, UPPER(SUBSTRING(title,1,20))ASUpper, LOWER(UPPER(SUBSTRING(title,1,20)))AsLowerUpper FROMtitles WHEREpricebetween11.00and20.00 运行结果为： LowerUpperLowerUpper ------------------------------------------------------------ thebusyexecutive'sTHEBUSYEXECUTIVE'Sthebusyexecutive's cookingwithcomputeCOOKINGWITHCOMPUTEcookingwithcompute straighttalkaboutSTRAIGHTTALKABOUTstraighttalkabout ⑵USEpubs GO IFEXISTS(SELECTnameFROMsysobjectsWHEREname='Oakland_authors'ANDtype='P') DROPPROCEDUREOakland_authors GO 创建一个存储过程，该存储过程包含所有来自加利福尼亚州奥克兰市的作者的信息。 USEpubs GO CREATEPROCEDUREOakland_authors AS SELECTau_fname,au_lname,address,city,zip FROMpubs..authors

WHEREcity='Oakland' andstate='CA' ORDERBYau_Iname,au_fname GO 查看该存储过程的源代码 SELECTo.id,c.text FROMsysobjectsoINNERJOINsyscommentscONo.id=c.id WHEREo.type='P'ando.name='Oakland authors' 将执行该存储过程的权限授予public角色， GRANTEXECUTEONOakland authorsTOpublic GO 下面对该存储过程进行重新定义，使其能够显示所有来自加利福尼亚州的作者，而不考 虑来自其它地区的作者。 ALTERPROCEDUREOakland_authors WITHENCRYPTION AS SELECTau_fname,au_lname,address,city,zip FROMpubs..authors WHEREstate='CA' ORDERBYau lname,au fname GO 查看该存储过程的具体源代码 SELECTo.id,c.text FROMsysobjectsoINNERJOINsyscommentscONo.id=c.id WHEREo.type='P'ando.name='Oakland_authors' GO (3)创建了一个触发器，当插入或更新雇员工作级别(job1v1s)时，该触发器检查指定雇 员的工作级别（由此决定薪水）是否处于为该工作定义的范围内。若要获得适当的范围，必须 引用jobs表。其程序清单如下： CREATETRIGGERemployee_insupd

WHEREcity='Oakland' andstate='CA' ORDERBYau_lname,au_fname GO 查看该存储过程的源代码 SELECTo.id,c.text FROMsysobjectsoINNERJOINsyscommentscONo.id=c.id WHEREo.type='P'ando.name='Oakland_authors' 将执行该存储过程的权限授予 public 角色. GRANTEXECUTEONOakland_authorsTOpublic GO 下面对该存储过程进行重新定义，使其能够显示所有来自加利福尼亚州的作者，而不考 虑来自其它地区的作者。 ALTERPROCEDUREOakland_authors WITHENCRYPTION AS SELECTau_fname,au_lname,address,city,zip FROMpubs..authors WHEREstate='CA' ORDERBYau_lname,au_fname GO 查看该存储过程的具体源代码 SELECTo.id,c.text FROMsysobjectsoINNERJOINsyscommentscONo.id=c.id WHEREo.type='P'ando.name='Oakland_authors' GO ⑶创建了一个触发器，当插入或更新雇员工作级别(job_lvls)时，该触发器检查指定雇 员的工作级别(由此决定薪水)是否处于为该工作定义的范围内。若要获得适当的范围，必须 引用 jobs 表。其程序清单如下： CREATETRIGGERemployee_insupd

ONemployee FORINSERT,UPDATE AS /*从jobs表中获取工作级别*/ DECLARE@min_1vltinyint, @max_lvltinyint, @emp lvltinyint, @job idsmallint SELECT@min_1vl=min_lvl, @max lvl=max lvl, @emp_lvl=i.job_lvl, @job id=i.job id FROMemployeeeINNERTOINinsertediONe.emp id=i.emp id JOINjobsjONi.job id=i.job id IF(@job_id=1)and(@emp_lvl<>10) BEGIN RAISERROR('Jobidlexpectsthedefaultlevelof10.',16,1) ROLLBACKTRANSACTION END ELSE IFNOT(@emp_lvlBETWEEN@min_1vlAND@max_1vl) BEGIN RAISERROR('Thelevelfor job id:%dshouldbebetween%dand%d.', 16,1,@job id,@min_lvl,@max lvl) ROLLBACKTRANSACTION END (4)首先创建了两个表：一个employeeData表和一个auditEmployeeData表。人力资源 部的成员可以修改employeeData表，该表包含敏感的雇员薪水信息。如果更改了雇员的社 会保险号码(SSN)、年薪或银行帐户，则生成审核记录并插入到auditEmployeeData审核表 中

ONemployee FORINSERT,UPDATE AS /*从 jobs 表中获取工作级别*/ DECLARE@min_lvltinyint, @max_lvltinyint, @emp_lvltinyint, @job_idsmallint SELECT@min_lvl=min_lvl, @max_lvl=max_lvl, @emp_lvl=i.job_lvl, @job_id=i.job_id FROMemployeeeINNERJOINinsertediONe.emp_id=i.emp_id JOINjobsjONj.job_id=i.job_id IF(@job_id=1)and(@emp_lvl<>10) BEGIN RAISERROR('Jobid1expectsthedefaultlevelof10.',16,1) ROLLBACKTRANSACTION END ELSE IFNOT(@emp_lvlBETWEEN@min_lvlAND@max_lvl) BEGIN RAISERROR('Thelevelforjob_id:%dshouldbebetween%dand%d.', 16,1,@job_id,@min_lvl,@max_lvl) ROLLBACKTRANSACTION END ⑷首先创建了两个表：一个 employeeData 表和一个 auditEmployeeData 表。人力资源 部的成员可以修改 employeeData 表，该表包含敏感的雇员薪水信息。如果更改了雇员的社 会保险号码(SSN)、年薪或银行帐户，则生成审核记录并插入到 auditEmployeeData 审核表 中

其程序清单如下： CREATETABLEemployeeData( emp idintNOTNULL emp bankAccountNumberchar (10)NOTNULL, emp salaryintNOTNULL, emp_SSNchar(11)NOTNULL, emp lnamenchar (32)NOTNULL emp fnamenchar (32)NOTNULL, emp_managerintNOTNULL go CREATETABLEauditEmployeeData( audit_log_iduniqueidentifierDEFAULTNEWID(), audit_log_typechar(3)NOTNULL, audit_emp_idintNOTNULL, audit emp bankAccountNumberchar(10)NULL, audit_emp_salaryintNULL, audit_emp_SSNchar(11)NULL audit usersysnameDEFAULTSUSER SNAME(. audit changeddatetimeDEFAULTGETDATE() GO CREATETRIGGERupdEmployeeData ONemployeeData FORupdateAS IF(COLUMNS UPDATED()&14)>0 /*表示当第2、3或4列的数据被修改时，将开始执行下列语句。*/ BEGIN INSERTINTOauditEmployeeData (audit_log_type

其程序清单如下： CREATETABLEemployeeData( emp_idintNOTNULL, emp_bankAccountNumberchar(10)NOTNULL, emp_salaryintNOTNULL, emp_SSNchar(11)NOTNULL, emp_lnamenchar(32)NOTNULL, emp_fnamenchar(32)NOTNULL, emp_managerintNOTNULL ) go CREATETABLEauditEmployeeData( audit_log_iduniqueidentifierDEFAULTNEWID(), audit_log_typechar(3)NOTNULL, audit_emp_idintNOTNULL, audit_emp_bankAccountNumberchar(10)NULL, audit_emp_salaryintNULL, audit_emp_SSNchar(11)NULL, audit_usersysnameDEFAULTSUSER_SNAME(), audit_changeddatetimeDEFAULTGETDATE() ) GO CREATETRIGGERupdEmployeeData ONemployeeData FORupdateAS IF(COLUMNS_UPDATED()&14)>0 /*表示当第 2、3 或 4 列的数据被修改时，将开始执行下列语句。*/ BEGIN INSERTINTOauditEmployeeData (audit_log_type

audit_emp_id, audit_emp_bankAccountNumber, audit_emp_salary, audit emp SSN) SELECT'OLD', del.emp_id, del.emp bankAccountNumber, del.emp_salary, del.emp_SSN FROMdeleteddel INSERTINTOauditEmployeeData (audit_log_type, audit_emp_id, audit emp bankAccountNumber, audit_emp_salary, audit_emp_SSN) SELECT'NEW', ins.emp_id, ins.emp_bankAccountNumber, ins.emp salary, ins.emp_SSN FROMinsertedins END GO (⑤)创建了一个修改触发器，该触发器防止用户修改表sales的销售日期。 其程序清单如下： createtriggertri sales upd onsales forupdate as

audit_emp_id, audit_emp_bankAccountNumber, audit_emp_salary, audit_emp_SSN) SELECT'OLD', del.emp_id, del.emp_bankAccountNumber, del.emp_salary, del.emp_SSN FROMdeleteddel INSERTINTOauditEmployeeData (audit_log_type, audit_emp_id, audit_emp_bankAccountNumber, audit_emp_salary, audit_emp_SSN) SELECT'NEW', ins.emp_id, ins.emp_bankAccountNumber, ins.emp_salary, ins.emp_SSN FROMinsertedins END GO ⑸创建了一个修改触发器，该触发器防止用户修改表 sales 的销售日期。 其程序清单如下： createtriggertri_sales_upd onsales forupdate as

ifupdate(salesdate) begin raiserror('youcannotmodifythiscolumn') rollbacktransaction end go (6(I)使用LTRIM函数删除字符变量中的起始空格。 DECLARE@string_to_trimvarchar(60) SET@string_to_trim='Fivespacesareatthebeginningof this string.' SELECT'Hereisthestringwithouttheleadingspaces:' LTRIM(@string_to_trim 运行结果为： Hereisthestringwithouttheleadingspaces:Fivespacesareatthebeginningofthisstr ing. (2)使用LEFT函数返回字符串abcdefg最左边的4个字符。 SELECTLEFT('abcdefg',4) 运行结果为： abcd (3)使用可选的start location参数从notes列的第五个字符开始查找"wonderful"。 USEpubs SELECTCHARINDEX (wonderful',notes,5)FROMtitles WHEREtitle_id='TC3218' 运行结果为： 46 (4)将指定的字符串的排列顺序颠倒。 selectreverse(123),reverse("abc")

ifupdate(salesdate) begin raiserror(‘youcannotmodifythiscolumn’) rollbacktransaction end go ⑹⑴使用 LTRIM 函数删除字符变量中的起始空格。 DECLARE@string_to_trimvarchar(60) SET@string_to_trim='Fivespacesareatthebeginningofthis string.' SELECT'Hereisthestringwithouttheleadingspaces:'+ LTRIM(@string_to_trim 运行结果为： ------------------------------------------------------------------------ Hereisthestringwithouttheleadingspaces:Fivespacesareatthebeginningofthisstr ing. ⑵使用 LEFT 函数返回字符串 abcdefg 最左边的 4 个字符。 SELECTLEFT('abcdefg',4) 运行结果为： ------- abcd ⑶使用可选的 start_location 参数从 notes 列的第五个字符开始查找"wonderful"。 USEpubs SELECTCHARINDEX('wonderful',notes，5)FROMtitles WHEREtitle_id='TC3218' 运行结果为： ----------- 46 ⑷将指定的字符串的排列顺序颠倒。 selectreverse(123),reverse("abc")

运行结果为： 321cba (⑤)在第一个字符串(abcdef)中删除从第二个位置（字符b)开始的三个字符，然后在删除 的起始位置插入第二个字符串，创建并返回一个字符串。 SELECTSTUFF (abcdef',2,3,'ijklmn') 运行结果为： ai jklmnef (71)计算所有商业类书籍的平均预付款和本年度迄今为止的销售额。 USEpubs SELECTAVG(advance),SUM(ytd sales) FROMtitles WHEREtype='business' (2)返回titles表中所有版税费用的标准偏差。 USEpubs SELECTSTDEV (royalty) FROMtitles (3)显示可以与选择列表中的其它聚合函数结合使用的COUNT()。 USEpubs SELECTCOUNT (*)AVG(price) FROMtitles WHEREadvance>S1000 (4)返回titles表中所有royalty值的方差。 USEpubs SELECTVAR(royalty) FROMtitles (8)createdatabasecompany onprimary (name=company data

运行结果为： ---------------- 321cba ⑸在第一个字符串(abcdef)中删除从第二个位置(字符 b)开始的三个字符，然后在删除 的起始位置插入第二个字符串，创建并返回一个字符串。 SELECTSTUFF('abcdef',2,3,'ijklmn') 运行结果为： --------- aijklmnef ⑺⑴计算所有商业类书籍的平均预付款和本年度迄今为止的销售额。 USEpubs SELECTAVG(advance),SUM(ytd_sales) FROMtitles WHEREtype='business' ⑵返回 titles 表中所有版税费用的标准偏差。 USEpubs SELECTSTDEV(royalty) FROMtitles ⑶显示可以与选择列表中的其它聚合函数结合使用的 COUNT(*)。 USEpubs SELECTCOUNT(*),AVG(price) FROMtitles WHEREadvance>$1000 ⑷返回 titles 表中所有 royalty 值的方差。 USEpubs SELECTVAR(royalty) FROMtitles ⑻createdatabasecompany onprimary (name=company_data

filename='d:\mssql7\data\company.mdf', size=10, maxsize=unlimited, filegrowth=10%) logon (name=company log, filename='d:\mssql7\data\company.Idf', size=1, maxsize=5, filegrowth=1) 输出为： TheCREATEDATABASEprocessisallocating10.00MBondisk'company_data'. TheCREATEDATABASEprocessisallocating1.00MBondisk'company_log'. (9)createdatabaseemployees onprimary (name=employeel, filename='d:\mssql7\data\employeel.mdf', size=10, maxsize=unlimited, filegrowth=10%), (name=employee2, filename=' d:\mssql7\datalemployee2.mdf', size=20, maxsize=100, filegrowth=1) logon (name=employeelogl, filename='d:\mssql7\data\employeelogl.ldf', size=10. maxsize=50

filename='d:\mssql7\data\company.mdf', size=10, maxsize=unlimited, filegrowth=10%) logon (name=company_log, filename='d:\mssql7\data\company.ldf', size=1, maxsize=5, filegrowth=1) 输出为： TheCREATEDATABASEprocessisallocating10.00MBondisk'company_data'. TheCREATEDATABASEprocessisallocating1.00MBondisk'company_log'. ⑼createdatabaseemployees onprimary (name=employee1, filename=’d:\mssql7\data\employee1.mdf’, size=10, maxsize=unlimited, filegrowth=10%), (name=employee2, filename=’d:\mssql7\data\employee2.mdf’, size=20, maxsize=100, filegrowth=1) logon (name=employeelog1, filename=’d:\mssql7\data\employeelog1.ldf’, size=10, maxsize=50

filegrowth=1), (name=employeelog2, filename='d:\mssql7\data\employeelog2.ldf', size=10, maxsize=50, filegrowth=1) 输出结果为： TheCREATEDATABASEprocessisallocating10.00MBondisk'employee1'. TheCREATEDATABASEprocessisallocating20.00MBondisk'employee2'. TheCREATEDATABASEprocessisallocating10.00MBondisk'employeelogl'. TheCREATEDATABASEprocessisallocating10.00MBondisk'employeelog2'. (0Alterdatabaseemployees Addfilegroupdatal Alterdatabaseemployees Addfile (name=employee3, filename='d:\mssql7\data\employee3.ndf', size=1, maxsize=50, filegrowth=1), (name=employee4, filename='d:\mssql7\datalemployee4.ndf', size=2, maxsize=50, filegrowth=10%) tofilegroupdatal Alterdatabaseemployees addlogfile (name=employeelog3, filename='d:\mssql7\datalemployeelog3.ldf

filegrowth=1), (name=employeelog2, filename=’d:\mssql7\data\employeelog2.ldf’, size=10, maxsize=50, filegrowth=1) 输出结果为： TheCREATEDATABASEprocessisallocating10.00MBondisk'employee1'. TheCREATEDATABASEprocessisallocating20.00MBondisk'employee2'. TheCREATEDATABASEprocessisallocating10.00MBondisk'employeelog1'. TheCREATEDATABASEprocessisallocating10.00MBondisk'employeelog2'. ⑽Alterdatabaseemployees Addfilegroupdata1 Alterdatabaseemployees Addfile (name=employee3, filename=’d:\mssql7\data\employee3.ndf’, size=1, maxsize=50, filegrowth=1), (name=employee4, filename=’d:\mssql7\data\employee4.ndf’, size=2, maxsize=50, filegrowth=10%) tofilegroupdata1 Alterdatabaseemployees addlogfile (name=employeelog3, filename=’d:\mssql7\data\employeelog3.ldf’

size=1, maxsize=50. filegrowth=1) 输出结果为： Extendingdatabaseby1.00MBondisk'employee3'. Extendingdatabaseby2.00MBondisk'employee4'. Extendingdatabaseby1.00MBondisk'employeelog3'. (IDcreatetablejbqk (numberchar(8)notnull, namechar(8)notnull, sexchar(2)notnull, birthdaydatetime, departmentchar(12) GO createtablecourse (c numberchar (4)notnull, c_namechar(20)notnull, perioddecimal(3,0), t_numberchar(4)notnull); GO createtableteacher (t numberchar(4)notnull, t_namechar(8), titlechar(10)); GO createtablesle_course (numberchar (8)notnull, c numberchar(4), scoredecimal(3,0));

size=1, maxsize=50, filegrowth=1) 输出结果为： Extendingdatabaseby1.00MBondisk'employee3'. Extendingdatabaseby2.00MBondisk'employee4'. Extendingdatabaseby1.00MBondisk'employeelog3'. ⑾createtablejbqk (numberchar(8)notnull, namechar(8)notnull, sexchar(2)notnull, birthdaydatetime, departmentchar(12) ); GO createtablecourse (c_numberchar(4)notnull, c_namechar(20)notnull, perioddecimal(3,0), t_numberchar(4)notnull); GO createtableteacher (t_numberchar(4)notnull, t_namechar(8), titlechar(10)); GO createtablesle_course (numberchar(8)notnull, c_numberchar(4), scoredecimal(3,0));