トップページ -> 教材 -> リレーショナルデータベースの基礎 -> SQL 入門演習
[サイトマップへ], [サイト内検索へ],

SQL 入門演習

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

演習で行うこと

  • LIKE と % の組み合わせ: 文字列のパターンマッチ
  • ORDER BY と COUNT の組み合わせ: ソート
  • GROUP BY と COUNT の組み合わせ: グループごとの数え上げ
  • GROUP BY と COUNT と HAVING の組み合わせ: COUNT で数えたタップル数に関して条件を指定し,タップルを絞り込む.

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

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

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

    1. File」→ 「Open

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

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

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

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

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

      要するに,/home/<ユーザ名>/SQLite のの mydb を選ぶ. 


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

    以前の授業で定義した products テーブルを使う。 products テーブルのテーブル定義が残っている場合には、ここの操作は必要ない。 products テーブルのテーブル定義が残っていない場合には、次の手順で定義する。

    SQL を用いて,products テーブルを定義し,一貫性制約を記述する. (Define 'products' table and 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 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)

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


    テーブルへの行の挿入 (Populare a table)

    以前の授業で定義した products テーブルを使う。

    下記の操作により、演習用のデータ(1000行)を、products テーブルに格納する。

    1. Sqliteman で、products右クリック (right click) し、「Populate Table...」を選ぶ.

    2. Number of Rows to Populate に「1000」を設定、 ecost の行は「Random Number」に設定し、 「Populate」をクリック

    3. 「Close」をクリック


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

    SELECT * FROM products WHERE id = 123;
    

    LIKE と % の組み合わせ: 文字列のパターンマッチ

    「aa」あるいは「aA」あるいは「Aa」あるいは「AA」を含むという条件での検索。 検索条件では「'%AA%'」と書いている。 大文字と小文字の両方が検索条件にマッチする。

    SELECT * FROM products WHERE type LIKE '%AA%';
    

    先頭が「B」あるいは「b」であるという条件での検索。 検索条件では「'B%'」と書いている。 大文字と小文字の両方が検索条件にマッチする。

    SELECT * FROM products WHERE type LIKE 'B%';
    

    末尾が「C」あるいは「c」であるという条件での検索。 検索条件では「'C%'」と書いている。 大文字と小文字の両方が検索条件にマッチする。

    SELECT * FROM products WHERE type LIKE '%C';
    

    ORDER BY を用いたソート

    末尾が「C」あるいは「c」であるという条件での検索。 検索条件では「'C%'」と書いている。 検索結果を  「ORDER BY product_name」でソートする.

    SELECT * FROM products WHERE type LIKE '%C' ORDER BY product_name;
    

    今度は、「ORDER BY product_name DESC」でソートする.末尾に「DESC」を付けることにより、 結果が降順になる

    SELECT * FROM products WHERE type LIKE '%C' ORDER BY product_name DESC;
    

    GROUP BY と COUNT の組み合わせ: グループごとの数え上げ

    「substr(<属性名>, X, Y)」は、X文字列目から、Y文字分取り出すという意味。 例えば、 「substr(procuct_name, 1, 1)」は、先頭文字を(文字を1個だけ)取り出すという意味。

    GROUP BY はグループ化。

    SELECT substr(product_name, 1, 1), COUNT(*) FROM products GROUP BY substr(product_name, 1, 1);
    

    上の図では、「先頭がAのものが16行、先頭がBのものが15行、先頭がCのものが1行という意味になる。

    procuct_name の先頭文字は aからz, AからZの 52 通りある。

    今後は、行数で絞り込む。次のSQLでは、行数が24を超えるものに絞り込んでいる。

    SELECT substr(product_name, 1, 1), COUNT(*) 
    FROM products 
    GROUP BY substr(product_name, 1, 1) 
    HAVING COUNT(substr(product_name, 1, 1)) > 24;