第2课-检索数据
2.9 挑战题
1、编写SQL语句，从Customers表中检索所有的ID（cust_id）
SELECT cust_id from Customers
正确答案：
SELECT cust_id
FROM Customers;
2、
SELECT DISTINCT prod_id from OrderItems
正确答案：
SELECT DISTINCT prod_id
FROM OrderItems;
3、
#SELECT * from Customers
SELECT cust_id from Customers
正确答案：
SELECT *
# SELECT cust_id
FROM Customers;
第3课-排序检索数据
1、
SELECT cust_name from Customers ORDER BY cust_name DESC;
正确答案：
SELECT cust_name
FROM Customers
ORDER BY cust_name DESC;
2、
SELECT cust_id, order_num FROM Orders
ORDER BY cust_id, order_num DESC;（这里应该是按订单日期来进行排序）
正确答案：
SELECT cust_id, order_num
FROM Orders
ORDER BY cust_id, order_date DESC;
3、
SELECT number,item_price FROM OrderItems
ORDER BY number DESC,item_price DESC;
正确答案：
SELECT quantity, item_price
FROM OrderItems
ORDER BY quantity DESC, item_price DESC;
4、题目四回答正确
第4课-过滤数据
1、
SELECT prod_id, prod_name FROM Products
WHERE prod_price=9.49;
正确答案：
SELECT prod_id, prod_name
FROM Products
WHERE prod_price = 9.49;
2、
SELECT prod_id, prod_name FROM Products
WHERE prod_price >=9.49;
正确答案：
SELECT prod_id, prod_name
FROM Products
WHERE prod_price >= 9;
3、
SELECT DISTINCT order_num FROM OrderItems
WHERE number >=100;
正确答案：
SELECT DISTINCT order_num
FROM OrderItems
WHERE quantity >=100;
4、
SELECT prod_name, prod_price FROM Products
WHERE prod_price BETWEEN 3 AND 6
ORDER BY prod_price DESC;
正确答案：
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 3 AND 6
ORDER BY prod_price;
第5课-高级数据过滤
1、
SELECT vend_name FROM Vendors
WHERE country="USA" AND state="CA";
正确答案：
SELECT vend_name
FROM Vendors
WHERE vend_country = 'USA' AND vend_state = 'CA';
2、
SELECT order_num, prod_id, number FROM OrderItems
WHERE number >=100 AND prod_id IN ('BR01','BR02','BR03')
正确答案：
-- Solution 1
SELECT order_num, prod_id, quantity
FROM OrderItems
WHERE (prod_id='BR01' OR prod_id='BR02' OR prod_id='BR03')
AND quantity >=100;
-- Solution 2
SELECT order_num, prod_id, quantity
FROM OrderItems
WHERE prod_id IN ('BR01','BR02','BR03')
AND quantity >=100;
第6课-用通配符进行过滤
1、
SELECT prod_name, prod_desc FROM Products
WHERE prod_desc Like '%toy%';
正确答案：
SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%';
2、
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 NOT prod_desc LIKE '%toy%'
ORDER BY prod_name;
3、
SELECT prod_name, prod_desc FROM Products
(WHERE prod_desc Like '%toy%')
AND
(WHERE prod_desc Like '%carrots%');
正确答案：
SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE '%carrots%';
4、
SELECT prod_name, prod_desc FROM Products
WHERE prod_desc Like '%toy%carrots%';
正确答案：
SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%carrots%';
