?? 1、專欄介紹

「SQL面試題庫」是由 不是西紅柿 發(fā)起，全員免費參與的SQL學(xué)習(xí)活動。我每天發(fā)布1道SQL面試真題，從簡單到困難，涵蓋所有SQL知識點，我敢保證只要做完這100道題，不僅能輕松搞定面試，代碼能力和工作效率也會有明顯提升。

1.1 活動流程

1. 整理題目：西紅柿每天無論刮風(fēng)下雨，保證在8am 前，更新一道新鮮SQL面試真題。
2. 粉絲打卡：粉絲們可在評論區(qū)寫上解題思路，或者直接完成SQL代碼，有困難的小伙伴不要著急，先看別人是怎么解題的，邊看邊學(xué)，不懂就問我。
3. 交流討論：為了方便交流討論，可進(jìn)入 數(shù)據(jù)倉庫 。
4. 活動獎勵：我每天都會看評論區(qū)和群里的內(nèi)容，對于積極學(xué)習(xí)和熱心解答問題的小伙伴，紅包鼓勵，以營造更好的學(xué)習(xí)氛圍。

1.2 你的收獲

1. 增強(qiáng)自信，搞定面試：在求職中，SQL是經(jīng)常遇到的技能點，而這些題目也多數(shù)是真實的面試題，刷題可以讓我們更好地備戰(zhàn)面試，增強(qiáng)自信，提升自己的核心競爭力。

2. 鞏固SQL語法，高效搞定工作：通過不斷練習(xí)，能夠熟悉SQL的語法和常用函數(shù)，掌握SQL核心知識點，提高SQL編寫能力。代碼能力提升了，工作效率自然高了。

3. 提高數(shù)據(jù)處理能力、鍛煉思維能力：SQL是數(shù)據(jù)處理的核心工具，通過刷題可以讓我們更好地理解數(shù)據(jù)處理的過程，提高數(shù)據(jù)分析的效率。SQL題目的難度不一，需要在一定時間內(nèi)解決問題，培養(yǎng)了我們對問題的思考能力、解決問題的能力和對時間的把控能力等。

?? 2、今日真題

題目介紹： Fix Product Name Format fix-product-name-format

難度簡單

SQL架構(gòu)

Table:

Sales

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| sale_id      | int     |
| product_name | varchar |
| sale_date    | date    |
+--------------+---------+
sale_id is the primary key for this table.
Each row of this table contains the product name and the date it was sold.

Since table Sales was filled manually in the year 2000,

product_name

may contain leading and/or trailing white spaces, also they are case-insensitive.

Write an SQL query to report

• product_name
  

  in lowercase without leading or trailing white spaces.

• sale_date
  

  in the format

  ('YYYY-MM')
  

• total
  

  the number of times the product was sold in this month.

Return the result table ordered by

product_name

in ascending order, in case of a tie order it by

sale_date

in ascending order.

The query result format is in the following example.

``` Sales +------------+------------------+--------------+ | sale_id | product_name | sale_date | +------------+------------------+--------------+ | 1 | LCPHONE | 2000-01-16 | | 2 | LCPhone | 2000-01-17 | | 3 | LcPhOnE | 2000-02-18 | | 4 | LCKeyCHAiN | 2000-02-19 | | 5 | LCKeyChain | 2000-02-28 | | 6 | Matryoshka | 2000-03-31 | +------------+------------------+--------------+

Result table: +--------------+--------------+----------+ | product_name | sale_date | total | +--------------+--------------+----------+ | lcphone | 2000-01 | 2 | | lckeychain | 2000-02 | 2 | | lcphone | 2000-02 | 1 | | matryoshka | 2000-03 | 1 | +--------------+--------------+----------+

In January, 2 LcPhones were sold, please note that the product names are not case sensitive and may contain spaces. In Februery, 2 LCKeychains and 1 LCPhone were sold. In March, 1 matryoshka was sold. ```

sql
select trim(lower(product_name)) as product_name, 
        date_format(sale_date,'%Y-%m') as sale_date,
        count(*) as total 
from Sales 
group by trim(lower(product_name)), date_format(sale_date,'%Y-%m') 
order by product_name asc, sale_date asc

注意大小寫、空格 文章來源地址http://www.zghlxwxcb.cn/news/detail-539929.html

• 已經(jīng)有靈感了？在評論區(qū)寫下你的思路吧！