城院数据库系统原理实验9

分别用相关子查询和不相关子查询 查询没有生成过订单的员工姓名 select LastName FirstName from Employees where not exists select from Orders where Employees EmployeeID EmployeeID select LastName FirstName from Employees where EmployeeID in select EmployeeID from Orders where Employees EmployeeIDOrders EmployeeID 查询自己下级生成的订单数量最多的员工 注 每个下级员工的订单数和最多 select employeeid from Employees where employeeid in select employeeid from orders group by employeeid having count orderid all select count orderid from orders group by employeeid and reportsto is not null 查询订单最多的城市名称 select shipcity from Orders group by shipcity having count orderid all select count orderid from Orders group by shipcity 查询订单数量最多的产品的供应商 select companyname from suppliers where supplierid in select supplierid from products where productid in select productid from orderdetails group by productid having count orderid all select count orderid from orderdetails group by productid 查询订单金额最高的员工姓名 select LastName FirstName from Employees where employeeid in select employeeid from orders where orderid in select orderid from orderdetails where quantity Unitprice select max quantity Unitprice from orderdetails 查询产品种类数最多的供应商名称 select supplierid from products group by supplierid having count productid all select count distinct productid from products group by supplierid 将订单数最多的员工的备注信息改为 优秀员工 update employees set notes 优秀员工 where employeeid in select employeeid from orders group by employeeid having count orderid all select count orderid from orders group by employeeid 通过一个 SQL 语句建立表 字段包括产品 ID 产品名称 订单中产品总量 其中产品 ID 为主码 并通过一个 SQL 语句填充这张表 insert into chanpin select b productid a productname count quantity from OrderDetails b products a where a productid b productid group by b productid a productname 在员工表中找一个员工 删除这个员工相关的所有数据 多个 SQL 语句 select from employees select from orders where employeeid 8 delete from employees where employeeid 8 delete from orders where e