https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=298
💖牛客题目, SQL必知必会。不断熟悉,不断进步,加油!👍 💬 ⭐️
SQL题目
一、高级联接
101. inner join on
检索每个顾客的名称和所有的订单号一
SELECT a.cust_name, b.order_num
FROM Customers a INNER JOIN Orders b ON a.cust_id = b.cust_id
ORDER BY a.cust_name
102. right / left join on
检索每个顾客的名称和所有的订单号二
(列出所有顾客,即使顾客没下过单)
SELECT b.cust_name, a.order_num
FROM Orders a RIGHT JOIN Customers b ON a.cust_id = b.cust_id
ORDER BY cust_name
103. outer join
MySQL 没有 outer join, 用 union 左右外连接
SELECT prod_name, order_num
FROM Products a LEFT JOIN OrderItems b ON a.prod_id = b.prod_id
UNION
SELECT prod_name, order_num
FROM OrderItems a RIGHT JOIN Products b ON a.prod_id = b.prod_id
ORDER BY prod_name
104. 联表+计算
返回产品名称和每一项产品的总订单数
SELECT prod_name, COUNT(order_num) orders
FROM Products a LEFT JOIN OrderItems b ON a.prod_id = b.prod_id
GROUP BY prod_name
ORDER BY prod_name
105. 同上题
SELECT a.vend_id , COUNT(prod_id) prod_id
FROM Vendors a LEFT JOIN Products b ON a.vend_id = b.vend_id
GROUP BY vend_id
ORDER BY vend_id
二、组合查询
106. UNION 的使用
将两个 SELECT 语句结合起来一
SELECT prod_id, quantity
FROM OrderItems WHERE quantity = 100
UNION
SELECT prod_id, quantity
FROM OrderItems WHERE prod_id like 'BNBG%'
ORDER BY prod_id
UNION 会自动去重, UNION ALL 不去重
107.
将两个 SELECT 语句结合起来二
SELECT * FROM OrderItems WHERE quantity = 100 OR prod_id like 'BNBG%'
108. UNION
组合 Products 表中的产品名称(prod_name)和 Customers 表中的顾客名称(cust_name)并返回,然后按产品名称对结果进行升序排序。
SELECT prod_name a FROM Products
UNION
SELECT cust_name a FROM Customers
ORDER BY a
108. SQL 纠错
用 UNION 最后一个ORDER BY, 不能每个SELECT 都有自己的一个 ORDER BY.
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'MI'
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'IL'
ORDER BY cust_name;