SQL 题目——必知必会

本文为牛客网SQL必知必会篇题目的题解，刷题地址为SQL必知必会

SQL1 从 Customers 表中检索所有的 ID

SELECT cust_id
FROM Customers;

SQL2 检索并列出已订购产品的清单

SELECT DISTINCT prod_id
FROM OrderItems;

SQL3 检索所有列

SELECT cust_id, cust_Name
FROM Customers;

SQL4 检索顾客名称并且排序

SELECT cust_name
FROM Customers
ORDER BY cust_name DESC;

SQL5 对顾客ID和日期排序

SELECT cust_id, order_num
FROM Orders
ORDER BY cust_id, order_date DESC;

SQL6 按照数量和价格排序

SELECT quantity, item_price
FROM OrderItems
ORDER BY quantity DESC, item_price DESC;

SQL7 检查SQL语句

SELECT vend_name
FROM Vendors
ORDER BY vend_name DESC;

SQL8 返回固定价格的产品

SELECT prod_id, prod_name
FROM Products
WHERE prod_price = 9.49;

SQL9 返回更高价格的产品

SELECT prod_id, prod_name
FROM Products
WHERE prod_price >= 9;

SQL10 返回产品并且按照价格排序

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 3 AND 6;

SQL11 返回更多的产品

SELECT order_num
FROM OrderItems
GROUP BY order_num
HAVING sum(quantity) >= 100;

SQL12 检索供应商名称

SELECT vend_name
FROM Vendors
WHERE vend_country = 'USA' AND vend_state = 'CA';

SQL13 检索并列出已订购产品的清单

SELECT order_num, prod_id, quantity
FROM OrderItems
WHERE prod_id IN ('BR01', 'BR02', 'BR03') AND quantity >= 100;

SQL14 返回所有价格在 3美元到 6美元之间的产品的名称和价格

SELECT prod_name, prod_price 
FROM Products
WHERE prod_price >= 3 AND prod_price <= 6
ORDER BY prod_price;

SQL15 纠错2

SELECT vend_name 
FROM Vendors 
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name;

SQL16 检索产品名称和描述（一）

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%';

SQL17 检索产品名称和描述（二）

SELECT prod_name, prod_desc 
FROM Products
WHERE prod_desc NOT LIKE '%toy%';

SQL18 检索产品名称和描述（三）

SELECT prod_name, prod_desc 
FROM Products
WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE '%carrots%'; 

SQL19 检索产品名称和描述（四）

SELECT prod_name, prod_desc 
FROM Products
WHERE prod_desc LIKE '%toy%carrots%';

SQL20 别名

SELECT vend_id, vend_name AS vname, vend_address AS vaddress, vend_city AS vcity 
FROM Vendors
ORDER BY vname;

SQL21 打折

SELECT prod_id, prod_price, 0.9 * prod_price AS sale_price
FROM Products

SQL22 顾客登录名

SELECT cust_id, cust_name, UPPER(CONCAT(LEFT(cust_contact, 2), LEFT(cust_city, 3))) AS user_login
FROM Customers;

SQL23 返回 2020 年 1 月的所有订单的订单号和订单日期

SELECT order_num, order_date
FROM Orders
WHERE YEAR(order_date) = 2020 AND MONTH(order_date) = 1
ORDER BY order_date;

SQL24 确定已售出产品的总数

SELECT SUM(quantity) AS items_ordered
FROM OrderItems;

SQL25 确定已售出产品项 BR01 的总数

SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE prod_id = 'BR01';

SQL26 确定 Products 表中价格不超过 10 美元的最贵产品的价格

SELECT MAX(prod_price) AS max_price
FROM Products
WHERE prod_price <= 10;

SQL27 返回每个订单号各有多少行数

SELECT order_num, COUNT(order_num) AS order_lines
FROM OrderItems
GROUP BY order_num
ORDER BY order_lines;

SQL28 每个供应商成本最低的产品

SELECT vend_id, MIN(prod_price) AS cheapest_item
FROM Products
GROUP BY vend_id
ORDER BY cheapest_item;

SQL29 返回订单数量总和不小于100的所有订单的订单号

SELECT order_num
FROM OrderItems
GROUP BY order_num
HAVING SUM(quantity) >= 100
ORDER BY order_num;

SQL30 计算总和

SELECT order_num, SUM(item_price * quantity) AS total_price
FROM OrderItems
GROUP BY order_num
HAVING SUM(item_price * quantity) >= 1000
ORDER BY order_num;

SQL31 纠错3

SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY order_num
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;

SQL32 返回购买价格为 10 美元或以上产品的顾客列表

SELECT DISTINCT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num FROM OrderItems WHERE item_price >= 10);

SQL33 确定哪些订单购买了 prod_id 为 BR01 的产品（一）

SELECT cust_id, order_date
FROM Orders
WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'BR01')
ORDER BY order_date;

SQL34 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一）

SELECT cust_email
FROM Customers
WHERE cust_id IN (
    SELECT cust_id
    FROM Orders
    WHERE order_num IN (
        SELECT order_num
        FROM OrderItems
        WHERE prod_id = 'BR01'
    )
);

SQL35 返回每个顾客不同订单的总金额

SELECT cust_id, (SELECT SUM(item_price * quantity) FROM OrderItems WHERE OrderItems.order_num = Orders.order_num) AS total_ordered
FROM Orders
Order BY total_ordered DESC;

SQL36 从 Products 表中检索所有的产品名称以及对应的销售总数

SELECT prod_name, (SELECT SUM(quantity) FROM OrderItems WHERE OrderItems.prod_id = Products.prod_id) AS quant_sold 
FROM Products;

SQL37 返回顾客名称和相关订单号

SELECT cust_name, order_num
FROM Customers, Orders
WHERE Customers.cust_id = Orders.cust_id
ORDER BY cust_name, order_num;

SQL38 返回顾客名称和相关订单号以及每个订单的总价

SELECT cust_name, Orders.order_num, (quantity * item_price) AS OrderTotal
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id AND Orders.order_num = OrderItems.order_num
ORDER BY cust_name, Orders.order_num;

SQL39 确定哪些订单购买了 prod_id 为 BR01 的产品（二）

SELECT cust_id, order_date
FROM Orders
WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'BR01')
ORDER BY order_date;

SQL40 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（二）

SELECT cust_email
FROM OrderItems, Orders, Customers
WHERE prod_id = 'BR01' AND OrderItems.order_num = Orders.order_num AND Orders.cust_id = Customers.cust_id; 

SQL41 确定最佳顾客的另一种方式（二）

SELECT cust_name, SUM(item_price * quantity) AS total_price
FROM OrderItems, Orders, Customers
WHERE OrderItems.order_num = Orders.order_num AND Orders.cust_id = Customers.cust_id
GROUP BY cust_name
HAVING SUM(item_price * quantity) >= 1000
ORDER BY total_price;

SQL42 检索每个顾客的名称和所有的订单号（一）

SELECT cust_name, order_num
FROM Customers, Orders
WHERE Customers.cust_id = Orders.cust_id
ORDER BY cust_name;

SQL43 检索每个顾客的名称和所有的订单号（二）

SELECT cust_name, order_num
FROM Customers LEFT JOIN Orders ON Customers.cust_id = Orders.cust_id
ORDER BY cust_name;

SQL44 返回产品名称和与之相关的订单号

SELECT prod_name, order_num
FROM Products LEFT JOIN OrderItems ON Products.prod_id = OrderItems.prod_id
ORDER BY prod_name;

SQL45 返回产品名称和每一项产品的总订单数

SELECT prod_name, COUNT(order_num)
FROM Products LEFT JOIN OrderItems ON Products.prod_id = OrderItems.prod_id
GROUP BY prod_name
ORDER BY prod_name;

SQL46 列出供应商及其可供产品的数量

SELECT Vendors.vend_id, COUNT(prod_id) AS prod_id
FROM Vendors LEFT JOIN Products ON Vendors.vend_id = Products.vend_id
GROUP BY Vendors.vend_id
ORDER BY Vendors.vend_id;

SQL47 将两个 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;

SQL48 将两个 SELECT 语句结合起来（二）

SELECT prod_id, quantity
FROM OrderItems
WHERE quantity = 100 OR prod_id LIKE 'BNBG%'
ORDER BY prod_id;

SQL49 组合 Products 表中的产品名称和 Customers 表中的顾客名称

(SELECT prod_name
FROM Products)
UNION
(SELECT cust_name
FROM Customers)
ORDER BY prod_name;

SQL50 纠错4

(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;