SQL 入門1

userlistのすべてを表示

```sql

postgres=# select *from userlist;

 id |  name   | address | age
----+---------+---------+-----
  2 | nao     | kyoto   |  22
  3 | john    | newyork |  34
  4 | lisa    | paris   |  24
  6 | ryo     | fuhuoka |  40
  7 | bob     | tokyo   |  23
  8 | jack    | kyoto   |  35
  9 | mary    | newyork |  25
 10 | linda   | paris   |  26
 11 | yosuke  | tokyo   |  41
 12 | takashi | fuhuoka |  19
 13 | saori   | kyoto   |  21
 14 | yuki    | paris   |  20
 15 | tomo    | newyork |  31
 16 | take    | tokyo   |  36
 17 | yusuke  | nagoya  |  22
 19 | mari    | newyork |  20
 20 | sachi   | nagoya  |  36
 21 | jun     | tokyo   |  34
 22 | yasuko  | nagoya  |  28
 23 | reo     | nagoya  |  33
 24 | hana    | tokyo   |  22
 25 | asa     | newyork |  25
 26 | ryo     | paris   |  36
 27 | kyoko   | tokyo   |  34
 28 | chan    | paris   |  28
 29 | misa    | newyork |  18
 30 | toshi   | paris   |  22
 31 | reiko   | tokyo   |  32
 32 | kiki    | fuhuoka |  35
 33 | lulu    | tokyo   |  26
 34 | panda   | tokyo   |  35
 35 | mashi   | newyork |  27
 36 | zuke    | tokyo   |  33
 37 | dore    | paris   |  28
 38 | mishima | tokyo   |  25
 39 | lyly    | fuhuoka |  19
 40 | osu     | newyork |  22
 41 | sati    | tokyo   |  25
 42 | kiriko  | fuhuoka |  27
  1 | sato    | london  |  18
  5 | kaoru   | london  |  15
 18 | nori    | london  |  19
(42 rows)
```
20歳以上の人数を抽出する

```sql

postgres=# select count(*) from userlist 
           where age >=20
           ;
 count
-------
    36
(1 row)
```
東京に住んでいる人を抽出する

```sql
postgres=# select * from userlist 
           where address='tokyo'
           ;

 id |  name   | address | age
----+---------+---------+-----
  7 | bob     | tokyo   |  23
 11 | yosuke  | tokyo   |  41
 16 | take    | tokyo   |  36
 21 | jun     | tokyo   |  34
 24 | hana    | tokyo   |  22
 27 | kyoko   | tokyo   |  34
 31 | reiko   | tokyo   |  32
 33 | lulu    | tokyo   |  26
 34 | panda   | tokyo   |  35
 36 | zuke    | tokyo   |  33
 38 | mishima | tokyo   |  25
 41 | sati    | tokyo   |  25
(12 rows)
```
年齢の低い順に並べる

```sql

postgres=# select * from userlist 
           order by age
           ;

 id |  name   | address | age
----+---------+---------+-----
  5 | kaoru   | london  |  15
  1 | sato    | london  |  18
 29 | misa    | newyork |  18
 12 | takashi | fuhuoka |  19
 18 | nori    | london  |  19
 39 | lyly    | fuhuoka |  19
 19 | mari    | newyork |  20
 14 | yuki    | paris   |  20
 13 | saori   | kyoto   |  21
  2 | nao     | kyoto   |  22
 17 | yusuke  | nagoya  |  22
 24 | hana    | tokyo   |  22
 30 | toshi   | paris   |  22
 40 | osu     | newyork |  22
  7 | bob     | tokyo   |  23
  4 | lisa    | paris   |  24
  9 | mary    | newyork |  25
 25 | asa     | newyork |  25
 41 | sati    | tokyo   |  25
 38 | mishima | tokyo   |  25
 33 | lulu    | tokyo   |  26
 10 | linda   | paris   |  26
 35 | mashi   | newyork |  27
 42 | kiriko  | fuhuoka |  27
 37 | dore    | paris   |  28
 22 | yasuko  | nagoya  |  28
 28 | chan    | paris   |  28
 15 | tomo    | newyork |  31
 31 | reiko   | tokyo   |  32
 23 | reo     | nagoya  |  33
 36 | zuke    | tokyo   |  33
 27 | kyoko   | tokyo   |  34
 21 | jun     | tokyo   |  34
  3 | john    | newyork |  34
 32 | kiki    | fuhuoka |  35
  8 | jack    | kyoto   |  35
 34 | panda   | tokyo   |  35
 26 | ryo     | paris   |  36
 20 | sachi   | nagoya  |  36
 16 | take    | tokyo   |  36
  6 | ryo     | fuhuoka |  40
 11 | yosuke  | tokyo   |  41
(42 rows)
```
addressのグループごとに何人いるか表示する

```sql

postgres=# select address,count(*) from userlist 
           group by address
           ;

 address | count
---------+-------
 kyoto   |     3
 london  |     3
 nagoya  |     4
 paris   |     7
 tokyo   |    12
 fuhuoka |     5
 newyork |     8
(7 rows)
```
addressごとの人数を抽出し、列名をareaに変更して表示する

```sql

postgres=# select address as "area",count(*) from userlist 
           group by address
           ;

  area   | count
---------+-------
 kyoto   |     3
 london  |     3
 nagoya  |     4
 paris   |     7
 tokyo   |    12
 fuhuoka |     5
 newyork |     8
(7 rows)
```

distinctを用いて重複を省いてaddressを表示させる

```sql

postgres=# select distinct address from userlist
           ;

 address
---------
 kyoto
 london
 nagoya
 paris
 tokyo
 fuhuoka
 newyork
(7 rows)
```

20才以上で東京に住んでいる人を抽出する

```sql
postgres=# select * from userlist 
           where age >=20 
           and 
           address='tokyo'
           ;

 id |  name   | address | age
----+---------+---------+-----
  7 | bob     | tokyo   |  23
 11 | yosuke  | tokyo   |  41
 16 | take    | tokyo   |  36
 21 | jun     | tokyo   |  34
 24 | hana    | tokyo   |  22
 27 | kyoko   | tokyo   |  34
 31 | reiko   | tokyo   |  32
 33 | lulu    | tokyo   |  26
 34 | panda   | tokyo   |  35
 36 | zuke    | tokyo   |  33
 38 | mishima | tokyo   |  25
 41 | sati    | tokyo   |  25
(12 rows)
```
アドレスごとの最高、最低、平均、合計年齢と人数を算出する

```sql

postgres=#  select address, max(age), min(age), avg(age), sum(age), 
            count(*) from userlist 
            group by address
            ;

 address | max | min |         avg         | sum | count
---------+-----+-----+---------------------+-----+-------
 kyoto   |  35 |  21 | 26.0000000000000000 |  78 |     3
 london  |  19 |  15 | 17.3333333333333333 |  52 |     3
 nagoya  |  36 |  22 | 29.7500000000000000 | 119 |     4
 paris   |  36 |  20 | 26.2857142857142857 | 184 |     7
 tokyo   |  41 |  22 | 30.5000000000000000 | 366 |    12
 fuhuoka |  40 |  19 | 28.0000000000000000 | 140 |     5
 newyork |  34 |  18 | 25.2500000000000000 | 202 |     8
(7 rows)
```

【実践問題】
奨学金の給付制度により、東京、京都、fuhuokaの内、
人数が5人以上の場合、1人7000円の給付をすることになった。
該当する都市のみテーブルに表示しなさい。
また結果を/home/lisa/data/result4.csvに保存しなさい。
カラムはaddress, count, moneyだけで良い。ただしmoneyの大きい順番で表示すること。

```sql

postgres=#  select address,
            count(*),count(*)*7000 as "money" from userlist 
            where address in ('tokyo','fuhuoka','kyoto')
            group by address having count(*)>=5 order by count(*) desc
            ;
```

表示結果は以下の通り

```sql

 address | count | money
---------+-------+-------
 tokyo   |    14 | 98000
 fuhuoka |     5 | 35000
(2 rows)
```

【実践問題】
東京と京都でイベントを開催することになり、
チラシの配布が必要になった。
ここに住んでいる20代の人に対して、
年齢の20倍のチラシをノルマにしたい。
name, address, age, printのカラムを表示し、
枚数の多い順番で表示しなさい。
ただしprintがチラシの枚数である。

表示コマンドは以下のとおり

```sql

postgres=#  select address, age, age*20 as "print" from userlist 
            where 
                (address in ('tokyo','kyoto')) 
            and 
                (age between 20 and 29)  
            order by print desc
            ;
```

表示結果は以下になる

```sql

 address | age | print
---------+-----+-------
 tokyo   |  26 |   520
 tokyo   |  25 |   500
 tokyo   |  25 |   500
 tokyo   |  23 |   460
 tokyo   |  22 |   440
 kyoto   |  22 |   440
 kyoto   |  21 |   420
(7 rows)
```

【実践問題】
東京、京都、名古屋に住んでいる10代の人が
londonに留学することになった。
それに伴い、住所を変更したい。
住所を変更し、name, address, ageを表示しなさい。

表示コマンドは以下の通り
※まず対象者を抽出する。

```sql
postgres=#  select address, age from userlist 
            where 
                 (address in ('tokyo','kyoto','nagoya')) 
            and 
                 (age between 10 and 19)
            ;
```

対象者が表示される。この場合は3名

```sql

 address | age
---------+-----
 tokyo   |  18
 nagoya  |  15
 tokyo   |  19
(3 rows)
```

その上で、住所変更措置を下記の通り実行する

```sql
postgres=# update userlist set address='london' 
           where
                (address in ('tokyo','kyoto','nagoya')) 
           and
                (age between 10 and 19)
           ;
```

対象者を抽出する

```sql

postgres=# select * from userlist 
           where address='london'
           ;
```

住所が変更された対象者が表示される

```sql

 id | name  | address | age
----+-------+---------+-----
  1 | sato  | london  |  18
  5 | kaoru | london  |  15
 18 | nori  | london  |  19
(3 rows)

```