開発環境
- 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 6 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.06 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.02 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.01 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.01 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.02 sec) mysql> quit Bye $
0 コメント:
コメントを投稿