软件学院实验报告课程：数据库原理与实用技术实验 学期： 学年第二学期 任课教师： 专业： 信息安全 学号：20 姓名： 成绩： 实验4数据查询一、实验目的1、 理解T-SQL语言的使用2、 熟练掌握数据查询语句3、 掌握合计函数的使用二、实验内容1、CAP数据库的查询(记录每个查询的SQL语句和查询结果)(1) 建立CAP数据库，输入C、A、P、四张表；(2) 完成课后习题[3.2]b、[3.5]> [3.8]a,b、[3.ll]b,f,j,l[3.2] (b)Retrieve aid values of agents who receive the maximum percent commission. 解：Select aidfrom agentswhere percents>=all(select percentsfrom agents)aid1 a03[3.5 ] Consider the problem to find all (cid, aid) pairs where the customer does not place an order through the agent. This can be accomplished with the Select statement：Select cid, aidfrom customers c. agents awhere not exists(select * from orders x where x.cid = c.cid and x.aid =a.aid);Is it possible to achieve this result using the NOT IN predicate in place of the NOT EXISTS predicate with a single Subquery? With more than one Subquery? Explain your answer and demonstrate any equivalent form by execution.解：Select cid, aidfrom customers c, agents awhere cid not in(select cidfrom orderswhere aid 二 a.aid)cid aid1c002aOl2c002a023c002a044c002a065c003aOl6c003a027c003a048c003a059c003a0610c004aOl11c004a0212c004a0313c004a0414c004a0515c006a0216c006a0417c006a05[3.8](a) Write a Select statement with no WHERE clause to retrieve all customer cids and the maximum money each spends on any product. Label the columns of the resulting table: eid, MAXSPENT.解：Select distinct cid as eid, MAX(dollars) AS MAXSPENTfrom ordersgroup by cideidMAXSPENT1cOOl720. 00002c002880.00003c0031104.00004c004460.00005c006600. 0000(b) Write a query to retrieve the AVERAGE value (over all customers) of the MAXSPENT of query (a) 解：Select distinct cid as eid, MAX(dollars) AS MAXSPENTinto #Spentfrom ordersgroup by cidselect avg(MAXSPENT) as averagefrom #Spentaverage1752.8000[3.11] (b) We say that a customer x orders a product y in an average quantity A if A is avg(qty) for all orders rows with cid = x and pid = y. Is it possible in a single SQL statement to retrieve cid values of customers who order all the products that they receive in average quantities (by product) of at least 300? 解：Select cidfrom customerswhere not exists(select *From orders,productswhere orders.cid = customers.cid and orders.pid =products.pid group by orders.cid,orders.pidhaving (select avg(qty) from orders)<300)(f) Get pid values of products that are ordered by all customers in Dallas.解：Select distinct pidfrom orderswhere cid in(select cidfrom customerswhere city='Dallas,)pid1p032p05(j) Use a single Update statement to raise the prices of all products warehoused in Duluth or Dallas by 10%. Then restore the original values by rerunning the procedure that you originally used to create and load the products table.解：Update orders set dollars = dollars* 1.1where pid in(select pidfrom orderswhere pid in(select pidfrom productswhere city='Duluth' or city=,Dallas,))select dollars,pidfrom ordersorder by piddollarspid1495. 0000pOl2495. 0000pOl3506.0000pOl4550.0000pOl440.0000pOl6180. 0000p027594. 0000p038968.0000p039774.4000p0310594.0000p0411495.0000p05121214.4000 p05131214.4000 p0514792. 0000p0615720. 0000p0716600. 0000p07Update orders set dollars = dollars/1.1 where pid in(select pidfrom orderswhere pid in(select pidfrom productswhere city=*Duluth' or city=,Dallas,))select dollars,pidfrom ordersorder by piddollarspid1450.0000pOl2450.0000pOl3460.0000pOl4500.0000pOl5400.0000pOl6180.0000p027540.0000p038880.0000p039704. 0000p0310540.0000p0411450.0000p05121104.0000 p05131104.0000 p0514720.0000p0615720.0000p0716600.0000p07(1) Write an SQL query to get aid and percent values of agents who take orders from all customers who live in Duluth. The aid values should be reported in order by decreasing percent. (Note that if percent is not retrieved in the select list, we cannot order by these values.)解：Select aid,percents from agentswhere aid in(select aidfrom orderswhere cid in(select cidfrom customers where city='Duluth')) order by percents descaidpercents1a0372a0463aOl64a0265a0556a0652、Employee数据库的查询(记录每个查询的SQL语句和查询结果)(1)向表中插入数据。
Insert Into person Values (000001；田林7男1982-4-77初级,；00r )Insert Into person Values (000002；郭达'，'男71953-2-14V高级','001')Insert Into person Values (000003；王林'，'女71984-8-257初级','002')Insert Into person Values (000004；王辉'，'男71984-5-1T；初级','002')Insert Into person Values (000005；罗大右男"968-12-107高级'「002')Insert Into person Values (000006；蔡琴弦1978-4-20?中级','003')Insert Into person Valu。