開発環境
- OS: OS X Lion - Apple
- データベース言語: SQL
- リレーショナルデータベース: MySQL
『初めてのSQL』(Alan Beaulieu 著、株式会社クイープ 翻訳、オライリー・ジャパン、2006年、ISBN4-87311-281-8) の5章(複数テーブルからのデータの取得), 5.6(練習問題)5-1を解いてみる。
5-1.
入出力結果(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 4
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.01 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.03 sec)
mysql> desc employee;
+--------------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+----------------------+------+-----+---------+----------------+
| emp_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| fname | varchar(20) | NO | | NULL | |
| lname | varchar(20) | NO | | NULL | |
| start_date | date | NO | | NULL | |
| end_date | date | YES | | NULL | |
| superior_emp_id | smallint(5) unsigned | YES | MUL | NULL | |
| dept_id | smallint(5) unsigned | YES | MUL | NULL | |
| title | varchar(20) | YES | | NULL | |
| assigned_branch_id | smallint(5) unsigned | YES | MUL | NULL | |
+--------------------+----------------------+------+-----+---------+----------------+
9 rows in set (0.02 sec)
mysql> desc branch;
+-----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+----------------+
| branch_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| address | varchar(30) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| state | varchar(2) | YES | | NULL | |
| zip | varchar(12) | YES | | NULL | |
+-----------+----------------------+------+-----+---------+----------------+
6 rows in set (0.03 sec)
mysql> select e.emp_id,e.fname,e.lname,b.name
-> from employee e inner join branch b
-> on e.assigned_branch_id = b.branch_id;
+--------+----------+-----------+---------------+
| emp_id | fname | lname | name |
+--------+----------+-----------+---------------+
| 1 | Michael | Smith | Headquarters |
| 2 | Susan | Barker | Headquarters |
| 3 | Robert | Tyler | Headquarters |
| 4 | Susan | Hawthorne | Headquarters |
| 5 | John | Gooding | Headquarters |
| 6 | Helen | Fleming | Headquarters |
| 7 | Chris | Tucker | Headquarters |
| 8 | Sarah | Parker | Headquarters |
| 9 | Jane | Grossman | Headquarters |
| 10 | Paula | Roberts | Woburn Branch |
| 11 | Thomas | Ziegler | Woburn Branch |
| 12 | Samantha | Jameson | Woburn Branch |
| 13 | John | Blake | Quincy Branch |
| 14 | Cindy | Mason | Quincy Branch |
| 15 | Frank | Portman | Quincy Branch |
| 16 | Theresa | Markham | So. NH Branch |
| 17 | Beth | Fowler | So. NH Branch |
| 18 | Rick | Tulman | So. NH Branch |
+--------+----------+-----------+---------------+
18 rows in set (0.10 sec)
mysql> quit;
Bye
$
0 コメント:
コメントを投稿