Mind teaser SQL

Question

我最近一直试图回答在http://www.sql-ex.ru/learn_exercises.php#answer_ref上发现的问题，因为我是sql方面的新手，从我以前关于sql的文章中可以看到这一点。我回答了这个问题：

练习: 23家制造商至少生产一台速度不低于750 MHz的pc和一台速度不低于750 MHz的笔记本电脑。结果集:制造者

以下是我的回答，这是不正确的，因为它使用或。有人可以给我一个链接搜索或者我应该使用什么连接来引导我找到一个好的方向吗？

  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

Answer 1

关闭，如果表和列定义正确(我没有该站点的登录名)，应该如下所示：

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的pc和一台带有速度>=750的笔记本电脑，这是由同一个人制造的。

Answer 2

假设支持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

Answer 3

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)