数据库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行

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行

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行

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行

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行

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行

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行

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