#从 Customers 表中检索所有的 ID（cust_id）
select cust_id from Customers；

/*检索并列出已订购产品（prod_id）的清单*/
select distinct prod_id from OrderItems；

#编写 SQL语句，检索 Customers 表中所有的列，再编写另外的 SELECT语句，仅检索顾客的 ID
/*select *from Customers */select cust_id from Customers；

#从 Customers 中检索所有的顾客名称（cust_names），并按从 Z 到 A 的顺序显示结果
select cust_names from Customers order by cust_names desc；

#从 Orders 表中检索顾客 ID（cust_id）和订单号（order_num），并先按顾客 ID 对结果进行排序，再按订单日期倒序排列
select cust_id,order_num from Orders order by cust_id，order_num desc；

#OrderItems 表中的数量和价格（item_price），并按数量由多到少、价格由高到低排序
select quantity,item_price from OrderItems order by quantity desc，item_price desc；

#从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 9.49 美元的产品
select prod_id,prod_name from Products where prod_price=9.49；

#从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 9 美元或更高的产品
select prod_id,prod_name from Products where prod_price>=9；

#从 OrderItems 表中检索出所有不同订单号（order_num），其中包含 100 个或更多的产品
select distinct order_num from OrderItems where order_num>=100；

#Products 表中所有价格在 3 美元到 6 美元之间的产品的名称（prod_name）和价格（prod_price），然后按价格对结果进行排序
select prod_name,prod_price from Products where prod_price between 3 and 6 order byprod_price asc；

#从 Vendors 表中检索供应商名称（vend_name），仅返回加利福尼亚州的供应商（这需要按国家[USA]和州[CA]进行过滤，没准其他国家也存在一个加利福尼亚州）
select vend_name from Vendors where vend_country="USA" and vend_state="CA"；

这里开始关键字都用大写字母书写，规范化书写习惯

#查找所有至少订购了总量 100 个的 BR01、BR02 或BR03 的订单。你需要返回 OrderItems 表的订单号（order_num）、产品 ID（prod_id）和数量，并按产品 ID 和数量进行过滤
SELECT order_num，prod_id，quantity FROM OrderItems WHERE quantity=100 AND prod_id IN("BR01","BR02","BR03")；

#从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中包含 toy 一词的产品
SELECT prod_name,prod_desc FROM Products WHERE prod_desc LIKE '%toy%'；

#编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品。这次，按产品名称对结果进行排序
SELECT prod_name,prod_desc FROM Products WHERE prod_desc NOT LIKE '%toy%' ORDER BY prod_name；

#从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中同时出现 toy 和 carrots 的产品
SELECT prod_name,prod_desc FROM Products WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE '%carrots%'；

#编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现 toy 和 carrots 的产品
SELECT prod_name,prod_desc FROM Products WHERE prod_desc LIKE '%toy%carrots%' ；

#从 Vendors 表中检索vend_id、vend_name、vend_address 和 vend_city，将 vend_name重命名为 vname，将 vend_city 重命名为 vcity，将 vend_address重命名为 vaddress。按供应商名称对结果进行排序
SELECT vend_id,vend_address AS vaddress,vend_city AS vcity,vend_name AS  vname FROM Vendors ORDER BY vname；

#从 Products 表中返回 prod_id、prod_price 和 sale_price。sale_price 是一个包含促销价格的计算字段（打折促销，所有产品均降价 10%）
SELECT prod_id,prod_price*0.8,sale_price FROM Products；

略有难度#编写 SQL 语句，返回顾客 ID（cust_id）、顾客名称（customer_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成
SELECT cust_id,ustomer_name,UPPER('LEFT(cust_contact,3)'+'LEFT(cust_city,3)') AS user_login FROM customers；

略有难度#编写 SQL 语句，返回 2020 年 1 月的所有订单的订单号（order_num）和订单日期（order_date），并按订单日期排序
SELECT order_num,order_date FROM orders WHERE YEAR(order_date)=2020 AND MONTH(order_date)=01 ORDER BY DAY(order_date);

#编写 SQL 语句，确定已售出产品的总数（使用 OrderItems 中的quantity 列）
SELECT COUNT(quantity) FROM OrderItems;

#修改刚刚创建的语句，确定已售出产品项（prod_item）BR01 的总数
SELECT SUM(quantity) FROM OrderItems WHERE prod_item='BR01';

#编写 SQL 语句，确定 Products 表中价格不超过 10 美元的最贵产品的价格（prod_price）。将计算所得的字段命名为 max_price
SELECT MAX(prod_price) AS max_price FROM Products WHERE prod_price<=10;

#OrderItems 表包含每个订单的每个产品。编写 SQL 语句，返回每个订单号（order_num）各有多少行数（order_lines），并按 order_lines对结果进行排序
SELECT order_num,COUNT(*) AS order_lines FROM OrderItems ORDER BY order_lines;

#编写 SQL 语句，返回名为 cheapest_item 的字段，该字段包含每个供应商成本最低的产品（使用 Products 表中的 prod_price），然后从最低成本到最高成本对结果进行排序
SELECT MIN(prod_price) AS cheapest_item FROM Products ORDER BY cheapest_item;
#请编写 SQL 语句，返回至少含 100 项的所有订单的订单号（OrderItems 表中的 order_num）
SELECT order_num FROM OrderItems GROUP BY order_num HAVING COUNT(items)>=100;

#编写 SQL 语句，返回总价至少为 1000 的所有订单的订单号（OrderItems 表中的order_num））。提示：需要计算总和（item_price 乘以 quantity）。按订单号对结果进行排序。
SELECT order_num FROM OrderItems GROUP BY order_num HAVING SUM(item_price*quantity) >=1000 ORDER BY order_num;

#使用子查询，返回购买价格为 10 美元或以上产品的顾客列表。你需要使用 OrderItems 表查找匹配的订单号（order_num），然后使用Order 表检索这些匹配订单的顾客 ID（cust_id）
select cust_name, cust_contact from customers where cust_id in(SELECT cust_id FROM Order WHERE order_num IN (SELECT order_num from OrderItems WHERE order_pirce>=10 ));

#你想知道订购 BR01 产品的日期。编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 BR01 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date）。按订购日期对结果进行排序。
SELECT cust_id,order_date FROM Orders WHERE order_num IN(SELECT order_num FROM OrderItems WHERE prod_id='BR01') ORDER BY order_date;

#在上一个挑战题，返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email）。提示：这涉及 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id
SELECT prod_id FORM Customers WHERE cust_id IN(SELECT cust_id FROM Orders WHERE order_num IN(SELECT order_num FROM OrderItems WHERE prod_id='BR01') );

#需要一个顾客 ID 列表，其中包含他们已订购的总金额。编写 SQL语句，返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回total_ordered 以便返回每个顾客的订单总数。将结果按金额从大到小排序。提示：你之前已经使用 SUM()计算订单总数。
SELECT cust_id,(SELECT SUM(item_price*quantity) FROM OrderItems WHERE Orders.order_num=OrderItems.order_num) AS total_pirce FROM Orders ORDER BY total_pirce DESC;

#编写 SQL 语句，从 Products 表中检索所有的产品名称（prod_name），以及名为 quant_sold 的计算列，其中包含所售产品的总数（在 OrderItems 表上使用子查询和 SUM(quantity)检索）
SELECT prod_name,(SELECT SUM(quantity) FROM OrderItems  WHERE OrderItems.prod_id=Products.prod_id)  AS quant_sold FROM Products;

#编写 SQL 语句，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），并 按顾客名称再按订单号对结果进行排序。实际上是尝试两次，一次使用简单的等联结语法，一次使用 INNER JOIN
SELECT cust_name,order_num FROM Customers INNER JOIN Orders ON Customers.cust_id=Orders.cust_id ORDER BY cust_name,order_num;
SELECT cust_name,order_num FROM customers, orders WHERE customers.cust_id= orders.cust_id ORDER BY cust_name,order_num;

#除了返回顾客名称和订单号，添加第三列 OrderTotal，其中包含每个订单的总价。有两种方法可以执行：使用 OrderItems 表的子查询来创建 OrderTotal 列，或者将 OrderItems 表与现有表联结并使用聚合函数。提示：请注意需要使用完全限定列名的地方。
SELECT cust_name,order_num,SUM(item_price*quantity) AS OrderTotal FROM Customers,Orders,OrderItems WHERE Customers.cust_id=Orders.cust_id AND Orders.order_num=OrderItems.order_num ORDER BY cust_name,order_num

#重新创建为第 11 课挑战题 3 编写的 SQL 语句，这次使用 ANSI 的 INNER JOIN 语法。在之前编写的代码中使用了两个嵌套的子查询。要重新创建它，需要两个 INNER JOIN 语句，每个语句的格式类似于本课讲到的 INNER JOIN 示例，而且不要忘记WHERE 子句可以通过 prod_id 进行过滤。
SELECT customers.cust_id, cust_email FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id INNER JOIN orderitems ON orders.order_num=orderitems.order_num WHERE prod_id="BR01";

#混合使用联结、聚合函数和分组。回到第 10 课，当时的挑战是要求查找值等于或大于 1000的所有订单号。这些结果很有用，但更有用的是订单数量至少达到这个数的顾客名称。因此，编写 SQL 语句，使用联结从 Customers表返回顾客名称（cust_name），并从 OrderItems 表返回所有订单的总价提示：要联结这些表，还需要包括 Orders 表（因为 Customers 表与 OrderItems 表不直接相关，Customers 表与 Orders 表相关，而Orders 表与 OrderItems 表相关）。不要忘记 GROUP BY 和 HAVING，并按顾客名称对结果进行排序。你可以使用简单的等联结或 ANSI 的INNER JOIN 语法。或者，如果你很勇敢，请尝试使用两种方式编写
SELECT cust_name,SUM(item_price * quantity) AS OrderTotal FROM customers,orders,orderitems WHERE Customers.cust_id=Orders.cust_id AND Orders.order_num=OrderItems.order_num GROUP BY order_num HAVING OrderTotal >= 1000 ORDER BY order_num;
SELECT cust_name,SUM(item_price * quantity) AS OrderTotal FROM customers INNER JOIN Orders ON Customers.cust_id=Orders.cust_id INNER JOIN orderitems ON Orders.order_num=OrderItems.order_num GROUP BY order_num HAVING OrderTotal >= 1000 ORDER BY order_num;

#使用 INNER JOIN 编写 SQL语句，以检索每个顾客的名称（Customers表中的 cust_name）和所有的订单号（Orders 表中的 order_num）
SELECT cust_name,order_num FROM Customers INNER JOIN Orders ON customers.cust_id=orders.cust_id;

#修改刚刚创建的 SQL 语句，仅列出所有顾客，即使他们没有下过订单。
SELECT cust_name,order_num FROM Customers LEFT OUTER JOIN Orders ON customers.cust_id=orders.cust_id;

#使用 OUTER JOIN 联结 Products 表和 OrderItems 表，返回产品名称（prod_name）和与之相关的订单号（order_num）的列表，并按商品名称排序.
SELECT prod_name,order_num FROM Products LEFT OUTER JOIN OrderItems ON products.prod_id = orderitems.prod _id ORDER BY prod_name;

#修改上一题中创建的 SQL 语句，使其返回每一项产品的总订单数（不是订单号）
SELECT prod_name,COUNT(order_num) FROM Products LEFT OUTER JOIN OrderItems ON products.prod_id = orderitems.prod _id GROUP BY prod_name ORDER BY prod_name;

#编写 SQL语句，列出供应商（Vendors 表中的 vend_id）及其可供产品的数量，包括没有产品的供应商。你需要使用 OUTER JOIN 和 COUNT()聚合函数来计算 Products 表中每种产品的数量。注意：vend_id 列会显示在多个表中，因此在每次引用它时都需要完全限定它。
SELECT vend_id,COUNT(prod_id) FROM Vendors LEFT OUTER JOIN Products ON Vendors.vend_id=Products.vend_id GROUP BY Vendors.vend_id;

#编写 SQL 语句，将两个 SELECT 语句结合起来，以便从 OrderItems表中检索产品 ID（prod_id）和 quantity。其中，一个 SELECT 语句过滤数量为 100 的行，另一个 SELECT 语句过滤 ID 以 BNBG 开头的产品。按产品 ID 对结果进行排序
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;

#重写刚刚创建的 SQL 语句，仅使用单个 SELECT 语句。 
SELECT prod_id,quantity FROM OrderItems WHERE quantity=100 OR prod_id LIKE 'BNBG%' ORDER BY prod_id;

#编写 SQL 语句，组合 Products 表中的产品名称（prod_name）和 Customers 表中的顾客名称（cust_name）并返回，然后按产品名称对结果进行排序。
SELECT prod_name FROM Products UNION SELECT cust_name FROM Customers ORDER BY prod_name;
SELECT cust_name, prod_name FROM customers, orders, orderitems,products WHERE customers.cust_id=orders.cust_id AND orderitems.order_num = orders.order_num AND products.prod_id = orderitems.prod_id; 
 

#使用 INSERT 和指定的列，将你自己添加到 Customers 表中。明确列出要添加哪几列，且仅需列出你需要的列
INSERT INTO Customers() VALUES();

#备份 Orders 表和 OrderItems 表
CREATE TABLE ORDER_ITEMS AS SELECT DISTINCT(*) FROM Orders,OrderItems WHERE();
CREATE TABLE ORDER_ITEMS AS SELECT DISTINCT(*) FROM Orders LEFT OUTER JOIN OrderItems WHERE() ;

# 美国各州的缩写应始终用大写。编写 SQL语句来更新所有美国地址，包括供应商状态（Vendors 表中的 vend_state）和顾客状态（Customers表中的 cust_state），使它们均为大写
UPDATE Vendors SET vend_state=UPPER(vend_state);
UPDATE Customers SET cust_state=UPPER(cust_state);

#第 15 课的挑战题 1 要求你将自己添加到 Customers 表中。现在请删除自己。确保使用 WHERE 子句（在 DELETE 中使用它之前，先用 SELECT对其进行测试），否则你会删除所有顾客
SELECT * FROM Customers WHERE cust_id='me';
DELETE FROM Customers WHERE cust_id='me';

#在 Vendors 表中添加一个网站列（vend_web）。你需要一个足以容纳URL 的大文本字段
ALTER TABLE Vendors ADD vend_web varchar(50);

#使用 UPDATE 语句更新 Vendor 记录，以便加入网站（你可以编造任何地址）
UPDATE Vendors SET vend_web='baidu.com' WHERE vend_id=1022；

#创建一个名为 CustomersWithOrders 的视图，其中包含 Customers表中的所有列，但仅仅是那些已下订单的列。提示：可以在 Orders表上使用 JOIN 来仅仅过滤所需的顾客，然后使用 SELECT 来确保拥有正确的数据
CREATE VIEW CustomersWithOrders AS SELECT * FROM Customers INNER JOIN Orders ON Customers.cust_id=Orders.cust_id;