城院数据库系统原理实验.doc

《城院数据库系统原理实验.doc》由会员分享，可在线阅读，更多相关《城院数据库系统原理实验.doc（9页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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(orderid)=all(sele

3、ct 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 by productid)

4、查询订单金额最高的员工姓名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 supplierid having coun

5、t(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)通过一个SQL语句建立表,字段包

6、括产品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)