開発環境
- OS: OS X Lion - Apple
- データベース言語: SQL
- リレーショナルデータベース: MySQL
『初めてのSQL』(Alan Beaulieu 著、株式会社クイープ 翻訳、オライリー・ジャパン、2006年、ISBN4-87311-281-8) の5章(複数テーブルからのデータの取得), 5.6(練習問題)5-3.を解いてみる。
5-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 5
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 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.04 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 officer;
+------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+----------------+
| officer_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| cust_id | int(10) unsigned | NO | MUL | NULL | |
| fname | varchar(30) | NO | | NULL | |
| lname | varchar(30) | NO | | NULL | |
| title | varchar(20) | YES | | NULL | |
| start_date | date | NO | | NULL | |
| end_date | date | YES | | NULL | |
+------------+----------------------+------+-----+---------+----------------+
7 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.00 sec)
mysql> select e1.emp_id,e1.fname,e1.lname
-> from employee e inner join employee e2
-> on e1.emp_id = e2.emp_id
-> where e1.dept_id !=e2.superior_emp_id;
ERROR 1054 (42S22): Unknown column 'e1.emp_id' in 'field list'
mysql> select * from employee;
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
| emp_id | fname | lname | start_date | end_date | superior_emp_id | dept_id | title | assigned_branch_id |
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
| 1 | Michael | Smith | 2001-06-22 | NULL | NULL | 3 | President | 1 |
| 2 | Susan | Barker | 2002-09-12 | NULL | 1 | 3 | Vice President | 1 |
| 3 | Robert | Tyler | 2000-02-09 | NULL | 1 | 3 | Treasurer | 1 |
| 4 | Susan | Hawthorne | 2002-04-24 | NULL | 3 | 1 | Operations Manager | 1 |
| 5 | John | Gooding | 2003-11-14 | NULL | 4 | 2 | Loan Manager | 1 |
| 6 | Helen | Fleming | 2004-03-17 | NULL | 4 | 1 | Head Teller | 1 |
| 7 | Chris | Tucker | 2004-09-15 | NULL | 6 | 1 | Teller | 1 |
| 8 | Sarah | Parker | 2002-12-02 | NULL | 6 | 1 | Teller | 1 |
| 9 | Jane | Grossman | 2002-05-03 | NULL | 6 | 1 | Teller | 1 |
| 10 | Paula | Roberts | 2002-07-27 | NULL | 4 | 1 | Head Teller | 2 |
| 11 | Thomas | Ziegler | 2000-10-23 | NULL | 10 | 1 | Teller | 2 |
| 12 | Samantha | Jameson | 2003-01-08 | NULL | 10 | 1 | Teller | 2 |
| 13 | John | Blake | 2000-05-11 | NULL | 4 | 1 | Head Teller | 3 |
| 14 | Cindy | Mason | 2002-08-09 | NULL | 13 | 1 | Teller | 3 |
| 15 | Frank | Portman | 2003-04-01 | NULL | 13 | 1 | Teller | 3 |
| 16 | Theresa | Markham | 2001-03-15 | NULL | 4 | 1 | Head Teller | 4 |
| 17 | Beth | Fowler | 2002-06-29 | NULL | 16 | 1 | Teller | 4 |
| 18 | Rick | Tulman | 2002-12-12 | NULL | 16 | 1 | Teller | 4 |
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
18 rows in set (0.39 sec)
mysql> select e1.emp_id,e1.fname,e1.lname
-> from employee e1 inner join employee e2
-> on e1.emp_id = superior_emp_id
-> where e1.dept_id != e2.dept_id;
ERROR 1052 (23000): Column 'superior_emp_id' in on clause is ambiguous
mysql> select e1.emp_id,e1.fname,e1.lname from employee e1 inner join employee e2 on e1.emp_id = e2.superior_emp_id where e1.dept_id != e2.dept_id;
+--------+--------+-----------+
| emp_id | fname | lname |
+--------+--------+-----------+
| 3 | Robert | Tyler |
| 4 | Susan | Hawthorne |
+--------+--------+-----------+
2 rows in set (0.43 sec)
mysql> select e1.emp_id,e1.fname,e1.lname from employee e1 inner join employee e2 on e1.superior_emp_id = e2.emp_id where e1.dept_id != e2.dept_id;
+--------+-------+-----------+
| emp_id | fname | lname |
+--------+-------+-----------+
| 4 | Susan | Hawthorne |
| 5 | John | Gooding |
+--------+-------+-----------+
2 rows in set (0.37 sec)
mysql> quit;
Bye
$
0 コメント:
コメントを投稿