Loading... 数据库SQL上机练习 > 1.求供应了某两种不同目录(自己指定)下的产品的供应商姓名。 选择目录1和目录2 ```sql select distinct companyname from Production.Suppliers where supplierid in ( select A.supplierid from Production.Products A, Production.Products B where A.supplierid = B.supplierid and A.categoryid = 1 and B.categoryid = 2 ); ``` 查询结果:共4行 | company | | :- | | Supplier CIYNM | | Supplier GQRCV | | Supplier SVIYA | | Supplier SWRXU | > 2.求没有供应某两种不同目录(自己指定)下的产品的供应商姓名。 选择目录1和目录2 ```sql select distinct companyname from Production.Suppliers where supplierid not in ( select distinct A.supplierid from Production.Products A where A.categoryid = 1 or A.categoryid = 2 ); ``` 查询结果:共17行,列举前5行 | company | | :- | | Supplier BWGYE | | Supplier EQPNC | | Supplier ERVYZ | | Supplier FNUXM | | Supplier JDNUG | | ··· | > 3.求只向与自己位于不同城市的顾客供应产品的供应商姓名。 ```sql select distinct companyname from Production.Suppliers where supplierid not in ( select distinct Suppliers.supplierid from Sales.Orders, Sales.OrderDetails, Production.Products, Production.Suppliers, Sales.Customers where Orders.orderid = OrderDetails.orderid and OrderDetails.productid = Products.productid and Products.supplierid = Suppliers.supplierid and Orders.custid = Customers.custid and Customers.city = Suppliers.city ) ``` 查询结果:共26行,列举前5行 | company | | :- | | Supplier BWGYE | | Supplier CIYNM | | Supplier ELCRN | | Supplier EQPNC | | Supplier ERVYZ | | ··· | > 4.求每个城市中供应产品数量最多的供应商姓名。 ```sql select distinct shipcity, companyname, sum(qty) from Production.Suppliers, Production.Products, Sales.OrderDetails, Sales.Orders A where Products.supplierid = Suppliers.supplierid and OrderDetails.productid = Products.productid and A.orderid = OrderDetails.orderid group by shipcity, Products.supplierid, companyname having sum(qty) >= all ( select sum(qty) from Production.Suppliers, Production.Products, Sales.OrderDetails, Sales.Orders where Products.supplierid = Suppliers.supplierid and OrderDetails.productid = Products.productid and OrderDetails.orderid = Orders.orderid and A.shipcity = Orders.shipcity group by Products.supplierid ) ``` 查询结果:共75行,列举前5行 | shipcity | companyname | sum(qty) | | :- | :- | :- | | Aachen | Supplier OAVQT | 30 | | Albuquerque | Supplier OGLRK | 185 | | Anchorage | Supplier GQRCV | 165 | | Århus | Supplier SVIYA | 144 | | Barcelona | Supplier SVIYA | 12 | | ··· | ··· | ··· | > 5.列出每个目录下被购买次数最多的产品。 ```sql select A.categoryid, productname from Production.Categories A, Production.Products, Sales.OrderDetails where Products.categoryid = A.categoryid and OrderDetails.productid = Products.productid group by A.categoryid, Products.productid, productname having count(orderid) >= all ( select count(orderid) from Production.Categories, Production.Products, Sales.OrderDetails where Products.categoryid = Categories.categoryid and OrderDetails.productid = Products.productid and A.categoryid = Products.categoryid group by Categories.categoryid, Products.productid ) ORDER BY A.categoryid ``` 查询结果:共8行,列举前8行 | category | productname | | :- | :- | | 1 | Product QOGNU | | 2 | Product LUNZZ | | 3 | Product WUXYK | | 4 | Product UKXRI | | 5 | Product VKCMF | | 6 | Product BLCAX | | 7 | Product APITJ | | 8 | Product TTEEX | > 6.是否存在这样的<目录、供应商>对,该供应商供应了该目录下的所有产品? ```sql select categoryname, companyname from Production.Categories, Production.Suppliers, Production.Products A where A.supplierid = Suppliers.supplierid and A.categoryid = Categories.categoryid group by A.categoryid, A.supplierid, categoryname, companyname having count(productid) = ( select count(productid) from Production.Categories, Production.Products where Products.categoryid = Categories.categoryid and A.categoryid = Categories.categoryid ) ``` 查询结果:共0行,所以并没有这样的<目录、供应商>对 > 7.定义每个员工参与的所有订单的产品数量\*产品单价\*折扣率之和作为员工的业绩,列出每个城市的业绩排在前三的员工。 ```sql with YJ(empid, yeji) as ( select Employees.empid, sum(unitprice * qty * (1.0 - discount)) yeji from HR.Employees, Sales.Orders, Sales.OrderDetails where Employees.empid = Orders.empid and Orders.orderid = OrderDetails.orderid group by Employees.empid ) select shipcity, Employees.empid, lastname, firstname, yeji from HR.Employees, Sales.Orders A, Sales.OrderDetails, YJ where Employees.empid = A.empid and A.orderid = OrderDetails.orderid and YJ.empid = Employees.empid group by shipcity, Employees.empid, lastname, firstname ,yeji having yeji >= any ( select yeji from ( select top 100 percent yeji, ROW_NUMBER() over (ORDER BY yeji desc) as row from (select yeji from YJ, Sales.Orders where YJ.empid = Orders.empid and A.shipcity = Orders.shipcity group by YJ.empid,yeji ) s order by yeji desc ) m where row <= 3 ) order by shipcity; ``` 查询结果:共207行,列举前6行 | shipcity | empid | lastname | firstname | yeji | | :- | :- | :- | :- | :- | | Aachen | 3 | Lew | Judy | 202812.8430000 | | Aachen | 4 | Peled | Yael | 232890.8460000 | | Aachen | 1 | Davis | Sara | 192107.6045000 | | Albuquerque | 1 | Davis | Sara | 192107.6045000 | | Albuquerque | 3 | Lew | Judy | 202812.8430000 | | Albuquerque | 4 | Peled | Yael | 232890.8460000 | | ··· | ··· | ··· | ··· | ··· | > 8.员工业绩定义同上,找出这样的员工,他的业绩好于所有雇佣日期在他之后的其他员工的业绩。 ```sql with T1(empid, yeji) as ( select Employees.empid, sum(unitprice * qty * (1.0 - discount)) yeji from HR.Employees, Sales.Orders, Sales.OrderDetails where Employees.empid = Orders.empid and Orders.orderid = OrderDetails.orderid group by Employees.empid ) select distinct A.empid, lastname,firstname from T1 A,HR.Employees AA where A.empid=AA.empid and not exists( select * from T1 B, HR.Employees BB where B.yeji>A.yeji and BB.hiredate > AA.hiredate and B.empid=BB.empid ) order by empid ``` 查询结果:共3行 | empid | lastname | firstname | | :- | :- | :- | | 4 | Peled | Yael | | 8 | Cameron | Maria | | 9 | Dolgopyatova | Zoya | > 9.按时间顺序列出每位顾客购买的所有产品,要求将所有产品放在一个字段中输出。 数据量小时可用 ```sql declare mycursor insensitive cursor for select Orders.custid, productname from Sales.Customers, Sales.Orders, Sales.OrderDetails, Production.Products where Orders.custid = Customers.custid and Orders.orderid = OrderDetails.orderid and OrderDetails.productid = Products.productid order by Customers.custid, orderdate; open mycursor; declare @data table ( custid INT NOT NULL, productnamelist text NOT NULL ) declare @lastcustid INT, @thiscustid INT, @productname nvarchar(40), @productnamelist varchar(8000); set @lastcustid = -1; print (@@fetch_status) fetch next from mycursor into @thiscustid,@productname; print (@@fetch_status) while @@fetch_status = 0 begin if (@thiscustid = @lastcustid) begin set @productnamelist = @productnamelist + ',' + @productname; end else begin if (@lastcustid = -1) begin set @lastcustid = @thiscustid; set @productnamelist = @productname end else begin insert into @data values (@lastcustid, @productnamelist); set @lastcustid = @thiscustid; set @productnamelist = @productname; end end fetch next from mycursor into @thiscustid,@productname; if (@@fetch_status = -1) begin insert into @data values (@lastcustid, @productnamelist); end end; select * from @data; close mycursor; deallocate mycursor; ``` ```sql declare mycursor insensitive cursor for select Orders.custid, productname from Sales.Customers, Sales.Orders, Sales.OrderDetails, Production.Products where Orders.custid = Customers.custid and Orders.orderid = OrderDetails.orderid and OrderDetails.productid = Products.productid order by Customers.custid, orderdate; open mycursor; declare @data table ( custid INT NOT NULL, productnamelist varchar(max) NOT NULL ) declare @lastcustid INT, @thiscustid INT, @productname nvarchar(40); set @lastcustid = -1; print (@@fetch_status) fetch next from mycursor into @thiscustid,@productname; print (@@fetch_status) while @@fetch_status = 0 begin if (@thiscustid = @lastcustid) begin update @data set productnamelist = productnamelist + ',' + @productname where custid = @thiscustid; end else begin insert into @data values (@thiscustid, @productname); set @lastcustid = @thiscustid; end fetch next from mycursor into @thiscustid,@productname; end; select * from @data; close mycursor; deallocate mycursor; ``` 查询结果:共89行,列举前两行和最后一行 | custid | productnamelist | | :- | :- | | 1 | Product CBRRL,Product LSOFL,Product OFBNT,Product ICKNK,Product IMEHJ,Product JYGFE,Product LUNZZ,Product UKXRI,Product VAIIV,Product OFBNT,Product MYMOI,Product ACRVI | | 2 | Product COAXA,Product TOONT,Product RJVNM,Product WHBYK,Product PWCJB,Product NUNAW,Product POXFU,Product GEEOO,Product XKXDO,Product QMVUN | | ··· | ··· | | 91 | Product XWOXC,Product ACRVI,Product HHYDP,Product WHBYK,Product RECZE,Product RECZE,Product QAQRL,Product TBTBL,Product APITJ,Product NEVTJ,Product XYZPE,Product XYZPE,Product QOGNU,Product BKAZJ,Product BWRLG,Product WUXYK | 最后修改:2020 年 10 月 11 日 © 禁止转载 打赏 赞赏作者 支付宝微信 赞 0 如果觉得我的文章对你有用,请随意赞赏