【学习笔记-SQL必知必会】课后挑战题代码

--第四课挑战题
SELECT prod_id,prod_name
FROM Products
WHERE prod_price=9.49;

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

SELECT DISTINCT order_num
FROM OrderItems
WHERE quantity>=100;

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

--第五课挑战题
SELECT vend_name
FROM Vendors
WHERE vend_country='USA' AND vend_state='CA';

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

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

--第六课挑战题
SELECT prod_name,prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%';

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


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

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

--第七课挑战题
SELECT vend_id,
       vend_name AS vname,  
	   vend_city AS vcity,
	   vend_address AS vaddress
FROM Vendors
ORDER BY vname;

SELECT prod_id,
       prod_price,
	   prod_price*0.9 AS sales_price
FROM Products;

--第八课挑战题
SELECT cust_id,cust_name,
       UPPER(LEFT(cust_contact,2)+LEFT(cust_city,3)) AS user_login
FROM Customers;

SELECT order_num,order_date
FROM Orders
WHERE DATEPART(yy,order_date)=2020 AND  DATEPART(mm,order_date)=1
ORDER BY order_date;


--第九课挑战题
SELECT SUM(quantity) AS items_ordered
FROM OrderItems;

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

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

--第十课挑战题
SELECT order_num,COUNT(*) AS order_lines
FROM OrderItems
GROUP BY order_num
ORDER BY order_lines;

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

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

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;

--第十一课挑战题
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
                    FROM OrderItems 
					WHERE item_price>=10);

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

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'));

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

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

--第十二课挑战题
--使用等联结方法
SELECT cust_name,order_num
FROM Customers,Orders
WHERE Customers.cust_id = Orders.cust_id
ORDER BY cust_name,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,
      (SELECT SUM(item_price*quantity)
	   FROM OrderItems
	   WHERE OrderItems.order_num = Orders.order_num) AS OrderTotal
FROM Customers,Orders
WHERE Customers.cust_id = Orders.cust_id
ORDER BY cust_name,order_num;

SELECT cust_id,order_date
FROM Orders,OrderItems
WHERE Orders.order_num = OrderItems.order_num
AND prod_id='BR01'
ORDER BY order_date;

--使用等值联结查询
SELECT cust_email
FROM Customers,OrderItems,Orders
WHERE Customers.cust_id = Orders.cust_id
AND Orders.order_num = OrderItems.order_num
AND prod_id = 'BR01';

--使用内联结查询
SELECT 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';

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