採用面接他で「SQLできますか？」と聞かれて無力だった話1

　はじめに

自分はエンジニア未経験者であるので面接の際に

「SQLってできます？(わかります？) 」

と殆どの場合聞かれます。
先日の採用面接の際には

「具体的にはindexをチューニングすることってできます？」

と聞かれました。
DB自体はアプリ作ってるので設計もリレーションを考えたりして一応したことはありますし、データのやり取りも当然やってます。
ただし、それはフレームワークの中の話で、生のSQL自体は最初の頃少し触れたくらいでなおかつindexをチューニングするという言葉の意味がわからず、嘘のつけない私は生のSQLはそんなにやったことありませんと力なく答えることになってしまいました。
なので、これを気にQueryの書き方くらいはちゃんと答えられるようにしようと思ってこの記事を書きました。
どこまで続くはわかりません。

Queryの書き方

やったこと: SQLZOOのSELECT from Nobel Tutorial quizまで

基本的にはこれです。

SELECT 取得したいカラム FROM テーブル名 WHERE 条件やオプション

例えば以下のテーブルで国名がJapanのデータが欲しい場合

テーブル名 world
name:国名
continent:大陸
area:面積
population:人口
gdp:国内総生産

SELECT name FROM world WHERE name = 'Japan';

となる。
条件をさらに指定したい場合は

SELECT name,population FROM world WHERE name IN ('Japan','China','UK');
-- → 'Japan','China','UK'のname・populationを取得

SELECT name,population FROM world WHERE name NOT IN ('Japan','China','UK');
-- → 'Japan','China','UK'を除く国の国名と人口を取得

という風に書くことができる。
つまり、WHERE カラム IN()でそのカラムのうちINの引数を含むものだけを返してくれるということがわかる。

条件句の色々

条件句はいつものAND、OR、XOR(どちらかが一方だけがTrueの場合、Trueを返す)を絡めて書いていくことができます。

範囲を指定したい(BETWEEN)

SELECT name,population FROM world WHERE area BETWEEN 3000000 AND 3500000;
-- → areaが3000000~3500000平方kmである国の国名と人口を取得

-- BETWEENを使わずに以下のような指定もできる
SELECT name,population FROM world WHERE area >= 3000000 AND area <= 3500000;
-- → areaが3000000以上かつ3500000以下である国の国名と人口を取得

特定の文字列を含む、特定の文字列で始まるor終わる(LIKE)

SELECT name,population FROM world WHERE name LIKE 'B%'
-- → nameがBで始まる国の国名と人口を取得
SELECT name,population FROM world WHERE name LIKE '%B'
-- → nameがBで終わる国の国名と人口を取得
SELECT name,population FROM world WHERE name LIKE '%B%'
-- → nameにBを含む国の国名と人口を取得
SELECT name,population FROM world WHERE name LIKE 'B%' AND name LIKE '%n'
-- → nameがBで始まり、nで終わる国の国名と人口を取得
SELECT name,population FROM world WHERE name NOT LIKE '%B%' AND name LIKE '%n'
-- → nameにBを含まず、nで終わる国の国名と人口を取得

どちらか一方だけTrue(XOR)

SELECT name,population,area FROM world WHERE area >= 2500000 XOR population >= 100000000;
-- → 人口が1億以上であるか、面積が2500000平方km、どちらか一方の条件だけ満たす国の国名と人口と面積を取得

-- 使っているSQLによってはXOR演算子がない場合があるので以下ような書き方をしないといけない場合もある

SELECT name,population,area FROM world WHERE area >= 3000000 AND population < 250000000 OR area < 3000000 AND population >= 250000000;
SELECT name,population,area FROM world WHERE area >= 2500000 <> population >= 100000000;

SELECT応用編

SELECTは単にカラムを指定するだけじゃなく、以下のようにカラムのデータを加工したものを返すこともできる。

SELECT name, gdp/population FROM world WHERE population >= 10000000;
-- → 人口1000万人以上である国の国名と国民一人ひとりあたりの国内総生産を取得

端数をまとめたい場合は以下ような例がある。

-- ROUND関数を使う
ROUND(7253.86, 0)
-- >  7254
ROUND(7253.86, 1)
-- ->  7253.9
ROUND(7253.86,-3)
-- ->  7000

SELECT name,ROUND(population/1000000,2), ROUND(gdp/1000000000,2) FROM world WHERE continent = 'South America';
-- 南アメリカ大陸にある国の国名と人口（100万人単位）とGDP（10億ドル単位）を小数点第二位までに揃えて取得する。

SELECT name,ROUND(gdp/population/1000, 0)*1000 FROM world WHERE gdp >= 1000000000000;
-- GDPが1兆ドル以上の国の国名と国民一人当たりのGDPを1000ドル単位に直して取得する。

文字列の長さを取得したい場合はLENGTH()を使う。
SQLの種類によってはlen()じゃないとエラーが出る。

SELECT name,capital FROM world WHERE len(name) = len(capital);
-- 国名 name と首都 capital が同じ長さの国の、国名と首都を取得する

-- 応用編(LEFT()を使って文字列の任意の場所の1文字を選択する)

SELECT name,capital FROM world WHERE LEFT(name,1) = LEFT(capital,1) AND name <> capital;
-- 国名と首都の先頭の文字が同じである国の、国名と首都名を表示する。ただし、国名と首都名が同じ場合は除く

重複を省きたい時はDISTINCT()を使う。

SELECT COUNT(DISTINCT yr) FROM nobel WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject = 'Medicine')
-- subject = 'Medicine' でない年度の回数を表示
-- 年度の回数を表示したいので、同じ年度のsubject = 'Medicine'でない賞の数だけ計数しないようにDISTINCTを使う