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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s