開発環境
- OS: OS X Lion - Apple
- データベース言語: SQL
- リレーショナルデータベース: MySQL
『初めてのSQL』(Alan Beaulieu 著、株式会社クイープ 翻訳、オライリー・ジャパン、2006年、ISBN4-87311-281-8) の5章(複数テーブルからのデータの取得), 5.6(練習問題)5-2.を解いてみる。
5-2.
入出力結果(Terminal)
$ mysql -u lrngsql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.18 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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bank |
| mysql |
| performance_schema |
| sample_store |
| store |
| test |
+--------------------+
7 rows in set (0.05 sec)
mysql> use bank;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables
-> ;
+----------------+
| Tables_in_bank |
+----------------+
| account |
| branch |
| business |
| customer |
| department |
| employee |
| favorite_food |
| individual |
| officer |
| person |
| product |
| product_type |
| tmp |
| transaction |
+----------------+
14 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.07 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.00 sec)
mysql> desc rpdocut;
ERROR 1146 (42S02): Table 'bank.rpdocut' doesn't exist
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> 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.40 sec)
mysql> select fed_id
-> from customer
-> where cust_type_cd = 'I';
+-------------+
| fed_id |
+-------------+
| 111-11-1111 |
| 222-22-2222 |
| 333-33-3333 |
| 444-44-4444 |
| 555-55-5555 |
| 666-66-6666 |
| 777-77-7777 |
| 888-88-8888 |
| 999-99-9999 |
+-------------+
9 rows in set (0.00 sec)
mysql> select * from account;
+------------+------------+---------+------------+------------+--------------------+--------+----------------+-------------+---------------+-----------------+
| account_id | product_cd | cust_id | open_date | close_date | last_activity_date | status | open_branch_id | open_emp_id | avail_balance | pending_balance |
+------------+------------+---------+------------+------------+--------------------+--------+----------------+-------------+---------------+-----------------+
| 1 | CHK | 1 | 2000-01-15 | NULL | 2005-01-04 | ACTIVE | 2 | 10 | 1057.75 | 1057.75 |
| 2 | SAV | 1 | 2000-01-15 | NULL | 2004-12-19 | ACTIVE | 2 | 10 | 500.00 | 500.00 |
| 3 | CD | 1 | 2004-06-30 | NULL | 2004-06-30 | ACTIVE | 2 | 10 | 3000.00 | 3000.00 |
| 4 | CHK | 2 | 2001-03-12 | NULL | 2004-12-27 | ACTIVE | 2 | 10 | 2258.02 | 2258.02 |
| 5 | SAV | 2 | 2001-03-12 | NULL | 2004-12-11 | ACTIVE | 2 | 10 | 200.00 | 200.00 |
| 7 | CHK | 3 | 2002-11-23 | NULL | 2004-11-30 | ACTIVE | 3 | 13 | 1057.75 | 1057.75 |
| 8 | MM | 3 | 2002-12-15 | NULL | 2004-12-05 | ACTIVE | 3 | 13 | 2212.50 | 2212.50 |
| 10 | CHK | 4 | 2003-09-12 | NULL | 2005-01-03 | ACTIVE | 1 | 1 | 534.12 | 534.12 |
| 11 | SAV | 4 | 2000-01-15 | NULL | 2004-10-24 | ACTIVE | 1 | 1 | 767.77 | 767.77 |
| 12 | MM | 4 | 2004-09-30 | NULL | 2004-11-11 | ACTIVE | 1 | 1 | 5487.09 | 5487.09 |
| 13 | CHK | 5 | 2004-01-27 | NULL | 2005-01-05 | ACTIVE | 4 | 16 | 2237.97 | 2897.97 |
| 14 | CHK | 6 | 2002-08-24 | NULL | 2004-11-29 | ACTIVE | 1 | 1 | 122.37 | 122.37 |
| 15 | CD | 6 | 2004-12-28 | NULL | 2004-12-28 | ACTIVE | 1 | 1 | 10000.00 | 10000.00 |
| 17 | CD | 7 | 2004-01-12 | NULL | 2004-01-12 | ACTIVE | 2 | 10 | 5000.00 | 5000.00 |
| 18 | CHK | 8 | 2001-05-23 | NULL | 2005-01-03 | ACTIVE | 4 | 16 | 3487.19 | 3487.19 |
| 19 | SAV | 8 | 2001-05-23 | NULL | 2004-10-12 | ACTIVE | 4 | 16 | 387.99 | 387.99 |
| 21 | CHK | 9 | 2003-07-30 | NULL | 2004-12-15 | ACTIVE | 1 | 1 | 125.67 | 125.67 |
| 22 | MM | 9 | 2004-10-28 | NULL | 2004-10-28 | ACTIVE | 1 | 1 | 9345.55 | 9845.55 |
| 23 | CD | 9 | 2004-06-30 | NULL | 2004-06-30 | ACTIVE | 1 | 1 | 1500.00 | 1500.00 |
| 24 | CHK | 10 | 2002-09-30 | NULL | 2004-12-15 | ACTIVE | 4 | 16 | 23575.12 | 23575.12 |
| 25 | BUS | 10 | 2002-10-01 | NULL | 2004-08-28 | ACTIVE | 4 | 16 | 0.00 | 0.00 |
| 27 | BUS | 11 | 2004-03-22 | NULL | 2004-11-14 | ACTIVE | 2 | 10 | 9345.55 | 9345.55 |
| 28 | CHK | 12 | 2003-07-30 | NULL | 2004-12-15 | ACTIVE | 4 | 16 | 38552.05 | 38552.05 |
| 29 | SBL | 13 | 2004-02-22 | NULL | 2004-12-17 | ACTIVE | 3 | 13 | 50000.00 | 50000.00 |
+------------+------------+---------+------------+------------+--------------------+--------+----------------+-------------+---------------+-----------------+
24 rows in set (0.39 sec)
mysql> select * from customer;
+---------+-------------+--------------+-----------------------+------------+-------+-------------+
| cust_id | fed_id | cust_type_cd | address | city | state | postal_code |
+---------+-------------+--------------+-----------------------+------------+-------+-------------+
| 1 | 111-11-1111 | I | 47 Mockingbird Ln | Lynnfield | MA | 01940 |
| 2 | 222-22-2222 | I | 372 Clearwater Blvd | Woburn | MA | 01801 |
| 3 | 333-33-3333 | I | 18 Jessup Rd | Quincy | MA | 02169 |
| 4 | 444-44-4444 | I | 12 Buchanan Ln | Waltham | MA | 02451 |
| 5 | 555-55-5555 | I | 2341 Main St | Salem | NH | 03079 |
| 6 | 666-66-6666 | I | 12 Blaylock Ln | Waltham | MA | 02451 |
| 7 | 777-77-7777 | I | 29 Admiral Ln | Wilmington | MA | 01887 |
| 8 | 888-88-8888 | I | 472 Freedom Rd | Salem | NH | 03079 |
| 9 | 999-99-9999 | I | 29 Maple St | Newton | MA | 02458 |
| 10 | 04-1111111 | B | 7 Industrial Way | Salem | NH | 03079 |
| 11 | 04-2222222 | B | 287A Corporate Ave | Wilmington | MA | 01887 |
| 12 | 04-3333333 | B | 789 Main St | Salem | NH | 03079 |
| 13 | 04-4444444 | B | 4772 Presidential Way | Quincy | MA | 02169 |
+---------+-------------+--------------+-----------------------+------------+-------+-------------+
13 rows in set (0.05 sec)
mysql> select * from product;
+------------+-------------------------+-----------------+--------------+--------------+
| product_cd | name | product_type_cd | date_offered | date_retired |
+------------+-------------------------+-----------------+--------------+--------------+
| AUT | auto loan | LOAN | 2000-01-01 | NULL |
| BUS | business line of credit | LOAN | 2000-01-01 | NULL |
| CD | certificate of deposit | ACCOUNT | 2000-01-01 | NULL |
| CHK | checking account | ACCOUNT | 2000-01-01 | NULL |
| MM | money market account | ACCOUNT | 2000-01-01 | NULL |
| MRT | home mortgage | LOAN | 2000-01-01 | NULL |
| SAV | savings account | ACCOUNT | 2000-01-01 | NULL |
| SBL | small business loan | LOAN | 2000-01-01 | NULL |
+------------+-------------------------+-----------------+--------------+--------------+
8 rows in set (0.00 sec)
mysql> quit;
Bye
$
0 コメント:
コメントを投稿