–For Product table, receive result set in the form of a table with columns: maker, pc, laptop, and printer.
–For each maker, this table must include "yes" if a maker has products of corresponding type or "no" otherwise.
–In the first case (yes), specify in brackets (without spaces) the quantity of available distinct models of corresponding type
–(i.e. being in PC, Laptop, and Printer tables).
–maker pc laptop printer
–A yes(2) yes(2) yes(3)
–B yes(1) yes(1) no
–C no yes(1) no
–D no no yes(2)
–E yes(1) no yes(1)
select
maker, case when pc = 0 then ‘yes(0)’ when pc > 0 then ‘yes(‘ + rtrim(ltrim(cast(pc as varchar))) +‘)’ else ‘no’ end , case when laptop = 0 then ‘yes(0)’ when laptop > 0 then ‘yes(‘ + rtrim(ltrim(cast(laptop as varchar))) +‘)’ else ‘no’ end , case when printer = 0 then ‘yes(0)’ when printer > 0 then ‘yes(‘ + rtrim(ltrim(cast(printer as varchar))) +‘)’ else ‘no’ endfrom
(
select distinct maker, ( select case when (select count(model) from product where type=‘pc’ and maker = po.maker) > 0 then (select count(distinct pc.model) from product p1 inner join pc on p1.model = pc.model where maker = po.maker) else NULL end ) pc, ( select case when (select count(model) from product where type=‘laptop’ and maker = po.maker) > 0 then (select count(distinct laptop.model) from product p1 inner join laptop on p1.model = laptop.model where maker = po.maker) else NULL end ) laptop, ( select case when (select count(model) from product where type=‘printer’ and maker = po.maker) > 0 then (select count(distinct printer.model) from product p1 inner join printer on p1.model = printer.model where maker = po.maker) else NULL end ) printerfrom
product po) x