A SQL example that not works

Question

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 .

1. Use a bit of indentation - Helps readability 2. What do you mean 'not works' 3. Why use fiddle sqlfiddle.com for a example to demonstration and also potential people to try an answer — Ed Heal
– Ed Heal, Commented Jan 24, 2014 at 19:43
I mean it doesn't return the currect result And also thank you for introduce me that site — Ali
– Ali, Commented Jan 24, 2014 at 19:45
@ali - Sort out indentation - Sort out a fiddle - You might then get somewhere — Ed Heal
– Ed Heal, Commented Jan 24, 2014 at 19:48

neubert · Accepted Answer · 2014-01-24 20:00:34Z

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.

Collectives™ on Stack Overflow

A SQL example that not works

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related