CODEWITHSUNDEEP
sql
Ali
Ali

Reputation: 160

A SQL example that not works

I wrote this code 

select maker.product , product.type
from product
join laptop on laptop.model=product.model
join pc on pc.model=product.model
join printer on printer.model=product.model 
WHERE (product.type= 'pc' AND 
(NOT EXISTS (SELECT maker 
FROM Product
WHERE product.type = 'laptop'
))and (NOT EXISTS (SELECT maker 
FROM Product
WHERE product.type = 'printer'  
))
or 
product.type= 'laptop' AND 
(NOT EXISTS (SELECT maker 
FROM Product
WHERE product.type= 'pc' 
))and (NOT EXISTS (SELECT maker 
FROM Product
WHERE product.type= 'printer'
))
or
type = 'printer' AND 
(NOT EXISTS (SELECT maker 
FROM Product
WHERE product.type= 'laptop' 
))and (NOT EXISTS (SELECT maker 
FROM Product
WHERE product.type= 'pc' 
)))
group by product.maker
having count(product.model) > 1

in this site sql-ex.ru in exercise 14 but it didn't work. the question was : 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).

And it's Scheme is

Scheme Picture

Sorry for my bad English speaking ! Please help ! Thank you .

Upvotes: 0

Views: 274

Answers (1)

neubert
neubert

Reputation: 16792

Here's a more readable version of your code (unless I've made a mistake):

select maker.product , product.type
from product
join laptop on laptop.model=product.model
join pc on pc.model=product.model
join printer on printer.model=product.model 
WHERE (
  product.type= 'pc' AND 
  (NOT EXISTS (SELECT maker FROM Product WHERE product.type = 'laptop')) and
  (NOT EXISTS (SELECT maker FROM Product WHERE product.type = 'printer'))
  or 
  product.type= 'laptop' AND 
  (NOT EXISTS (SELECT maker FROM Product WHERE product.type= 'pc')) and
  (NOT EXISTS (SELECT maker FROM Product WHERE product.type= 'printer'))
  or
  type = 'printer' AND 
  (NOT EXISTS (SELECT maker FROM Product WHERE product.type= 'laptop')) and
  (NOT EXISTS (SELECT maker FROM Product WHERE product.type= 'pc'))
)
group by product.maker
having count(product.model) > 1

Basically, it looks like you need to do parenthesis between each of the OR's.

Upvotes: 0

Related Questions