Answer the question
In order to leave comments, you need to log in
Sql-ex, what are the features of the "Computer Firm" test database?
Good afternoon, I'm solving problems from the sql-ex.ru website and for several days now I've been puzzling over the 75th problem, it seems that the problem is not difficult, but the 2nd check base says otherwise)
Initially, I joined each table with the Product table
select
maker,
max(l.price) as laptop,
max(pc.price) as pc,
max(r.price) as printer
from
Product t
left join Laptop l
on t.model=l.model
left join PC
on t.model=pc.model
left join Printer r
on t.model=r.model
group by
maker
with
B as (
select
p.maker, p.type, max(a.price)
from
Product p
inner join (
select distinct model, price from Laptop
union
select distinct model, price from PC
union
select distinct model, price from Printer
) a
on
p.model=a.model
group by
p.maker, p.type
)
select
b1.maker,
coalesce((select max from B where maker=b1.maker and type='Laptop'), null) as Laptop,
coalesce((select max from B where maker=b1.maker and type='PC'), null) as PC,
coalesce((select max from B where maker=b1.maker and type='Printer'), null) as Printer
from
B b1
group by
b1.maker
Answer the question
In order to leave comments, you need to log in
You forgot the condition "... which have products with a known price in at least one of the tables..."
That is, you need to:
select
maker,
max(l.price) as laptop,
max(pc.price) as pc,
max(r.price) as printer
from
Product t
left join Laptop l
on t.model=l.model
left join PC
on t.model=pc.model
left join Printer r
on t.model=r.model
group by
maker
having
max(l.price) IS NOT NULL
OR max(pc.price) IS NOT NULL
OR max(r.price) IS NOT NULL
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question