開発環境
- OS: OS X Lion - Apple
- データベース言語: SQL
- リレーショナルデータベース: MySQL
『初めてのSQL』(Alan Beaulieu 著、株式会社クイープ 翻訳、オライリー・ジャパン、2006年、ISBN4-8733-181-8) の10章(結合)、10.4(練習問題)10-3を解いてみる。
10-3.
SQL文(BBEdit)
SELECT a.account_id, a.product_cd, i.fname, i.lname, b.name from account a left outer join individual i on a.cust_id = i.cust_id left outer join business b on a.cust_id = b.cust_id;
入出力結果(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 5
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> 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> desc individual
-> ;
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| cust_id | int(10) unsigned | NO | PRI | NULL | |
| fname | varchar(30) | NO | | NULL | |
| lname | varchar(30) | NO | | NULL | |
| birth_date | date | YES | | NULL | |
+------------+------------------+------+-----+---------+-------+
4 rows in set (0.03 sec)
mysql> desc business;
+-------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| cust_id | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(40) | NO | | NULL | |
| state_id | varchar(10) | NO | | NULL | |
| incorp_date | date | YES | | NULL | |
+-------------+------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> SELECT a.account_id, a.product_cd, i.fname, i.lname, b.names
-> from account a left outer join individual i
-> on a.cust_id = i.cust_id left outer join business b
-> on a.cust_id = b.cust_id;
ERROR 1054 (42S22): Unknown column 'b.names' in 'field list'
mysql> SELECT a.account_id, a.product_cd, i.fname, i.lname, b.name
-> from account a left outer join individual i
-> on a.cust_id = i.cust_id left outer join business b
-> on a.cust_id = b.cust_id;
+------------+------------+----------+---------+------------------------+
| account_id | product_cd | fname | lname | name |
+------------+------------+----------+---------+------------------------+
| 1 | CHK | James | Hadley | NULL |
| 2 | SAV | James | Hadley | NULL |
| 3 | CD | James | Hadley | NULL |
| 4 | CHK | Susan | Tingley | NULL |
| 5 | SAV | Susan | Tingley | NULL |
| 7 | CHK | Frank | Tucker | NULL |
| 8 | MM | Frank | Tucker | NULL |
| 10 | CHK | John | Hayward | NULL |
| 11 | SAV | John | Hayward | NULL |
| 12 | MM | John | Hayward | NULL |
| 13 | CHK | Charles | Frasier | NULL |
| 14 | CHK | John | Spencer | NULL |
| 15 | CD | John | Spencer | NULL |
| 17 | CD | Margaret | Young | NULL |
| 18 | CHK | Louis | Blake | NULL |
| 19 | SAV | Louis | Blake | NULL |
| 21 | CHK | Richard | Farley | NULL |
| 22 | MM | Richard | Farley | NULL |
| 23 | CD | Richard | Farley | NULL |
| 24 | CHK | NULL | NULL | Chilton Engineering |
| 25 | BUS | NULL | NULL | Chilton Engineering |
| 27 | BUS | NULL | NULL | Northeast Cooling Inc. |
| 28 | CHK | NULL | NULL | Superior Auto Body |
| 29 | SBL | NULL | NULL | AAA Insurance Inc. |
+------------+------------+----------+---------+------------------------+
24 rows in set (0.08 sec)
mysql> quit
Bye
$
0 コメント:
コメントを投稿