KATOエンジニヤリング開発日誌

「アウトプット無きエンジニアにインプットもチャンスも無い」の精神で書いています

新人エンジニア向け、仮想環境でSQLに慣れてみる 後編

f:id:masayuki_kato:20170227234413j:plain

前回の記事の続きです。

www.kato-eng.info

練習環境のテーブルを作成する。

CREATE TABLE test
(pref_name CHAR(2) PRIMARY KEY,
 population INTEGER);
 
INSERT INTO test VALUES('徳島',100),('香川',200),('愛媛',150),
                       ('高知',200),('福岡',300),('佐賀',100),
                       ('長崎',200),('東京',400),('群馬',50);
                       
SELECT * FROM test;
> pref_name | population 
-----------+------------
 徳島      |        100
 香川      |        200
 愛媛      |        150
 高知      |        200
 福岡      |        300
 佐賀      |        100
 長崎      |        200
 東京      |        400
 群馬      |         50
  • CREATE TABLEでデータベース内にテーブルを作成する。
  • CREATE TABLEの括弧内にテーブルの情報を記述する。
  • この例ではデータ型がCHAR型で主キーの「pref_name」列と、データ型がINTEGER型の「population」列を作成している。
  • INSERT INTOでテーブル内にデータを格納できる。
  • SELECT文でデータを格納した「test」テーブルの中身を確認している。

問題1

testテーブルのpref_name列が「東京」の行を取得する。

※実行結果

 pref_name | population 
-----------+------------
 東京      |        400
(1 行)

問題1答え

SELECT *
FROM test
WHERE pref_name = '東京';

又は

SELECT pref_name, population
FROM test
WHERE pref_name = '東京';
  1. FROM句で使用するテーブルを考える。「test」テーブルを使用するのでFROM句には「test」を指定する。
  2. WHERE句で使用する式を考える。pref_name列が「東京」の行を取得するのでWHERE句には「pref_name = ‘東京'」を指定する。
  3. GROUP BY句、HAVING句を使用しなくてもよいので今回は記述しない。
  4. SELECT句で使用する列を考える。今回は行を取得するので「*」か「pref_name, population」を指定する。

SELECT句に「*(アスタリスク)」を指定すると全ての列を指定したことになるが、基本は必要な列を指定するのが良いと思う。

実際の業務システムではカラム数が多いので「*」で指定すると結構なデータ量になってしまう。


問題2

testテーブルのpopulation列の数字が100以下の行を取得する。 ※実行結果

 pref_name | population 
-----------+------------
 徳島      |        100
 佐賀      |        100
 群馬      |         50
(3 行)

問題2答え

SELECT pref_name, population
FROM test
WHERE population <= 100;
  1. 「test」テーブルを使用するのでFROM句には「test」を指定する。
  2. population列が100以下の行を取得するのでWHERE句には「population <= 100」を指定する。「population < 100」では無いので注意。
  3. GROUP BY句、HAVING句を使用しなくてもよいので今回は記述しない。
  4. SELECT句で使用する列は「*」か「pref_name, population」を指定する。

問題3

CREATE TABLE test2
(parent CHAR(3),
 child CHAR(3),
 PRIMARY KEY(parent, child));
--PRIMARY KEY(*列名1*, *列名2*)で複合の主キーを作成する事ができる。
 
 INSERT INTO test2 VALUES('錠','開'),('優作','龍平'),('優作','翔太'),
                         ('純一郎','考太郎'),('純一郎','進次郎'),('太郎','一郎'),
                         ('太郎','次郎'),('太郎','三郎'),('圭子','ヒカル');
                                           
SELECT * FROM test2;
> parent | child  
--------+--------
 錠     | 開  
 優作   | 龍平 
 優作   | 翔太 
 純一郎 | 孝太郎 
 純一郎 | 進次郎
 太郎   | 一郎
 太郎   | 次郎
 太郎   | 三郎
 圭子   | ヒカル  
(9 行)

「test2」という親と子の名前の情報を持っているテーブルを作成し、INSERT INTOでデータを格納する。

親ごとの子の数を数える。

※出力結果

 parent | 子の数 
--------+--------
 錠     |      1
 優作   |      2
 純一郎 |      2
 太郎   |      3
 圭子   |      1
(5 行)

ヒント

  1. FROM句で使用するテーブルを考える。今回使用するテーブルは「test2」だけでよい。
  2. WHERE句で使用する式を考える。今回は行を選択する必要がないのでWHERE句は記述の必要無し。
  3. GROUP BY句で集合を作成する必要があるか考える。親ごとの集合にする必要があるのでGROUP BY句には「parent」を指定する必要がある。
  4. HAVING句でGROUP BY句でグループ化した集合で選択する必要があるか考える。今回は集合に対して選択する必要は無いので記述しない。
  5. SELECT句で表示する列を考える。親の名前を出力する必要があるので「parent」を指定する。「parent」ごとの子の数を表示する必要があるので「COUNT(child)」を指定する必要がある。

問題3答え

SELECT parent, COUNT(child) AS 子の数
FROM test2
GROUP BY parent;
  • GROUP BYを使用するとデータが集合するのでSELECT句に「*」は指定できない。
  • SELECT句の「COUNT」はグループ化したそれぞれの集合の括弧内の値の数を数える。
  • SELECT句の列名の後に「AS 任意の列名」をつけると任意の列名を指定することができる。

ちなみに下記のSQL文は不正である。*1

SELECT parent AS 親名, COUNT(child) AS 子の数
FROM test2
GROUP BY 親名;

SELECT句で指定した「親名」という列の別名をGROUP BY句で指定しているが、SQL文の実行順序は「FROM→WHERE→GROUP BY→HAVING→SELECT」の為、GROUP BY句の実行時には「parent」の列別名はまだ指定されていないからである。


問題4

「test2」テーブルを使って、親ごとの子の数を数えた上で、子の数が2人以上の親を取得する。

※出力結果

 parent | 子の数 
--------+--------
 優作   |      2
 純一郎 |      2
 太郎   |      3
(3 行)

ヒント

  1. FROM句で使用するテーブルを考える。今回使用するテーブルは「test2」だけでよい。
  2. WHERE句で使用する式を考える。今回は行を選択する必要がないのでWHERE句は記述の必要無し。
  3. GROUP BY句で集合を作成する必要があるか考える。親ごとの集合にする必要があるのでGROUP BY句には「parent」を指定する必要がある。
  4. HAVING句でGROUP BY句でグループ化した集合で選択する必要があるか考える。今回は集合に対して子の数が2人以上という条件があるので、HAVING句には「COUNT(child) >= 2」を指定する。
  5. SELECT句で表示する列を考える。親の名前を出力する必要があるので「parent」を指定する。「parent」ごとの子の数を表示する必要があるので「COUNT(child)」を指定する必要がある。

問題4答え

SELECT parent, COUNT(child) AS 子の数
FROM test2
GROUP BY parent
HAVING COUNT(child) >= 2;

WHERE句には行ごとの選択条件を記述して、HAVING句には集合に対しての選択条件を記述する。


問題5

CREATE TABLE test3
(class_name CHAR(1),
 gender CHAR(1),
 number INTEGER,
 CONSTRAINT valid_gender
 CHECK(gender IN('M','F')),
 PRIMARY KEY(class_name, gender));
 --class_name = クラス名
 --gender = 性別 (M=男, F=女)
 --number = 人数
 --「CONSTRAINT 制約名 CHECK 制約内容」でテーブルに対する制約を課す事ができる。今回の場合は「gender」列に「M」か「F」しかデータを入れられない制約を課している。
 
 INSERT INTO test3 VALUES('A','M',15),('A','F',15),
                         ('B','M',10),('B','F',10),
                         ('C','M',8),('C','F',22),
                         ('D','M',30),('D','F',0),
                         ('E','M',16),('E','F',14);
                                           
SELECT * FROM test3;
> class_name | gender | number 
------------+--------+--------
 A          | M      |     15
 A          | F      |     15
 B          | M      |     20
 B          | F      |     10
 C          | M      |      8
 C          | F      |     22
 D          | M      |     30
 D          | F      |      0
 E          | M      |     16
 E          | F      |     14
(10 行)

「test3」テーブルというクラスの男女ごとの人数を表すテーブルを作成する。

「test3」テーブルからclass_nameが「A,B,C」の男女の合計が25人以上の行を表示する。

※出力結果

 class_name | sum 
------------+-----
 C          |  30
 A          |  30
(2 行)

ヒント

  1. FROM句で使用するテーブルを考える。今回使用するテーブルは「test3」だけでよい。
  2. WHERE句で使用する式を考える。今回は「class_name」が「A, B, C」という指定があるのでWHERE句には「class_name = IN(‘A’, ‘B’, ‘C’)」を指定する。
  3. GROUP BY句で集合を作成する必要があるか考える。クラス名ごとの集合にする必要があるのでGROUP BY句には「class_name」を指定する必要がある。
  4. HAVING句でGROUP BY句でグループ化した集合で選択する必要があるか考える。今回は集合に対して男女の合計数が25人以上という条件があるので、HAVING句には「SUM(number) >= 25」を指定する。
  5. SELECT句で表示する列を考える。クラス名を出力する必要があるので「class_name」を指定する。「class_name」の男女の合計数を表示する必要があるので「SUM(number)」を指定する必要がある。

問題5答え

SELECT class_name, SUM(number) AS 男女合計数
FROM test3
WHERE class_name IN('A', 'B', 'C')
GROUP BY class_name
HAVING SUM(number) >= 25;

「IN(値1, 値2, 値3,….)」で「OR」と同等の処理が行える。

今回の場合は「class_name = ‘A’ or class_name = ‘B’ or class_name = ‘C'」と同じ処理を行っている。

UPDATE文

テーブルの値を変更する際には「UPDATE」文を使用する。

SELECT * FROM test3;
> class_name | gender | number 
------------+--------+--------
 A          | M      |     15
 A          | F      |     15
 B          | M      |     10
 B          | F      |     10
 C          | M      |      8
 C          | F      |     22
 D          | M      |     30
 D          | F      |      0
 E          | M      |     16
 E          | F      |     14
(10 行)

UPDATE test3 SET number = 10 WHERE class_name = 'D' AND gender = 'F';
>UPDATE 1

SELECT * FROM test3;
> class_name | gender | number 
------------+--------+--------
 A          | M      |     15
 A          | F      |     15
 B          | M      |     10
 B          | F      |     10
 C          | M      |      8
 C          | F      |     22
 D          | M      |     30
 E          | M      |     16
 E          | F      |     14
 D          | F      |     10 ←numberが0から10に更新されている。
(10 行)
UPDATE テーブル名 SET 更新したい列名 = 更新したい値 WHERE 列名 = 値 

WHERE句を忘れると対象のテーブルの対象の列の値が全て更新されるので注意。

SELECT * FROM test3;
> class_name | gender | number 
------------+--------+--------
 A          | M      |     15
 A          | F      |     15
 B          | M      |     10
 B          | F      |     10
 C          | M      |      8
 C          | F      |     22
 D          | M      |     30
 E          | M      |     16
 E          | F      |     14
 D          | F      |     10
(10 行)

UPDATE test3 SET number = 0;
>UPDATE 10

SELECT * FROM test3;
> class_name | gender | number 
------------+--------+--------
 A          | M      |      0
 A          | F      |      0
 B          | M      |      0
 B          | F      |      0
 C          | M      |      0
 C          | F      |      0
 D          | M      |      0
 E          | M      |      0
 E          | F      |      0
 D          | F      |      0
(10 行)

CASE式の利用

CASE WHEN 条件1 THEN 条件1に合致する場合の処理
      WHEN 条件2 THEN 条件2に合致する場合の処理
      WHEN 条件3 THEN 条件3に合致する場合の処理
      ELSE 条件に合致しない場合の処理 END;

CASE式は値では無く「式」なのでSELECT句以外の句にも記述可能。


問題6

CREATE TABLE test4
(emp_nbr INTEGER,
 salary INTEGER,
 PRIMARY KEY(emp_nbr),
 CONSTRAINT valid_salary
 CHECK(salary >= 0));
>CREATE TABLE
  
INSERT INTO test4 VALUES(1, 300000),(2, 270000),(3, 220000),(4, 290000);
>INSERT 0 6

SELECT * FROM test4;
> emp_nbr | salary 
---------+--------
       1 | 300000
       2 | 270000
       3 | 220000
       4 | 290000
(4 行)

従業員と給料を管理するテーブルを作成する。

このテーブルに対し、下記の条件で更新をする。

  1. 現在の給料が300000以上の従業員は10%の減給とする
  2. 現在の給料が250000以上、280000以下の従業員は20%の昇給とする。

従業員番号1が10%の減給、従業員番号2が20%の昇給となるので下記のようになるはずである。

emp_nbr salary
1 270000
2 324000
3 220000
4 290000

但し、単純にUPDATE文を2回実行しても上手くいかない。

UPDATE test4 SET salary = salary * 0.9 WHERE salary >=300000;
UPDATE test4 SET salary = salary * 1.2 WHERE salary BETWEEN 250000 AND 280000;

SELECT * FROM test4;
>  emp_nbr | salary 
---------+--------
       1 | 324000 ←間違った更新
       2 | 324000
       3 | 220000
       4 | 290000
(4 行)

従業員番号1は300000以上のsalaryなので最初の更新で10%の減給となるが、次の更新で250000以上280000以下の条件に合致するので20%の昇給も該当してしまう為、このような事が起こる。


問題6答え

UPDATE test4 SET salary = CASE WHEN salary >= 300000
                                 THEN salary * 0.9
                                 WHEN salary BETWEEN 250000 AND 280000
                                 THEN salary * 1.2
                                 ELSE salary END;
--「ELSE」は省略可能だが、省略すると暗黙的に「ELSE NULL」となるため、「salary」と記述しないと2つの条件に合致しない従業員のsalaryがNULLになってしまう。

SELECT * FROM test4;
>emp_nbr | salary 
---------+--------
       1 | 270000
       2 | 324000
       3 | 220000
       4 | 290000
(4 行)

*1:※PostgreSQLでは実行出来るが…