数据库概论——作业SQL上机

博主： Rainshaw
发布时间：2019 年 05 月 10 日
1184 次浏览
暂无评论
9995字数
分类：数据库概论 SQL

数据库SQL上机练习

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

```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行

> 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行

> 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行

> 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行

> 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 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

数据库概论——作业SQL上机

Rainshaw • 2019 年 05 月 10 日

数据库SQL上机练习

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

查询结果：共4行

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

查询结果：共17行，列举前5行

> 3.求只向与自己位于不同城市的顾客供应产品的供应商姓名。

查询结果：共26行，列举前5行

> 4.求每个城市中供应产品数量最多的供应商姓名。

查询结果：共75行，列举前5行

> 5.列出每个目录下被购买次数最多的产品。

查询结果：共8行，列举前8行

> 6.是否存在这样的<目录、供应商>对，该供应商供应了该目录下的所有产品？

查询结果：共0行，所以并没有这样的<目录、供应商>对

> 7.定义每个员工参与的所有订单的产品数量\*产品单价\*折扣率之和作为员工的业绩，列出每个城市的业绩排在前三的员工。

查询结果：共207行，列举前6行

> 8.员工业绩定义同上，找出这样的员工，他的业绩好于所有雇佣日期在他之后的其他员工的业绩。

查询结果：共3行

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

数据量小时可用

print (@@fetch_status)

fetch next from mycursor into @thiscustid,@productname;
print (@@fetch_status)

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)

close mycursor;
deallocate mycursor;
```

查询结果：共89行，列举前两行和最后一行

数据库概论——作业SQL上机

发表评论取消回复

Typecho 评论异步通知插件——Notice

Typecho 插件——MacHighlight 代码语法高亮插件

Handsome 主题解决 CodePrettify 代码高亮插件与 Vditor 兼容问题

修复 Handsome 主题评论可见内格式错乱问题

Typecho 复制版权提醒插件

动量和能量测试题（二）

Typecho 下 handsome 主题文章特殊格式

Handsome 主题升级 todo

操作系统信任链扩展——操作系统与虚拟化安全实验二

Ubuntu 4k 显示器缩放设置

数据库概论——作业SQL上机

发表评论 取消回复

数据库概论——作业SQL上机

发表评论取消回复