数据库SQL上机练习

1.求供应了某两种不同目录（自己指定）下的产品的供应商姓名。

选择目录1和目录2

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

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.求只向与自己位于不同城市的顾客供应产品的供应商姓名。
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.求每个城市中供应产品数量最多的供应商姓名。
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行

shipcitycompanynamesum(qty)
AachenSupplier OAVQT30
AlbuquerqueSupplier OGLRK185
AnchorageSupplier GQRCV165
ÅrhusSupplier SVIYA144
BarcelonaSupplier SVIYA12
·········
5.列出每个目录下被购买次数最多的产品。
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行

categoryproductname
1Product QOGNU
2Product LUNZZ
3Product WUXYK
4Product UKXRI
5Product VKCMF
6Product BLCAX
7Product APITJ
8Product TTEEX
6.是否存在这样的<目录、供应商>对，该供应商供应了该目录下的所有产品？
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.定义每个员工参与的所有订单的产品数量*产品单价*折扣率之和作为员工的业绩，列出每个城市的业绩排在前三的员工。
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行

shipcityempidlastnamefirstnameyeji
Aachen3LewJudy202812.8430000
Aachen4PeledYael232890.8460000
Aachen1DavisSara192107.6045000
Albuquerque1DavisSara192107.6045000
Albuquerque3LewJudy202812.8430000
Albuquerque4PeledYael232890.8460000
···············
8.员工业绩定义同上，找出这样的员工，他的业绩好于所有雇佣日期在他之后的其他员工的业绩。
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行

empidlastnamefirstname
4PeledYael
8CameronMaria
9DolgopyatovaZoya
9.按时间顺序列出每位顾客购买的所有产品，要求将所有产品放在一个字段中输出。

数据量小时可用

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;
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行，列举前两行和最后一行

custidproductnamelist
1Product CBRRL,Product LSOFL,Product OFBNT,Product ICKNK,Product IMEHJ,Product JYGFE,Product LUNZZ,Product UKXRI,Product VAIIV,Product OFBNT,Product MYMOI,Product ACRVI
2Product COAXA,Product TOONT,Product RJVNM,Product WHBYK,Product PWCJB,Product NUNAW,Product POXFU,Product GEEOO,Product XKXDO,Product QMVUN
······
91Product 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 日 05 : 21 PM
© 禁止转载
如果觉得我的文章对你有用，请随意赞赏