開発環境
- OS: OS X Lion - Apple
- データベース言語: SQL
- リレーショナルデータベース: MySQL
『初めてのSQL』(Alan Beaulieu 著、株式会社クイープ 翻訳、オライリー・ジャパン、2006年、ISBN4-8733-181-8) の3章(クエリ入門), 3.8(練習問題)3-4を解いてみる。
3-4.
SQL文(TextWrangler)
select p.product_cd, a.cust_id, a.avail_balance from product p inner join account a on p.product_cd = a.product_cd where p.product_type_cd = 'ACCOUNT';
入出力結果(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 6 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.00 sec) mysql> desc product ; +-----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-------------+------+-----+---------+-------+ | product_cd | varchar(10) | NO | PRI | NULL | | | name | varchar(50) | NO | | NULL | | | product_type_cd | varchar(10) | NO | MUL | NULL | | | date_offered | date | YES | | NULL | | | date_retired | date | YES | | NULL | | +-----------------+-------------+------+-----+---------+-------+ 5 rows in set (0.05 sec) mysql> select product_type_cd from product; +-----------------+ | product_type_cd | +-----------------+ | ACCOUNT | | ACCOUNT | | ACCOUNT | | ACCOUNT | | LOAN | | LOAN | | LOAN | | LOAN | +-----------------+ 8 rows in set (0.00 sec) mysql> select * from account; +------------+------------+---------+------------+------------+--------------------+--------+----------------+-------------+---------------+-----------------+ | account_id | product_cd | cust_id | open_date | close_date | last_activity_date | status | open_branch_id | open_emp_id | avail_balance | pending_balance | +------------+------------+---------+------------+------------+--------------------+--------+----------------+-------------+---------------+-----------------+ | 1 | CHK | 1 | 2000-01-15 | NULL | 2005-01-04 | ACTIVE | 2 | 10 | 1057.75 | 1057.75 | | 2 | SAV | 1 | 2000-01-15 | NULL | 2004-12-19 | ACTIVE | 2 | 10 | 500.00 | 500.00 | | 3 | CD | 1 | 2004-06-30 | NULL | 2004-06-30 | ACTIVE | 2 | 10 | 3000.00 | 3000.00 | | 4 | CHK | 2 | 2001-03-12 | NULL | 2004-12-27 | ACTIVE | 2 | 10 | 2258.02 | 2258.02 | | 5 | SAV | 2 | 2001-03-12 | NULL | 2004-12-11 | ACTIVE | 2 | 10 | 200.00 | 200.00 | | 7 | CHK | 3 | 2002-11-23 | NULL | 2004-11-30 | ACTIVE | 3 | 13 | 1057.75 | 1057.75 | | 8 | MM | 3 | 2002-12-15 | NULL | 2004-12-05 | ACTIVE | 3 | 13 | 2212.50 | 2212.50 | | 10 | CHK | 4 | 2003-09-12 | NULL | 2005-01-03 | ACTIVE | 1 | 1 | 534.12 | 534.12 | | 11 | SAV | 4 | 2000-01-15 | NULL | 2004-10-24 | ACTIVE | 1 | 1 | 767.77 | 767.77 | | 12 | MM | 4 | 2004-09-30 | NULL | 2004-11-11 | ACTIVE | 1 | 1 | 5487.09 | 5487.09 | | 13 | CHK | 5 | 2004-01-27 | NULL | 2005-01-05 | ACTIVE | 4 | 16 | 2237.97 | 2897.97 | | 14 | CHK | 6 | 2002-08-24 | NULL | 2004-11-29 | ACTIVE | 1 | 1 | 122.37 | 122.37 | | 15 | CD | 6 | 2004-12-28 | NULL | 2004-12-28 | ACTIVE | 1 | 1 | 10000.00 | 10000.00 | | 17 | CD | 7 | 2004-01-12 | NULL | 2004-01-12 | ACTIVE | 2 | 10 | 5000.00 | 5000.00 | | 18 | CHK | 8 | 2001-05-23 | NULL | 2005-01-03 | ACTIVE | 4 | 16 | 3487.19 | 3487.19 | | 19 | SAV | 8 | 2001-05-23 | NULL | 2004-10-12 | ACTIVE | 4 | 16 | 387.99 | 387.99 | | 21 | CHK | 9 | 2003-07-30 | NULL | 2004-12-15 | ACTIVE | 1 | 1 | 125.67 | 125.67 | | 22 | MM | 9 | 2004-10-28 | NULL | 2004-10-28 | ACTIVE | 1 | 1 | 9345.55 | 9845.55 | | 23 | CD | 9 | 2004-06-30 | NULL | 2004-06-30 | ACTIVE | 1 | 1 | 1500.00 | 1500.00 | | 24 | CHK | 10 | 2002-09-30 | NULL | 2004-12-15 | ACTIVE | 4 | 16 | 23575.12 | 23575.12 | | 25 | BUS | 10 | 2002-10-01 | NULL | 2004-08-28 | ACTIVE | 4 | 16 | 0.00 | 0.00 | | 27 | BUS | 11 | 2004-03-22 | NULL | 2004-11-14 | ACTIVE | 2 | 10 | 9345.55 | 9345.55 | | 28 | CHK | 12 | 2003-07-30 | NULL | 2004-12-15 | ACTIVE | 4 | 16 | 38552.05 | 38552.05 | | 29 | SBL | 13 | 2004-02-22 | NULL | 2004-12-17 | ACTIVE | 3 | 13 | 50000.00 | 50000.00 | +------------+------------+---------+------------+------------+--------------------+--------+----------------+-------------+---------------+-----------------+ 24 rows in set (0.00 sec) mysql> select p.product_cd, a.cust_id, a.avail_balance from product p inner join account a on p.product_cd = a.product_cd where p.product_type_cd = 'ACCOUNT'; +------------+---------+---------------+ | product_cd | cust_id | avail_balance | +------------+---------+---------------+ | CD | 1 | 3000.00 | | CD | 6 | 10000.00 | | CD | 7 | 5000.00 | | CD | 9 | 1500.00 | | CHK | 1 | 1057.75 | | CHK | 2 | 2258.02 | | CHK | 3 | 1057.75 | | CHK | 4 | 534.12 | | CHK | 5 | 2237.97 | | CHK | 6 | 122.37 | | CHK | 8 | 3487.19 | | CHK | 9 | 125.67 | | CHK | 10 | 23575.12 | | CHK | 12 | 38552.05 | | MM | 3 | 2212.50 | | MM | 4 | 5487.09 | | MM | 9 | 9345.55 | | SAV | 1 | 500.00 | | SAV | 2 | 200.00 | | SAV | 4 | 767.77 | | SAV | 8 | 387.99 | +------------+---------+---------------+ 21 rows in set (0.02 sec) mysql> quit Bye $
0 コメント:
コメントを投稿