Ex 14

–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’

end

from

(

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

) printer

from

product po) x