■SQLメモ(3)
1.さらに、いろいろな操作をするために、声優テーブルも作成。
SQL文
CREATE TABLE vatable (id CHAR(4), name TEXT, age INT4, birthday DATE);
結果
テーブル "public.vatable"
カラム | 型 | 修飾語
----------+--------------+--------
id | character(4) |
name | text |
age | integer |
birthday | date |
2.データの追加
SQL文
INSERT INTO vatable VALUES('V001', '折笠愛', 41, '1963-12-12');
INSERT INTO vatable VALUES('V002', '折笠富美子', 30, '1974-12-27');
INSERT INTO vatable VALUES('V003', '水野愛日', 28, '1977-07-27');
INSERT INTO vatable VALUES('V004', '清水愛', 24, '1981-03-26');
INSERT INTO vatable VALUES('V005', '田村ゆかり', 29, '1976-02-27');
INSERT INTO vatable VALUES('V006', '田中理恵', 26, '1979-01-03');
INSERT INTO vatable VALUES('V007', '國府田マリ子', 36, '1969-09-05');
INSERT INTO vatable VALUES('V008', '井上喜久子', 41, '1964-09-25');
INSERT INTO vatable VALUES('V009', '宮村優子', 32, '1972-12-04');
結果
id | name | age | birthday
------+--------------+-----+------------
V001 | 折笠愛 | 41 | 1963-12-12
V002 | 折笠富美子 | 30 | 1974-12-27
V003 | 水野愛日 | 28 | 1977-07-27
V004 | 清水愛 | 24 | 1981-03-26
V005 | 田村ゆかり | 29 | 1976-02-27
V006 | 田中理恵 | 26 | 1979-01-03
V006 | 國府田マリ子 | 36 | 1969-09-05
V006 | 井上喜久子 | 41 | 1964-09-25
V006 | 宮村優子 | 32 | 1972-12-04
(9 行)
3.「CDテーブル」上で、レーベルは何種類あったっけ？
SQL文
SELECT COUNT(DISTINCT lavelid) FROM cdtable;
結果
count
-------
5
(1 行)
4.「CDテーブル」のCDが全部売れればどのくらい儲かる？
SQL文
SELECT sum( price * stock ) FROM cdtable;
結果
sum
-------
75071
(1 行)
もしかして、CDは結構儲かる？
5.声優さんの平均年齢は？
SQL文
SELECT AVG(age) FROM vatable;
結果
avg
---------------------
31.8888888888888889
(1 行)
結構・・・
6.じゃあ、最年長は？
SQL文
SELECT MAX(age) FROM vatable;
結果
max
-----
41
(1 行)
やっぱりおねえちゃん。
7.「声優さん」だけのCDリストが欲しい。
SQL文
SELECT cdtable.title, vatable.name, cdtable.price
FROM cdtable, vatable
WHERE cdtable.va = vatable.name
ORDER by price;
結果
title | name | price
--------------------+--------------+-------
Angel Fish | 清水愛 | 1050
Lrycal Step | 田村ゆかり | 1300
chara de Rie | 田中理恵 | 1701
魂 | 宮村優子 | 2100
manabee Happy Wave | 水野愛日 | 2440
Truth | 折笠愛 | 2446
Lune | 折笠富美子 | 2446
みずうみ | 井上喜久子 | 2905
ビタミン パンチ | 國府田マリ子 | 3000
(9 行)
愛ちゃんは声やってない。
8.ついでに、レーベルごとに並び替えてよ。
SQL文
SELECT cdtable.title, vatable.name, cdtable.lavelid
FROM cdtable, vatable
WHERE cdtable.va = vatable.name
ORDER BY price, lavelid DESC;
結果
title | name | lavelid
--------------------+---------- --+---------
Angel Fish | 清水愛 | K01
Lrycal Step | 田村ゆかり | K01
chara de Rie | 田中理恵 | V01
魂 | 宮村優子 | V01
manabee Happy Wave | 水野愛日 | G01
Truth | 折笠愛 | G01
Lune | 折笠富美子 | G01
みずうみ | 井上喜久子 | P01
ビタミン パンチ | 國府田マリ子 | K01
(9 行)
9.さらに売り上げも追加。
SQL文
SELECT cdtable.title, vatable.name, cdtable.lavelid, price*stock AS 売り上げ
FROM cdtable, vatable
WHERE cdtable.va = vatable.name
ORDER BY 売り上げ;
結果
title | name | lavelid | 売り上げ
--------------------+--------------+---------+----------
chara de Rie | 田中理恵 | V01 | 1701
Lrycal Step | 田村ゆかり | K01 | 2600
Lune | 折笠富美子 | G01 | 4892
みずうみ | 井上喜久子 | P01 | 5810
Truth | 折笠愛 | G01 | 7338
Angel Fish | 清水愛 | K01 | 7350
魂 | 宮村優子 | V01 | 8400
manabee Happy Wave | 水野愛日 | G01 | 12200
ビタミン パンチ | 國府田マリ子 | K01 | 21000
(9 行)
マリ姉売れまくり。
10.CDの声優とマージしたい
SQL文
SELECT cdtable.title, vatable.name
FROM cdtable LEFT JOIN vatable
ON cdtable.va = vatable.name;
結果
title | name
----------------------+--------------
みずうみ | 井上喜久子
彼女はゴキゲンななめ |
魂 | 宮村優子
manabee Happy Wave | 水野愛日
Angel Fish | 清水愛
Truth | 折笠愛
Lune | 折笠富美子
Lrycal Step | 田村ゆかり
chara de Rie | 田中理恵
ビタミン パンチ | 國府田マリ子
(10 行)
愛ちゃんは声の仕事やってないので抜けます。
11.これを副問い合わせで書いてよ。
SQL文
SELECT va FROM cdtable WHERE va IN (SELECT name FROM vatable );
結果
va
--------------
折笠愛
折笠富美子
水野愛日
清水愛
田村ゆかり
田中理恵
宮村優子
井上喜久子
國府田マリ子
(9 行)
12.古株の人は誰？
SQL文
SELECT va FROM cdtable WHERE va IN (SELECT name FROM vatable WHERE age > 30 );
結果
va
--------------
折笠愛
宮村優子
井上喜久子
國府田マリ子
(4 行)
まだまだ、皆さんがんばります。
13.若手は？
SQL文
select * from vatable where birthday not between '1960-1-1' and '1980-1-1';
結果
id | name | age | birthday
------+--------+-----+------------
V004 | 清水愛 | 24 | 1981-03-26
(1 行)
14.声優さんだけのテーブルが作りたくなった。newcdtableとして新しく作ろう。
SQL文
CREATE TABLE newcdtable (id CHAR(3), title TEXT, va TEXT, price INT4, labelid CHAR(3), stock INT4);
結果
カラム | 型 | 修飾語
---------+--------------+--------
id | character(3) |
title | text |
va | text |
price | integer |
labelid | character(3) |
stock | integer |
15.まず、オリジナルからコピーしよう。
SQL文
INSERT INTO newcdtable (id,title,va,price,labelid, stock) SELECT * FROM cdtable;
結果
id | title | va | price | labelid | stock
-----+----------------------+--------------+-------+---------+-------
001 | Truth | 折笠愛 | 2446 | G01 | 3
002 | Lune | 折笠富美子 | 2446 | G01 | 2
003 | manabee Happy Wave | 水野愛日 | 2440 | G01 | 5
004 | Angel Fish | 清水愛 | 1050 | K01 | 7
005 | Lrycal Step | 田村ゆかり | 1300 | K01 | 2
006 | chara de Rie | 田中理恵 | 1701 | V01 | 1
007 | 彼女はゴキゲンななめ | 嘉陽愛子 | 1890 | A01 | 2
010 | 魂 | 宮村優子 | 2100 | V01 | 4
009 | みずうみ | 井上喜久子 | 2905 | P01 | 2
008 | ビタミン パンチ | 國府田マリ子 | 3000 | K01 | 7
(10 行)
16.ついでに、20％オフにしよう。
SQL文
UPDATE newcdtable SET price = price * 0.8 ;
結果
id | title | va | price | labelid | stock
-----+----------------------+--------------+-------+---------+-------
001 | Truth | 折笠愛 | 1957 | G01 | 3
002 | Lune | 折笠富美子 | 1957 | G01 | 2
003 | manabee Happy Wave | 水野愛日 | 1952 | G01 | 5
004 | Angel Fish | 清水愛 | 840 | K01 | 7
005 | Lrycal Step | 田村ゆかり | 1040 | K01 | 2
006 | chara de Rie | 田中理恵 | 1361 | V01 | 1
007 | 彼女はゴキゲンななめ | 嘉陽愛子 | 1512 | A01 | 2
010 | 魂 | 宮村優子 | 1680 | V01 | 4
009 | みずうみ | 井上喜久子 | 2324 | P01 | 2
008 | ビタミン パンチ | 國府田マリ子 | 2400 | K01 | 7
(10 行)
17.最後に声優DBにない人を削除。
SQL文
DELETE FROM newcdtable WHERE va NOT IN (SELECT name FROM vatable);
結果
id | title | va | price | labelid | stock
-----+--------------------+--------------+-------+---------+-------
001 | Truth | 折笠愛 | 1957 | G01 | 3
002 | Lune | 折笠富美子 | 1957 | G01 | 2
003 | manabee Happy Wave | 水野愛日 | 1952 | G01 | 5
004 | Angel Fish | 清水愛 | 840 | K01 | 7
005 | Lrycal Step | 田村ゆかり | 1040 | K01 | 2
006 | chara de Rie | 田中理恵 | 1361 | V01 | 1
010 | 魂 | 宮村優子 | 1680 | V01 | 4
009 | みずうみ | 井上喜久子 | 2324 | P01 | 2
008 | ビタミン パンチ | 國府田マリ子 | 2400 | K01 | 7
(9 行)
最終更新：2005年10月22日 11:32
