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