テーブル定義とSQLのテスト実行

テーブル定義

特記事項：

• 日本語の属性名：「個人ＩＤ」のように「ＩＤ」に全角文字を使うと，Java で具合が悪く，あまり悩みたくなかったので半角の「ID」にしている．

create table 投稿者(
    投稿者ID integer primary key,
    名前表記 CHAR(32) not null,
    漢字姓 CHAR(32) not null,
    かな姓 CHAR(32),
    漢字名 CHAR(32) not null,
    かな名 CHAR(32),
    性別 CHAR(1),
    住所 VARCHAR(256),
    年齢 integer,
    電話番号 CHAR(16)
);

create table 作品評価(
    投稿者ID INTEGER not null REFERENCES 投稿者(投稿者ID),
    作成日 DATE not null,
    タイトル VARCHAR(128) not null,
    著者名 VARCHAR(128),
    発行所 VARCHAR(128),
    版 CHAR(16),
    評価 CHAR(8) not null,
    感想 VARCHAR(20000)
);

create table ファイル(
    ファイル名 VARCHAR(200) PRIMARY KEY,
    投稿者ID INTEGER not null REFERENCES 投稿者(投稿者ID)
);

SQL 演習

行の挿入

以後の SQL 演習を行う準備として，行の挿入を繰り返して，サンプルとなるテーブルを作る．

PostgreSQL の場合にはnullでは無く，NULLを使ってください．

insert into 投稿者 values(1,'日本語','山田','やまだ','花太郎','はなたろう','M','福岡県福岡市東区箱崎', 21,'090-1234-5678');
insert into 作品評価 values(1,'2007-09-25','ディジタル画像処理',NULL,'ＣＧ‐ＡＲＴＳ協会',NULL,'良い','画像処理技術に関してわかりやすく解説されている');
insert into 作品評価 values(1,'2006-11-25','システムバイオロジーの展開','北野宏明',NULL,NULL,'普通','システムバイオジーについて理解を深めることができた');
insert into 作品評価 values(1,'2007-09-26','オブジェクト指向データベース',NULL,NULL,NULL,'悪い','オブジェクト指向データベースを用いたシステム開発の特徴について理解することができた');

insert into 投稿者 values(2,'日本語','山本','やまもと','花子','はなこ','F','福岡県北九州市小倉南区曽根', 25,'090-8765-4321');
insert into 作品評価 values(2,'2007-09-27','関係データベースシステム','平尾隆行',NULL,NULL,'良い',NULL);
insert into 作品評価 values(2,'2007-09-28','画像処理アルゴリズム','斉藤恒雄','近代科学社','初版','良い','画像処理に関するアルゴリズムについて理解を深めることができた');

insert into ファイル values('sample1.xml',1);
insert into ファイル values('sample2.xml',2);

結合

PERSON_IDが１の感想

「substr(感想,1,100)」のように書くと，１文字目から５０文字目まで抜き出します（全角の場合）．

SELECT substr(感想,1,100) as ID1の感想一覧
FROM   投稿者, 作品評価
WHERE  投稿者.投稿者ID = 作品評価.投稿者ID
       AND 投稿者.投稿者ID=1
       AND 感想 is not null;

実行結果の例

ID1の感想一覧
===========================================================================================
画像処理技術に関してわかりやすく解説されている　　　　　　　　　　　　　　　　　　　　　　　　　　　
システムバイオジーについて理解を深めることができた　　　　　　　　　　　　　　　　　　　　　　　　　
オブジェクト指向データベースを用いたシステム開発の特徴について理解することができた　　　　　　　　　

'オブジェクト指向データベース'の評価をした人の氏名と電話番号

SELECT  投稿者.漢字姓, 投稿者.漢字名, 投稿者.電話番号
FROM    投稿者, 作品評価
WHERE   投稿者.投稿者ID = 作品評価.投稿者ID
        AND 作品評価.タイトル = 'オブジェクト指向データベース';

実行結果の例

漢字姓 漢字名 電話番号
======================
山田   花太郎 090-1234-5678

集約

評価と，評価ごとの数の集計値

SELECT   評価,COUNT(*)
FROM     作品評価
group by 評価;

実行結果の例

評価 count
======================
普通 1
良い 3
悪い 2

女性が報告した感想の数の集計

SELECT   count(*)
FROM     投稿者, 作品評価
WHERE    投稿者.投稿者ID = 作品評価.投稿者ID
         AND 性別='F'
         AND 感想 is not null;

実行結果の例

COUNT
============
1

2007年9月に男性が報告した作品の数の集約値

日付を処理するために，substr と cast を使う．

SELECT   count(*)
FROM     投稿者, 作品評価
WHERE    投稿者.投稿者ID=作品評価.投稿者ID
         AND 性別='M'
         AND substr(cast(作成日 as char(10)),1,7)='2007-09';

実行結果の例

COUNT
============
2

評価を'良い'または'普通'とつけた人と, その人がそのように評価した評価の数の集計値

SELECT  投稿者.投稿者ID, 投稿者.漢字姓, 投稿者.漢字名, COUNT(*)
FROM    投稿者, 作品評価
WHERE   投稿者.投稿者ID = 作品評価.投稿者ID
        AND
        ( 作品評価.評価 = '良い'
          OR 作品評価.評価 = '普通')
group by
        投稿者.投稿者ID, 投稿者.漢字姓, 投稿者.漢字名;

実行結果の例

投稿者ID 漢字姓 漢字名 count
==========================
1      山田   花太郎 2
2      山田   花子   2

評価を'良い'とつけた冊数が2冊以上の人とその人がそのように評価した本の冊数

SELECT  投稿者.投稿者ID, 投稿者.漢字姓, 投稿者.漢字名, COUNT(*)
FROM    投稿者, 作品評価
WHERE   投稿者.投稿者ID = 作品評価.投稿者ID AND 作品評価.評価 = '良い'
group by
        投稿者.投稿者ID, 投稿者.漢字姓, 投稿者.漢字名
HAVING
        COUNT(*) >= 2;

実行結果の例

投稿者ID 漢字姓 漢字名 count
==========================
2      山田   花子   2