iT邦幫忙

1

在hackerrank上的SQL刷題記錄

sql

接續完全沒接觸過,一日自己摸索SQL的自學記錄
簡單了解語法後,直接嘗試挑戰hackerrank來熟悉操作

雖說基礎語法看起來蠻簡單的,實際刷了一下題目後,
有些語法第一次看也很難自己憑空想出來,
自己筆記一下

查詢語法的網站: SQL語法教學

select distinct- 去除重複的資料

題目: Weather Observation Station 3
資料表名稱: STATION
欄位: id, city, state, lat_n(最北邊緯度), long_w(最西邊經度)
要求: 取出id是偶數的city欄位,資料不得重複

select distinct city from station
where id%2 = 0

COUNT(distinct 欄位)- 不重複的資料有幾筆

題目: Weather Observation Station 4
資料表名稱: STATION
欄位: id, city, state, lat_n(最北邊緯度), long_w(最西邊經度)
要求: 回傳「所有城市數量」(可能重複)減去「不重複的城市數量」的值

一開始想起來覺得蠻困惑的,
查詢指令知道

select COUNT(CITY) from STATION

是「所有城市數量」

select COUNT(distinct CITY) from STATION

是「不重複的城市數量」

目前不知sql裡面是否有像其它程式語言有「變數」的用法,
想很久不知道怎麼合起來寫

參考答案:

select COUNT(CITY) - COUNT(distinct CITY) from STATION

order by- 排序, limit- 限制取出幾筆資料

題目: Weather Observation Station 5
資料表名稱: STATION
欄位: id, city, state, lat_n(最北邊緯度), long_w(最西邊經度)
要求: 回傳名字長度最小的city與長度,
及名字長度最大的city與長度
(若長度相等,回傳字典序最小的,題目內有範例)

解析: 這一題我也想了非常久,
比如說單純用select min(CITY) from STATION是不行的,
因為單純取min(CITY)只會取到字典序最小的字串,
不會取到「最短的」字串

第一個需要的指令可用order by,指定按什麼條件排序
(asc是遞增,desc是遞減)

譬如說用下列指令先看一下印的結果

SELECT CITY, LENGTH(CITY) 
FROM STATION 
ORDER BY LENGTH(CITY) asc, city asc

結果:

Amo 3 
Lee 3 
Roy 3 
Acme 4 
Alba 4 
Bono 4 
…
White Horse Beach 17 
West Baden Springs 18 
Marine On Saint Croix 21 

可以看到城市先按長度、其次按字母順序排了
可是我只要一筆資料(最小的那筆),不需要那麼多筆啊?
可以用limit指令表示取幾筆資料(參考:SQL LIMIT限制筆數)

參考解答(用「分號」區隔兩筆資料):

SELECT CITY, LENGTH(CITY) 
FROM STATION 
ORDER BY LENGTH(CITY) asc, city asc limit 1;

SELECT CITY, LENGTH(CITY) 
FROM STATION 
ORDER BY LENGTH(CITY) desc, city asc limit 1;

模糊比對- 萬用字元與like

題目: Weather Observation Station 6
資料表名稱: STATION
欄位: id, city, state, lat_n(最北邊緯度), long_w(最西邊經度)
要求: 回傳名字開頭為a,e,i,o,u的city,並且不得重複

當我們需要查詢符合某種形式的字串,可以用like,
通常搭配兩個特別的「萬用字元」使用

  • % (百分比符號):代表零個、一個、或數個字母。
  • _ (底線):代表剛好一個字母。

(參考資料: SQL 萬用字元)

這種查法有點像是說,我想要在字典裡查一個成語,
比如「馬到成功」,
如果我記得完整的成語,
便可以下指令「查詢 = "馬到成功"」

但是今天我如果不幸忘記我要查的成語叫什麼,
只有模糊的印象這個成語是 馬 _ 成功,
第二個字忘了,便可以下指令「查詢 = "馬 _ 成功"」,
用 _ (底線)取代你忘記的字

參考解答:

select distinct city from station 
where city like "a%"
or city like "e%"
or city like "i%"
or city like "o%"
or city like "u%"

一些語法化簡技巧(邏輯上的化簡、字串函數、正則表達式)

題目: Weather Observation Station 8
資料表名稱: STATION
欄位: id, city, state, lat_n(最北邊緯度), long_w(最西邊經度)
要求: 回傳名字開頭與結尾均為a,e,i,o,u的city,並且city不得重複

這一題跟上一題非常相似,
因為開頭與結尾都是a,e,i,o,u,共5*5=25種組合,
可以將上述程式展開寫25行

參考解答1:

select distinct city from station 
where city like "a%a"
or city like "e%a"
or city like "i%a"
or city like "o%a"
or city like "u%a"

or city like "a%e"
or city like "e%e"
or city like "i%e"
or city like "o%e"
or city like "u%e"

or city like "a%i"
or city like "e%i"
or city like "i%i"
or city like "o%i"
or city like "u%i"

or city like "a%o"
or city like "e%o"
or city like "i%o"
or city like "o%o"
or city like "u%o"

or city like "a%u"
or city like "e%u"
or city like "i%u"
or city like "o%u"
or city like "u%u"

但身為程式設計師,自然是不喜歡這種落落長的寫法,
以下提供用and化簡的版本,
沒有用到高深的知識

參考解答2:

select distinct city from station 
where (city like "a%"
or city like "e%"
or city like "i%"
or city like "o%"
or city like "u%")
and (city like "%a"
or city like "%e"
or city like "%i"
or city like "%o"
or city like "%u")

另外,也可以用sql的字串函數LEFTRIGHT來做

參考解答3:

select distinct city from station 
where left(city,1) in ('a','e','i','o','u') 
and right(city, 1) in ('a','e','i','o','u')

還有一種比較高級的,使用「正則表達式」,
但別人可能比較容易看不懂,
可以看看就好

參考解答4:

select distinct city from station 
where city regexp '^[aeiou].*[aeiou]$'

not- 邏輯否定

題目: Weather Observation Station 9
資料表名稱: STATION
欄位: id, city, state, lat_n(最北邊緯度), long_w(最西邊經度)
要求: 回傳名字開頭為a,e,i,o,u的city,並且city不得重複

其實一直做到這題為止,
可以發現做下來都是類似題,
邊做可以邊學到新技巧,
循序漸近又不會一下子太難,
自己覺得這種練習模式蠻好的

剛剛學會了找名字開頭為a,e,i,o,u的city,
那開頭「不是」的怎麼做呢?
很簡單,加個not就好了,很口語吧?

參考解答:

select distinct city from station 
where left(city,1) not in ('a','e','i','o','u') 

心得與結語

總之呢,用hackerrank學習還算蠻有成就感的,
好像在破關一樣,
花了一天的時間把sql技能刷到3星了(滿級5星)

https://ithelp.ithome.com.tw/upload/images/20200725/20117114kxcND2Suyr.png


尚未有邦友留言

立即登入留言