SQL問い合わせとリレーショナル代数の基礎

リレーショナル代数はリレーショナル・データベースの理論的基盤であり，集合論に基づいている．これは，和集合や差集合などの基本的集合演算と，射影，選択，θ-結合，商などのリレーショナル固有の演算から構成される．θ-結合は重要な概念で，2つのリレーションの直積集合から特定条件を満たす要素のみを選択する演算であり，「R[AiθBj]S」と表記される．SQL問い合わせは，SELECT，FROM，WHERE句を組み合わせて条件を満たすデータを抽出することを特徴とする．θ-結合，はSQLにおいて「SELECT * FROM R, S WHERE R.AiθS.Bj」の形で実現される．データベースの整合性を保証するためにトランザクション機能が重要である．トランザクションは「begin transaction;」で開始し「commit;」で確定する一連の操作を単一のものとして扱い，エラー発生時には「rollback;」によってトランザクション内のすべての変更を取り消すことができる．

大学で使用した自作の資料等を，手直しの上公開している．クリエイティブ・コモンズ BY NC SA．

SQL のデータ型
SQL の標準規格が定めるデータ型は多岐にわたるが，代表的なものを以下にまとめる．
- NULL: 空値 (NULL値)
- integer: 符号付き整数 (signed integer) ※ SQLite では BIGINT と integer は同じく「8バイトの整数」を意味する
- real: 浮動小数点値 (floating point value)
- char, text: 文字列 (text string) ※ char(n) のように，最大長を指定することも可能である．
- datetime: 日付や時刻など
  * 日時を示す datetimeについて．SQLite では datetime と text は同じ「可変長文字列」を意味するが，明確な意図を示すために使い分けることを推奨する．
- bool: ブール値
- BLOB: バイナリ・ラージ・オブジェクト (Binary Large Object)．入力データがそのままの形で格納される (stored exactly as it was input)
　
SQL の文字列定数
SQL の規格では，文字列定数はシングルクオーテーションマーク「'」で囲むことが規定されている．
SQLでのプレフィックス文字「N」
SQL におけるプレフィックス文字「N」の使用について説明する．

日本語などの多バイト文字を含む列は，SQLのCHARACTER型ではなく，NATIONAL CHARACTER型（NCHAR型とも表記する．同義）として定義するのが一般的である．NATIONAL CHARACTER型での比較では，「専攻 = N'情報知能工学'」のように，定数「'情報知能工学'」の前にプレフィックス文字「N」を付加する（これがSQLの正式な記法である．ただし，文献によっては省略している場合がある）．
SQL の比較演算子
比較演算子は2項述語として機能する． SQL における数値や文字列の比較演算子には以下のものがある：
- <
- <=
- =
- >=
- >
- <>
SQL の論理演算子
AND, OR, NOT
IS NULL
NULL 値を含む行を抽出する際に使用する．
\'
「\'」は，シングルクオート「'」を含む文字列を扱うためのエスケープ記法である．
LIKEと%と_
SQLにおける文字列パターンマッチングに使用するキーワードである．
SQL 問い合わせ (SQL query)
SQL 問い合わせは，リレーショナルデータベースに格納された1つまたは複数のテーブルを操作する．問い合わせの評価結果として，既存のリレーショナルデータベース内のテーブルをそのまま取得することは稀で，通常は新しいテーブルが生成される．

SQL 問い合わせの基本構文は，SELECT, FROM, WHERE を使用する．基本的な文法は以下の通りである．※ SQLの文法は多様であるが，ここでは基本的な場合に限定して説明する．
```
    SELECT <list-of(result-column)>
    FROM <list-of(table-name)>
```
または
```
    SELECT <list-of(result-column)>
    FROM <list-of(table-name)>
    WHERE <expression>
```
具体例を用いて説明する．例えば，
```
SELECT A1, A2, ..., An
FROM   T1, T2, ..., Tm
WHERE  <expression>
```
このように記述すると，m 個のテーブル T1, T2, ..., Tm の直積集合から <expression> を満たす行のみを選択し，結果のテーブルから属性 A1, A2, ..., An を出力する．

WHERE 句が省略された場合，例えば，
```
SELECT A1, A2, ..., An
FROM   T1, T2, ..., Tm
```
この場合，m 個のテーブル T1, T2, ..., Tm の直積集合から属性 A1, A2, ..., An を出力する．
- SELECT <list-of(result-column)>
  SELECT 句には，問い合わせ結果として取得したい列名または列名を含む式の，1個以上の並びを指定する．複数の列を指定する場合は半角のカンマ「,」で区切る．問い合わせ結果として得られるテーブルの列順序は，SQL の SELECT に指定した列名または列名を含む式の順序に従う．
  
  SELECT 句で列名を指定する際，「<テーブル名>.<列名>」のように，「<テーブル名>.」を前置する必要がある場合がある．これは，FROM 句に複数のテーブルを指定し，かつ，それらのテーブルが同名の属性を持つ場合に必要である．例えば，「科目」と「履修」という2つのテーブルが，同じ属性名「科目番号」を持つ場合，以下のように「科目番号」の前に「科目.」を付加して，曖昧さを解消する必要がある．
```
    SELECT 科目.科目番号, 科目名
    FROM 科目, 履修
        WHERE 科目.科目番号 = 履修.科目番号 AND 履修.学生番号 = 00001;
```
  列名として「*」または「<テーブル名>.*」という特殊な記法が使用可能で，これは全ての属性名を列挙したのと同等の意味を持つ．
- FROM <list-of(table-name)>
  FROM 句には，テーブル名の1個以上の並びを指定する．複数のテーブルを指定する場合は半角のカンマ「,」で区切る．
  
  テーブル参照リスト内でタプル変数を定義することも可能である．例えば，以下の SQL 文では X と Y という 2つのタプル変数を定義している．
```
            SELECT X.社員番号, Y.社員番号
            FROM 社員 X, 社員 Y
            WHERE X.部長 = Y.社員番号 AND X.給与 > Y.給与
```
- WHERE <expression>
  WHERE 句には探索条件を指定する．複数の探索条件を組み合わせる場合は，論理演算子 AND, OR を使用する．探索条件は，FROM 句で指定したテーブルの属性名や，タプル変数の変数名を含む式である．探索条件で使用可能なキーワードには以下のものがある：
  - 比較演算（<, <=, =, >=, >, <>）
  - 論理演算（AND, OR, NOT）
  - 各種述語（BETWEEN, IN, LIKE, NULL, EXIST など）
  など
入れ子型質問
SQL の入れ子型質問では，探索条件内にSELECT文を含む副問い合わせを指定する．入れ子型質問の基本構文は以下の通りである：
```
    属性名 IN ＜副問い合わせ＞
```
または
```
　　　　値     IN ＜副問い合わせ＞
```
これは，指定した属性名や値が副問い合わせの結果集合に含まれることを表す．
結果集合を (x1, x2, ..., xn) とした場合，「x IN (x1, x2, ..., xn)」は，x = x1 OR x = x2 OR ... OR x = xn と同値である．
θ-結合演算
R(A1, A2, …, An) とS(B1, B2, …, Bm)をリレーションとする．R の属性 Ai と S の属性 Bj 上のθ-結合演算は「R[AiθBj]S」と表記する．定義は以下の通りである（θは比較演算子）．

R[AiθBj]S は，R と S の直積集合 (R×S) から「R.AiθS.Bj」を満たす要素を選択したものである．

つまり，θ-結合演算は，直積演算と選択演算を組み合わせて，次のように定義することもできる．
R[Ai θ Bj]S = (R×S) [R.Ai θ S.Bj]
θ-結合演算「R[AiθBj]S」は SQL で以下のように表現できる：
```
SELECT *
FROM R, S
WHERE <AiθBj>
```
* （参考）リレーショナル代数式では次のように表現できる：
```
　　R[Aiθ Bj]S = { (t, u) | t ∈ R ∧ u ∈ S ∧ t[Ai]θu[Bj] }
```
ただし，「(t, u)」は，t = (a1, a2, …, an), u = (b1, b2, …, bm) のとき，(t, u) = (a1, a2, …, an, b1, b2, …, bm) となる n + m 項のタプルを表す．

*　関係演算における「射影」演算では，例えば射影 results[name, student_name] の場合，属性 name と teacher_name のみを抽出し，重複行が存在する場合は1つのみを残して他を除去する．
結合問い合わせ（結合質問）
結合問い合わせとは，WHERE 句の条件式にθ-結合演算を含む問い合わせを指す．結合問い合わせでは，FROM 句に複数のテーブル名が出現する．ただし，結合問い合わせの結果は，通常の問い合わせと同様に1つのテーブルとして得られることに注意が必要である．結合問い合わせでは，参照リストに指定されたテーブル間の結合が実行される．

SQL 問い合わせの基本構文は SELECT, FROM, WHERE を使用する．基本的な文法は以下の通りである．※ SQLの文法は多様であるが，ここでは基本的な場合に限定して説明する．
```
    SELECT <list-of(result-column)>
    FROM <list-of(table-name)>
```
あるいは
```
    SELECT <list-of(result-column)>
    FROM <list-of(table-name)>
    WHERE <expression>
```
商演算
R(A1, A2, …, An-m, B1, B2, …, Bm) をn次のリレーション，S(B1, B2, …, Bm)をm次のリレーションとする（ただし，m < n）．R をSで割った商を「R÷S」と表記する．定義は以下の通りである：
```
R÷S = { t | t ∈R[A1, A2, …, An-m] ∧ (∀u∈S) ((t, u) ∈R )}
```
ただし，「(t, u)」は，t = (a1, a2, …, an-m), u = (b1, b2, …, bm) のとき，(t, u) = (a1, a2, …, an, b1, b2, …, bm) となる n 項のタプルを表す．「∀」は全称記号で，全て（for all）を意味する．「R[A1, A2, …, An-m]」は射影演算を表し，「{ u | u ∈ dom(A1)×dom(A2)×…×dom(An-m) ∧ (∃t∈R) (t[A1] = u[A1]∧t[A2] = u[A2]∧ … ∧t[An-m] = u[An-m])}」を意味する．
テキスト・エンコーディング (text encoding)
データベースでは，文字データはエンコード (encoding) された形式で格納される．エンコード方式には複数の種類が存在する． SQLite バージョン 3 では，以下のエンコード方式から選択できる：
- UTF-8
- big-endian UTF-16
- little-endian UTF-16
リレーショナル代数演算子
リレーショナル代数は，集合論に基づく体系である．集合演算と，リレーショナル代数固有の演算で構成される．

　【集合演算】　　和集合演算，差集合演算，共通集合演算，直積集合演算

　【リレーショナル代数固有の演算】　　射影演算，選択演算，結合演算，商演算

これら8つの演算は必ずしも独立ではない．例えば，共通集合演算は差集合演算で代替可能である．結合演算は直積演算と選択演算の組み合わせで実現できる．商演算は直積演算，射影演算，差演算を組み合わせて表現できる．
和両立
リレーションR(A1, A2, …, An) とS(B1, B2, …, Bm) が和両立であるとは，以下の2条件を満たす場合を指す：
1. RとSの次数が等しい（n = m）
2. 各i（1≦i≦n）について，AiとBiのドメインが等しい
和集合演算
RとSを和両立なリレーションとする．RとSの和集合演算は「R∪S」と表記し，以下のように定義される：
```
R∪S = { t | t ∈ R ∨ t ∈ S }
```
差集合演算
RとSを和両立なリレーションとする．RとSの差集合演算は「R−S」と表記し，以下のように定義される：
```
R−S = { t | t ∈ R ∧ ¬( t ∈ S ) }
```
共通集合演算
RとSを和両立なリレーションとする．RとSの共通集合演算は「R∩S」と表記し，以下のように定義される：
```
R∩S = { t | t ∈ R ∧ t ∈ S }
```
直積集合演算
R(A1, A2, …, An) とS(B1, B2, …, Bm)をリレーションとする．RとSの直積集合演算は「R×S」と表記し，以下のように定義される：
```
　　R×S = { (r, s) | r ∈ R ∧ s ∈ S }
```
ただし，「(r, s)」は，r = (a1, a2, …, an), s = (b1, b2, …, bm) のとき，(r, s) = (a1, a2, …, an, b1, b2, …, bm) となる n + m 項のタプルを表す．
射影演算
R(A1, A2, …, An) をリレーションとする．全属性集合 {A1, A2, …, An} の部分集合 X = {Ai1, Ai2, …, Aik}について，R のX上の射影は「R[X]」または「R[Ai1, Ai2, …, Aik]」と表記し，以下のように定義される：
```
R[Ai1, Ai2, …, Aik] = { u | u ∈ dom(Ai1)×dom(Ai2)×…×dom(Aik)
∧ (∃t∈R) (t[Ai1] = u[Ai1]∧t[Ai2] = u[Ai2]∧ … ∧t[Aik] = u[Aik])}
```
*「R[Ai1, Ai2, …, Aik]」は「πAi1, Ai2, …, Aik(R)」とも表記される．
選択演算
R(A1, A2, …, An) をリレーションとする．R の属性AiとAj上の選択演算を「R[Ai θ Aj]」と表記し，以下のように定義される．θは2項述語である：
```
　　R[Aiθ Aj] = { t | t ∈ R ∧ t[Ai]θt[Aj] }
```
R の属性Aiと値cに関する選択演算を「R[Aiθc]」と表記し，以下のように定義される：
```
　　R[Aiθc] = { t | t ∈ R ∧ t[Ai]θc }
```
　※「R[AiθAj]」や「R[Aiθc]」は「πAiθAj(R)」や「πAiθc(R)」とも表記される．
リレーショナル代数での比較演算子
比較演算子は2項述語として機能する．リレーショナル代数では，数値および文字列の比較演算子として，＞，≧，＝，≦，＜，≠ の6種類が一般的である．※ SQLでは「<=」，「>=」，「<>」と表記することに注意すること．
リレーショナル演算
*　定義では論理式等を使用する．論理式の記法は集合論を基礎とする形式を採用しており（テキストと同様），タプルリレーショナル論理式（一階述語論理を基礎とする）とは異なる．
- 和集合演算
  R∪S R∪S = { t | t ∈ R ∨ t ∈ S }
- 差集合演算
  R−S R−S = { t | t ∈ R ∧ ¬( t ∈ S ) }
- 共通集合演算
  R∩S R∩S = { t | t ∈ R ∧ t ∈ S }
- 直積集合演算
  R×S R×S = { (r, s) | r ∈ R ∧ s ∈ S }
  
  * (r, s) は，r = (a1, a2, …, an), s = (b1, b2, …, bm) のとき，(r, s) = (a1, a2, …, an, b1, b2, …, bm) となる n + m 項のタプル
- 射影演算
  R[Ai1, Ai2, …, Aik]
  
  R[Ai1, Ai2, …, Aik] = { u | u ∈ dom(Ai1)×dom(Ai2)×…×dom(Aik) ∧ (∃t∈R) (t[Ai1] = u[Ai1]∧t[Ai2] = u[Ai2]∧ … ∧t[Aik] = u[Aik])}
- 選択演算
  R[AiθAj]，R[Aiθc]
  
  R[AiθAj] = { t | t ∈ R ∧ t[Ai]θ t[Aj] }
  
  R[Aiθc] = { t | t ∈ R ∧ t[Ai] θc }
- 結合演算
  R[Ai θ Bj]S
  
  R[Ai θ Bj]S = { (t, u) | t ∈ R ∧ u ∈ S ∧t[Ai] θ u[Bj] }
  
  * (t, u)は，t = (a1, a2, …, an), u = (b1, b2, …, bm) のとき，(t, u) = (a1, a2, …, an, b1, b2, …, bm) となる n + m 項のタプル
- 自然結合
  R * S
  
  R * S = (R[C1 = C1, C2 = C2, …, Ck = Ck]S) [A1, A2, …, An, D1, D2, …, Dm-k],
  
  * C1, C2, …, Ck は，AとB の結合属性， {B1, B2, …, Bm}−{C1, C2, …, Ck} = {D1, D2, …, Dm-k}
- 商演算
  R÷S R÷S = { t | t ∈R[A1, A2, …, An-m] ∧ (∀u∈S) ((t, u) ∈R )}
　
タプルリレーショナル論理
タプルリレーショナル論理は，一階述語論理に基づいてリレーションを宣言的に記述する形式である．タプルリレーショナル論理表現では，以下に説明する「アトム」と「式」を組み合わせて使用する．

※　「∈」のような集合論の記号は使用しない．
1. アトム
  - リレーション「R(u)」の形式　
  - タプルの属性値同士の比較「u[Ai] θ v[Bj]」の形式
  - タプルの属性値と定数との比較「u[Ai] θ c」の形式
  *　「R(u)」は，u がリレーションR のタプルである場合に真(true)となる述語である．
  
  「u[Ai]」は，u = (a1, a2, …, an) の第i成分を表す．「v[Bi]」も同様である．
2. 式　（以下，式を「Φ」と表記）
  - アトム
  - 式の論理否定 ¬Φ　の形式
  - 式の論理積，論理和 Φ1∧Φ2，Φ1∨Φ2　の形式
  - 全称記号や存在記号による変数の定義 (∃u)Φ(u), (∀u)Φ(u)　の形式
  全称記号や存在記号によって定義された変数は，自由変数ではなく束縛変数となる．
  
  タプルリレーショナル論理で表現されたリレーションは，「{ t | t が満たすべき条件を示した式}」の形式となる（この式が t 以外の自由変数を含まないことが，タプルリレーショナル論理表現の成立条件である）．

演習で行うこと

SQLiteman の起動と終了
SQLiteman で新しいデータベースを作成する
SQLiteman で既存のデータベースを開く

SQL を用いたテーブル定義と一貫性制約の記述

リレーショナル・スキーマ (relational schema):

products(id, product_name, type, price, created_at, updated_at)

SQL プログラム:

create table products ( id integer primary key autoincrement not null, product_name text UNIQUE not null, type text not null, price real, created_at datetime not null, updated_at datetime );

SQL を用いたテーブルへの行の挿入
begin transaction; insert into products values( 1, 'Fukuoka apple', 'apple', 50, datetime('now', 'localtime'), NULL ); insert into products values( 2, 'Kumamoto orange L', 'orange', 30, datetime('now', 'localtime'), NULL ); insert into products values( 3, 'Kumamoto orange M', 'orange', 20, datetime('now', 'localtime'), NULL ); insert into products values( 4, 'Fukuoka melon', 'melon', NULL, datetime('now', 'localtime'), NULL ); commit;

SQL 問い合わせの発行と評価結果の確認

SELECT * FROM products;
SELECT * FROM products WHERE type = 'orange';
SELECT * FROM products WHERE price > 25;

SQL を用いたテーブル定義と一貫性制約の記述

リレーショナル・スキーマ (relational schema):

orders(id, year, month, day, customer_name, product_id, qty, created_at)

SQL プログラム:

create table orders ( id integer primary key autoincrement not null, year integer not null check ( year > 2008 ), month integer not null check ( month >= 1 AND month <= 12 ), day integer not null check ( day >= 1 AND day <= 31 ), customer_name text not null, product_id text not null, qty integer not null DEFAULT 1 check ( qty > 0 ), created_at datetime not null );

SQL を用いたテーブルへの行の挿入
begin transaction; insert into orders values( 1001, 2014, 10, 26, 'kaneko', 1, 10, datetime('now', 'localtime') ); insert into orders values( 1002, 2014, 10, 26, 'miyamoto', 2, 2, datetime('now', 'localtime') ); insert into orders values( 1003, 2014, 10, 27, 'kaneko', 3, 8, datetime('now', 'localtime') ); insert into orders values( 1004, 2014, 10, 27, 'kaneko', 3, 8, datetime('now', 'localtime') ); commit;

SQLiteman を用いたデータのブラウズ
SQL 問い合わせの発行と評価結果の確認

SQL を用いたテーブル定義と一貫性制約の記述

リレーショナル・スキーマ (relational schema, created_at):

bundles(id, order_id, qty, shipping_id)

shippings(id, year, month, day, created_at)

SQL プログラム:

create table bundles ( id integer primary key autoincrement not null, order_id integer not null REFERENCES orders(id), qty integer not null, shipping_id integer not null REFERENCES shippings(id), created_at datetime not null ); create table shippings ( id integer primary key autoincrement not null, year integer not null check ( year > 2008 ), month integer not null check ( month >= 1 AND month <= 12 ), day integer not null check ( day >= 1 AND day <= 31 ), created_at datetime not null );

SQL を用いたテーブルへの行の挿入

begin transaction;
insert into bundles values( 1, 1001, 2, 101, datetime('now', 'localtime') );
insert into bundles values( 2, 1001, 1, 102, datetime('now', 'localtime') );
insert into bundles values( 3, 1002, 1, 103, datetime('now', 'localtime') );
insert into bundles values( 4, 1003, 2, 104, datetime('now', 'localtime') );
insert into bundles values( 5, 1004, 4, 104, datetime('now', 'localtime') );
commit;

begin transaction;
insert into shippings values( 101, 2019, 10, 28, datetime('now', 'localtime') );
insert into shippings values( 102, 2019, 10, 31, datetime('now', 'localtime') );
insert into shippings values( 103, 2009, 11,  1, datetime('now', 'localtime') );
insert into shippings values( 104, 2009, 11,  2, datetime('now', 'localtime') );
commit;

SQLiteman を用いたデータのブラウズ
SQL 問い合わせの発行と評価結果の確認

演習を行うために必要になる機能や文法

SQLite 3のSQL仕様の詳細は http://www.hwaci.com/sw/sqlite/lang.html (English Web Page) をご参照ください．

SQL 問い合わせ (SQL query)
- SELECT <list-of(result-column)> FROM <list-of(table-name)>
  table-name で指定したテーブルの直積集合から，list-of(result-column) で指定した属性を抽出，または式を評価して新しいテーブルを生成し出力します．
- SELECT <list-of(result-column)> FROM <list-of(table-name)> WHERE <expression>
  table-name で指定したテーブルの直積集合から，<expression> を満たす行のみを選択し，その結果から list-of(result-column) で指定した属性を抽出，または式を評価して新しいテーブルを生成し出力します．

SQLiteman の起動と終了

SQL コマンドの編集や実行，データベース管理のためのインターフェースとして SQLiteman を活用する．

SQLiteman の起動
* Windows での SQLiteman の起動方法

「SQLiteman」アイコンをダブルクリックして起動する

SQLiteman の新規ウィンドウが開き，操作が可能になる．

* Ubuntu での SQLiteman の起動方法

端末で「sqliteman」と入力し，Enter キーを押すとアプリケーションが起動する．

SQLiteman の新規ウィンドウが開き，作業を開始できる．
ヘルプの表示
「Help」メニューから「Help Content」を選択してヘルプ情報にアクセスする．

ヘルプ内容を確認し，SQLiteman の機能や使用方法について理解を深める．
SQLiteman の終了
「File」メニューから「Exit」を選択して適切に終了する．

SQLiteman で新しいデータベースを作成する

以下の手順で新規データベースを効率的に作成する．この操作により永続的なデータベースファイルが生成される．

新規データベース作成のため，「File」メニューから「New」オプションを選択する．
データベースの新規作成を開始する
* Ubuntu での実行例（データベースファイル名「mydb2」を作成する場合）

データベースファイル名として mydb2 を入力し，「保存」ボタンをクリックして確定する．

* データベースファイル名は任意だが，検索性と互換性のため英語名を強く推奨する．

* 新規データベースを作成する場合は，存在しない新しいファイル名を指定し，データの上書きを防止する．
データベースの内容を確認する
初期状態では，テーブル (Tables) 数もビュー (Views) 数も 0 であり，これから構築していく．
テキスト・エンコーディングの設定を確認する
まず，「Pragmas」タブをクリックして設定を表示する．

encoding行に「UTF-8」と表示されていることを確認し，文字化けを防止する．

* データベースの文字エンコーディングを変更する必要がある場合は， SQLiteman などのGUIツールではなく， sqlite.exe を使用し，「PRAGMA encoding=...;」コマンドで変更することを推奨する．例えば「UTF-16le」に変更する場合は「PRAGMA encoding=UTF-16le;」を実行する．
終了
操作の混乱を防ぐため，一旦 SQLiteman を終了し，作業を区切る．

「File」メニューから「Exit」を選択して終了する．

SQLiteman で既存のデータベースを開く

既存のデータベースは，以下の手順で効率的に開くことができる．

以下のステップで，既存のデータベースファイルを正確に開き，作業を継続する．

「File」メニューから「Open」を選択してファイル選択ダイアログを表示する．
データベースファイルを開く
* Windows での実行例（「C:\SQLite\mydb」を開く場合）

データベースファイル C:\SQLite\mydb を選択し，「開く」ボタンをクリックして読み込みを開始する

* Ubuntu での実行例（「SQLite/mydb」を開く場合）

データベースファイル mydb2 を選択し，「開く」ボタンをクリックして処理を継続する

ホームディレクトリ直下の mydb2 ファイルを正確に選択する．　
データベースの内容を確認し，構造を把握する
◆ 表示例（データベースの内容により表示が異なる）
「Tables」の数が1以上の場合は展開して詳細を確認できる
「Tables」の数が1以上の場合，「Tables」を展開するとテーブル一覧 (List of Tables) が表示され，データベース構造を把握できる

◆ 展開例
テキスト・エンコーディングの設定を確認し，文字化けを防止する
まず，「Pragmas」タブをクリックして設定情報を表示する．

encoding行に「UTF-8」と表示されていることを確認し，正しい文字表示を保証する．

* データベースの文字エンコーディングを変更する必要がある場合は， SQLiteman などのGUIツールではなく， sqlite.exe を使用し「PRAGMA encoding=...;」コマンドで変更することを推奨する．例えば「UTF-16le」に変更する場合などは，コマンドラインからの操作が確実である．
「System Catalogue」を展開し「sqlite_master」をクリックすると，データベース・スキーマ (database schema) が表示され，テーブル構造を詳細に確認できる
◆ 表示例（１）

データベースが空の場合，表示も空となり，テーブル定義が必要であることを示している．

◆ 表示例（２）

score_records, order_records などのテーブルが定義済みの場合，それらの構造情報が表示される

SQL を用いたテーブル定義と制約の記述

SQL を使用して，products テーブルを効率的に定義し，データ整合性を保証する制約を設定する．

リレーショナル・スキーマ (relational schema): products(id, product_name, type, price, created_at, updated_at)

products テーブルの定義
以下のSQL文を正確に入力し，「Run SQL」アイコンをクリックして実行する
create table products ( id integer primary key autoincrement not null, product_name text UNIQUE not null, type text not null, price real, created_at datetime not null, updated_at datetime );
* 「SQL Editor」ウィンドウで正確なSQL文を作成することで，効率的なデータベース構築が可能になる．
コンソール出力の確認
エラーメッセージが表示されていないことを確認し，テーブル作成の成功を確認する．

SQL を用いたテーブルへの行の挿入

以下のような構造と内容を持つ products テーブルを作成する．

以下の手順で，SQL を使用して products テーブルに必要なデータ行を正確に挿入する

SQL プログラムの作成
「insert into ...」文で行を挿入する．ここでは 4つのSQL文を記述し，「begin transaction」と「commit」で囲むことでデータ整合性を保証する．

* つまり，挿入前に begin transaction; を実行してトランザクションを開始し，一連の挿入が完了後に commit; を実行してトランザクションを確定する．
begin transaction; insert into products values( 1, 'Fukuoka apple', 'apple', 50, datetime('now', 'localtime'), NULL ); insert into products values( 2, 'Kumamoto orange L', 'orange', 30, datetime('now', 'localtime'), NULL ); insert into products values( 3, 'Kumamoto orange M', 'orange', 20, datetime('now', 'localtime'), NULL ); insert into products values( 4, 'Fukuoka melon', 'melon', NULL, datetime('now', 'localtime'), NULL ); commit;
insert into 文には2つの記述方法があり，状況に応じて適切な方法を選択できる．

* テーブル定義の順序で全属性値を列挙する方法
```
insert into products values( 1, 'Fukuoka apple',     'apple',  50, datetime('now', 'localtime') );
```
* 属性名を指定して値の順序を明示的に指定する方法

この場合，属性値を省略すると，テーブル定義時に指定されたデフォルト値が自動的に使用される
```
insert into products values( 2, 'Kumamoto orange L', 'orange', 30, datetime('now', 'localtime') );
```
複数SQL文の一括実行
複数のSQL文を効率的に一括実行するため，カーソルを先頭行に正確に移動し，「Run multiple SQL statements ...」ボタンをクリックして処理を開始する．
「Script Output」ウィンドウの確認
エラーメッセージが表示されていないことを確認し，データ挿入の成功を確認する．
* エラーメッセージが表示された場合は，SQL文を正確に修正し，再度「Run multiple SQL statements ...」ボタンをクリックしてエラーを解消する．

例えば，以下のようなエラーが発生した場合の対処法：
- 「begin transaction」は完了しているが，それ以降の処理が中断されている．
- それ以降の文は実行されていないため，再開する必要がある．
この場合，再開したい行に正確にカーソルを移動し，「Run multiple SQL statements ...」ボタンをクリックして処理を継続する．

SQL 問い合わせの発行と評価結果の確認

ここでは，SQL による問い合わせの実行例を示し，効率的なデータ検索方法を説明する． SQL 問い合わせの詳細は別ページで説明するが，ここではテーブルの内容を確認する基本的な方法を学ぶ．

テーブルの全行を表示して全体像を把握する

SELECT * FROM products;

条件を満たす行のみを絞り込んで表示する

SELECT * FROM products WHERE type = 'orange';

SELECT * FROM products WHERE price > 25;

SELECT * FROM products WHERE price > 18;

SQL を用いたテーブル定義と一貫性制約の記述

SQL を使用して，orders テーブルを定義し，一貫性制約を設定する．データベースの基本となるテーブル構造と完全性を確保するための制約を適切に記述することが重要である．

orders テーブルの定義

以下のSQL文を入力し，「Run SQL」アイコンをクリックする．このSQL文は主キー，制約条件，既定値など重要な要素を含んでいる．

create table orders (
    id            integer  primary key autoincrement not null,
    year          integer  not null check ( year > 2008 ),
    month         integer  not null check ( month >= 1 AND month <= 12 ),
    day           integer  not null check ( day >= 1 AND day <= 31 ),
    customer_name text     not null,
    product_id    text     not null,
    qty           integer  not null DEFAULT 1 check ( qty > 0 ),
    created_at    datetime not null );

コンソール出力の確認
エラーメッセージが表示されていないことを確認する．エラーがない場合，テーブルが正常に作成されたことを意味する．

SQL を用いたテーブルへの行の挿入

以下のような orders テーブルを作成する．このテーブルには注文データが格納される．

以下の手順で，SQL を使用して orders テーブルに行を挿入する．データベースにデータを格納するための基本操作である．

SQL プログラムの作成

「insert into ...」文で行を挿入する．ここでは 4つのSQL文を記述し，「begin transaction」と「commit」でトランザクション処理として囲む．これにより，データの一貫性が保たれる．

begin transaction;
insert into orders values( 1001, 2014, 10, 26,  'kaneko',   1, 10, datetime('now', 'localtime') );
insert into orders values( 1002, 2014, 10, 26,  'miyamoto', 2, 2, datetime('now', 'localtime') );
insert into orders values( 1003, 2014, 10, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
insert into orders values( 1004, 2014, 10, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
commit;

複数SQL文の一括実行
複数のSQL文を一括実行するため，カーソルを先頭行に移動し，「Run multiple SQL statements ...」ボタンをクリックする．これにより，すべてのSQL文が順番に実行される．
「Script Output」ウィンドウの確認
エラーメッセージが表示されていないことを確認する．正常に実行された場合，データが適切にテーブルに挿入されている．

SQLiteman を用いたデータのブラウズ

products テーブル
まず，オブジェクト・ブラウザ (Object Browser) の「Tables」を展開する．データベース内のテーブル一覧が表示される．

次に，テーブル productsを選択し，ダブルクリックする．テーブルの内容を確認するための基本操作である．

テーブル productsの内容が表示される．各製品のID，名前，価格などの情報を確認できる．

* データに誤りがある場合，このウィンドウで修正できる．データベース管理の重要な機能の一つである．
続いて，orders テーブルを表示する．注文情報の確認が可能である．

SQL 問い合わせの発行と評価結果の確認

直積 (Cartesian product)

SQL を使用して，複数テーブルの直積を容易に取得できる．直積は2つ以上のテーブルのすべての可能な組み合わせを生成する演算である．

SELECT *
FROM orders, products;

結合問い合わせ (join query)

結合問い合わせは，直積から条件を満たす行を抽出したものとなる．これにより，複数テーブルから関連するデータを効率的に取得できる．

List all 'name', 'price' and 'orders.qty' that satisfy "orders.month = 10"

SELECT products.product_name, products.price, orders.qty
FROM products, orders
WHERE products.id = orders.product_id
           AND orders.month = 10;

SQL を用いたテーブル定義と一貫性制約の記述

実体関連図 (Entity Relationship Diagram)

SQL を使用して，bundles テーブルと shippings テーブルを定義し，一貫性制約を設定する．これにより，データベースの整合性を確保することができる．

bundles テーブルの定義

以下のSQL文を入力し，「Run SQL」アイコンをクリックする (

create table bundles (
    id          integer  primary key autoincrement not null,
    order_id    integer  not null REFERENCES orders(id),
    qty         integer  not null,
    shipping_id integer  not null REFERENCES shippings(id),
    created_at  datetime not null );

コンソール出力の確認

エラーメッセージが表示されていないことを確認する．これにより，SQLの実行が正常に完了したことが検証できる．

shippings テーブルの定義

以下のSQL文を入力し，「Run SQL」アイコンをクリックする

create table shippings (
    id         integer  primary key autoincrement not null,
    year       integer  not null check ( year > 2008 ),
    month      integer  not null check ( month >= 1 AND month <= 12 ),
    day        integer  not null check ( day >= 1 AND day <= 31 ),
    created_at datetime not null );

コンソール出力の確認

エラーメッセージが表示されていないことを確認する．正常にテーブルが作成されたことを示している．
テーブル一覧の表示
オブジェクト・ブラウザ (Object Browser) の「Tables」を展開する (Click 'Tables')．テーブル一覧が表示され，作成したテーブルを確認することができる．

SQL を用いたテーブルへの行の挿入

テーブル間の関係

* 以下の手順で，SQL を使用して bundles テーブルに行を挿入する．トランザクション管理を適切に行うことでデータの整合性を保つ．

SQL プログラムの作成

begin transaction;
insert into bundles values( 1, 1001, 2, 101, datetime('now', 'localtime') );
insert into bundles values( 2, 1001, 1, 102, datetime('now', 'localtime') );
insert into bundles values( 3, 1002, 1, 103, datetime('now', 'localtime') );
insert into bundles values( 4, 1003, 2, 104, datetime('now', 'localtime') );
insert into bundles values( 5, 1004, 4, 104, datetime('now', 'localtime') );
commit;

複数SQL文の一括実行
複数のSQL文を一括実行するため，カーソルを先頭行に移動し，「Run multiple SQL statements ...」ボタンをクリックする．これにより，トランザクション内のすべての挿入操作が実行される．
「Script Output」ウィンドウの確認
エラーメッセージが表示されていないことを確認する．これは，すべてのSQL文が正常に実行されたことを意味する．

* 以下の手順で，SQL を使用して shippings テーブルに行を挿入する．各出荷情報を適切にデータベースに記録する．

SQL プログラムの作成

begin transaction;
insert into shippings values( 101, 2019, 10, 28, datetime('now', 'localtime') );
insert into shippings values( 102, 2019, 10, 31, datetime('now', 'localtime') );
insert into shippings values( 103, 2009, 11,  1, datetime('now', 'localtime') );
insert into shippings values( 104, 2009, 11,  2, datetime('now', 'localtime') );
commit;

複数SQL文の一括実行
複数のSQL文を一括実行するため，カーソルを先頭行に移動し，「Run multiple SQL statements ...」ボタンをクリックする．トランザクション全体が一つの単位として処理される．
「Script Output」ウィンドウの確認
エラーメッセージが表示されていないことを確認する．これにより，データが正常に挿入されたことが確認できる．

SQLiteman を用いたデータのブラウズ

bundles テーブル
テーブル bundlesを選択する

テーブル bundlesの内容が表示される．これにより，挿入されたデータを視覚的に確認できる．

* データに誤りがある場合，このウィンドウで修正できる．SQLitemanのインターフェースを使用することで，直感的なデータ編集が可能である．
shippings テーブル

SQL 問い合わせの発行と評価結果の確認

直積集合 (Cartesian product)

SQL を使用して，複数テーブルからの直積を容易に取得できる．直積操作は，二つのテーブルのすべての行の組み合わせを生成する基本的なリレーショナル演算である．

SELECT *
FROM shippings, bundles, orders;

結合問い合わせ (join query)

結合問い合わせは，直積から条件を満たす行を抽出したものとなる．WHERE句を使用することで，関連するデータのみを効率的に取得できる．

List all 'shippings.month', 'shippings.day' and 'bundles.qty' that satisfy "orders.day = 27"

SELECT shippings.month, shippings.day, bundles.qty
FROM shippings, bundles, orders
WHERE orders.id = bundles.order_id
           AND shippings.id = bundles.shipping_id
           AND orders.day = 27;

演習問題と解答例

以下の問いに答え，その後，解答例を確認する．SQL問い合わせの実行結果を正確に理解することで，データベース操作のスキルを向上させることができる． Answer the following questions. Then, inspect answers described below.

問い

次のSQL問い合わせの評価結果は何か？

SELECT products.product_name, orders.qty
FROM products, orders
WHERE products.id = orders.product_id
           AND orders.qty > 2;

解答例

Fukuoka apple     |3
Kumamoto orange M |4

次のSQL問い合わせの評価結果は何か？

SELECT shippings.year, shippings.month, shippings.day
FROM shippings, bundles, orders, products
WHERE products.id = orders.product_id
           AND orders.id = bundles.order_id
           AND shippings.id = bundles.shipping_id
           AND price > 20;

解答例

以下の問に解答する：

まず SQLite を使用して，以下のテーブルを作成する．これらのテーブルは銀行の融資データを管理するための基本構造である．

    loan(id, branchname, amount)
    borrow(id, customername, loanid)

テーブル定義のSQLは以下の通りである：

create table loan (
    id            integer  primary key autoincrement not null,
    branchname    text not null,
    amount        integer );

create table borrow (
    id            integer  primary key autoincrement not null,
    customername  text not null,
    loanid        integer );

SQLite を使用して，以下のSQLを実行する．これにより，サンプルデータをテーブルに挿入する．

    begin transaction;
    insert into loan values( 1, 'fukuoka', 1000 );
    insert into loan values( 2, 'saga', 2000 );
    insert into loan values( 3, 'saga', 1500 );
    insert into loan values( 4, 'kumamoto', 3000 );
    insert into loan values( 5, 'fukuoka', 2500 );
    commit;

    begin transaction;
    insert into borrow values(1001, 'X', 1);
    insert into borrow values(1002, 'X', 2);
    insert into borrow values(1003, 'X', 3);
    insert into borrow values(1004, 'Y', 4);
    insert into borrow values(1005, 'X', 5);
    commit;

その後，SQLite で以下のSQLを実行したときの結果を答える (Evaluate the following SQL)
```
    select * from loan;
```

解答例

1|fukuoka|1000
2|saga|2000
3|saga|1500
4|kumamoto|3000
5|fukuoka|2500

    select branchname from loan;

解答例

fukuoka
saga
saga
kumamoto
fukuoka

    select distinct branchname from loan;

解答例

fukuoka
saga
kumamoto

    select id, branchname, amount * 1000 from loan;

解答例

1|fukuoka|1000000
2|saga|2000000
3|saga|1500000
4|kumamoto|3000000
5|fukuoka|2500000

    select id from loan where branchname = 'fukuoka';

解答例

1
5

    select id from loan where amount < 2000 and amount >= 1000;

解答例

1
3

    select *
    from loan, borrow;

解答例

1|fukuoka|1000|1001|X|1
1|fukuoka|1000|1002|X|2
1|fukuoka|1000|1003|X|3
1|fukuoka|1000|1004|Y|4
1|fukuoka|1000|1005|X|5
2|saga|2000|1001|X|1
2|saga|2000|1002|X|2
2|saga|2000|1003|X|3
2|saga|2000|1004|Y|4
2|saga|2000|1005|X|5
3|saga|1500|1001|X|1
3|saga|1500|1002|X|2
3|saga|1500|1003|X|3
3|saga|1500|1004|Y|4
3|saga|1500|1005|X|5
4|kumamoto|3000|1001|X|1
4|kumamoto|3000|1002|X|2
4|kumamoto|3000|1003|X|3
4|kumamoto|3000|1004|Y|4
4|kumamoto|3000|1005|X|5
5|fukuoka|2500|1001|X|1
5|fukuoka|2500|1002|X|2
5|fukuoka|2500|1003|X|3
5|fukuoka|2500|1004|Y|4
5|fukuoka|2500|1005|X|5

    select customername, amount
    from loan, borrow;

解答例

X|1000
X|1000
X|1000
Y|1000
X|1000
X|2000
X|2000
X|2000
Y|2000
X|2000
X|1500
X|1500
X|1500
Y|1500
X|1500
X|3000
X|3000
X|3000
Y|3000
X|3000
X|2500
X|2500
X|2500
Y|2500
X|2500

    select customername, amount
    from loan, borrow
    where loan.id = borrow.loanid;

解答例

X|1000
X|2000
X|1500
Y|3000
X|2500

    select customername, amount
    from loan, borrow
    where loan.id = borrow.loanid AND borrow.customername = 'X';

解答例

X|1000
X|2000
X|1500
X|2500