格納したデータを引き出すしくみ〜ＳＱＬ概説の例


図６−１　ＳＱＬによる発注書管理データベースのスキーマ定義
CREATE TALBE 取引先業者 (
	取引先ID  CHAR(5)	NOT NULL,
	会社名    CHAR(30),
	住所      CHAR(100),
	PRIMARY KEY	(取引先ID)  );

CREATE TABLE 発注書 (
	発注番号  CHAR(5)	NOT NULL,
	取引先ID  CHAR(5),
	扱い者    CHAR(40),
	PRIMARY KEY	(発注番号),
	FOREIGN KEY	(取引先ID) REFERENCES 取引先業者 );

CREATE TABLE 商品 (
	商品ID    CHAR(5)	NOT NULL,
	品名      CHAR(40),
	正価      DECIMAL(4),
	PRIMARY KEY	(商品ID)  );

CREATE TABLE 発注項目 (
	発注番号  CHAR(5)	NOT NULL,
	商品ID    CHAR(5)	NOT NULL,
	数量      INTEGER,
	単価      FLOAT,
	PRIMARY KEY	(発注番号,商品ID),
	FOREIGN KEY	(発注番号) REFERENCES  発注書,
	FOREING KEY	(商品ID)   REFERENCES  商品  );


図６−２　発注書管理データベースの表のタプル
アンダーラインのついている属性は、主キーであることを示している
取引先業者

	取引先ID	会社名		住所
	--------------------------------------------------
	c45		三木商店	千代田区神田神保町
	c61		菓子の山本	文京区根津

発注書

	発注番号	取引先ID	扱い者
	------------------------------------------
	3492		c61		井沢
	3494		c45		代田
	3499		c45		井沢

発注項目

	発注番号	商品ID		数量	単価
	--------------------------------------------
	3492		ch100		50	98
	3492		se100		30	218
	3492		ca103		50	92
	3492		ku101		100	185
	3494		ch100		100	95
	3494		ca103		50	87
	3494		ch200		20	145
	3494		ca142		100	80
	3494		ga107		50	77
	3499		ch100		40	99

商品

	商品ID		品名		正価
	--------------------------------------
	ch100		チョコレート	100
	se100		センベイ	250
	ca103		キャラメル	100
	ku101		クッキー	200
	ch200		棒チョコ	160
	ca142		キャンディ	100
	ga107		ガム		90

例６−１　表”発注項目”から単価が１００円未満の項目の商品ＩＤと数量と単価が知りたい
SELECT 商品ID,数量,単価
FROM 発注項目
WHERE 単価 < 100.0 ;

＊＊結果＊＊

商品ID	数量	単価
---------------------
ch100	50	98
ca103	50	92
ch100	100	95
ca103	50	87
ca142	100	80
ga107	50	77
ch100	40	99


例６−２　表”発注書”の取引先ＩＤを会社名で置き換えたい 
SELECT 発注番号,会社名,扱い者
FROM 取引先業者,発注書
WHERE 取引先業者.取引先ID = 発注書.取引先ID;
＊＊途中のようす（直積）＊＊

取引先ID　 会社名 住所 発注番号　 取引先ID　 扱い者　
----------------------------------------------
c45 三木商店 ・・・　 3492 c61 井沢
c61 菓子の山本　 ・・・ 3492 c61 井沢←
c45 三木商店 ・・・ 3494 c45 代田←
c61 菓子の山本 ・・・ 3494 c45 代田
c45 三木商店 ・・・ 3499 c45 井沢←
c61 菓子の山本 ・・・ 3499 c45 井沢

＊＊結果＊＊

発注番号　 会社名 扱い者
-----------------------
3494 三木商店 代田
3499 三木商店 井沢
3492 菓子の山本　 井沢


例６−３　チョコレートを卸している業者の名前が知りたい
SELECT 会社名
FROM   取引先業者,発注書,商品,発注項目
WHERE  商品.品名 = 'チョコレート"
AND    取引先業者.取引先ID = 発注書.取引先ID
AND    発注書.発注番号 = 発注項目.発注番号
AND    発注項目.商品ID = 商品.商品ID ;

＊＊結果＊＊

会社名
-----------
三木商店
三木商店
菓子の山本


例６−４　表”発注項目”から、商品IDごとの発注数の合計を求めたい 
SELECT 商品ID,SUM(数量)
FROM   発注項目
GROUP BY  商品ID ;

＊＊結果＊＊

商品ID		SUM(数量)
---------------------------
ca103		100
ca142		100
ch100		190
ch200		20
ga107		50
ku101		100
se100		30


例６−５　例６−４の問い合わせに、商品ＩＤごとの発注数の合計が１００個未満という条件をつけた 
SELECT 商品ID,SUM(数量)
FROM   発注項目
GROUP BY  商品ID
HAVING    SUM(数量) < 100 ;

＊＊結果＊＊

商品ID		SUM(数量)
-------------------------
ch200		20
ga107		50
se100		30


例６−６　表”発注項目”から、同じ商品でありながら発注番号によって単価が違った商品ＩＤを求めたい 
SELECT DISTINCT  AA.商品ID
FROM   発注項目 AA, 発注項目 BB
WHERE  AA.商品ID = BB.商品ID
AND    AA.単価 <> BB.単価 ;

＊＊結果＊＊

商品ID
-----------
ca103
ch100


例６−７　１商品につき５０個以上の発注をしたことのある扱い者の名前を求めたい 
SELECT DISTINCT 扱い者
FROM   発注書
WHERE  発注番号 IN
     ( SELECT 発注番号
　　　 FROM   発注項目
       WHERE  数量 >= 50 ) ;

＊＊結果＊＊

扱い者
----------
井沢
代田


例６−８　商品ごとに発注数の平均を求め、その商品について平均よりも多くの数量を発注したことのある扱い者の名前を求めたい 
SELECT DISTINCT 扱い者
FROM   発注書
WHERE  発注番号 IN
     ( SELECT 発注番号
　　　 FROM   発注項目 XX
       WHERE  数量 >
	     ( SELECT AVG(数量)
	       FROM   発注項目
	       WHERE  商品ID = XX.商品ID  )) ;

＊＊結果＊＊

扱い者
----------
代田