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

【概要】

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

【目次】

【サイト内の関連ページ】

公開資料は クリエイティブコモンズ 表示-非営利-継承 4.0 国際ライセンス（CC BY-NC-SA 4.0） で提供しており，事前の許可なく自由に利用できます．条件は著作者表示（BY），非営利目的のみ（NC），同一ライセンスでの再配布（SA）です．

用語

演習で行うこと

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

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

SQLite Manager Tool の起動と終了

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

  1. SQLite Manager Tool の起動

    * Windows での SQLite Manager Tool の起動方法

    「SQLite Manager Tool」アイコンをダブルクリックして起動する．

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

    * Ubuntu での SQLite Manager Tool の起動方法

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

    SQLite Manager Tool の新規ウィンドウが開き，作業を開始できる．

  2. ヘルプの表示

    メニューからヘルプ情報にアクセスできる．

    ヘルプ内容を確認し，SQLite Manager Tool の機能や使用方法について理解を深める．

  3. SQLite Manager Tool の終了

    ウインドウを閉じて終了する．

SQLite Manager Tool で新しいデータベースを作成する

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

  1. 新規データベース作成のため， ツールバーの「新規DB作成」ボタンをクリックする．
  2. データベースの新規作成を開始する

    * Ubuntu での実行例（データベースファイル名「mydb2」を作成する場合）

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

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

    * 新規データベースを作成する場合は，存在しない新しいファイル名を指定し，データの上書きを防止する．

  3. データベースの内容を確認する

    初期状態では，「スキーマナビゲータ」のテーブル一覧は空である．

  4. テキスト・エンコーディングの設定を確認する

    「SQLエディタ」に「PRAGMA encoding;」と入力し，「▶ SQL実行 (F5)」を押下すると，「出力」欄に設定が表示される．

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

    * データベースの文字エンコーディングを変更する必要がある場合は， 「SQLエディタ」に，「PRAGMA encoding=...;」コマンドを入力して実行する． 例えば「UTF-16le」に変更する場合は「PRAGMA encoding=UTF-16le;」を実行する．

  5. 終了

    一旦 SQLite Manager Tool を終了し，作業を区切る．

    ウインドウを閉じて終了する．

SQLite Manager Tool で既存のデータベースを開く

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

  1. ツールバーの「DBオープン (Ctrl+O)」ボタンをクリックしてファイル選択ダイアログを表示する．
  2. データベースファイルを開く

    * Windows での実行例（「C:\SQLite\mydb」を開く場合）

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

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

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

    ホームディレクトリ直下の mydb2 ファイルを選択する．

  3. 「スキーマナビゲータ」でデータベースの内容を確認する．

    ◆ 表示例（データベースの内容により表示が異なる）

  4. テーブルが1個以上ある場合は「スキーマナビゲータ」の一覧から詳細を確認できる

    「スキーマナビゲータ」にテーブルの一覧が表示される．

    ◆ 表示例

  5. テキスト・エンコーディングの設定を確認する

    「SQLエディタ」に「PRAGMA encoding;」と入力し，「▶ SQL実行 (F5)」を押下すると，「出力」欄に設定情報が表示される．

    encoding行に「UTF-8」と表示されていることを確認する．

    * データベースの文字エンコーディングを変更する必要がある場合は， 「SQLエディタ」に「PRAGMA encoding=...;」コマンドを入力して実行する． 例えば「UTF-16le」に変更する場合は「PRAGMA encoding=UTF-16le;」を実行する．

  6. 「SQLエディタ」に「SELECT * FROM sqlite_master;」と入力し，「▶ SQL実行 (F5)」を押下すると，「出力」欄にデータベース・スキーマ (database schema) が表示される

    ◆ 表示例（1）

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

    ◆ 表示例（2）

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

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

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

リレーショナル・スキーマ (relational schema): products(id, product_name, type, price, created_at, updated_at)
  1. products テーブルの定義

    以下のSQL文を入力し，「▶ SQL実行 (F5)」を押下して実行する． 

    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エディタ」欄に SQL 文を記述する．

  2. 出力欄の確認

    エラーメッセージが表示されていないことを確認する．

続いて，SQL を使用して，orders テーブルを定義し，一貫性制約を設定する．

  1. orders テーブルの定義

    以下のSQL文を入力し，「▶ SQL実行 (F5)」を押下する．この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 );
  2. 出力欄の確認

    エラーメッセージが表示されていないことを確認する．

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

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

以下の手順で，SQL を使用して products テーブルに行を挿入する．

  1. SQL プログラムの作成

    「insert into ...」文で行を挿入する．ここでは 4つのSQL文を記述し， 「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'), NULL );

    * 属性名を指定して値の順序を明示的に指定する方法

    この場合，属性値を省略すると，テーブル定義時に指定されたデフォルト値が自動的に使用される． 

    insert into products(id, product_name, type, price, created_at, updated_at)
values( 2, 'Kumamoto orange L', 'orange', 30, datetime('now', 'localtime'), NULL );
  2. 複数SQL文の一括実行

    複数のSQL文を一括実行するため， 「▶ SQL実行 (F5)」を押下する．

  3. 「出力」欄の確認

    「出力」欄にエラーメッセージが表示されていないことを確認する．

    * エラーメッセージが表示された場合は，SQL文を修正し，再度「▶ SQL実行 (F5)」を押下してエラーを解消する．

    例えば，以下のようなエラーが発生した場合の対処法：

    • 「begin transaction」は完了しているが，それ以降の処理が中断されている．
    • それ以降の文は実行されていないため，再開する必要がある．

    この場合，「SQLエディタ」の内容を，すでに実行済みの「begin transaction;」を除いた，再開したい行以降の内容に書き換えてから，「▶ SQL実行 (F5)」を押下して処理を継続する．

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

  1. 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;
  2. 複数SQL文の一括実行

    複数のSQL文を一括実行するため，「▶ SQL実行 (F5)」を押下する．

  3. 「出力」欄の確認

    「出力」欄にエラーメッセージが表示されていないことを確認する．

SQL 問い合わせの発行と評価結果の確認（products, orders）

条件を満たす行の抽出

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

SELECT * FROM products;

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

SELECT * FROM products WHERE type = 'orange';

SELECT * FROM products WHERE price > 25;

SELECT * FROM products WHERE price > 18;

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


実体関連図 (Entity Relationship Diagram)

SQL を使用して，bundles テーブルと shippings テーブルを定義し，一貫性制約を設定する．

  1. bundles テーブルの定義

    以下のSQL文を入力し，「▶ SQL実行 (F5)」を押下する 

    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 );
  2. 出力欄の確認

    エラーメッセージが表示されていないことを確認する．

  3. shippings テーブルの定義

    以下のSQL文を入力し，「▶ SQL実行 (F5)」を押下する 

    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 );
  4. 出力欄の確認

    「出力」欄にエラーメッセージが表示されていないことを確認する．

  5. テーブル一覧の表示

    「スキーマナビゲータ」にテーブル一覧が表示される．

SQL を用いたテーブルへの行の挿入（bundles, shippings）

テーブル間の関係

* 以下の手順で，SQL を使用して bundles テーブルに行を挿入する．

  1. 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;
  2. 複数SQL文の一括実行

    複数のSQL文を一括実行するため，「▶ SQL実行 (F5)」を押下する．

  3. 「出力」欄の確認

    「出力」欄にエラーメッセージが表示されていないことを確認する．

* 以下の手順で，SQL を使用して shippings テーブルに行を挿入する．

  1. 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;
  2. 複数SQL文の一括実行

    複数のSQL文を一括実行するため，「▶ SQL実行 (F5)」を押下する．

  3. 「出力」欄の確認

    「出力」欄にエラーメッセージが表示されていないことを確認する．

SQLite Manager Tool を用いたデータのブラウズ

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;

複数テーブルの直積 

SELECT *
FROM shippings, bundles, orders;

3テーブルの結合問い合わせ

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;

演習問題と解答例

以下の問いに答え，その後，解答例を確認する． Answer the following questions. Then, inspect answers described below.

問い

  1. 次のSQL問い合わせの評価結果は何か？ 
    SELECT products.product_name, orders.qty
FROM products, orders
WHERE products.id = orders.product_id
           AND orders.qty > 2;

    解答例 

    Fukuoka apple     |10
Kumamoto orange M |8
Kumamoto orange M |8

  2. 次の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;

    解答例 

    2019|10|28
2019|10|31
2009|11|1
  3. 以下の問に解答する：
    • まず 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を実行したときの結果を答える． 
          select * from loan;

    解答例 

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

  4.     select branchname from loan;

    解答例 

    fukuoka
saga
saga
kumamoto
fukuoka

  5.     select distinct branchname from loan;

    解答例 

    fukuoka
saga
kumamoto

  6.     select id, branchname, amount * 1000 from loan;

    解答例 

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

  7.     select id from loan where branchname = 'fukuoka';

    解答例 

    1
5

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

    解答例 

    1
3


  9.     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

  10.     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

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

    解答例 

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

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

    解答例 

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