トップページ -> 教材 -> リレーショナルデータベースの基礎 -> SQL 問い合わせ
[サイトマップへ], [サイト内検索へ],

SQL 問い合わせ

サイト構成 連絡先,業績など 実践知識 データの扱い コンピュータ 教材 サポートページ

URL: https://www.kkaneko.jp/cc/db/3.html

演習

サイト構成 連絡先,業績など 実践知識 データの扱い コンピュータ 教材 サポートページ

演習で行うこと


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

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


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

CREATE TABLE などの SQL コマンドを編集できるエディタの機能等をもったソフトウエアとして SQLiteman を使うことにします. (We use the SQLiteman as SQL editor, database manager interface, ...)

  1. SQLiteman の起動 (Start SQLiteman)

    Windows での SQLiteman の起動例

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

    SQLiteman の新しいウインドウが開く.(A New window appears)

    Ubuntu での SQLiteman の起動例

    端末で「sqliteman」+ Enter キーと操作する.

    SQLiteman の新しいウインドウが開く.(A New window appears)

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

    Help」→ 「Help Content」 と操作する

    ヘルプが表示されるので確認する

  3. SQLiteman の終了 (End SQLiteman)

    File」→ 「Exit」で終了.


SQLiteman で新しいデータベースを作成する (Create a new database)

以下の手順で,新しいデータベースを作成する.その結果,データベースファイルができる. (Create a new database)

  1. データベースを新規作成したいので 「File」→ 「New」 と操作する

  2. データベースの新規作成を開始する (Start a new databae creation)

    Ubuntu での実行例(データベースファイル名を「mydb2」にする場合)

    データベースファイル名である mydb2 を指定し, 「保存」をクリック

    ※ データベースファイル名は何でも良いが、英語の名前がよい.

    データベースを新規作成したいときは,データベースファイル名として「新しい」ものを指定すること

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

    このときテーブル (Tables) 数も,ビュー (Views) の数も 0 である.

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

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

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

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

  5. 終了 (End SQLiteman)

    あとの混乱を防ぎたいので、1度、SQLiteman を終了する

    File」→ 「Exit」で終了.


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

すでに作成済みのデータベースを,下記の手順で開くことができる.

以下の手順で,既存のデータベースファイルを開く. (Open an existing database file)

  1. File」→ 「Open

  2. データベースファイルを開く (Open Database File)

    Windows での実行例(「C:\SQLite\mydb」を開く場合)

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

    Ubuntu での実行例(「SQLite/mydb」を開く場合)

    データベースファイル mydb2 を選び, 「開く」をクリック (Click '開く' after choosing the database file "mydb2")

    要するに,ホームディレクトリの のmydb2 を選ぶ. 

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

    ◆ 表示例(データベースの中身によって表示が変わる)

  4. 「Tables」の数字が1以上の場合には展開できる

    「Tables」の数字が1以上のとき,「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')

    ◆ 表示の例(1)

    データベースが空の場合,表示も空.

    ◆ 表示の例(2)

    score_records, order_records などのテーブルを定義すみの場合


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

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

リレーショナル・スキーマ (relational schema): products(id, product_name, type, price, created_at, updated_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,
        price         real,
        created_at    datetime not null,
        updated_at    datetime );
    

    ※ 「SQL Editor」のウインドウには,SQL プログラムを書くことができる.
    (In the 'SQL Editor' 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; を実行し,一連の挿入が終わったら COMMIT; を実行する.(Issue "BEGIN TRANSACTION" before database update and "COMMIT" after database update).

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

    INSERT INTO には 2つの方法がある.(Two styles of "INSERT INTO")

    ■ 属性の値を,テーブル定義の順に全て並べる (List all attribute values. The order is the same as its table definition)

    INSERT INTO products VALUES( 1, 'Fukuoka apple',     'apple',  50, datetime('now') );
    

    ■ 属性の値の並び方を,属性名を使って明示的に指定する (Specify the order of attribute values using attribute name list)

    このとき,属性値を省略すると,テーブル定義のときに指定されたデフォルト値が使われる (defaults values are used)

    INSERT INTO products VALUES( 2, 'Kumamoto orange L', 'orange', 30, datetime('now') );
    

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

    複数の SQL 文を一括実行したいので,カーソルを先頭行に移動した後に, 「Run multiple SQL statements ...」のボタンをクリックする.
    (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 を書き直して,もう1度「Run multiple SQL statements ...」のボタンをクリックする.

    例えば,下記のようにエラーが出ていたとする.このときは,

    • 「BEGIN TRANSACTION」は終わっている.
    • それ以降は実行されていない

    このようなときは,再開したい行にカーソルをあわせて、「Run multiple SQL statements ...」のボタンをクリックする.


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

ここでは,SQL を用いた問い合わせの実行例を示す. SQL 問い合わせの詳細については,別の Web ページで説明する.ここでは,テーブルの中身を確認して欲しい.

テーブルの全ての行の表示 (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 price > 25;

SELECT * FROM products WHERE price > 18;


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


実体関連図 (Entity Relationship Diagram)

SQL を用いて,orders テーブルを定義し,一貫性制約を記述する. (Define a table 'orders'. 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 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. コンソールの確認 (Inspect console)

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


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

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

以下の手順で,SQL を用いて orders テーブルへの行の挿入を行う (Insert rows into table 'orders' 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 orders VALUES( 1001, 2014, 10, 26,  'kaneko',   1, 10, datetime('now') );
    INSERT INTO orders VALUES( 1002, 2014, 10, 26,  'miyamoto', 2, 2, datetime('now') );
    INSERT INTO orders VALUES( 1003, 2014, 10, 27,  'kaneko',   3, 8, datetime('now') );
    INSERT INTO orders VALUES( 1004, 2014, 10, 27,  'kaneko',   3, 8, datetime('now') );
    COMMIT;
    
    

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

    複数の SQL 文を一括実行したいので,カーソルを先頭行に移動した後に,「Run multiple SQL statements ...」のボタンをクリックする.
    (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 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 を用いたテーブル定義と一貫性制約の記述 (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,
        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. コンソールの確認 (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 文を一括実行したいので,カーソルを先頭行に移動した後に,「Run multiple SQL statements ...」のボタンをクリックする.
    (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 文を一括実行したいので,カーソルを先頭行に移動した後に,「Run multiple SQL statements ...」のボタンをクリックする.
    (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, orders;

結合問い合わせ (join query)

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

List all 'shippings.month', 'shiping.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.

問い (Questions)

  1. 次の SQL 問い合わせの評価結果は何か? (What is the evaluation result of the following SQL query).
    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
    

  2. 次の SQL 問い合わせの評価結果は何か? (What is the evaluation result of the following SQL query).
    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;
    

    解答例

    2009|10|28
    2009|10|31
    2009|11|1
    2009|11|2
    

  3. 下記の問に解答せよ