データベース論実習 1

1. データベースの初期化、起動、作成、停止

データベース の初期化 initdb

$ cd                                       
$ mkdir DB                          
$ initdb -D DB          #データベース初期化  

サーバーの起動

$ pg_ctl start -D DB             #サーバー起動
.....            出力が止まって、startedが表示されたら、returnを押す

データベースの作成

$ createdb test                   

データベースへの接続
$ psql test                        
   ... この間に
  \q                                     # 接続を切る

サーバの停止

$ cd                                     
$ pg_ctl stop -D DB 

$ pg_ctl start -D DB        
$ psql test                             # testというデータベースに接続します。
test=#   ...

2.  基礎的なSQL操作

テーブルを作る - CREATE TABLE -

class1
   name   char(20),
   age    integer,
   floor  integer
テーブル名は class1, 属性は name, age, floorでそれぞれ文字型(20文字),整数型, 整数型

データを挿入する - INSERT INTO -

テーブルから検索する 基本  SELECT ... FROM..

test=# SELECT * FROM class1;
            name            | age | floor
----------------------------+-----+-------
 ノイマン                     |  50 |     5
 ミンスキー                  |  35 |     4
 コッド                        |  43 |     5
 パスカル                     |  39 |     3
 チューリング                |  28 |     
(5 rows)

演算結果の表示

test=# SELECT name,age*floor FROM class1
test-# ;
            name         | ?column?
----------------------------+----------
 ミンスキー              |      140
 コッド                     |      215
 パスカル                  |      117
 チューリング           |      112
(4 rows)
+    加算
-    減算
*    乗算
/    除算

テーブルから検索する 条件指定    SELECT ... FROM.. WHERE

            test=# SELECT * FROM class1 WHERE age >= 30 AND age <= 39;
            name                      | age | floor
            -----------------------------+-----+-------
             ミンスキー                |  35 |     4
             パスカル                   |  39 |     3
            (2 rows)

LIKEによる比較

WHERE 属性名 LIKE ’・・・・・・’ ;   で下記のような指定ができる    
Dではじまる
address LIKE 'D%'
Dを含む
address LIKE '%D%'
2文字目がD
address LIKE '_D%'
Dではじまり、かつeを含む address LIKE 'D%e%'
D以外で始まる address NOT LIKE 'D%'

test=# SELECT * FROM class1 WHERE name LIKE 'コ%';
          name        | age | floor
-------------------------+-----+-------
 コッド                |  43 |     5
(1 row)


集合関数

test=# select MAX(age) from class1;
 max
-----
  43
(1 row)

SUM    総和
MAX    最大値
MIN    最小値
AVG    平均値
COUNT    総数

ある行の値を更新する UPDATE

行を削除する DELETE

データの並び替え ORDER BY

テーブルを削除する DROP


3.  ファイルによるSQL操作

        art.sql
DROP TABLE  art;         -- 間違ってCREATEした時のために、まずdropする。
                                  -- 存在しなくても、警告がでるだけ
CREATE TABLE art (      -- テーブル作成
   name  CHAR(20),
   age   INTEGER
);
                           -- art.sqlのファイルに以下のデータを挿入
INSERT INTO art (name,age) VALUES ('ピカソ',60);
INSERT INTO art (name,age) VALUES ('モネ',33);
INSERT INTO
art (name,age) VALUES ('ルノワール',40);
INSERT INTO
art (name,age) VALUES ('マネ',30);

SELECT * FROM art;      -- 挿入されたことを確認する。



課題1

以下の問いの答えに相当するファイル report1-1.sql, report_result1-1.d, report1-2.sql, report_result1-2.d, report1-3.sql, report_result1-3.dをe-mailに添付して送付してください

ファイルの名前は必ず指定したものになっていること。
本文は空白にせずビジネスメールの要領で課題を提出する旨など記載すること。

宛先 honda@is.kochi-u.ac.jp
件名 データベース論課題1
締め切り 7/11(火) 18:00


1.  "2 基礎的なSQL操作"の内容を一気に実行するためのファイルを report1-1.sqlに作成しなさい。またその実行結果をreport_result1-1.dに保存しなさい。

2.  あらかじめHotelDBというデータベースが作成されているものとして、これに対して、以下の内容を一気に実行するためのファイルを report1-2.sqlに作成しなさい。この際、各小問に相当する箇所の直前に __Q1というコメントをいれてください。 またその実行結果をreport_result1-2.dに保存しなさい。

<テーブル名:Hotel>
Hotel_No(4)       Hotel_Name(20)      Address(40)            Tel(13)
1001               タワーホテル           千葉県浦安市            04-1234-5678
1002               ルクソーホテル         福岡県福岡市            09-2468-1357
1003               スカイホテル      東京都江東区            03-0137-1113

<テーブル名:Room>
Hotel_No(4)  Room_No(3)   Room_Type(1)
1001         105          0
1002         102          2
1001         305          1
1001         208          0
1002         503          1
1002         505          0
1003         203          3

<テーブル名:Reservation>
Reservation_ID(5)  Hotel_No(4)      Room_No(3) Customer_ID(5)   staff_ID(4)      Checkin(10)
10001              1002               102        11111            2002           2015.04.01
10002              1002               503        11114            2001           2015.04.25
10003              1001               105        11113            2002           2015.04.16
10004              1002               505        11115            2001           2015.04.12
10005              1001               305        11112            2001           2015.04.21
10006              1001               208        11116            2002           2015.04.07
10007              1003               203        11117            2003           2015.04.30

Q1 HotelDBに上記のテーブルを作成する。なお、データ型はすべて文字列型で文字数は属性のあとの()内の数値とする。
Q2 上記の基本データをそれぞれのテーブルに入力した後、入力データをすべて表示する
Q3 Reservationテーブルをチェックインの順に並び替え、表示する
Q4 Customer_IDが11111と11112の人が予約したホテル名とホテルナンバーを表示する※2つのSQL文を使います。

3. あらかじめHotelDBというデータベースに対して2の内容が実行されていものと仮定する。更に下記の操作を行うを一気に実行するためのファイルを report1-3.sqlに作成しなさい。この際、各小問に相当する箇所の直前に __Q1というコメントをいれてください。 またその実行結果をreport_result1-3.dに保存しなさい。

Q1 スタッフの情報を管理するために<Staff>というテーブルを作成する
 
               <テーブル名:Staff>
               staff_ID(4)  Name(20)      Age(2)
               2001         アシカリ      40
               2002         イワガワ      50
               2003         モリ          30

Q2「モリ」が担当しているホテルのHotel_No,Hotel_Name, Addressを表示する   ※3つのSQL文を使います.

解答のヒント
(1) 「モリ」のName,staff_IDを表示するSQLを作成する.
(2) (1)の実行結果を見て,SQLを作成する.
(3) (2)の実行結果を見て,SQLを作成する.


Q3 タワーホテルに新たに特別ルームを開放することが決まり,その部屋のデータを追加挿入し、Roomテーブルの内容を表示する.
               <テーブル名:Room>
               Hotel_No(4)    Room_No(3)   Room_Type(1)
               1001           408          0

Q4 タワーホテルが2015年4月の間,2階の部屋を改修工事することになり,200番代の部屋を利用することができなくなった。そこで,改修工事期間中にタ ワーホテルの200番代の部屋を予約している顧客には「408」号室に予約を変更してもらうことになった。2015年4月中にタワーホテルの200番代の 部屋にチェックインの予約を入れている顧客のRoom_Noを「408」に変更するためのSQL文を作成し,その実行結果のReservationテーブ ルの内容を表示.※2つのSQL文を使います.


解答手順のヒント:

(1) 「タワーホテル」のHotel_Name,Hotel_Noを表示するSQLを作成する。
(2) (1)の実行結果を見て,文中の3つの条件を満たす顧客のRoom_Noを変更し,実行結果を表示する。