实验5.4　嵌套查询

实验5.4　嵌套查询

【实验目的】

①掌握IN/NOT IN谓词的使用。

②掌握θ ALL/θ SOME/θ ANY谓词的使用。

③掌握EXISTS/NOT EXISTS谓词的使用。

【实验内容】

①查询通过居住在Duluth或Dallas的代理商订了货的顾客的cid值。

②查询通过居住在Duluth或Dallas的代理商订了货的顾客的名字和折扣。

③查询佣金百分率最小的代理商的aid值。

④查询与居住在Dallas 或Boston 的顾客拥有相同折扣的顾客的编号和名字。

⑤查询既订购了产品p01又订购了产品p07的顾客的cid值。

⑥查询没有通过代理商a05订货的所有顾客的名字。

⑦查询订购了所有被顾客c006订购的产品的顾客的cid值。

⑧查询订购同一产品至少两次的顾客的名字。

⑨查询折扣值小于最大折扣值的顾客的cid值。

⑩查询至少有两个顾客订购的产品。

【实验步骤】

（1）带有IN谓词的嵌套查询

查询通过居住在Duluth或Dallas的代理商订了货的顾客的cid值。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口输入下面的SQL语句：

SELECT DISTINCT cid FROM orders
WHERE aid IN (SELECT aid FROM agents
WHERE city = 'Duluth' OR city = 'Dallas');

③单击工具栏中的图标  或者按下快捷键“Ctrl+Enter”，执行上面的SQL语句。观察“Output”输出区域面板，如果提示信息前有绿色小钩，则说明语句执行成功，结果如图5.23所示。

（2）带有IN谓词的多层嵌套查询

查询通过居住在Duluth或Dallas的代理商订了货的所有顾客的名字和折扣。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口输入下面的SQL语句：

SELECT cname, discnt FROM customers
WHERE cid IN (SELECT cid FROM orders
WHERE aid IN (SELECT aid FROM agents
WHERE city IN ('Duluth','Dallas')));

③单击工具栏中的图标  或者按下快捷键“Ctrl+Enter”，执行上面的SQL语句。观察“Output”输出区域面板，如果提示信息前有绿色小钩，则说明语句执行成功，结果如图5.24所示。

图5.24　带有IN谓词的多层嵌套的查询结果

（3）带有θ ALL谓词的嵌套查询

查询佣金百分率最小的代理商的aid值。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口输入下面的SQL语句：

SELECT aid FROM agents
WHERE percent <= ALL (SELECT percent FROM agents);

③单击工具栏中的  图标或者按下快捷键“Ctrl+Enter”，执行上面的SQL语句。观察“Output”输出区域面板，如果提示信息前有绿色小钩，则说明语句执行成功，结果如图5.25所示。

（4）带有θ SOME谓词的嵌套查询

查询与居住在Dallas 或Boston 的顾客拥有相同折扣的顾客的编号和名字。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口输入下面的SQL语句：

SELECT cid, cname FROM customers
WHERE discnt = SOME (SELECT discnt FROM customers
WHERE city = 'Dallas' OR city='Boston');

③单击工具栏中的图标  或者按下快捷键“Ctrl+Enter”，执行上面的SQL语句。观察“Output”输出区域面板，如果提示信息前有绿色小钩，说明语句执行成功，结果如图5.26所示。

图5.26　带有θ SOME谓词的查询结果

（5）带有 EXISTS谓词的嵌套查询

查询既订购了产品p01又订购了产品p07的顾客的cid值。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口输入下面的SQL语句：(https://www.daowen.com)

SELECT DISTINCT cid FROM orders x
WHERE pid = 'p01' AND EXISTS (SELECT * FROM orders
WHERE cid = x.cid AND pid = 'p07');
或
SELECT DISTINCT cid FROM orders x
WHERE pid = 'p01' AND cid IN (SELECT cid FROM orders
WHERE pid = 'p07');

③单击工具栏中的  图标或者按下快捷键“Ctrl+Enter”，执行上面的SQL语句。观察“Output”输出区域面板，如果提示信息前有绿色小钩，说明语句执行成功。两种写法的执行结果分别如图5.27和图5.28所示。

说明：该题与实验5.3的第6题相同，但是采用了另外两种不同的查询方式。

（6）带有NOT EXISTS谓词的嵌套查询

查询没有通过代理商a05订货的顾客的名字。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口输入下面的SQL语句：

SELECT DISTINCT c.cname FROM customers c
WHERE NOT EXISTS (SELECT * FROM orders x
WHERE c.cid = x.cid AND x.aid = 'a05');

③单击工具栏中的图标  或者按下快捷键“Ctrl+Enter”，执行上面的SQL语句。观察“Output”输出区域面板，如果提示信息前有绿色小钩，说明语句执行成功，结果如图5.29所示。

图5.29　带有NOT EXISTS谓词的查询结果

（7）带有NOT EXISTS谓词的多层嵌套查询

查询订购了所有被顾客c006订购的产品的顾客的cid值。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口输入下面的SQL语句：

SELECT c.cid FROM customers c
WHERE NOT EXISTS
        (SELECT pid FROM orders x WHERE x.cid = 'c006' AND
            NOT EXISTS (SELECT * FROM orders y
                WHERE x.pid = y.pid AND y.cid = c.cid));

③单击工具栏中的  图标或者按下快捷键“Ctrl+Enter”，执行上面的SQL语句。观察“Output”输出区域面板，如果提示信息前有绿色小钩，说明语句执行成功，结果如图5.30所示。

（8）FROM 后使用子查询

查询订购同一产品至少两次的顾客的名字。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口输入下面的SQL语句：

SELECT DISTINCT cname
FROM (SELECT o.cid AS spcid FROM orders o, orders x
        WHERE o.cid = x.cid AND o.pid = x.pid
            AND o.ordno <> x.ordno) AS y, customers c
            WHERE y.spcid = c.cid;

③单击工具栏中的  图标或者按下快捷键“Ctrl+Enter”，执行上面的SQL语句。观察“Output”输出区域面板，如果提示信息前有绿色小钩，说明语句执行成功。结果如图5.31所示。

（9）带有聚合函数的嵌套查询

查询折扣值小于最大折扣值的顾客的cid值。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口输入下面的SQL语句：

SELECT cid FROM customers
WHERE discnt < (SELECT MAX(discnt) FROM customers);

③单击工具栏中的图标  或者按下快捷键“Ctrl+Enter”，执行上面的SQL语句。观察“Output”输出区域面板，如果提示信息前有绿色小钩，说明语句执行成功。结果如图5.32所示。

图5.32　带有聚合函数的查询结果

（10）采用比较运算符的嵌套查询

查询至少有两个顾客订购的产品。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口输入下面的SQL语句：

SELECT pid FROM products p
WHERE 2 <= (SELECT COUNT(DISTINCT cid)
        FROM orders WHERE pid = p.pid);

③单击工具栏中的  图标或者按下快捷键“Ctrl+Enter”，执行上面的SQL语句。观察“Output”输出区域面板，如果提示信息前有绿色小钩，说明语句执行成功，结果如图5.33所示。