トップページ -> 研究道具箱と入門演習 -> データベース特論・支援 Web ページ -> SQL 問い合わせ
[サイトマップへ]   [全文検索へ]   [統計情報へ]   [掲示板へ]  

SQL 問い合わせ

URL: http://www.db.is.kyushu-u.ac.jp/rinkou/addb/3.html

データベース内の1つのテーブルに対する SQL 問い合わせの例.問い合わせの結果として,新しいテーブルが 1つ生成される.







演習

演習で行うこと


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

SQLite の SQL の説明は http://www.hwaci.com/sw/sqlite/lang.html (English Web Page) にある.


Sqliteman のインストール (Install Sqliteman)

  1. ディレクトリ C:\SQLite を使う

    ここに データベースファイルを置くことにする.

  2. Sqliteman の入手

    http://sqliteman.com/

  3. Setup-Sqliteman-1.2.1-win32.exe を実行してインストール

  4. ようこそ画面 (Welcome)

    Next」をクリック (Click 'Next')

  5. ライセンス条項の確認 (License Agreement)

    Next」をクリック (Click 'Next')

  6. コンポーネントの選択 (Choose Component)

    Next」をクリック (Click 'Next')

  7. インストール先の選択 (Choose Install Location)

    Next」をクリック (Click 'Next')

  8. スタートメニューフォルダの選択 (Choose Start Menu Folder)

    Install」をクリック (Click 'Install')

  9. インストール終了の確認 (Completing the Sqliteman 1.2.1 Setup Wizard)

  10. Sqliteman バージョン 1.2.1 が起動する


Sqliteman の起動と終了 (Start and end Sqliteman)

  1. Sqliteman の起動 (Start Sqliteman)

    「Sqliteman」 のアイコンをダブルクリック (double click "Sqliteman.exe")

    Sqliteman のウインドウが開く.(A New window appears)

  2. ヘルプの表示 (Help Content)

    Help」→ 「Help Content」で終了.

  3. 終了 (End Sqliteman)

    File」→ 「Exit」で終了.


Sqliteman で既存のデータベースを開く (Open an existing database using Sqliteman)

以下の手順で,既存のデータベース「C:\SQLite\mydb」を開く. (Open an existing database 'C:\SQlite\mydb')

  1. File」→ 「Open

  2. データベースファイル C:\SQLite\mydb を選び, 「開く」をクリック (Click '開く' after choosing the database file "C:\SQLite\mydb")

  3. データベースの中身が表示されるので確認する (Database appears)

  4. 「Tables」を展開すると,テーブルの一覧 (List of Tables) が表示されるので確認する (List of tables appears by clicking 'Tables')

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

    まず,「Pragmas」をクリック.(Click 'Pragmas')

    encodingの行に「UTF-8」のように表示されている.

    ※ もし,データベースの文字のエンコーディングを変えたいときは, Sqliteman のようなグラフィカルなツールを使うのではなく, sqlite.exe を起動し「PRAGMA encoding=...;」で変える方がずっと簡単でしょう. 例えば「UTF-16le」などに変えたいなど.

  6. 「System Catalogue」を展開し,「sqlite_master」をクリックすると,データベース・スキーマ (database schema) が表示されるので確認する (Database schema appears by clicking 'sqlite_master')


SQL を用いたテーブル定義と一貫性制約の記述 (Table definition and integrity constraint specification using SQL)

SQL を用いて,products テーブルを定義し,一貫性制約を記述する. (Define 'products' table and specify integrity constrants of the table using SQL)

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

    次の SQL を入力し,「Run SQL」のアイコンをクリック (Write the following SQL, and click "Run SQL" icon).

    CREATE TABLE products (
        id            INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
        product_name  TEXT     UNIQUE NOT NULL,
        type          TEXT     NOT NULL, 
        cost          REAL,
        created_at    DATETIME NOT NULL );
    

    ※ 「SQL Editor」のウインドウには,SQL プログラムを書くことができる. In the 'SQL string' window, you can write down SQL program(s).

  2. コンソールの確認 (Inspect console)

    エラーメッセージが出ていないことを確認


SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

次のような products テーブルを作る. (Construct table 'products')

以下の手順で,SQL を用いて products テーブルへの行の挿入を行う (Insert rows into table 'products' using SQL)

  1. SQL プログラムの記述

    「INSERT INTO ...」は行の挿入.ここには 4つの SQL 文を書き, 「BEGIN TRANSACTION」と「COMMIT」で囲む. ("INSERT INTO ..." means inserting a row into a table. Four SQL statements are wrote).

    BEGIN TRANSACTION;
    INSERT INTO products VALUES( 1, 'Fukuoka apple',     'apple',  50, datetime('now') );
    INSERT INTO products VALUES( 2, 'Kumamoto orange L', 'orange', 30, datetime('now') );
    INSERT INTO products VALUES( 3, 'Kumamoto orange M', 'orange', 20, datetime('now') );
    INSERT INTO products VALUES( 4, 'Fukuoka melon',     'melon',  NULL, datetime('now') );
    COMMIT;
    

  2. 複数の SQL 文の一括実行 (Run multiple SQL statements)

    複数の SQL 文を一括実行したいので,カーソルを先頭行に移動した後に, 「Move the cursor to the top statement. Click "Run multiple SQL statements from current cursor position in one batch" icon)

  3. 「Script Output」ウインドウの確認 (Inspect "Script Output" window)

    エラーメッセージが出ていないことを確認


SQL 問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the results)

SQL を用いた問い合わせの実行例を示す.

テーブルの全ての行の表示 (List all rows of a table)

 
SELECT * FROM products;

条件を満足する行のみの表示 (List the rows which satisfy a given condition)

 
SELECT * FROM products WHERE type = 'orange';

 
SELECT * FROM products WHERE cost > 25;

SELECT * FROM products WHERE cost > 18;


SQL を用いたテーブル定義と一貫性制約の記述 (Table defintion and integrity constraint specification using SQL)


実体関連図 (Entity Relationship Diagram)

SQL を用いて,requests テーブルを定義し,一貫性制約を記述する. (Define a table 'requests'. Specify integrity constrants of the table using SQL)

  1. products テーブルの定義 (Define a table)

    次の SQL を入力し,「Run SQL」のアイコンをクリック (Write the following SQL, and click "Run SQL" icon).

    CREATE TABLE requests (
        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 ),
        product_id INTEGER  NOT NULL REFERENCES products(id), 
        qty        INTEGER  NOT NULL,
        created_at DATETIME NOT NULL );
    

  2. コンソールの確認 (Inspect console)

    エラーメッセージが出ていないことを確認


SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

次のような requests テーブルを作る. (Construct table 'requests')

以下の手順で,SQL を用いて requests テーブルへの行の挿入を行う (Insert rows into table 'requests' using SQL)

  1. SQL プログラムの記述

    「INSERT INTO ...」は行の挿入.ここには 4つの SQL 文を書き, 「BEGIN TRANSACTION」と「COMMIT」で囲む. ("INSERT INTO ..." means inserting a row into a table. Four SQL statements are wrote).

    BEGIN TRANSACTION;
    INSERT INTO requests VALUES( 1001, 2009, 10, 28, 1, 3, datetime('now') );
    INSERT INTO requests VALUES( 1002, 2009, 11,  1, 2, 1, datetime('now') );
    INSERT INTO requests VALUES( 1003, 2009, 11,  2, 1, 2, datetime('now') );
    INSERT INTO requests VALUES( 1004, 2009, 11,  2, 3, 4, datetime('now') );
    COMMIT;
    

  2. 複数の SQL 文の一括実行 (Run multiple SQL statements)

    複数の SQL 文を一括実行したいので,カーソルを先頭行に移動した後に, 「Move the cursor to the top statement. Click "Run multiple SQL statements from current cursor position in one batch" icon)

  3. 「Script Output」ウインドウの確認 (Inspect "Script Output" window)

    エラーメッセージが出ていないことを確認


Sqliteman を用いたデータのブラウズ (Browse Data using Sqliteman)


SQL 問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the results)

直積 (Cartesian product)

SQL を使い,複数のテーブルの直積を簡単に得ることができる.

SELECT *
FROM requests, products;

結合問い合わせ (join query)

結合問い合わせは,直積から,条件を満足する行を選んだものになる.

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

 
SELECT products.product_name, products.cost, requests.qty
FROM products, requests
WHERE products.id = requests.product_id
           AND requests.month = 10;


SQL を用いたテーブル定義と一貫性制約の記述 (Table defintion and integrity constraint specification using SQL)


実体関連図 (Entity Relationship Diagram)

SQL を用いて,bundles テーブル,shippings テーブルを定義し,一貫性制約を記述する. (Define two table 'bundles' and 'shippings'. Specify integrity constrants of the table using SQL)

  1. bundles テーブルの定義

    次の SQL を入力し,「Run SQL」のアイコンをクリック (Write the following SQL, and click "Run SQL" icon).

    CREATE TABLE bundles (
        id          INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
        request_id  INTEGER  NOT NULL REFERENCES requests(id),
        qty         INTEGER  NOT NULL,
        shipping_id INTEGER  NOT NULL REFERENCES shippings(id),
        created_at  DATETIME NOT NULL );
    

  2. コンソールの確認 (Inspect console)

    エラーメッセージが出ていないことを確認

  3. shippings テーブルの定義

    次の SQL を入力し,「Run SQL」のアイコンをクリック (Write the following SQL, and click "Run SQL" icon).

    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. コンソールの確認 (Inspect console)

    エラーメッセージが出ていないことを確認

  5. テーブル一覧の表示 (List of tables)

    オブジェクト・ブラウザ (Object Browser) の中の「Tables」を展開 (Click 'Tables'). テーブル一覧が表示される.


SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

テーブル間の関係

■ 以下の手順で,SQL を用いて bundles テーブルへの行の挿入を行う (Insert rows into table 'bundles' using SQL)

  1. SQL プログラムの記述
     
    BEGIN TRANSACTION;
    INSERT INTO bundles VALUES( 1, 1001, 2, 101, datetime('now') );
    INSERT INTO bundles VALUES( 2, 1001, 1, 102, datetime('now') );
    INSERT INTO bundles VALUES( 3, 1002, 1, 103, datetime('now') );
    INSERT INTO bundles VALUES( 4, 1003, 2, 104, datetime('now') );
    INSERT INTO bundles VALUES( 5, 1004, 4, 104, datetime('now') );
    COMMIT;
    

  2. 複数の SQL 文の一括実行 (Run multiple SQL statements)

    複数の SQL 文を一括実行したいので,カーソルを先頭行に移動した後に, 「Move the cursor to the top statement. Click "Run multiple SQL statements from current cursor position in one batch" icon)

  3. 「Script Output」ウインドウの確認 (Inspect "Script Output" window)

    エラーメッセージが出ていないことを確認

■ 以下の手順で,SQL を用いて shippings テーブルへの行の挿入を行う (Insert rows into table 'shippings' using SQL)

  1. SQL プログラムの記述
     
    BEGIN TRANSACTION;
    INSERT INTO shippings VALUES( 101, 2009, 10, 28, datetime('now') );
    INSERT INTO shippings VALUES( 102, 2009, 10, 31, datetime('now') );
    INSERT INTO shippings VALUES( 103, 2009, 11,  1, datetime('now') );
    INSERT INTO shippings VALUES( 104, 2009, 11,  2, datetime('now') );
    COMMIT;
    

  2. 複数の SQL 文の一括実行 (Run multiple SQL statements)

    複数の SQL 文を一括実行したいので,カーソルを先頭行に移動した後に, 「Move the cursor to the top statement. Click "Run multiple SQL statements from current cursor position in one batch" icon)

  3. 「Script Output」ウインドウの確認 (Inspect "Script Output" window)

    エラーメッセージが出ていないことを確認


Sqliteman を用いたデータのブラウズ (Browse Data using Sqliteman)


SQL 問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the results)

直積集合 (Cartesian product)

SQL を使い,複数のテーブルからの直積を簡単に得ることができる.

SELECT *
FROM shippings, bundles, requests;

結合問い合わせ (join query)

結合問い合わせは,直積から,条件を満足する行を選んだものになる.

List all 'shippings.month', 'shiping.day' and 'bundles.qty' that satisfy "requests.month = 11"

 
SELECT shippings.month, shippings.day, bundles.qty
FROM shippings, bundles, requests
WHERE requests.id = bundles.request_id
           AND shippings.id = bundles.shipping_id
           AND requests.month = 11;

演習問題と解答例

次の問いに答えよ.その後,下記の解答例を確認せよ. Answer the following questions. Then, inspect answers described below.

問い (Questions)

  1. 次の SQL 問い合わせの評価結果は何か? (What is the evaluation result of the following SQL query).
    SELECT products.product_name, requests.qty
    FROM products, requests
    WHERE products.id = requests.product_id
               AND requests.qty > 2;
    
  2. 次の SQL 問い合わせの評価結果は何か? (What is the evaluation result of the following SQL query).
    SELECT shippings.year, shippings.month, shippings.day
    FROM shippings, bundles, requests, products
    WHERE products.id = requests.product_id
               AND requests.id = bundles.request_id
               AND shippings.id = bundles.shipping_id 
               AND cost > 20; 
    

解答例 (Answers)

※ 問い合わせ結果は1つのテーブルになる.その属性名には,元のテーブル名と属性名をドットでつなげたドット記法を用いている.