Geek asks and answers

Mind teaser sql

I am trying to answer the questions found at http://www.sql-ex.ru/learn_exercises.php#answer_ref recently, as I am new to sql, as you can see from my previous sql posts. I am through this question:

Exercise: 23 Find creators who produce at least both a computer with a speed of at least 750 MHz and a laptop with a speed of at least 750 MHz. Results Set: Maker

Below is my answer, which is incorrect since it uses or. can someone direct me to a good direction, either by providing me with a link where to look, or which connection 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
+3
source share
3 answers

, ( ), :

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

> 750 > 750, .

+3

INTERSECT:

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

:

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
+4
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)
0
source

More articles:


All Articles