《城院数据库系统原理实验(共9页).doc》由会员分享，可在线阅读，更多相关《城院数据库系统原理实验(共9页).doc（9页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、精选优质文档-倾情为你奉上分别用相关子查询和不相关子查询,查询没有生成过订单的员工姓名.select LastName,FirstNamefrom Employeeswhere not exists(select *from Orderswhere Employees.EmployeeID=EmployeeID)select LastName,FirstNamefrom Employeeswhere EmployeeID in(select EmployeeIDfrom Orderswhere Employees.EmployeeIDOrders.EmployeeID)查询自己下级生成的订单数
2、量最多的员工.(注:每个下级员工的订单数和最多)select employeeidfrom Employeeswhere employeeid in(select employeeidfrom ordersgroup by employeeid having count(orderid)=all(select count(orderid) from ordersgroup by employeeid)and reportsto is not null查询订单最多的城市名称。select shipcityfrom Ordersgroup by shipcity having count(orde
3、rid)=all(select count(orderid) from Ordersgroup by shipcity)查询订单数量最多的产品的供应商select companyname from supplierswhere supplierid in(select supplierid from productswhere productid in(select productidfrom orderdetailsgroup by productid having count(orderid)=all(select count(orderid)from orderdetailsgroup
4、by productid)查询订单金额最高的员工姓名select LastName,FirstNamefrom Employeeswhere employeeid in(select employeeidfrom orderswhere orderid in(select orderid from orderdetailswhere quantity*Unitprice=(select max(quantity*Unitprice)from orderdetails)查询产品种类数最多的供应商名称select supplierid from productsgroup by supplieri
5、d having count(productid)=all(select count(distinct productid) from productsgroup by supplierid)将订单数最多的员工的备注信息改为”优秀员工”update employeesset notes=优秀员工where employeeid in(select employeeid from ordersgroup by employeeid having count(orderid)=all(select count(orderid) from orders group by employeeid)通过一
6、个SQL语句建立表,字段包括产品ID,产品名称,订单中产品总量(其中产品ID为主码),并通过一个SQL语句填充这张表.insert into chanpinselect b.productid,a.productname,count(quantity)from OrderDetails b,products awhere a.productid=b.productidgroup by b.productid,a.productname在员工表中找一个员工,删除这个员工相关的所有数据(多个SQL语句)select * from employeesselect * from orders where employeeid=8delete from employees where employeeid=8;delete from orders where employeeid=8;delete from orderdetails where orderid in(select orderid from orders where employeeid=8)专心-专注-专业