Mind teaser SQL

I have been trying to answer the questions found on http://www.sql-ex.ru/learn_exercises.php#answer_ref recently since I am a newbie in sql, as you can see from my previous posts regarding sql. I across this question:

Exercise: 23 Find the makers producing at least both a pc having speed not less than 750 MHz and a laptop having speed not less than 750 MHz. Result set: Maker

The following is my answer which is incorrect since it uses or. can anyone guide me to a good direct开发者_Python百科ion by either giving me a link where to search or what join should I use.

  SELECT pt.maker    
    FROM product pt, 
         laptop l, 
         pc    
   WHERE (pt.model = pc.model 
     AND pc.speed >=750) 
      OR (pt.model = l.model 
     AND l.speed >=750)
GROUP BY pt.maker

Close, if the tables and column definitions are right (I don't have a login for that site), it should be something like:

SELECT distinct pt_pc.maker
FROM  
  laptop l 
    inner join product pt_l on pt_l.model=l.model, 
  pc
    inner join product pt_pc on pt_pc.model=pc.model
WHERE pc.speed >=750 and l.speed >=750 and pt_l.maker=pt_pc.maker

so basically you want one pc with speed >=750 and one laptop with speed >=750 made by the same guy.

Assuming INTERSECT is supported:

SELECT  maker
FROM    product p
JOIN    laptop l
ON      l.model = p.model
WHERE   speed >= 750
INTERSECT
SELECT  maker
FROM    product p
JOIN    pc
ON      pc.model = p.model
WHERE   speed >= 750

Cross-platform:

SELECT  maker
FROM    (
        SELECT  maker, 1 AS source
        FROM    product p
        JOIN    laptop l
        ON      l.model = p.model
        WHERE   speed >= 750
        UNION
        SELECT  maker, 2 AS source
        FROM    product p
        JOIN    pc
        ON      pc.model = p.model
        WHERE   speed >= 750
        ) q
GROUP BY
        maker
HAVING  COUNT(*) = 2

SELECT DISTINCT maker 
FROM product 
WHERE   maker IN (SELECT DISTINCT maker 
                  FROM product AS a 
                  INNER JOIN (SELECT DISTINCT model 
                              FROM pc 
                              WHERE speed>=750) AS b  ON a.model=b.model)
        AND maker IN (SELECT DISTINCT maker 
                      FROM product AS a 
                      INNER JOIN (SELECT DISTINCT model 
                                  FROM laptop 
                                  WHERE speed>=750) AS b ON a.model=b.model)