テーブル定義とSQLのテスト実行を行う.
特記事項:
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 演習を行う準備として,行の挿入を繰り返して,サンプルとなるテーブルを作る.
PostgreSQL の場合にはnullでは無く,NULLを使ってください.
insert into 投稿者 values(1,'日本語','山田','やまだ','花太郎','はなたろう','M','福岡県福岡市東区箱崎', 21,'090-1234-5678'); insert into 作品評価 values(1,'2007-09-25','ディジタル画像処理',NULL,'CG‐ARTS協会',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);
「substr(感想,1,100)」のように書くと,1文字目から50文字目まで抜き出します(全角の場合).
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
日付を処理するために,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
SELECT 投稿者.投稿者ID, 投稿者.漢字姓, 投稿者.漢字名, COUNT(*) FROM 投稿者, 作品評価 WHERE 投稿者.投稿者ID = 作品評価.投稿者ID AND 作品評価.評価 = '良い' group by 投稿者.投稿者ID, 投稿者.漢字姓, 投稿者.漢字名 HAVING COUNT(*) >= 2;
実行結果の例
投稿者ID 漢字姓 漢字名 count ========================== 2 山田 花子 2