9-30 結合で作成した集合と、集合演算

SQLパズル

Customersテーブル
CustomersID  Balance
-----------  -------
Customer1        100
Customer2        300
Customer3        600
Customer4        900

Ordersテーブル
CustomersID  OrderID
-----------  -------
Customer1    1-1
Customer1    1-2
Customer1    1-3
Customer1    1-4
Customer2    2-1
Customer2    2-2
Customer2    2-3
Customer3    3-1
Customer3    3-2
Customer4    4-1
Customer4    4-2
Customer4    4-3

Detailsテーブル
OrderID  ItemID
-------  ------
1-1      Item1
1-2      Item1
1-3      Item2
1-4      Item3
2-1      Item1
2-2      Item2
2-3      Item3
3-1      Item1
3-2      Item3
4-1      Item1
4-2      Item2
4-3      Item2

Itemsテーブル
ItemID  ItemName
------  --------
Item1   potion
Item2   elixir
Item3   ether

Customersテーブル(顧客テーブル)
Ordersテーブル(注文テーブル)
Detailsテーブル(注文明細テーブル)
Itemsテーブル(商品テーブル)

を参照して、
すべての商品を注文した、顧客の数と、Balanceの平均を求める

出力結果
BalanceConut  BalanceAvg
------------  ----------
           2         200

SQLパズル(日本語版)のパズル30 [全製品の購入] を参考にさせていただきました
SQLパズル 第2版のパズル31 [すべての製品を購入した顧客] を参考にさせていただきました

データ作成スクリプト

create table Customers(
CustomersID varchar2(9),
Balance     number(3),
primary key(CustomersID));

insert into Customers values('Customer1',100);
insert into Customers values('Customer2',300);
insert into Customers values('Customer3',600);
insert into Customers values('Customer4',900);

create table Orders(
CustomersID varchar2(9),
OrderID     varchar2(3),
primary key(CustomersID,OrderID));

insert into Orders values('Customer1','1-1');
insert into Orders values('Customer1','1-2');
insert into Orders values('Customer1','1-3');
insert into Orders values('Customer1','1-4');
insert into Orders values('Customer2','2-1');
insert into Orders values('Customer2','2-2');
insert into Orders values('Customer2','2-3');
insert into Orders values('Customer3','3-1');
insert into Orders values('Customer3','3-2');
insert into Orders values('Customer4','4-1');
insert into Orders values('Customer4','4-2');
insert into Orders values('Customer4','4-3');

create table Details(
OrderID varchar2(3),
ItemID  varchar2(5),
primary key(OrderID,ItemID));

insert into Details values('1-1','Item1');
insert into Details values('1-2','Item1');
insert into Details values('1-3','Item2');
insert into Details values('1-4','Item3');
insert into Details values('2-1','Item1');
insert into Details values('2-2','Item2');
insert into Details values('2-3','Item3');
insert into Details values('3-1','Item1');
insert into Details values('3-2','Item3');
insert into Details values('4-1','Item1');
insert into Details values('4-2','Item2');
insert into Details values('4-3','Item2');

create table Items(
ItemID   varchar2(5),
ItemName varchar2(6),
primary key(ItemID));

insert into Items values('Item1','potion');
insert into Items values('Item2','elixir');
insert into Items values('Item3','ether');
commit;

SQL

--■■■not existsを使う方法■■■
select count(*) as BalanceConut,
avg(Balance) as BalanceAvg
  from Customers a
 where not exists(select ItemID from Items
                   minus
                  select c.ItemID
                    from Orders b,Details c
                   where b.CustomersID = a.CustomersID
                     and b.OrderID = c.OrderID);

--■■■countを比較する方法■■■
select count(*) as BalanceConut,
avg(Balance) as BalanceAvg
  from Customers a
 where (select count(*) from Items)
     = (select count(distinct c.ItemID)
          from Orders b,Details c
         where b.CustomersID = a.CustomersID
           and b.OrderID = c.OrderID);

解説

差集合が空集合かチェックしてます

---