開発環境
- OS: OS X Lion - Apple
- データベース言語: SQL
- リレーショナルデータベース: MySQL
『初めてのSQL』(Alan Beaulieu 著、株式会社クイープ 翻訳、オライリー・ジャパン、2006年、ISBN4-87311-281-8) の10章(結合), 10.4(練習問題)10-3を解いてみる。
10-3.
入出力結果(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.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> 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.12 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.00 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.00 sec)
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.19 sec)
mysql> select cust_id from account;
+---------+
| cust_id |
+---------+
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
| 4 |
| 4 |
| 4 |
| 5 |
| 6 |
| 6 |
| 7 |
| 8 |
| 8 |
| 9 |
| 9 |
| 9 |
| 10 |
| 10 |
| 11 |
| 12 |
| 13 |
+---------+
24 rows in set (0.40 sec)
mysql> select account_id from account;
+------------+
| account_id |
+------------+
| 10 |
| 11 |
| 12 |
| 14 |
| 15 |
| 21 |
| 22 |
| 23 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 17 |
| 27 |
| 7 |
| 8 |
| 29 |
| 13 |
| 18 |
| 19 |
| 24 |
| 25 |
| 28 |
+------------+
24 rows in set (0.00 sec)
mysql> select account_id from account order by account_id decend;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'decend' at line 1
mysql> select account_id from account order by account_id desc;
+------------+
| account_id |
+------------+
| 29 |
| 28 |
| 27 |
| 25 |
| 24 |
| 23 |
| 22 |
| 21 |
| 19 |
| 18 |
| 17 |
| 15 |
| 14 |
| 13 |
| 12 |
| 11 |
| 10 |
| 8 |
| 7 |
| 5 |
| 4 |
| 3 |
| 2 |
| 1 |
+------------+
24 rows in set (0.00 sec)
mysql> select account_id from account order by account_id;
+------------+
| account_id |
+------------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 7 |
| 8 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 17 |
| 18 |
| 19 |
| 21 |
| 22 |
| 23 |
| 24 |
| 25 |
| 27 |
| 28 |
| 29 |
+------------+
24 rows in set (0.02 sec)
mysql> quit
Bye
$
0 コメント:
コメントを投稿