第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%';