【 1章 DDL問題 】
---
● Q1 DB作成
① create database test01 ;
② \l

---
● Q2 table作成(public)
① \q
   psql -U postgres -d test01 
② create table class( class_id int primary key , class_name varchar(5)) ;
③ create table student( id int constraint student_id_pkey_alias primary key , name text unique not null , class_id int ) ;
④ \d class
   \d student

---
● Q3 table情報更新
① alter table class add column nickname text ;
② alter table class rename column class_name to name ;
③ alter table class alter column name type text ;
④ alter table student alter column name drop not null ;
⑤ alter table student drop constraint student_name_key ;
⑥ alter table student add foreign key (class_id) references class(class_id) ;
⑦ alter table class add check ( name like '___' ) ;

---
● Q4 シーケンス追加とテーブル情報変更
① create sequence student_id_seq ; 
② alter table student alter column id set default nextval( 'student_id_seq') ;
③ \d student

---
● Q5 シーケンス関連動作の確認
① create sequence test_seq increment 10 maxvalue 30 start 10 ;
② select currval('test_seq') ;
③ select nextval('test_seq') ;
④ select currval('test_seq') ;
⑤ select nextval('test_seq') ;
⑥ select setval('test_seq',0) ;
⑦ select setval('test_seq',1) ;
⑧ drop sequence test_seq ;

---
● Q6 index付与
① create index student_name_index on student ( name ) ;
② create index class_id_manual_index on class (class_id) ; 
③ create index multi_column_index on student ( id, class_id ) ;
④ drop index class_id_manual_index ;
   drop index multi_column_index ;

---
● Q7 制約チェック
① insert into student( name, class_id) values ( 'AAA' , 1) ;
② insert into class(name) values ('1-1') ;
③ insert into class(class_id, name) values (1,'1-1-1') ;
④ insert into class(class_id, name) values (1,'1-1') ;
⑤ insert into class(class_id, name) values (1, '1-2') ;
⑥ insert into student(name, class_id) values ('AAA',1);
⑦ delete from class where class_id = 1 ;
⑧ delete from student where class_id = 1 ;
   delete from class where class_id = 1 ;

---
● Q8 indexメンテナンス
① cluster student using student_name_index ;
② reindex index student_name_index ;

---
● Q9 ビューとマテリアライズドビューの作成
① insert into class(class_id, name, nickname) values (1,'1-1', 'view') ;
② create view test_view as select class_id , nickname from class ;
③ create materialized view test_mate_view as select class_id , nickname from class ;
④ select * from test_view ;
   select * from test_mate_view ;

---
● Q10 ビューとマテリアライズドビューの動作確認: 参照元の変更とその影響
① insert into class(class_id, name, nickname) values (2,'2-1', 'mate_view') ;
② select * from test_view ;
   select * from test_mate_view ;
③ update class set nickname = 'view_r2' where class_id = 2 ;
   delete from class where class_id = 1 ;
④ select * from test_view ;
   select * from test_mate_view ;
⑤ refresh materialized view test_mate_view ;
   select * from test_mate_view ;

---
● Q11 ビューとマテリアライズドビューの動作確認: ビューに対する変更処理
① insert into test_view (class_id, nickname) values ( 3, 'view01') ;
② insert into test_mate_view(class_id, nickname) values ( 4, 'mate_view01') ;
③ update test_view set nickname = 'view02' where class_id = 3 ;
④ update test_mate_view set nickname= 'mate_view99' where class_id = 2 ;
⑤ select * from test_view  ;
　 select * from class ;
⑥ delete from test_view where class_id = 3 ;
⑦ delete from test_mate_view where class_id = 2 ; 

---
● Q12 ビュー・マテビューの削除
① drop view test_view ;
   drop materialized view test_mate_view ;
② select * from class ;
③ delete from class ;

---
● Q13 table作成(新スキーマ)
① create schema test_schema01 ;
   create schema test_schema02 ;
② create table test_schema01.test001( id int , name text ) ;
③ select * from test_schema01.test001 ;
④ SET search_path TO test_schema02 ;
⑤ create table test001( id int , name text ) ;
⑥ select * from test001 ;
⑦ create table test001( id int , name text ) ;
⑧ \d
⑨ SET search_path TO public , test_schema01, test_schema02 ;
⑩ \d
⑪ alter table test_schema02.test001 rename to test002 ;
⑫ \d
⑬ comment on table test002 is 'sample' ;
⑭ \d+
⑮ comment on table test002 is null ;
⑯ \d+

---
● Q14 スキーマ削除
① drop schema test_schema01 ;
② drop table test_schema01.test001 ;
③ drop schema test_schema01 ;

---
● Q15 スキーマ削除(まとめて削除）
① drop schema test_schema02 cascade ;

---
● Q16 dropでエラーとさせない
① drop table test001 ;
② drop table if exists test001 ;

---
● Q17 DB名変更とDB削除
① alter database test01 rename to ex_db ;
② \q
   psql -U postgres -d postgres
③ alter database test01 rename to ex_db ;
④ drop database ex_db ;

---

【 2章 DCL問題】

---
■ 準備
psql -U postgres -d postgres
create database test02 ;
\q

psql -U postgres -d test02 
create table student( id int , name text ) ;
insert into student(id, name) values ( 1, 'AAA'), ( 2, 'BBB'), (3, 'CCC') ;

---
● Q18 ロール作成
① create role superuser01 login superuser ; 
② create role user01 login password 'aaa' ;
③ \q
   psql -U superuser01 -d test02
④ \q
   psql -U user01 -d test02
⑤ create database test02_02 ;
⑥ \q
   psql -U postgres -d test02
   alter role superuser01 password 'aaa' ;
⑦ \q
   psql -U superuser01 -d test02
   create database test02_02 ; 

---
● Q19 権限付与
① select * from student ;
② \q
   psql -U user01 -d test02 
   select * from student ;
③ \q
   psql -U superuser01 -d test02
   grant all on student to user01 ;
④ \q
   psql -U user01 -d test02 
   select * from student ;

---
● Q20 ロールにロール付与
① \q
   psql -U superuser01 -d test02
② create role user02 login password 'aaa' ;　
③ \q
   psql -U user02 -d test02
④ select * from student ;
⑤ \q
   psql -U superuser01 -d test02
⑥ grant user01 to user02 ;
⑦ \q
   psql -U user02 -d test02
⑧ select * from student ;

---
● Q21 権限削除
① \q
   psql -U superuser01 -d test02
   revoke  select on student from user01 ;
② \q
   psql -U user01 -d test02
   select * from student ;
③ update student set id = 4 where id = 1 ;
④ update student set name  = 'ABC' ;

---
● Q22 所有者変更
① alter database test02_02 rename to test02_03 ;
② \q
   psql -U superuser01 -d test02
   alter database test02_02 owner to user01 ;
③ \q
   psql -U user01 -d test02
   alter database test02_02 rename to test02_03 ;
④ \q
   psql -U superuser01 -d test02
   alter role user01 createdb ;
⑤ \q
   psql -U user01 -d test02
   alter database test02_02 rename to test02_03 ;
⑥ \q
   psql -U superuser01 -d test02
   alter database test02_03 rename to test02_02 ;

---
● Q23 ロール削除
① \q
   psql -U postgres -d test02
   drop role superuser01 ;
② drop role user01 ;
③ drop database test02_02 ;
   revoke all on student from user01 ;
④ drop role user01 ;
⑤ drop postgres to user02 ;

---
● Q24 ビューと組み合わせたロールの活用
① create table person( id int , name text, status text ) ;
   insert into person(id, name, status) values ( 1, 'AAA','Active'), ( 2, 'BBB', 'Ended'), (3, 'CCC','Active') ;
② create view ope_view as select id as active_id from person where status != 'Ended' ;
③ create role ope ;
   grant select on ope_view to ope ;
④ create role ope001 login password 'aaa' ;
   grant ope to ope001 ;
⑤ \q
   psql -U ope001 -d test02
   select * from person ;
   select * from ope_view ;

---
● Q25 ロールに参加したロールの解除
① \q
   psql -U postgres -d test02
② revoke ope from ope001 ;
③ revoke all on ope_view from ope ;
　 drop role ope, ope001 ;

---

【 3章 TCL 】

---
■ 準備
psql -U postgres -d postgres
create database test03 ;
\q

psql -U postgres -d test03 
create table student( id int , name text ) ;
insert into student(id, name) values ( 1, 'AAA'), ( 2, 'BBB'), (3, 'CCC') ;

create schema test_schema ;

---
● Q26 トランザクション(正常) 
① select * from student ;
② begin ;
③ update student set name = 'ZZZ' where id = 1 ;
④ select * from student ;
⑤ commit ;
⑥ select * from student ;

---
● Q27 トランザクション(失敗)
① begin ; 
② selectt * from student ;
③ select * from student ;
④ commit ;

---
● Q28 トランザクション(savepoint復帰)
① begin ;
② update student set name = 'YYY' where id = 2 ;
③ savepoint save01 ;
④ updatexx ;
⑤ rollback to savepoint save01 ;
⑥ update student set name = 'XXX' where id = 3 ;
⑦ release savepoint save01 ;
⑧ commit ;
⑨ select * from student ;

---
● Q29 何もない所でトランザクションコマンドを打つと？
① select txid_current() ;
② select txid_current() ;
③ commit ;
④ select txid_current() ;
⑤ rollback ;
⑥ savepoint save01 ;
⑦ rollback to savepoint save01 ;
⑧ release savepoint save01 ;

---
● Q30 トランザクションID確認
① select txid_current() ;
② update student set name = 'AAA' where id = 1 ;
③ select txid_current() ;
④ begin ;
   update student set name = 'ABC' where id = 1 ;
   commit ;
⑤ select txid_current() ;

---
● Q31 トランザクション(ロック待ち)
① begin ;
② update student set name = 'AAA' where id = 1 ;
③ begin ;
④ select * from student ;
⑤ update student set name = 'BBB' where id = 2 ;
⑥ update student set name = 'CCC' where id = 1 ;
⑦ commit ;
⑧ commit ;
   select * from student ;

---
● Q32 明示的テーブルロック
① begin isolation level serializable ;
② lock table student ;
③ begin transaction isolation level serializable ;
④ select * from student ;
⑤ update student set name = 'DDD' where id = 3 ;
⑥ 端末A commit ;
   端末B commit ;

---
● Q33 明示的行ロック(共有ロック①)
① begin ;
② select * from student where id = 1 for share ;
③ begin ;
④ select * from student where id = 1 ;
⑤ update student set name = 'XYZ' where id = 2 ;
⑥ update student set name = 'UVW' where id = 1 ;
⑦ 端末A: rollback ;
   端末B: rollback ;

---
● Q34 明示的行ロック(共有ロック②)
① begin ;
② select * from student where id = 1 for share ;
③ begin ;
④ select * from student where id = 1 for update ;  
⑤ ctrl + C
   rollback ;
   begin ;
⑥ select * from student where id = 1 for share ;
⑦ update student set name = 'ACE' where id = 1 ;
⑧ 両端末： rollback ;

---
● Q35 明示的行ロック(排他ロック)
① begin ;
② select * from student where id = 1 for update ;
③ begin ;
④ select * from student where id = 1 ;
⑤ select * from student where id = 1 for share;
⑥ ctrl + C
   rollback ;
   begin ; 
⑦ select * from student where id = 1 for update ;
⑧ 両端末： rollback ;

---
● Q36 デッドロック
① begin ;
② update student set name = '111' where id = 1 ;
③ begin ;
④ update student set name = 'aaa' where id = 2 ; 
⑤ update student set name = '111' where id = 1 ;
⑥ update student set name = 'aaa' where id = 2 ; 
⑦ select * from student ;
⑧ 両端末： rollback ;

---
● Q37 トランザクション分離レベル(ダーティリード未発生・反復不能読み取り 発生)
① 両端末： begin transaction isolation level read uncommitted ;
② show transaction isolation level ;
③ update student set name = 'nonrepeat' where id = 1 ;
④ select * from student ;
⑤ commit ;
⑥ select * from student ;
   rollback ;

---
● Q38 トランザクション分離レベル(反復不能読み取り 改善)
① 両端末： begin transaction isolation level serializable;
② show transaction isolation level ; 
③ update student set name = 'clear' where id = 1 ;
④ select * from student ;
⑤ commit ;
⑥ select * from student ;
   rollback ;

---
● Q39 トランザクション分離レベル(ファントムリード 発生)
① begin ;
② show transaction isolation level ;
③ insert into student ( id , name ) values ( 4 , 'phantom') ;
④ select * from student ;
⑤ commit ;
⑥ select * from student ;
   rollback ;

---
● Q40 トランザクション分離レベル(ファントムリード 改善)
① begin ;
② set transaction isolation level repeatable read ;
③ show transaction isolation level  ;
④ delete from student where id = 4 ;
⑤ select * from student ;
⑥ commit ;
⑦ select * from student ;
   rollback ;

---
● Q41 トランザクション分離レベル(直列異常 改善)
① begin transaction isolation level serializable ;
② insert into student select 11, sum(id) from student ;
③ insert into student select 21, sum(id) from student ;
④ commit ;
⑤ commit ;
⑥ select * from student ;

---
● Q42 トランザクション分離レベル(直列異常 発生)
① begin transaction isolation level repeatable read ;
② insert into student select 12 ,sum(id) from student ;
③ insert into student select 22 , sum(id) from student ;
④ commit ;
⑤ commit ;
   select * from student ;

---
● Q43 SET文の影響範囲の理解
① begin ;
   SET transaction isolation level serializable ;
   commit ; 
   show transaction isolation level ;
② SET transaction isolation level serializable ;
   show transaction isolation level ;
③ SET search_path TO test_schema ;
   show search_path ;
   \q
   psql -U postgres test03
   show search_path ;

---
● Q44 トランザクション分離レベルの永続的変更(1)
① alter database test03 set search_path  = test_schema ;
   show search_path ;
   \q
   psql -U postgres test03
   show search_path ;
② alter database test03 set default_transaction_isolation = serializable ;
   \q
   psql -U postgres test03
   show transaction isolation level ;
   \q
   psql -U postgres postgres
   show transaction isolation level ;
③ \q
   psql -U postgres test03
   alter database test03 reset default_transaction_isolation ;
 （alter database test03 set default_transaction_isolation = default ;　でもOK)
   \q
   psql -U postgres test03
   show transaction isolation level ;

---
● Q45 トランザクション分離レベルの永続的変更(2)
① alter system set default_transaction_isolation = 'repeatable read' ;
   \q
   show transaction isolation level ;  

---
■ 後片付け  ※ Q45をやった人のみ
 alter system reset default_transaction_isolation ;

---

【 4章 DML(基礎編) 】 

---
■ 事前準備
psql -U postgres -d postgres
create database test04 ;
\q

psql -U postgres -d test04
create table dept ( dept_id int , name text ) ;
create table users ( id int , dept_id int , name text , grade numeric ) ;
create table device ( device_id int primary key, user_id int , status boolean , create_at timestamp default current_timestamp ) ;

insert into dept values ( 1 , 'AX' ) , (2 , 'BX'), (3, 'CX') ;
insert into users values ( 1, 1 , 'AAA',  3.5 ), (2, 1, 'BBB', 2.5), (3, 2, 'CDef', 3.2),(4, 3 , 'cdEF', 4.5 ) ;
insert into device values ( 1 , 1, true ) ;

---
● Q46 シンプルなselect(事前準備が正しくできているか確認）
select * from dept ;
select * from users ;
select * from device ;

---
● Q47 一部のレコード削除
delete from dept where dept_id = 2 returning * ;

---
● Q48 delete以外によるデータ削除
truncate dept ;
select * from dept ;

---
● Q49 データ投入
① insert into device values ( 2, 2 , null ) returning * ;
② insert into dept values ( 1, 'AX') , ( 2, 'BX') returning name ;
③ insert into dept select dept_id + 2 ,   name || dept_id::text  from dept returning * ;

---
● Q50 データ更新
update dept set name = replace ( name,  'X' , 'XBU')  where dept_id = 3 or dept_id = 4 returning * ;

---
● Q51 csvを使ったデータ運用
① copy device to '/tmp/export.csv' delimiter ',' csv ;
② OSコマンドの為、略
③ copy device from '/tmp/import.csv' delimiter ',' csv ;
④ truncate device ;
   copy device from '/tmp/import.csv' delimiter ',' csv ;
⑤ copy ( select * from device where user_id = 4 ) to '/tmp/export_id4.csv' delimiter ',' csv ;

---
● Q52 複数条件の指定方法
① select * from users where dept_id = 1 and grade > 3 ;
② select * from users where dept_id = 1 or dept_id = 2 ;
③ select * from users where not dept_id = 3 ;
④ select * from users where ( dept_id = 1 or dept_id = 2 ) and grade >= 3 ;

---
● Q53 算術関数の使用
① select * , round(grade) from users ;
② select * , round(grade) - trunc(grade) from users ;

---
● Q54 カラム名の指定
① select * , round(grade) - trunc(grade) as check  from users ;
② select id , dept_id , name , grade as "評価" from users ;

---
● Q55 集約関数の使用
① select avg(grade) from users ;
② select round(avg(grade),2) from users ;
③ select avg(grade) from users where dept_id = 1 ;

---
● Q56 集約関数とグルーピング
select dept_id , avg(grade) from users group by dept_id ;

---
● Q57 グルーピングと条件式
① select dept_id , avg(grade) from users where  not dept_id = 3 group by dept_id ;
② select dept_id , avg(grade) from users group by dept_id having avg(grade) > 3 ;
③ select dept_id , avg(grade) from users where not dept_id = 3 group by dept_id having avg(grade) > 3 ;

---
● Q58 該当レコード数の確認
① select count(*) from device ;
② select count(status) from device ;

---
● Q59 nullの取得
① select * from device where status is null ;
② select * from device where status is not null ;

---
● Q60 演算子の優先度
① select ( 3.5 + 2.5 )^2 / 2 ;
② select 3.5 + 2.5^2 / 2 ;

---
● Q61 時刻演算子
① select max(create_at) - min(create_at) from device ;
② select create_at - interval '15 hour' from device where device_id = 1 ;

---
● Q62 時刻関数
① select now() - min(create_at) from device ;
② select extract(year from create_at )from device ;

---
● Q63 時刻関数の取得タイミング
①-1 begin ; 
  -2 select now() ;
  -3 select now() ;
②-1 select statement_timestamp() ;
  -2 select statement_timestamp() ;
  -3 rollback ;
③-1 select now() ; select statement_timestamp() from (select pg_sleep(10)) a ;
  -2 select now() ; select clock_timestamp() from (select pg_sleep(10)) a ;

---
● Q64 データ型書式設定関数
select to_char(create_at , 'YYYYMMDD') from device ;

---
● Q65 文字関数
① select lower(name) from users where id = 1 ;
② select name || ':' || dept_id as "user名:dept_id" from users ;
③ select lpad(dept_id::text , 3 ,'0') from users ;

---
● Q66 generate_series
① select generate_series(1, 10) ;
② select (current_date + generate_series(-7, 6) ) ;
③ select ('192.168.10.1'::inet + generate_series(0, 9, 2) ) ;

---
● Q67 文字検索(like,ilike)
① select * from users where name like 'CD%' ;
② select * from users where name ilike '%de%' ;

---
● Q68 文字検索(simular to)
① select * from users where name similar to '%(A|ef)' ;

---
● Q69 文字検索(正規表現)
① select * from users where name ~ 'EF$' ; 
② select * from users where name !~* '^A' ;

---
● Q70 重複排除
① select distinct dept_id  from users ;
② select distinct id , dept_id from users ;
③ select distinct (id , dept_id )  from users ;
④ select distinct on (dept_id) dept_id from users ;
⑤ select distinct on (dept_id) grade, dept_id from users ;

---
● Q71 重複排除とヒット数取得の組み合わせ
① select count( distinct (dept_id) ) from users ;
② select distinct (status) from device ;
③ select count(distinct(status)) from device ;

---
● Q72 並び替え
① select * from users order by name ;
② select * from device order by user_id desc , device_id ;

---
● Q73 重複排除並び替えと並び替え指定、どちらが優先されるか
select distinct on (dept_id) dept_id from users order by dept_id desc;

---
● Q74 取得結果の範囲指定
① select * from users where grade > 3 limit 1 ;
② select * from users where grade > 3 order by grade desc limit 1 ;
③ select * from device order by create_at desc offset 1 limit 2 ;

---
● Q75 取得範囲の指定
select * from device where device_id between 2 and 4 ;

---
● Q76 まとめ問題
① select user_id , ( count(*) - count(status) ) as nullstatus from device group by user_id order by nullstatus desc;
② select dept_id, round(avg(grade),1) as avg  from users where not grade <= 3 or dept_id = 4 group by dept_id having avg(grade) >= 3.2 order by avg desc limit 3 ;

---

【 5章 DML(応用編)  】

---
■ 事前準備
psql -U postgres -d postgres
create database test05 ;
\q

psql -U postgres -d test05
create table dept ( dept_id int , name text ) ;
create table users ( user_id int , dept_id int , name text , grade numeric ) ;
create table device ( device_id int primary key, user_id int , status boolean , create_at timestamp default current_timestamp ) ;

insert into dept values ( 1 , 'AX' ) , (2 , 'BX'), (3, ''),(4,'AX') ;

insert into users values ( 1, 1 , 'AAA',  3.5 ), (2, 1, 'BBB', 2.5), (3, 2, 'CDef', 3.2),(4, 3 , 'cdEF', 4.5 ),(5, 5 , 'GGG', 3.8 ) ;

insert into device values ( 1 , 1, true ) ;insert into device values ( 2 , 2, null ) ;insert into device values ( 3 , 4, false ) ;insert into device values ( 4 , 4, true ) ; insert into device values ( 5 , 6, true ) ;

---
● Q77 結合1
① select * from users inner join dept on users.dept_id = dept.dept_id ;
② select * from users inner join device on users.user_id = device.user_id ;

---
● Q78 結合2
① select * from users inner join dept using (dept_id ) ;
② select * from users inner join device using (user_id) ;
③ select * from users natural join device;
④ select * from users natural join dept;

---
● Q79 結合3
① select * from users left outer join dept on users.dept_id = dept.dept_id ;
② select * from users left outer join dept using (dept_id) ;
③ select * from users left outer join device on users.user_id = device.user_id ;
④ select * from users left outer join device using (user_id) ;

---
● Q80 結合4
① select * from users right outer join dept on users.dept_id = dept.dept_id ;
② select * from users right outer join dept using(dept_id) ;
③ select * from users right outer join device on users.user_id = device.user_id ;
④ select * from users right outer join device using(user_id);

---
● Q81 結合5
① select * from users full outer join dept on users.dept_id = dept.dept_id ;
② select * from users full outer join dept using(dept_id) ;
③ select * from users full outer join device on users.user_id = device.user_id ;
④ select * from users full outer join device using(user_id);

---
● Q82 結合6
① select * from users left outer join dept on users.dept_id = dept.dept_id where dept.dept_id is null ;
② select * from users left outer join dept using (dept_id) where dept.name is null ;
③ select * from users right outer join dept on users.dept_id = dept.dept_id where users.dept_id is null ;

---
● Q83 結合7
① select * from users full outer join dept on users.dept_id = dept.dept_id where dept.dept_id is null or users.dept_id is null ;
② select * from users full outer join dept using (dept_id) where dept.name is null or users.name is null  ;

---
● Q84 結合8
① select * from users cross join device ;
② select * from users cross join device where users.user_id = device.user_id ;
③ select * from users , device ;
④ select * from users , device where users.user_id = device.user_id ; 　  

---
● Q85 3つのテーブル結合
select * from dept inner join users on dept.dept_id = users.dept_id inner join device on users.user_id = device.user_id ;

---
● Q86 3つのテーブル結合時の取得範囲指定1
① select * from dept left outer join users on dept.dept_id = users.dept_id left outer join  device on users.user_id = device.user_id where users.dept_id is null and device.user_id is null;
② select * from dept left outer join users using(dept_id) left outer join device using(user_id) where users.name is null and device.device_id is null;

---
● Q87 3つのテーブル結合時の取得範囲指定2
① select * from dept right outer join users on dept.dept_id = users.dept_id left outer join  device on users.user_id = device.user_id where dept.dept_id is null and device.user_id is null ;
② select * from dept inner join users on dept.dept_id = users.dept_id right outer join  device on users.user_id = device.user_id where dept.dept_id is null and users.user_id is null;

---
● Q88 3つのテーブル結合時の取得範囲指定3
select * from dept full outer join users on dept.dept_id = users.dept_id full outer join device on users.user_id = device.user_id where (users.dept_id is null and device.user_id is null) or (dept.dept_id is null and device.user_id is null) or ( dept.dept_id is null and users.dept_id is null ) ;

---
● Q89 select結果でupdateに近い処理
① update users set name = dept.name from dept where users.dept_id = dept.dept_id and users.user_id = 3 ;
② update users set name = dept.name from dept where dept.dept_id =4 and users.user_id = 5;

---
● Q90 副問合せの基本
① select * from (select dept_id ,count(*)  from users where grade <= 3.5 group by dept_id order by count desc ) a where a.count > 1 ;
② select * from (select dept_id ,count(*)  from users where grade <= 3.5 group by dept_id order by count desc ) a inner join dept on a.dept_id = dept.dept_id where a.count > 1 ;

[参考] ②は以下でも良い。
select * from (select dept.name ,count(*) from users inner join dept on users.dept_id = dept.dept_id where grade <= 3.5 group by dept.name order by count desc ) a where a.count > 1 ;

---
● Q91 3つの副問合せ
select * from (select dept_id ,count(*) from (select user_id , count(*) from device group by user_id ) a inner join users on a.user_id = users.user_id where count = 1 group by dept_id ) b inner join dept on b.dept_id = dept.dept_id where b.count > 1 ;

---
● Q92 副問合せ同士のjoin
select * from (select * from users where grade >= 3 ) a full outer join ( select user_id , count(*) from device group by user_id ) b on a.user_id = b.user_id order by a.user_id , b.user_id ;

---
● Q93 副問合せを条件に指定
select * from dept where dept_id = ( select dept_id from users order by grade  limit 1 ) ;

---
● Q94 fromより前の副問合せ
① select 'CX' as dept_name , (select name from users where user_id = 4), ( select device_id from device where user_id = 4 limit 1 ) ;
② select user_id, 'mask' as name from  users ;
③ select user_id, (select name from dept order by dept_id desc limit 1 ) as name from  users ;

---
● Q95 相関副問合せ
① select * from users as u1 where grade = ( select max(grade) from users as u2 where u1.dept_id = u2.dept_id ) ;
② select user_id, name , grade,  ( select max(grade) from users u2 where u1.dept_id = u2.dept_id) from users u1 ;
③ select user_id, name , ( select count(device_id) from device d1 where u1.user_id = d1.user_id ) from users u1 ;

---
● Q96 シンプルなinの使い方
select * from users where name in ('AX', 'BX') ;

---
● Q97 複数のレコードを条件とした副問合せ(in)
① select * from dept where dept_id in (select dept_id from users where grade <= 3.5 ) ;
② select * from dept where dept_id >= 2 and dept_id in (select dept_id from users where grade <= 3.5 ) ;
③ select * from dept where dept_id notin (select dept_id from users where grade <= 3.5 ) ;

---
● Q98 複数のレコードを条件とした副問合せ(some/any)
① select * from dept where dept_id = some (select dept_id from users where grade <= 3.5 ) ;
② select * from dept where dept_id = any (select dept_id from users where grade <= 3.5 ) ;
③ select * from dept where dept_id > some (select max(dept_id) from users where grade <= 3.5 ) ;

---
● Q99 some/anyと対となる動作をする指定
select * from dept where dept_id > all (select dept_id from users where grade <= 3.5 ) ;

---
●Q100 シンプルなexistsによる副問合せ
① select exists ( select * from users where user_id = 100 ) ;
② select * from dept where exists( select * from users where user_id = 1 ) ;
③ select * from dept where not exists( select * from users where user_id = 1 ) ;

---
● Q101 existsと相関副問合せの組み合わせ
select * from dept where exists ( select * from users where grade <= 3.5 and users.dept_id = dept.dept_id )  ;

---
● Q102 with句の使用
① with ex1 as (select * from users where grade <= 3.5 ) select distinct(dept.dept_id) ,dept.name from ex1, dept where ex1.dept_id = dept.dept_id  ;
② with ex1 as (select * from users where grade <= 3.5 ) select * from ex1, dept where ex1.dept_id = dept.dept_id ;
③ with a as ( select * from users where grade >= 3 ) , b as (select user_id , count(*) from device group by user_id ) select * from a full outer join b on a.user_id = b.user_id order by a.user_id , b.user_id ;

---
● Q103 2つのクエリ取得結果の結合
① select * from dept union all select dept_id , name from users order by dept_id, name;
② select * from dept union select dept_id , name from users order by dept_id , name;

---
● Q104 重複行の取得
select * from dept intersect select dept_id , name from users ;

---
● Q105 2つのselectの差分を取得
select dept_id , name from users except select * from dept order by dept_id, name ;

---
● Q106 重複排除した行の取得
select user_id from device except all select distinct on (user_id) user_id from device ;

---
● Q107 再帰的問い合わせ
① create sequence with_seq ;
② with recursive loop_test(loop_count, seq_value,  loop_x10) as ( select 1 , nextval('with_seq')::int , 10 union select loop_count + 1 , nextval('with_seq')::int, loop_count*10+10  from loop_test  where  loop_count < 10 ) select * from loop_test ;

---
● Q108 クエリ実施の詳細確認
① explain select * from users where user_id = 4 ;
② explain analyze select * from users where user_id = 4 ;

---
● Q109 Window関数1
① select * , avg(grade) over ( partition by dept_id) from users_window ;
② select dept_id ,  a.avg , dense_rank() over ( order by a.avg desc) from  ( select dept_id , avg(grade) from users_window group by  dept_id ) a ;
③ select user_id , dept_id , name , grade , round(avg,1) , dense_rank() over ( order by a.avg desc) from  ( select * , avg(grade) over ( partition by dept_id) from users_window ) a ;

---
● Q110 Window関数2
select user_id , dept_id , name , grade , round(avg,1) , (first_value(a.avg) over (order by a.avg desc ) - a.avg) as "差分"  from  ( select * , avg(grade) over ( partition by dept_id) from user_window ) a ;

---
● Q111 Window関数のフレーム毎の処理
① select user_id , dept_id , name , grade , dense_rank() over ( partition by dept_id order by grade) from users_window ;
② select * from (select user_id , dept_id , name , grade , dense_rank() over ( partition by dept_id order by grade)  from users_window ) a where dense_rank <= 2 ;

---
● Q112 Window関数と範囲指定
① select * , sum(grade) over (partition by dept_id order by grade desc rows between 1 preceding and current row ) from users_
window ;
② select * , sum(grade) over (partition by dept_id order by dept_id rows between unbounded preceding and current row ) from users_window ; 

---
● Q113 全体で範囲指定処理
① select * , (lag(grade, 1) over ( order by dept_id , grade desc ) + grade)  from users_window ;
② select * , (lead(grade, 1) over ( order by dept_id, grade desc  ) + grade)  from users_window ;

---
● Q114 UPSERT1
① select * from dept ;
② insert into dept( dept_id, name ) values ( 99, 'SP' ) on conflict (dept_id) do nothing ;
③ alter table dept add primary key (dept_id) ;
④ insert into dept( dept_id, name ) values ( 99, 'SP' ) on conflict (dept_id) do nothing ;
⑤ select * from dept ;
⑥ insert into dept( dept_id, name) values ( 99, 'SP2') on conflict (dept_id) do nothing ;
⑦ select * from dept ;

---
● Q115 UPSERT2
① insert into dept( dept_id, name) values ( 98, 'secret') on conflict (dept_id) do update set name = '重複' ;
② select * from dept ;
③ insert into dept( dept_id, name) values ( 98, 'secret2') on conflict (dept_id) do update set name = '重複' ;
④ select * from dept ;

---
● Q116 UPSERT3
① insert into dept( dept_id, name) values ( 98, '秘密'), (99, '特殊') on conflict (dept_id) do update set name = excluded.name ;
② select * from dept ;
③ delete from dept where dept_id in ( 98, 99) ;

---
● Q117 まとめ
 select a.user_id from (select user_id , count(status) from device group by user_id ) a where count in ( 0 , 2 ) union select users.user_id from users inner join dept on users.dept_id = dept.dept_id where dept.name is not null and users.name not in ( 'AX', 'BX') ;

---

【 6章 高度な操作問題 】

---
■ 事前準備
psql -U postgres -d postgres
create database test06a;
\q

psql -U postgres -d test06a
create table student ( id int primary key default 999  ,name text , grade int, class int ,c_group int, eval int ) ;

insert into student ( id, name , grade, class ,c_group, eval) values (1, 'AAA', 1, 1,1, 70 ), (2, 'BBB', 2, 1, 1, 80), (3, 'CCC', 1, 2,1, 50) ;

---
● Q118 自ら定義する型
① create domain post_code as text check (  value ~ '^[0-9]{3}-[0-9]{4}$' ) ;
② create table users ( id int, name text , p_code post_code) ;
③ insert into users values ( 1 , 'AAA', '111-2222' ) ;
④ insert into users values ( 2 , 'BBB', '111-33333') ;
⑤ update users set p_code = '1000' where id = 1 ;

---
● Q119 プリペアドステートメントを対話モードで試す
① prepare p_select as select * from users ;
② execute p_select(1) ;
③ prepare p_insert as insert into users values ( $1,$2, $3) ;
④ execute p_insert(2, 'BBB', '111-3333') ;
⑤ execute p_select (1) ;
⑥ deallocate p_select ;
   deallocate p_insert ;

---
● Q120 外部キー制約のアクション1
① create table dept ( id int , name text) ;
   create table employee ( id int , dept_name text references dept(name) on delete cascade on update cascade , name text  ) ;
② alter table dept add constraint unique_dept_name unique (name); 
   create table employee ( id int , dept_name text references dept(name) on delete cascade on update cascade , name text  ) ;
③ insert into dept ( id, name) values (1, 'AX'), (2, 'BX'), (3, 'CX') ;
   insert into employee ( id, dept_name, name) values (1, 'AX', 'AAA') , (2, 'BX','BBB'), (3, 'CX', 'CCC') ;
④ update employee set dept_name = 'test' ;
⑤ select * from employee ;
   update dept set name = 'DX' where id = 1 ;
   select * from employee ;
⑥ delete from dept where id = 3 ;
   select * from employee ;

---
● Q121 外部キー制約のアクション2
① alter table employee add constraint unique_emp_id unique (id); 
   alter table student add foreign key (id) references employee(id) on delete set default on update set null ;
② delete from student where id = 3; 
   alter table student add foreign key (id) references employee(id) on delete set default on update set null ;
③ update employee set id = 4 where id = 1 ;
④ alter table student drop constraint student_pkey ;
   alter table student alter column id drop not null ;
⑤ select * from student ;
   update employee set id = 4 where id = 1 ;
   select * from student ;
⑥ delete from employee where id = 2 ;
   ※ エラー原因：employeeのid 2が消えると、参照元のstudentのid 2もdefaultの値となる。studentのidにはdefault 999を指定していた。よって999となるはずだが、employee側に999のidがない。
⑦ insert into employee ( id, dept_name, name) values (999, 'BX', 'ZZZ') ;
⑧ select * from student ;
   delete from employee where id = 2 ;
   select * from student ;

---
● Q122 制約解除
① drop table dept cascade;
② alter table student drop constraint student_id_fkey ;

---
● Q123 複合外部キー制約
① alter table employee add constraint unique_emp_name unique (name); 
   create table person ( id int , name text , foreign key ( id, name) references employee (id, name) ) ;
② alter table employee add constraint unique_emp_multi_name unique (id, name); 
   create table person ( id int , name text , foreign key ( id, name) references employee (id, name) ) ;
③ select * from employee ;
④ insert into person values (4 , 'AAA') ;
⑤ insert into person values (999 , 'BBB') ;
⑥ insert into person values (999 , 'AAA') ;
⑦ drop table person ;

---
● Q124 条件に応じた出力
① insert into student ( id, name , grade, class ,c_group, eval) values (3, 'CCC', 1, 2,1, 50), (4,'DDD', 2, 2,1, 50), (5, 'EEE', 1, 2, 1, 90), (6, 'FFF', 2, 1, 2, 60)  ;
② select *, case when groupAvg >= 80 then '〇' when groupAvg >= 60 and groupAvg < 80 then '△'  else '×' end as jedge from  ( select grade, class, c_group ,avg(eval) as groupAvg  from student group by grade, class, c_group ) a;

---
● Q125 複数パターンによる集約処理の出力
① select grade, class,c_group ,avg(eval) from student group by grouping sets ( grade, class, c_group ) ;  
② select grade, class,c_group ,avg(eval) from student group by rollup ( grade, class, c_group ) ;  
③ select grade, class,c_group ,avg(eval) from student group by cube ( grade, class, c_group ) ;  

---

【 7章-2 パーティションとテーブル空間

---
■ 準備
mkdir ~/12/data01_ts1
mkdir ~/12/data01_ts2

psql -U postgres -d postgres
create database test06b ;
\q

psql -U postgres -d test06b

---
● Q126 リストパーティションとテーブル空間
① create tablespace ts1 location '/var/lib/postgresql/12/data01_ts1' ;
   create tablespace ts2 location '/var/lib/postgresql/12/data01_ts2' ;
② create table person_list ( id int, dept text, name text , memo timestamp ) partition by list (dept) ;
③ create table person_ax partition of person_list for values in ('AX') tablespace ts1 ; 
   create table person_bx partition of person_list for values in ('BX') tablespace ts2 ;  

---
● Q127 リストパーティションとテーブル空間の動作確認
① insert into person_list values ( 1, 'AX', 'AAA', now() ),( 2, 'BX', 'BBB', now() ),( 3, 'AX', 'CCC', now() ) ;
② select * from person_ax ;
   select * from person_bx ;
   select * from person_list ;
③ select oid, spcname, pg_tablespace_location(oid) from pg_tablespace ;
④ select n.nspname , c.relname, t.spcname from pg_class c join pg_namespace n on n.oid = c.relnamespace left join  pg_tablespace t ON t.oid = c.reltablespace where c.relkind = 'r' and n.nspname = 'public';
⑤ \q
   ls /var/lib/postgresql/12/data01_ts1/PG_12_201909212/16391/

---
● Q128 リストパーティションの子テーブルを跨いだ処理
① psql -U postgres -d test06b
   create index idx1 on person_list(dept) ;
② update person_list set dept = 'BX' where id = 3 ;
③ select * from person_ax ;
   select * from person_bx ;
④ update person_list set name = 'SameName' ;
⑤ select * from person_list ;

---
● Q129 リストパーティションと主キーについて
① create table person_list2 ( id int primary key , dept text, name text , memo timestamp ) partition by list (dept) ;

 ※ 失敗理由： 主キーにはパーティションキー(dept)を入れる必要がある

② create table person_list2 ( id int , dept text, name text , memo timestamp, primary key (id, dept) ) partition by list (dept) ;

---
● Q130 リストパーティションの範囲外の値について
① insert into person_list values ( 4, 'CX', 'DDD', now() ) ;
② create table person_default partition of person_list default ;
③ insert into person_list values ( 4, 'CX', 'DDD', now() ) ;
④ select * from person_list ;
   select * from person_default ;

---
● Q131 パーティションテーブルのデータ削除について
① delete from person_list where id > 2 ;
   select * from person_list ;
② drop table person_list ;
③ select * from person_ax ;
④ drop tablespace ts1 ;
   drop tablespace ts2 ;
⑤ select oid, spcname, pg_tablespace_location(oid) from pg_tablespace ;
⑥ \q
   ls ~/12/data01_ts1/   
   psql -U postgres -d test06b

---
● Q132 レンジパーティション
① create table person_range( id int, dept text, name text , memo timestamp ) partition by range (memo) ;
② create table person_2024 partition of person_range for values from ('2024-01-01') to ('2024-12-31') ;
   create table person_2023 partition of person_range for values from ('2023-01-01') to ('2023-12-31') ;
   create table person_default partition of person_range default ;
③ insert into person_range values ( 1, 'AX', 'AAA', now() ), ( 2, 'BX', 'BBB', now() - interval '1 year' ), ( 3, 'AX', 'CCC',  now() - interval '2 year' ) ;
     ※ 回答案は現在が2024年である場合に有効。実施年が変わる場合、それに合わせて変更すること。
④ select * from person_2024 ;
   select * from person_2023 ;
   select * from person_default ;
   select * from person_range ;
⑤ drop table person_range ;

---
● Q133 ハッシュパーティション
① create table person_hash( id int, dept text, name text , memo timestamp ) partition by hash (id) ;
② create table person_0 partition of person_hash for values with ( modulus 3, remainder 0 ) ;
   create table person_1 partition of person_hash for values with ( modulus 3, remainder 1 ) ;
③ create table person_default partition of person_hash default ;
　　エラー原因： ハッシュパーティションではデフォルトテーブルという考え方がない。(割り算の結果によってすべて振り分けられる為）
④ create table person_2 partition of person_hash for values with ( modulus 3, remainder 2 ) ;
⑤ insert into person_hash values ( 1, 'AX', 'AAA', now() ),( 2, 'BX', 'BBB', now() ),( 3, 'AX', 'CCC', now() ) ;
⑥ select * from person_0 ;
   select * from person_1 ;
   select * from person_2 ;
   select * from person_hash ;
⑦ drop table person_hash ;

---

【 2章-3 contrib(dblink) 】

---
■ 準備
/usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/12/data02
vi /var/lib/postgresql/12/data02/postgresql.conf
～～～
port = 5433
～～～

/usr/lib/postgresql/12/bin/pg_ctl -D /var/lib/postgresql/12/data02 start

psql -p 5433
\q

---
● Q134 dblinkによるDB作成
① psql -U postgres -d postgres
   create database test06c ;
② create extension dblink ;
③ select dblink_exec('dbname=postgres user=postgres host=127.0.0.1 port=5433','create database test06c') ; 
④ \q
   psql -p 5433 -d test06c 
⑤ \q
   psql -p 5432 -d test06c 

---
● Q135 dblinkによるテーブル作成
① create table person_1 ( id int , dept text, name text , memo text ) ;
② select dblink_exec('dbname=test06c user=postgres host=127.0.0.1 port=5433', 'create table person_2 ( id int , dept text, name text , memo text )' );
③ create extension dblink ; 
 ※ 両方のDB(5432,5433)で実施。(5432はpostgres DBではdblinkを使用する宣言をしたがtest06cではできていない)
④ select dblink_exec('dbname=test06c user=postgres host=127.0.0.1 port=5433', 'create table person_2 ( id int , dept text, name text , memo text )' );

---
● Q136 dblinkによる挿入とレコード取得
① insert into person_1 values ( 1, 'AX', 'AAA','01'), (2, 'BX','BBB','01'), (3, 'AX','CCC','01') ;
② select dblink_exec('dbname=test06c user=postgres host=127.0.0.1 port=5433', 'insert into person_2 values ( 4, ''CX'', ''DDD'',''02''), (5, ''CX'',''EEE'',''02'')' );
③ select * from person_1 union select * from dblink('dbname=test06c user=postgres host=127.0.0.1 port=5433', 'select * from person_2' ) as dblink_tbl(id int, dept text, name text, memo text) ;
④ select * from person_1 union select * from dblink('dbname=test06c user=postgres host=127.0.0.1 port=5433', 'select * from person_2' ) as dblink_tbl(id int, dept text, name text, memo text) order by id;

---
● Q137 dblinkを使ったupdate, delete
① select dblink_exec('dbname=test06c user=postgres host=127.0.0.1 port=5433', 'update person_2 set dept = ''DX'' where id = 5 ' );
② select dblink_exec('dbname=test06c user=postgres host=127.0.0.1 port=5433', 'delete from person_2 where id = 4 ' );
③ select * from person_1 union select * from dblink('dbname=test06c user=postgres host=127.0.0.1 port=5433', 'select * from person_2' ) as dblink_tbl(id int, dept text, name text, memo text) order by id;

---
● Q138 2相コミットメントを使う為の準備
① \q
   vi /var/lib/postgresql/12/data01/postgresql.conf
～～～
max_prepared_transactions = 10
～～～
   /var/lib/postgresql/12/data02/postgresql.conf
～～～
max_prepared_transactions = 10
～～～
   
   /usr/lib/postgresql/12/bin/pg_ctl -D /var/lib/postgresql/12/data01 restart
   /usr/lib/postgresql/12/bin/pg_ctl -D /var/lib/postgresql/12/data02 restart

   psql -p 5432 -d test06c 

---
● Q139 2相コミットメント(処理成功)
① begin ;
   insert into person_1 values ( 6, 'BX','FFF','01') ;
　 prepare transaction 'local' ;
② select dblink_exec('dbname=test06c user=postgres host=127.0.0.1 port=5433', $$ begin ; insert into person_2 values (7, 'CX','HHH','02' ) ; prepare transaction 'remote' ; $$ ) ;
③ select * from pg_prepared_xacts;
   select * from dblink('dbname=test06c user=postgres host=127.0.0.1 port=5433', 'select * from pg_prepared_xacts' ) as result(transaction1 text, gid1 text, prepared1 timestamp, ownerid1 text, database1 text);
④ commit prepared 'local';
   select dblink_exec('dbname=test06c user=postgres host=127.0.0.1 port=5433', 'commit prepared ''remote'' ' );
⑤ select * from pg_prepared_xacts;
   select * from dblink('dbname=test06c user=postgres host=127.0.0.1 port=5433', 'select * from pg_prepared_xacts' ) as result(transaction1 text, gid1 text, prepared1 timestamp, ownerid1 text, database1 text);
⑥ select * from person_1 union select * from dblink('dbname=test06c user=postgres host=127.0.0.1 port=5433', 'select * from person_2' ) as dblink_tbl(id int, dept text, name text, memo text) order by id;
   
---
● Q140 2相コミットメント(処理失敗)
① begin ;
   insert into person_1 values ( 8, 'AX','III','01') ;
　 prepare transaction 'local' ;
② select dblink_exec('dbname=test06c user=postgres host=127.0.0.1 port=5433', $$ begin ; insert into person_2 values (9, 'CX','JJJ','02' ) ; prepare transaction 'remote' ; $$ ) ;
③ rollback prepared 'local' ;
④ select * from dblink('dbname=test06c user=postgres host=127.0.0.1 port=5433', 'select * from pg_prepared_xacts' ) as result(transaction1 text, gid1 text, prepared1 timestamp, ownerid1 text, database1 text);
⑤ select dblink('dbname=test06c user=postgres host=127.0.0.1 port=5433', 'rollback prepared ''remote'' ' );
⑥ select * from pg_prepared_xacts;
   select * from dblink('dbname=test06c user=postgres host=127.0.0.1 port=5433', 'select * from pg_prepared_xacts' ) as result(transaction1 text, gid1 text, prepared1 timestamp, ownerid1 text, database1 text);
   select * from person_1 union select * from dblink('dbname=test06c user=postgres host=127.0.0.1 port=5433', 'select * from person_2' ) as dblink_tbl(id int, dept text, name text, memo text) order by id;

---
■ 後片付け
\q
/usr/lib/postgresql/12/bin/pg_ctl -D /var/lib/postgresql/12/data02 stop

---

【 2章-4 PL/pgSQLと自動処理 】

---
■ 事前準備
psql -U postgres -d postgres
create database test06d ;
\q

psql -U postgres -d test06d
create table dept ( dept_id int, name text, psn_cnt int) ;
create table person ( id int , dept text , name text , memo text) ;
create table tbl_history ( ope_time timestamp, memo text) ;

insert into dept(dept_id,name) values ( 1, 'AX'), (2, 'BX') ;

---
● Q141 シンプルなトリガーの用意
① 
create function simple_func() returns trigger as $$
begin
 insert into history( ope_time, memo ) values ( now(), 'simple1') ;
 return null ;
end ;
$$  language plpgsql ;

② create trigger simple_func_tg after insert on person for each row execute function simple_func() ;
③ select proname, prosrc from pg_proc where proname = 'simple_func' ;
④ select tgname, t.tgrelid::regclass, proname from pg_trigger t join pg_proc p on t.tgfoid = p.oid where tgname = 'simple_func_tg';

---
● Q142 トリガーの動作確認
① insert into person values ( 1, 'AX','AAA','memo') ;
② select * from person ;
③
create or replace function simple_func() returns trigger as $$
begin
 insert into tbl_history( ope_time, memo ) values ( now(), 'simple1') ;
 return null ;
end ;
$$  language plpgsql ;
④ insert into person values ( 1, 'AX','AAA','memo') ;
⑤ update person set memo = 'memo2' where id = 1 ;
⑥ select * from person ;
   select * from tbl_history ;

---
● Q143 IF文を含んだファンクション
①
create or replace function simple_func() returns trigger as $$
begin
 if( tg_op = 'INSERT') then
   insert into tbl_history( ope_time, memo ) values ( now(), tg_op || ',' || tg_table_name || ',' || new.id || ',' || new.dept || ',' || new.name || ',' || new.memo) ;
 elsif( tg_op = 'UPDATE') then
   insert into tbl_history( ope_time, memo ) values ( now(), tg_op || ',' || tg_table_name || ',' || new.id || ',' || new.dept || ',' || new.name || ',' || new.memo || ',' || old.id || ',' || old.dept || ',' || old.name || ',' || old.memo) ;
 elsif( tg_op = 'DELETE') then
   insert into tbl_history( ope_time, memo ) values ( now(), tg_op || ',' || tg_table_name || ',' || old.id || ',' || old.dept || ',' || old.name || ',' || old.memo) ;
 end if ;
 return null ;
end ;
$$  language plpgsql ;

drop trigger simple_func_tg on person ;

create trigger simple_func_tg after insert or update or delete on person for each row execute function simple_func() ;

② insert into person values ( 2, 'BX','BBB','memo') ;
   update person set memo = 'memo2' where id = 2 ;
   delete from person where id = 2 ;
   select * from tbl_history ;
③ insert into person(id, dept, name)  values ( 3, 'AX','CCC') ;
   select * from tbl_history ;

---
● Q144 キーワードに該当する情報がない場合の処理
① create table tbl_history2 ( id int , name text ) ; 
②
create function simple_func2() returns trigger as $$
begin
 insert into tbl_history2( id, name ) values ( new.id , new.name ) ;
 return null ;
end ;
$$  language plpgsql ;
③ create trigger simple_func_tg2 after insert on person for each row execute function simple_func2() ;
④ insert into person(id, memo)  values ( 4, 'simple_func2') ;
   select * from tbl_history ;
   select * from tbl_history2 ;

---
● Q145 関数の行ごと処理とSQL文ごと処理の違い
① 
create function do_sql_func() returns trigger as $$
begin
 insert into tbl_history( ope_time, memo ) values ( now() , 'do SQL now' ) ;
 return null ;
end ;
$$  language plpgsql ;

② create trigger upt_sql_tg after update on person for statement execute function do_sql_func() ;
③ update person set dept = 'AX2' where dept = 'AX' ;
   select * from tbl_history ;

   ※ 空欄理由： personテーブルの変更箇所の1つ(AX2となったレコードの1つ)のid 3 (name CCC)レコードのmemo欄がnullの為。

---
● Q146 登録したい情報空欄化 一次改善
① update person set memo = 'add_memo' where id = 3 ;
② update person set dept = 'AX3' where dept = 'AX2' ;
③ select * from tbl_history ;

---
● Q147 登録したい情報空欄化 恒久改善
① update person set memo = null where id = 3 ; 
② select * from tbl_history ; 
③
create or replace function simple_func() returns trigger as $$
begin
 if( tg_op = 'INSERT') then
   insert into tbl_history( ope_time, memo ) values ( now(), tg_op || ',' || tg_table_name || ',' || new.id || ',' || new.dept || ',' || new.name || ',' || new.memo) ;
 elsif( tg_op = 'UPDATE') then
   insert into tbl_history( ope_time, memo ) values ( now(), tg_op || ',' || tg_table_name || ',' || new.id || ',' || new.dept || ',' || new.name || ',' || coalesce(new.memo, '') || ',' || old.id || ',' || old.dept || ',' || old.name || ',' || coalesce(old.memo, '')) ;
 elsif( tg_op = 'DELETE') then
   insert into tbl_history( ope_time, memo ) values ( now(), tg_op || ',' || tg_table_name || ',' || old.id || ',' || old.dept || ',' || old.name || ',' || old.memo) ;
 end if ;
 return null ;
end ;
$$  language plpgsql ;

④ update person set dept = 'AX4' where dept = 'AX3' ;
   select * from tbl_history ; 

---
● 追加準備1
update person set dept = 'BX' , name = 'DDD' where id = 4 ;
update person set dept = 'AX' where dept = 'AX4' ;
create table total_psn_cnt( total_cnt int ) ;
insert into total_psn_cnt values (0) ;

---
● Q148 特定カラムが処理された場合のトリガー
①
create or replace function do_sql_func() returns trigger as $$
begin
 update dept set psn_cnt = ( select count(*) from person where dept = old.dept ) where name = old.dept ;
 update dept set psn_cnt = ( select count(*) from person where dept = new.dept ) where name = new.dept ;
 return null ;
end ;
$$  language plpgsql ;

   ※ 本来はinsert, deleteされた場合も考慮が必要だが、今回は練習問題の為、簡易化。（更新のみ考慮）

② drop trigger upt_sql_tg on person ;
   create trigger upt_sql_tg after update of dept on person for row execute function do_sql_func() ;

③ update person set dept = 'BX' where dept = 'AX' ;
　 select * from dept ;

④ update person set dept = 'AX2' where id = 1 ;
   select * from dept ;

---
● Q149 SQL文ごと実施関数の集計処理
① 
create function sum_psn_func() returns trigger as $$
begin
 update total_psn_cnt set total_cnt = ( select count(*) from person ) ;
 return null ;
end ;
$$  language plpgsql ;

create trigger sum_psn_tg after insert or delete or truncate on person for statement execute function sum_psn_func() ;

② insert into person(id, dept, name) values ( 5, 'BX','EEE') ;
   select * from total_psn_cnt ;

③ delete from person where id = 5 ;
   select * from total_psn_cnt ;

   ※ old, newのキーワードがなければSQL文での集計処理ができる事を確認できた。

---
● Q150 テーブル間のズレの防止(外部キー制約と同じ動きをトリガーファンクションで実現）
① insert into dept(dept_id, name) values (3, 'AX2');
② 
create function upt_dept_name() returns trigger as $$
begin
  update person set dept = new.name where dept = old.name ;
  return null ;
end;
$$ language plpgsql ;

create trigger upt_dept_name_tg after update of name on dept for row execute function upt_dept_name() ;

③ update dept set name = 'AX3' where name = 'AX2' ;
   select * from person ;
④ 
create function upt_person_dept() returns trigger as $$
begin
  if not exists ( select * from dept where name = new.dept ) then
     raise exception 'tried to change dept column, but does not exist dept table ' ;
  end if ;
  return new ;
end;
$$ language plpgsql ;

create trigger upt_dept_name_tg before update of dept on person for row execute function upt_person_dept() ;

⑤ update person set dept = 'AX4' where dept = 'AX3' ;
⑥ update person set dept = 'AX3' where id = 3 ;
   select * from dept ;
   select * from person ;

---
● Q151 やや複雑な入力値チェック
①
create function psn_cnt_sql_check_func() returns trigger as $$
begin
 if new.psn_cnt = -1 and old.psn_cnt != 0 then
   raise exception 'tried to put -1 on psn_cnt, but now it`s not 0' ;
 end if ;

 if new.psn_cnt < -1 then
   raise exception 'tried to put less than -1 ' ;
 end if ;
 return new ;
end ;
$$  language plpgsql ;

create trigger psn_cnt_check_tg before update of psn_cnt on dept for row execute function psn_cnt_sql_check_func() ;

② update dept set psn_cnt = -1 where name = 'BX' ;
③ update dept set psn_cnt = -2 where name = 'AX' ;
④ update dept set psn_cnt = -1 where name = 'AX' ;
   update dept set psn_cnt = 10 where name = 'AX3' ;
   select * from dept ;

---
● 追加準備2
create table contact( person_id int, email text) ;
insert into contact values ( 3, 'ccc@example.com' ) ;
create view simple_person as select id , memo from person ;
create view person_contact as select p.id, p.name , c.email from person p join contact c on p.id = c.person_id ;

---
● Q152 トリガーファンクションによる処理代替
① insert into person_contact values ( 5, 'EEE', 'eee@example.com') ;
②
create function ins_psn_con() returns trigger as $$
begin
 insert into person(id, name ) values (new.id, new.name) ;
 insert into contact(person_id, email ) values (new.id, new.email) ;
 return new ;
end ;
$$  language plpgsql ;

③ create trigger ins_psn_con_tg instead of insert on person for row execute function ins_psn_con() ;
④ create trigger ins_psn_con_tg instead of insert on person_contact for row execute function ins_psn_con() ;
⑤ insert into person_contact values ( 5, 'EEE', 'eee@example.com') ;
   select * from person_contact ;
   select * from person ;
   select * from contact ;

---
● Q153 ルールによる代替処理
① insert into simple_person values ( 6, 'memo') ;

② create rule ins_simple_psn_rule as on insert to simple_person do instead select * from simple_person ;
   create rule upt_simple_psn_rule as on update to simple_person do instead select * from simple_person ;
   create rule del_simple_psn_rule as on delete to simple_person do instead select * from simple_person ;
③ create rule ins_total_psn_rule as on insert to total_psn_cnt do instead select * from total_psn_cnt ;
④ select * from pg_rules where rulename = 'ins_total_psn_rule' ;
⑤ select * from pg_rules where tablename = 'simple_person';
⑥ insert into simple_person values ( 7, 'memo') ;
   insert into total_psn_cnt values ( 10 ) ;

---
● Q154 ルールによる追加処理について
① create rule del_total_psn_rule as on delete to total_psn_cnt do also insert into total_psn_cnt select count(*) from person ;
② delete from total_psn_cnt ;
   select * from total_psn_cnt ;
③ drop rule ins_total_psn_rule on total_psn_cnt ;
   insert into total_psn_cnt values (0) ;
   delete from total_psn_cnt ;
   select * from total_psn_cnt ;
④ drop rule del_total_psn_rule on total_psn_cnt ;
   insert into total_psn_cnt values (100) ;
   create rule ins_total_psn_rule as on insert to total_psn_cnt do also delete from total_psn_cnt ;
⑤ insert into total_psn_cnt select count(*) from person ;
　 select * from total_psn_cnt ;

   ※ 期待通り動かない理由は補足ページ参照。

⑥ drop rule ins_total_psn_rule on total_psn_cnt ;
   insert into total_psn_cnt select count(*) from person ;
   create rule upt_total_psn_rule as on update to total_psn_cnt do also insert into tbl_history values ( now(), 'old_cnt: ' || old.total_cnt || ' new_cnt: ' || new.total_cnt ) ;
⑦ update total_psn_cnt set total_cnt = 100 ;
   select * from total_psn_cnt  ;
   select * from tbl_history ;

　※ ルールもnew, oldキーワードが使えることが確認できた

---
● Q155 トリガー,ルール確認に便利なメタコマンド
① \d person
② \d+ simple_person 

---
● 追加準備3
delete from person where id > 4 ;
update person set memo = 2018 where id = 1; 
update person set memo = 2019 where id = 3;
update person set memo = 2020 where id = 4;  
create table salary (psn_id int , sal int ) ;
insert into salary(psn_id) values (1), (3), (4) ;

---
● Q156 ストアドファンクション1
① 
create function get_sal( p_id int) returns int as $$
declare
 cur_year int  ;
 tenure int ;
 f_memo text ;
 base_sal int := 200000 ;
begin 
 select to_char(now(), 'YYYY') into cur_year ;
 select memo into f_memo from person where id = p_id ;
 tenure := cur_year - f_memo::int ;
 return base_sal + tenure * 1000 ;
end;
$$ language plpgsql ;

② select get_sal(1) ;

---
● Q157 ストアドファンクション2
①
create function get_all_sal() returns table ( psn_id int , sal int )  as $$
declare
 psn_rcd record ;
begin 
 for psn_rcd in select id from person loop 
   return query select psn_rcd.id , get_sal( psn_rcd.id ) ;
 end loop ;
end;
$$ language plpgsql ;
　 
② select get_all_sal() ;

---
● Q158 ストアドファンクション3
①
create function set_all_sal() returns text as $$
declare
 psn_rcd record ;
begin 
 for psn_rcd in select id from person loop 
   update salary set sal = ( select get_sal( psn_rcd.id ) ) where psn_id = psn_rcd.id  ;
 end loop ;
return 'update complete!!' ;
end;
$$ language plpgsql ;

② select set_all_sal() ;
③ select * from salary ;

---
● Q159 ストアドプロシージャ
① 
create procedure add_person ( p_id int[] , p_dept text[] , p_name text[]) language plpgsql as $$
begin
 for i in 1..array_length(p_id,1) loop
  if exists ( select * from dept where name = p_dept[i] ) then
   insert into person( id , dept , name) values ( p_id[i], p_dept[i], p_name[i]) ;
   insert into salary( psn_id) values ( p_id[i] ) ;
   commit ;
  else
   rollback ;
  end if;
 end loop ;
end ;
$$ ;

② select * from pg_proc where proname = 'add_person';
③ call add_person( array[ 5, 6, 7 ], array['BX','CX','BX'], array['EEE','FFF','HHH']) ;
④ select * from person ;
⑤ drop procedure add_person ;

---
■ 追加準備4
update salary set sal = 300000 where psn_id = 5 ;
update salary set sal = 307000 where psn_id = 7 ;

---
● Q160 カーソル
① begin ;
   declare my_cs cursor for select * from person ;
② fetch next from my_cs ;
③ fetch next from my_cs ; 
④ fetch prior from my_cs ;
⑤ fetch last from my_cs ;
⑥ close my_cs ;
   rollback ; 

---
● Q161 カーソルとファンクションのシンプルな組み合わせ
① 
create function get_30over_sal_cursor() returns setof int as $$
declare
 sal_cursor cursor for select sal from salary where sal >= 300000 ;
 sal_rcd int ;
begin 
 open sal_cursor ;

 loop
    fetch sal_cursor into sal_rcd  ;
    exit when not found ;
    return next sal_rcd ;

 end loop;
 close sal_cursor ;
end;
$$ language plpgsql ;

② select get_30over_sal_cursor() ;

---
● Q162 カーソル処理の細かい制御
①
create or replace function get_30over_sal_cursor() returns setof int as $$
declare
 sal_cursor cursor for select sal from salary where sal >= 300000 ;
 sal_rcd int ;
begin 
 open sal_cursor ;

 loop
    fetch sal_cursor into sal_rcd  ;
    exit when not found ;
    
    if sal_rcd % 100000 != 0 then
       return next sal_rcd ;
    end if ;
 end loop;
 close sal_cursor ;
end;
$$ language plpgsql ;

② select get_30over_sal_cursor() ;

---
● Q163 for文を使ったカーソル操作
①
create or replace function get_30over_sal_cursor() returns setof int as $$
declare
 sal_cursor cursor for select sal from salary where sal >= 300000 ;
 sal_rcd int ;
begin 
 for sal_rcd in sal_cursor loop
    return next sal_rcd ;
 end loop;
end;
$$ language plpgsql ;

② select get_30over_sal_cursor() ;

---
● Q164 カーソルとプロシージャ
①
create procedure set_30over_sal_cursor() language plpgsql as $$
declare
 sal_cursor cursor for select psn_id , sal from salary where sal >= 300000 ;
 sal_rcd record ;
begin 
 open sal_cursor ;

 loop
    fetch sal_cursor into sal_rcd  ;
    exit when not found ;
    update person set memo = sal_rcd.sal::text where id = sal_rcd.psn_id ;
 end loop;
 close sal_cursor ;
end;
$$  ;

② call set_30over_sal_cursor() ;
   select * from person ;

---

【 7章-1 +αその1】

---
● 準備
psql -U postgres -d postgres
create database test07a ;
\q

psql -U postgres -d test07a
create table dept ( dept_id int, name text, psn_cnt int) ;
create table person ( id int , dept text, name text, memo text) ;
create table tbl_history ( ope_time timestamp, memo text) ;

insert into dept(dept_id, name) values (1, 'AX'), (2, 'BX');

---
● Q165 ルールの処理実行順序1
① create rule ins_test_rule as on insert to tbl_history do also insert into tbl_history values ( now(), 'also!!' ) ;
② insert into tbl_history values ( now(), 'SQL!!') ;
     ※ insertされたらinsertする為、alsoでinsertした際にまたルールが起動する。
　　　　よって無限ループとなってしまう。
③ drop rule ins_test_rule on tbl_history ;

---
● Q166 ルールの処理実行順序2
① create rule ins_test_rule as on insert to dept do also insert into tbl_history select clock_timestamp(), 'also!!' from ( select pg_sleep(5) ) as a  ;
② insert into dept values (6, clock_timestamp(), 1 )  ;
   select * from dept ;
   select * from tbl_history ;
③ drop rule ins_test_rule on dept ;

---
● Q167 ルールの処理実行順序3
① create rule upt_test_rule as on update to dept do also insert into tbl_history values ( clock_timestamp(), 'also!!') ;
② update dept set dept_id = 7 , name = clock_timestamp() where dept_id = ( select 6 from ( select pg_sleep(5) ) a )  ;
③ drop rule upt_test_rule on dept ;

---

【 7章-2 +αその2】

---
● 事前準備
create database test07b ;
psql -U postgres -d test07b
create table dept ( dept_id int , name text ) ;
create table users ( id int , dept_id int , name text , grade numeric ) ;

insert into dept values ( 1 , 'AX' ) , (2 , 'BX'), (3, ''), (4, 'AX') ;
insert into users values ( 1, 1 , 'AAA',  3.5 ), (2, 1, 'BBB', 2.5), (3, 2, 'CDef', 3.2),(4, 3 , 'cdEF', 4.5 ) ;

create table student ( name text , english int , math int , science int ) ;
create table history ( person text , action text , timestamp timestamp)  ;
create table grade ( student_id int ,grade int ) ;
insert into grade values ( 1, 80) , ( 2 ,63) , (3, -1) , (4, -1) , (5 , 96) ;

---
● Q168 generate_series を使った試験データ投入
① insert into student select ( name || int_num ) as create_num  , round( random() * 100), round( random() * 100), round( random() * 100 ) from users , generate_series(1, 10) as int_num;

② insert into history values ( 'A', 'call', current_timestamp - interval '365 days') , ('B','replay', current_timestamp - interval '365 days') ;
se
   insert into history select person , action ,create_time from history , (select generate_series(current_timestamp - interval '364 days' , current_timestamp - interval '340 days', '1 days' ) as create_time )a order by a.create_time  ;

---
● Q169 テーブルの情報を1列で取得する
① select dept from dept ;
② select dept , users from dept , users ;

---
● Q170 重複を削ったうえで、nullもカウントするには？
select count(coalesce( a.name, 'xx')) from (select distinct(name) from dept ) a ;

---
● Q171 特定情報を持つ情報を除いた計算
select avg(nullif(grade, -1)) from grade ;

---
● Q172 レコードごとの最大値・最小値
select * , greatest(english, math ,science) , least(english, math,science) from student ;

---
● Q173 ランダムにデータを取得する
① select * from student tablesample bernoulli(10) ;
② select * from student order by random() limit 4 ;

---
● Q174 selectした結果をそのままテーブルとして生成する
① create table student2 as select * from student limit 10 ;
② select * from student2 ;

---
● Q175 テーブルの継承による親子テーブル化
① create table users2 ( id int , dept_id int , name text , grade numeric ) inherits ( users );
② \d users2
③ insert into users2 values ( 5, 3, 'DDD', 3.5) ;
④ select * from users ;
⑤ select * from users2 ;
⑥ select * from only users ;

---
● Q176 現在情報の取得
① select current_database() ;
② select current_schema() ;
③ select version() ;
④ select current_role;

---
● Q177 様々なindex
① create unique index uni_idx on users(id) ;
② create index part_idx on users ( grade ) where grade >= 3 ;
③ create index func_idx on users ( upper(name)) ;
④ create index brin_id on users using BRIN ( id ) ;
⑤ \d users

---
● Q178 roleのDB権限
① create role user01 login password 'aaa' ;
② \q
   psql -U user01 test07b
③ create schema schema_test01 ;
④ \q
   psql -U postgres test07b
⑤ grant create on database test07b to user01 ;
⑥ \q
   psql -U user01 test07b
⑦ create schema schema_test01 ;
   \q
   psql -U postgres test07b

---
● Q179 roleの作成+α
① create user user99 with password 'aaa' ;
② create group group01 ;
③ grant select on student to group01 ;
④ grant group01 to user99 ;
   \q
   psql -U user99 test07b
⑤ select * from student ;
   insert into student values ( 'A', 1,1,1) ;
   \q
   psql -U postgres test07b

---
● Q180 ロングトランザクション排除(強制停止)
① begin;
② select * from pg_stat_activity ;
③ select pg_terminate_backend(52100) ;
    ※ pidは環境により異なる
④ select * from student ;
⑤ rollback ;

---
● Q181 ロングクエリの停止
① select pg_sleep(100) ;
② select * from pg_stat_activity ;
③ select pg_cancel_backend(52100) ;
     ※ pg_terminate_backend()でも可能だが③のログメッセージは多少変化する

---
● Q182 サイクルシーケンスの動確 
① create sequence test_seq increment 10 maxvalue 30 start 10 cycle ;
② select nextval('test_seq') ;

---
● Q183 試験データ作成
1. create table src_ip ( ip inet ) ;
2. create table dst_ip ( ip inet ) ;
3. insert into src_ip select '192.168.0.1'::inet + generate_series(0, 4) ;
4. insert into dst_ip select '172.16.0.1'::inet + generate_series(0, 4) ;
5. create sequence test_seq1 ;
6. create sequence test_seq2 ;
7. create table test_tbl ( id int default nextval('test_seq1') , src_ip inet , dst_ip inet) ;
8. insert into test_tbl(src_ip , dst_ip)  select * from src_ip , dst_ip ;
9. create table port_tbl ( id int default nextval('test_seq2') , src_port int , dst_port int) ;
10. insert into port_tbl (src_port , dst_port) select * from (select 50000 + generate_series(0,4)) a, (select 60000 + generate_series(0,4)) b ;
11. alter table test_tbl add column src_port int ;
12. alter table test_tbl add column dst_port int ;
13. update test_tbl set src_port = port_tbl.src_port from port_tbl where test_tbl.id = port_tbl.id ;
14. update test_tbl set dst_port = port_tbl.dst_port from port_tbl where test_tbl.id = port_tbl.id ;
15. create sequence test_seq3 ;
16. create table time_tbl ( id int default nextval('test_seq3') , entry_time timestamp(0)) ;
17. insert into time_tbl (entry_time) SELECT * FROM generate_series('2022-01-01 00:00:00'::timestamp,'2023-01-25 00:00:01', '1 seconds') limit 25;
18. alter table test_tbl add column proto text ;
19. update test_tbl set proto = 'TCP' ;
20. alter table test_tbl add column entry_time timestamp(0) ;
21. alter table test_tbl add column latency int ;
22. update test_tbl set latency = 300 ;
23. update test_tbl set entry_time = time_tbl.entry_time from time_tbl where test_tbl.id = time_tbl.id ;
24. copy ( select entry_time, src_ip , dst_ip , src_port , dst_port , proto , latency from test_tbl order by id ) to 'C:\Users\Public\data3.csv' delimiter ',' csv ;