实验4数据查询.docx

实验4数据查询.docx

《实验4数据查询.docx》由会员分享，可在线阅读，更多相关《实验4数据查询.docx（11页珍藏版）》请在冰点文库上搜索。

实验4数据查询

云南大学软件学院实验报告

实验4数据查询

一、实验目的

（1）理解T-SQL语言的使用。

（2）熟练掌握数据查询语句。

（3）掌握合计函数的使用。

二、实验内容

1、CAP数据库的查询（记录每个查询的SQL语句和查询结果）

（1）建立CAP数据库，输入C、A、P、O四张表；

（2）完成课后习题[3.2]b、[3.5]、[3.8]a,b、[3.11]b,f,j,l

[3.2]（b）Retrieveaidvaluesofagentswhoreceivethemaximumpercentcommission.

Answer：

Theaidvaluesofagentswhoreceivethemaximumpercentcommissionisa03.

selectaidfromAGENTSwherepercents=any（selectmax（percents）fromAGENTS）

[3.5]Considertheproblemtofindall（cid,aid）pairswherethecustomerdoesnotplaceanorderthroughtheagent.ThiscanbeaccomplishedwiththeSelectstatement

selectcid,aid

fromcustomerscagentsa

wherenotexists

（select*fromordersxwherex.cid=c.cidandx.aid=a.aid）;

IsitpossibletoachievethisresultusingtheNOTINpredicateinplaceoftheNOTEXISTSpredicatewithasingleSubquery?

WithmorethanoneSubquery?

Explainyouransweranddemonstrateanyequivalentformbyexecution.

（1）selectcid,aidfromCUSTOMERS,AGENTSwhere（cidnotin（selectcidfromORDERSwhereORDERS.aid=AGENTS.aid））

selectcid,aidfromCUSTOMERS,AGENTSwhere（aidnotin（selectaidfromORDERSwhereORDERS.cid=CUSTOMERS.cid））

（2）

selectcid,aidfromCUSTOMERS,AGENTSwhere（cidnotin（selectcidfromORDERSwherecidin（selectcidfromORDERSwhereORDERS.aid=AGENTS.aid）））

[3.8]（a）WriteaSelectstatementwithnoWHEREclausetoretrieveallcustomercidsandthemaximummoneyeachspendsonanyproduct.Labelthecolumnsoftheresultingtable:

eid,MAXSPENT.

SELECTcid,max（dollars）asMAXSPENTfromordersgroupbycid

（b）WriteaquerytoretrievetheAVERAGEvalue（overallcustomers）oftheMAXSPENTofquery（a）

SELECTcid,max（dollars）asMAXSPENTINTO#OrderPayfromorders

groupbycid

SELECTavg（MAXSPENT）asaveragefrom#OrderPay

[3.11]（b）WesaythatacustomerxordersaproductyinanaveragequantityAifAisavg（qty）forallordersrowswithcid=xandpid=y.IsitpossibleinasingleSQLstatementtoretrievecidvaluesofcustomerswhoorderalltheproductsthattheyreceiveinaveragequantities（byproduct）ofatleast300?

selectdistinctcidfromordersgroupbycid,pidhavingavg（qty）>=300

（f）GetpidvaluesofproductsthatareorderedbyallcustomersinDallas.

selectp.pidfromproductsp

wherenotexists（select*fromcustomersc

wherec.city='Dallas'and

notexists（select*fromordersx

wherex.cid=c.cidandx.pid=p.pid））

（j）UseasingleUpdatestatementtoraisethepricesofallproductswarehousedinDuluthorDallasby10%.Thenrestoretheoriginalvaluesbyrerunningtheprocedurethatyouoriginallyusedtocreateandloadtheproductstable.

UPDATEPRODUCTSSETprice=price*1.1wherecity='Duluth'orcity='Dallas'

（l）WriteanSQLquerytogetaidandpercentvaluesofagentswhotakeordersfromallcustomerswholiveinDuluth.Theaidvaluesshouldbereportedinorderbydecreasingpercent.（Notethatifpercentisnotretri

useCAP

GO

selectaid,percentsfromagentsa

wherenotexists

（select*fromcustomersc

wherec.city='Duluth'and

notexists（select*fromordersx

wherex.cid=c.cidandx.aid=a.aid））

orderbya.percents

2、Employee数据库的查询（记录每个查询的SQL语句和查询结果）

（1）向表中插入数据。

（2）将职工编号为000006的员工3月份基本工资增加为3000，奖金增加到800。

Updatesalary

Setbase='3000',Bonus='800'

wherePno='000006'andMonth='3'

（3）员工000009已经离开公司，将该员工的数据删除

DELETEFROMpersonWHEREPno='000009'DELETEFROMsalaryWHEREPno='000009'

（4）简单条件查询

✓查询person表中所有不重复的职称。

SELECTDISTINCTProfFROMperson

✓查询具有高级职称的女员工信息

SELECT*FROMpersonpwherep.Prof='高级'andp.Sex='女'

✓查询职工姓名为黎明的员工数据

SELECT*FROMpersonpwherep.Pname='黎明'

✓查询各部门的实发工资总数

SELECTDeptno,SUM（Fact）astotalfromsalary,departmentWHEREpnoin（SELECTpnofrompersonwheredepartment.deptno=person.deptno）groupbydeptno

（5）复杂条件查询

✓查询平均工资高于3000的部门名和对应的平均工资。

selectdeptno,avg（Fact）asaverageINTO#AVESALARYfromdepartment,salarywherepnoin（selectpnofrompersonwheredepartment.deptno=person.deptno

）groupbydeptnoselectdname,averagefromdepartment,#AVESALARYwhereaverage>'3000'anddepartment.deptno=#AVESALARY.deptno

✓查询1月份实发工资比平均实发工资高的员工姓名和实发工资额。

SELECTpname,Factfromperson,salarywhereFact>=ALL（SELECTaveragefrom#AVESALARYwhere#AVESALARY.Deptno=person.Deptno）andmonth='1'andperson.pno=salary.pno

✓查询2月份实发工资比一月高的员工姓名。

selectpnamefromperson,salaryxwheremonth='2'andFact>（selectFactfromsalaryywherex.pno=y.pnoandmonth='1'）andperson.pno=x.pno

✓利用sql语句将1，2，3月累积的员工的实发工资按降序排序

selectpno,sum（Fact）asssalaryINTOsumsalaryfromsalarygroupbypno

orderbyssalaryDESC