子查询过滤

1.查询包含物品TNT2的所有订单的编号
2.查询具有前一步骤列出的订单编号的所有客户ID

SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

子查询作为计算字段

1.从customers表中查询客户列表
2.对于查询出的每个客户，统计其在orders表中的订单数目

SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;