wsdbd_lab7

select
c.customerkey,
c.lastname,
c.firstname,
oh.channelkey
from customer c
left join orderheader oh on c.customerkey = oh.customerkey
full join orderchannel od on od.channelkey = oh.channelkey

where oh.orderkey is null


select
*
from customer c cross join paymentmethod pm
left join orderheader oh on c.customerkey = oh.customerkey and oh.paymentmethodkey = pm.paymentmethodkey
where oh.orderkey is null
order by c.customerkey


select
*
from paymentmethod pm cross join productsubcategory psc
left join
(orderheader oh
inner join orderdetail od on oh.orderkey = od.orderkey
inner join product pd on pd.productkey = od.productkey)
on pm.paymentmethodkey = oh.paymentmethodkey and
pd.productsubcategorykey = psc.productsubcategorykey
where oh.orderkey is null
order by psc.productsubcategorykey


select
*
from orderchannel oc
cross join country c

left join orderheader oh on oc.channelkey = oh.channelkey and oh.countrykey = c.countrykey

where oh.orderkey is null


select
*
from deliverymethod dm cross JOIN productcategory pc

left join
(
productsubcategory psc inner join product pd on pd.productsubcategorykey = psc.productsubcategorykey
inner join orderdetail od on pd.productkey = od.productkey
inner join orderheader oh on oh.orderkey = od.orderkey
)
on pc.productcategorykey = psc.productcategorykey and dm.deliverymethodkey = oh.deliverymethodkey

where oh.orderkey is null

order by 1, 2


select
c.customerkey, c.firstname, c.lastname
from customer c inner join city ct on c.citykey = ct.citykey
inner join country co on co.countrykey = ct.countrykey
where co.countryname = 'United States'
minus
SELECT DISTINCT c.customerkey, c.firstname, c.lastname FROM customer c
inner join orderheader oh on c.customerkey = oh.customerkey
INNER join paymentmethod pm on oh.paymentmethodkey = pm.paymentmethodkey

where pm.paymentmethodname = 'PayPal'


order by c.countryname