userlistのすべてを表示
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歳以上の人数を抽出する
postgres=# select count(*) from userlist
where age >=20
;
count
-------
36
(1 row)
東京に住んでいる人を抽出する
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)
年齢の低い順に並べる
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のグループごとに何人いるか表示する
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に変更して表示する
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を表示させる
postgres=# select distinct address from userlist
;
address
---------
kyoto
london
nagoya
paris
tokyo
fuhuoka
newyork
(7 rows)
20才以上で東京に住んでいる人を抽出する
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)
アドレスごとの最高、最低、平均、合計年齢と人数を算出する
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の大きい順番で表示すること。
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
;
表示結果は以下の通り
address | count | money
---------+-------+-------
tokyo | 14 | 98000
fuhuoka | 5 | 35000
(2 rows)
【実践問題】
東京と京都でイベントを開催することになり、
チラシの配布が必要になった。
ここに住んでいる20代の人に対して、
年齢の20倍のチラシをノルマにしたい。
name, address, age, printのカラムを表示し、
枚数の多い順番で表示しなさい。
ただしprintがチラシの枚数である。
表示コマンドは以下のとおり
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
;
表示結果は以下になる
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を表示しなさい。
表示コマンドは以下の通り
※まず対象者を抽出する。
postgres=# select address, age from userlist
where
(address in ('tokyo','kyoto','nagoya'))
and
(age between 10 and 19)
;
対象者が表示される。この場合は3名
address | age
---------+-----
tokyo | 18
nagoya | 15
tokyo | 19
(3 rows)
その上で、住所変更措置を下記の通り実行する
postgres=# update userlist set address='london'
where
(address in ('tokyo','kyoto','nagoya'))
and
(age between 10 and 19)
;
対象者を抽出する
postgres=# select * from userlist
where address='london'
;
住所が変更された対象者が表示される
id | name | address | age
----+-------+---------+-----
1 | sato | london | 18
5 | kaoru | london | 15
18 | nori | london | 19
(3 rows)