開発環境
- OS: OS X Lion - Apple
- データベース言語: SQL
- リレーショナルデータベース: MySQL
『初めてのSQL』(Alan Beaulieu 著、株式会社クイープ 翻訳、オライリー・ジャパン、2006年、ISBN4-8733-181-8) の9章(サブクエリ), 9.7(練習問題)9-4を解いてみる。
9-4.
SQL文(TextWrangler)
SELECT e.fname, e.lname, ( SELECT d.name FROM department d WHERE d.dept_id = e.dept_id) dept_name,( SELECT b.name FROM branch b WHERE b.branch_id = e.assigned_branch_id) branch_name FROM employee e;
入出力結果(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 2
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 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.04 sec)
mysql> show tables;
+----------------+
| Tables_in_bank |
+----------------+
| account |
| branch |
| business |
| customer |
| department |
| employee |
| individual |
| officer |
| product |
| product_type |
| tmp |
| transaction |
+----------------+
12 rows in set (0.00 sec)
mysql> select department;
ERROR 1054 (42S22): Unknown column 'department' in 'field list'
mysql> select desc department;
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 'desc department' at line 1
mysql> desc department;
+---------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+----------------+
| dept_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
+---------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 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.00 sec)
mysql> SELECT e.fname, e.lname, (
-> SELECT d.dept_name FROM department d
-> WHERE d.dept_id = e.dept_id) dept_name,(
-> SELECT b.branch_name FROM branch b
-> WHERE b.branch_id = e.assigned_branch_id) branch_name
-> FROM employee;
ERROR 1054 (42S22): Unknown column 'e.fname' in 'field list'
mysql> SELECT e.fname, e.lname, (
-> SELECT d.dept_name FROM department d
-> WHERE d.dept_id = e.dept_id) dept_name,(
-> SELECT b.branch_name FROM branch b
-> WHERE b.branch_id = e.assigned_branch_id) branch_name
-> FROM employee e;
ERROR 1054 (42S22): Unknown column 'd.dept_name' in 'field list'
mysql> SELECT e.fname, e.lname, (
-> SELECT d.name FROM department d
-> WHERE d.dept_id = e.dept_id) dept_name,(
-> SELECT b.name FROM branch b
-> WHERE b.branch_id = e.assigned_branch_id) branch_name
-> FROM employee e;
+----------+-----------+----------------+---------------+
| fname | lname | dept_name | branch_name |
+----------+-----------+----------------+---------------+
| Michael | Smith | Administration | Headquarters |
| Susan | Barker | Administration | Headquarters |
| Robert | Tyler | Administration | Headquarters |
| Susan | Hawthorne | Operations | Headquarters |
| John | Gooding | Loans | Headquarters |
| Helen | Fleming | Operations | Headquarters |
| Chris | Tucker | Operations | Headquarters |
| Sarah | Parker | Operations | Headquarters |
| Jane | Grossman | Operations | Headquarters |
| Paula | Roberts | Operations | Woburn Branch |
| Thomas | Ziegler | Operations | Woburn Branch |
| Samantha | Jameson | Operations | Woburn Branch |
| John | Blake | Operations | Quincy Branch |
| Cindy | Mason | Operations | Quincy Branch |
| Frank | Portman | Operations | Quincy Branch |
| Theresa | Markham | Operations | So. NH Branch |
| Beth | Fowler | Operations | So. NH Branch |
| Rick | Tulman | Operations | So. NH Branch |
+----------+-----------+----------------+---------------+
18 rows in set (0.04 sec)
mysql> quit
Bye
$
0 コメント:
コメントを投稿