SQL training camp -- Task02: SQL basic query and sorting

This note is the learning content of Alibaba cloud Tianchi Longzhu plan SQL training camp. The link is: https://tianchi.aliyun.com/specials/promotion/aicampsql

preface

This note is the learning content of Alibaba cloud Tianchi Longzhu plan SQL training camp. The link is: https://tianchi.aliyun.com/specials/promotion/aicampsql

1. Part I exercise questions and answers

Exercise 1

Write an SQL statement and select the goods with "registration date (register after April 28, 2009)" from the product table. The query result should include two columns: product_name and register_date.

SELECT product_name, regist_date
FROM product
WHERE regist_date > '2009-04-28';

Exercise 2

Please state the returned results when executing the following three SELECT statements on the product table.

SELECT *
FROM product
WHERE purchase_price = NULL;
SELECT *
  FROM product
 WHERE purchase_price <> NULL;
SELECT *
  FROM product
 WHERE product_name > NULL;

Answer: the above three statements are wrong. You cannot use comparison operators to test NULL values, such as =, <, or < >.

The IS NULL and IS NOT NULL operators must be used.

Exercise 3

The SELECT statement in code listing 2-22 (section 2-2) can extract the ratio of sales price to purchase price from the product table
price) is more than 500 yen. Please write two SELECT statements that can get the same result. The execution results are as follows.

product_name | sale_price | purchase_price 
-------------+------------+------------
T shirt         |   1000    | 500
 motion T Shirt       |    4000    | 2800
 pressure cooker        |    6800    | 5000
  • Method 1:
SELECT product_name, sale_price, purchase_price
 FROM product
 WHERE sale_price >= purchase_price + 500;
  • Method 2:
SELECT product_name, sale_price, purchase_price
 FROM product
 WHERE sale_price - 500 >= purchase_price;

Exercise 4

Please write a SELECT statement and SELECT the records from the product table that meet the conditions of "office supplies and kitchenware with a profit of more than 100 yen after 10% discount on the sales unit price". The query results should include the product_name column, the product_type column and the profit after 10% discount on the sales unit price (the alias is set to profit).

Tip: a 10% discount on the sales unit price can be obtained by multiplying the value of saleprice column by 0.9, and the profit can be obtained by subtracting the value of purchase_price column from this value.

SELECT product_name, product_type,
sale_price * 0.9 - purchase_price AS profit
FROM product
WHERE sale_price * 0.9 - purchase_price > 100
AND ( product_type = 'Office supplies'
OR product_type = 'Kitchen utensils');

2. Part II exercise questions and answers

Exercise 5

Please indicate all syntax errors in the following SELECT statement.

SELECT product_id, SUM(product_name)
--book SELECT There is an error in the statement.
 FROM product 
 GROUP BY product_type 
 WHERE regist_date > '2009-09-01';

There are three mistakes in this topic:
Error ①
The character field product_name cannot be aggregated in SUM
Error ②
The WHERE statement should be written before the GROUP BY statement (after the FROM statement)
Error ③
The GROUP BY field (product_type) is different from the SELECT field (product_id)

Note: the aggregate function must appear at the same time as GROUP BY

Exercise 6

Please write a SELECT statement to find the product category whose total sales unit price (sales_price column) is 1.5 times greater than the total purchase unit price (purchase_price column). The execution results are as follows.

product_type | sum  | sum 
-------------+------+------
clothes         | 5000 | 3300
 Office Supplies      |  600 | 320

SELECT product_type, SUM(sale_price), SUM(purchase_price)
FROM product
GROUP BY product_type
HAVING SUM(sale_price) > SUM(purchase_price) * 1.5;

Exercise 7

Previously, we used the SELECT statement to SELECT all records in the product table. At that time, we used the order by clause to specify the sorting order, but now we can't remember how to specify it. Please think about the contents of the order by clause according to the following execution results.

SELECT *
FROM product
ORDER BY regist_date DESC, sale_price;

Tags: Database MySQL SQL

Posted on Wed, 24 Nov 2021 12:47:59 -0500 by enchance