资源描述:
《数据库系统原理与设计实验教程实验1》由会员上传分享,免费在线阅读,更多相关内容在教育资源-天天文库。
1、实验一简单查询在订单数据库中,完成如下的查询:(1)查询所有业务部门的员工姓名、职称、薪水。selectemployeeName,headShip,salaryfromemployeewheredepartment='业务科'(2)查询名字中含有“有限”的客户姓名和所在地。selectCustomerName,addressfromCustomerwhereCustomerNamelike'%有限%'(3)查询出姓“王”并且姓名的最后一个字为“成”的员工。select*fromemployeewhereemployeeNamel
2、ike'王%成'没有结果(4)查询住址中含有上海或南昌的女员工,并显示其姓名、所属部门、职称、住址,其中性别用“男”和“女”显示。selectemployeeName,department,headship,address,sex=Casesexwhen'M'then'男'when'F'then'女'endfromemployeewhereaddresslike'%上海%'oraddresslike'%南昌%'andsex='F'(5)在订单明细表OrderDetail中挑出销售金额大于等于10000元的订单。selectord
3、erNofromOrderDetailgroupbyorderNohavingsum(quantity*price)>=10000(6)选取订单金额最高的前10%的订单数据。SELECTTOP10PERCENTorderNoFROMOrderdetailGROUPBYorderNoORDERBYsum(quantity*price)DESC(7)查询出职务为“职员”或职务为“科长”的女员工的信息。select*fromemployeewhere(headship='职员'orheadship='科长')andsex='F'(8)
4、查找定单金额高于8000的所有客户编号。1)查询总金额高于8000元的客户编号selectCustomerNofromOrderMastera,Orderdetailbwherea.orderNo=b.orderNogroupbyCustomerNohavingsum(quantity*price)>80002)查找定单金额高于8000的所有客户编号selectCustomerNofromOrderMasterwhereorderNoin(selectorderNofromOrderDetailgroupbyorderNohav
5、ingsum(quantity*price)>8000)(9)选取编号界于“C20050001”和“C20050004”的客户编号、客户名称、客户地址。selectCustomerNo,CustomerName,addressfromCustomerwhereCustomerNobetween'C20050001'and'C20050004'(11)找出同一天进入公司服务的员工。Selecta.employeeNo,a.employeeName,b.employeeNo,b.employeeNamefromEmployeea,E
6、mployeeasbwherea.employeeNo!=b.employeeNoanda.employeeName>b.employeeNameand(a.hireDate=b.hireDate)(12)在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。1)首先计算订单主表的订单金额updateOrderMastersetorderSum=totalSumfromOrderMastera,(selectorderNo,sum(quantity*price)tota
7、lSumfromOrderDetailgroupbyorderNo)bwherea.orderNO=b.orderNo2)SELECT*FROMOrderMasterWHEREorderSum>any(SELECTorderSumFROMOrderMasterWHEREsalerNo='E2005002'ANDorderDate='20080109')(13)查询既订购了“52倍速光驱”商品,又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。SelectcustomerNo,orderNo,orderSumfromOr
8、derMasterwherecustomerNoin(selectcustomerNofromOrderMastera,OrderDetailb,Productcwherea.orderNo=b.orderNoandb.productNo=c.productNo