開発環境
- OS: OS X Lion - Apple
- データベース言語: SQL
- リレーショナルデータベース: MySQL
『初めてのSQL』(Alan Beaulieu 著、株式会社クイープ 翻訳、オライリー・ジャパン、2006年、ISBN4-8733-181-8) の5章(複数のテーブルからのデータの取得), 5.6(練習問題)5-2を解いてみる。
5-2.
SQL文(TextWrangler)
select a.account_id, c.fed_id, p.name from account a inner join customer c on a.cust_id = c.cust_id inner join product p on a.product_cd = p.product_cd where c.cust_type_cd = 'I';
入出力結果(Terminal)
$ mysql -u lrngsql -p bank
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.5.24 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show tables;
+----------------+
| Tables_in_bank |
+----------------+
| account |
| branch |
| business |
| customer |
| department |
| employee |
| individual |
| officer |
| product |
| product_type |
| tmp |
| transaction |
+----------------+
12 rows in set (0.00 sec)
mysql> desc customer;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------------+
| cust_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| fed_id | varchar(12) | NO | | NULL | |
| cust_type_cd | enum('I','B') | NO | | NULL | |
| address | varchar(30) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| state | varchar(20) | YES | | NULL | |
| postal_code | varchar(10) | YES | | NULL | |
+--------------+------------------+------+-----+---------+----------------+
7 rows in set (0.07 sec)
mysql> desc product;
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| product_cd | varchar(10) | NO | PRI | NULL | |
| name | varchar(50) | NO | | NULL | |
| product_type_cd | varchar(10) | NO | MUL | NULL | |
| date_offered | date | YES | | NULL | |
| date_retired | date | YES | | NULL | |
+-----------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> desc account;
+--------------------+----------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+----------------------------------+------+-----+---------+----------------+
| account_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| product_cd | varchar(10) | NO | MUL | NULL | |
| cust_id | int(10) unsigned | NO | MUL | NULL | |
| open_date | date | NO | | NULL | |
| close_date | date | YES | | NULL | |
| last_activity_date | date | YES | | NULL | |
| status | enum('ACTIVE','CLOSED','FROZEN') | YES | | NULL | |
| open_branch_id | smallint(5) unsigned | YES | MUL | NULL | |
| open_emp_id | smallint(5) unsigned | YES | MUL | NULL | |
| avail_balance | float(10,2) | YES | | NULL | |
| pending_balance | float(10,2) | YES | | NULL | |
+--------------------+----------------------------------+------+-----+---------+----------------+
11 rows in set (0.06 sec)
mysql> select a.account_id, c.fed_id, p.name
-> from account a inner join customer c
-> on a.cust_id = c.cust_id inner join product p
-> on a.product_cd = p.product_cd
-> where c.cust_type_cd = 'I';
+------------+-------------+------------------------+
| account_id | fed_id | name |
+------------+-------------+------------------------+
| 1 | 111-11-1111 | checking account |
| 2 | 111-11-1111 | savings account |
| 3 | 111-11-1111 | certificate of deposit |
| 4 | 222-22-2222 | checking account |
| 5 | 222-22-2222 | savings account |
| 7 | 333-33-3333 | checking account |
| 8 | 333-33-3333 | money market account |
| 10 | 444-44-4444 | checking account |
| 11 | 444-44-4444 | savings account |
| 12 | 444-44-4444 | money market account |
| 13 | 555-55-5555 | checking account |
| 14 | 666-66-6666 | checking account |
| 15 | 666-66-6666 | certificate of deposit |
| 17 | 777-77-7777 | certificate of deposit |
| 18 | 888-88-8888 | checking account |
| 19 | 888-88-8888 | savings account |
| 21 | 999-99-9999 | checking account |
| 22 | 999-99-9999 | money market account |
| 23 | 999-99-9999 | certificate of deposit |
+------------+-------------+------------------------+
19 rows in set (0.10 sec)
mysql> quit
Bye
$
0 コメント:
コメントを投稿