ch16.sql

select p_brand,
	p_type,
	p_size,
	count(distinct ps_suppkey) as supplier_cnt
from partsupp, part
where p_partkey = ps_partkey
	and p_brand <> 'Brand#45'
	and p_type not like 'MEDIUM POLISHED%'
	and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
	and ps_suppkey not in (
		select s_suppkey
		from supplier
		where s_comment like '%Customer%Complaints%'
	)
group by p_brand, p_type, p_size
order by supplier_cnt desc,
	p_brand, p_type, p_size;