ITスクール DAY30 Oracle 関係・パターン・集合演算子/GROUP BY /HAVING/SubQuery

SELECT復習

先日、作ったお菓子のテーブルから読みたいデーターをリードする復習を行いました。

--全てのスナックの名前、価格、賞味期限を取得
SELECT s_name "商品名", s_price "価格", s_exp "賞味期限" FROM SNACK;

--全てのスナックの名前、価格、消費税(価格の10%)を取得
SELECT s_name "商品名", s_price "価格", s_price/10 "消費税" FROM SNACK;

--全てのスナックの名前、価格、販売価格(20%割引)を取得
SELECT s_name "商品名", s_price "価格", s_price*0.8 "販売価格(20%割引)" FROM SNACK;

--2000円より大きい価格の平均価格を取得
SELECT s_price > 2000 の AVG(s_price)  FROM SNACK;

--最高価格、最低価格、総スナック種類数は？
SELECT MAX(s_price)、MIN(s_price)、COUNT(*) FROM SNACK;

--2000円以上のスナックの全情報を取得
SELECT AVG(s_price) FROM snack WHERE s_price>2000;

--2000円未満のスナックの名前、価格、販売価格(20%割引)を取得
SELECT s_name "商品名" ,s_price "価格" ,s_price*0.8 "販売価格(20%割引)" FROM snack WHERE s_price<2000;

--2000円のスナックは何個？
SELECT count(*) FROM snack WHERE s_price<2000;

--ハニーバターチップの価格は？
SELECT s_price FROM snack WHERE s_name = 'ハニーバターチップ';

--オリオン社のスナックの価格合計は？
SELECT SUM(s_price) FROM snack WHERE s_maker = 'オリオン';;

--今月中に食べるべきスナックの名前、価格、賞味期限は？
SELECT s_name "商品名", s_price "価格" ,s_exp "賞味期限" FROM snack WHERE S_EXP < TO_DATE('2023-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND s_exp >= SYSDATE;

最後の条件は、今月末まで食べれるお菓子の名前、値段、賞味期間をREADすることでしたが、
SYSDATEを入れることは視野に入れなかったです。今日が8月22日ですが、SYSDATEを入れないとすでに賞味期間が過ぎた21日、20日なども含まれますので、両方入れることが重要だと考えました。まだ、TO_DATEの使い方が慣れてないですが、それ以外はDMLを理解すれば、難しくはありませんでした。

PK（人造キー）

PKとFK、Sequenceを活用する問題でした。掲示板テーブルの条件ないにPKに設定できるものがなかったので、自然キーではなく、人造キーが必要だと考え、勝手に番号を作りましたが、、、
それが答えでした！先生が考えさせるためにわなをかけた自習でした。

＜条件＞

ウェブサイトで掲示板に関するテーブルとそれを[参照する]掲示板コメントテーブルを作成したいです。
掲示板テーブルの構造は[投稿者/掲示板の内容/投稿時刻]に関する部分があれば良いです。
コメントテーブルには[投稿者/コメントの内容/投稿時刻]に関する部分があればいいです。

1. テーブルの構造を理解して、掲示板テーブルを作成します

掲示板(
    post_number number(5) primary key,
    user_id varchar2(15 char) NOT NULL,
    user_text varchar2(300 char) NOT NULL,
    writtentime date NOT NULL
);
 
--シーケンスを作成します
CREATE SEQUENCE table_seq;

2. テーブルの構造を理解して、コメントテーブルを作成します
   [条件: 参照されるテーブルの内容が削除されると、関連するコメントも削除される]

コメント(
     post_number number(5) primary key,
     board_post_number number(5) NOT NULL,
     user_id varchar2(15 char) NOT NULL,
     user_comment varchar2(300 char) NOT NULL,
     writtentime date NOT NULL,
     制約 fk_board_post_number foreign key(board_post_number)
      references 掲示板(post_number) on delete cascade
);
 
--同様シーケンスを作成します：
CREATE SEQUENCE comment_seq;

3. 掲示板テーブルに2つの値を挿入し、各掲示板ごとに2つのコメントを挿入します
   [条件: 書き込み時間の値は現在の日付/時刻に統一]

INSERT INTO "掲示板" VALUES(table_seq.nextval,'john','こんにちは',sysdate);
INSERT INTO "掲示板" VALUES(table_seq.nextval,'ken','こんにちは',sysdate);
 
コメントに以下の値を挿入します：
INSERT INTO "コメント" VALUES(comment_seq.nextval,1,'zill','わーい',sysdate);
INSERT INTO "コメント" VALUES(comment_seq.nextval,2,'zill','わーい',sysdate);

SELECT * FROM "掲示板";
SELECT * FROM "コメント";

-- 特定の投稿を削除するためのクエリ
--投稿番号が1の投稿を削除します
DELETE FROM "掲示板" WHERE post_number = 1;
SELECT * FROM "コメント";

関係演算子

NOT, AND, OR, BETWEEN, IN

AND & BETWEEN

-- 価格が1000円以上3000円以下のお菓子の全体内容を検索
-- ANDを使用した方法
SELECT * FROM snack WHERE S_PRICE >= 1000 AND S_PRICE <= 3000;

-- BETWEENを使用した方法
SELECT * FROM snack WHERE S_PRICE BETWEEN 1000 AND 3000;

BETWEENは以上/以下で条件を設定するため、1000円を超え、3000円未満ではBETWEENが使えません。

OR & IN

-- 価格が1000円、2000円、3000円のお菓子の全体内容を検索
SELECT * FROM snack WHERE S_PRICE = 1000 OR S_PRICE = 2000 OR S_PRICE = 3000;
-- 連続していない値を取得したい場合は、INを使用
SELECT * FROM snack WHERE S_PRICE IN(1000, 2000, 3000);
-- NOT INを使用した方法
SELECT * FROM snack WHERE S_PRICE NOT IN(1000, 2000, 3000);

INは、使うところが多い気がしましたが、また初めての演算子だったので、慣れるには少し時間がかかると思います。サブQueryを使用する際に、リターンするローが2つ以上の場合は、INを活用する方法があります。後ほどサブQueryでもう一度説明します。

パターン演算子

LIKE

-- LIKE '%ㅋ' : ㅋで終わるもの
-- LIKE 'ㅋ%' : ㅋで始まるもの
-- LIKE '%ㅋ%' : ㅋが含まれるもの
-- なぜLIKEの代わりに( = 'ㅋ%')は機能しないのか？
-- 等号を使って式を表すと、パターンとして認識するのではなく、'ㅋ%'文字そのままとして認識してしまう

-- '해'が含まれる会社で作られたお菓子の名前を検索
SELECT s_name FROM snack WHERE s_maker LIKE '%해%';

-- '이'が含まれるお菓子の平均価格
SELECT ROUND(AVG(s_price), 1) FROM snack WHERE s_name LIKE '%이%';

-- オリオンお菓子または、コッカルコン〜の名前、メーカー、価格を検索
SELECT s_name, s_maker, s_price FROM snack WHERE s_maker ='オリオン' OR s_name LIKE '%꼬칼콘%';

-- お菓子の名前に새콤달콤を含むか、꼬칼콘を含むお菓子の名前、メーカー、価格を検索
SELECT s_name, s_maker, s_price FROM snack WHERE s_name LIKE '%새콤달콤%' OR s_name LIKE '%꼬칼콘%';

REFEXP_LIKE

-- REGEXP_LIKE(カラム名, パターン1||パターン2|...)
SELECT s_name, s_maker, s_price FROM snack WHERE REGEXP_LIKE(S_NAME, '^새콤달콤|이$');

パターンの前に^を入れれば、パターン値で始まる文字列を検索
パターンの後に$を入れれば、パターン値で終わる文字列を検索
複数のパターンを使って検索したい場合は|を追加します。

集合演算子

UNION

和集合として、重複したものは除去して出力します。

SELECT 1 ID, '数学' SUBJECT , 90 SCORE FROM DUAL UNION
SELECT 1 ID, '数学' SUBJECT , 90 SCORE FROM DUAL UNION
SELECT 1 ID, '数学' SUBJECT , 90 SCORE FROM DUAL;

ID  SUBJECT　SCORE      
1   数学     90

UNION ALL

和集合として、重複していてもすべて出力

SELECT 1 ID, '数学' SUBJECT , 90 SCORE FROM DUAL UNION ALL
SELECT 1 ID, '数学' SUBJECT , 90 SCORE FROM DUAL UNION ALL
SELECT 1 ID, '数学' SUBJECT , 90 SCORE FROM DUAL;

ID  SUBJECT　SCORE      
1   数学     90
1   数学     90
1   数学     90

INTERSECT

2つのテーブルの集合のうち共通する積集合を出力します。

SELECT 1 ID, '数学' SUBJECT, 90 SCORE FROM DUAL UNION
SELECT 2 ID, '英語' SUBJECT, 80 SCORE FROM DUAL INTERSECT 
-- 和集合で数学、英語の両方が含まれる
SELECT 1 ID, '数学' SUBJECT, 90 SCORE FROM DUAL; 
-- 積集合で共通の値である数学のみ出力

ID  SUBJECT　SCORE      
1   数学     90

MINUS

第1のSELECT文の中で第2のSELECT文にはない差集合(値)を出力します。

SELECT 1 ID, '数学' SUBJECT, 90 SCORE FROM DUAL MINUS
SELECT 1 ID, '数学' SUBJECT, 80 SCORE FROM DUAL MINUS -- ここまでは第1のSELECT文の出力
SELECT 1 ID, '数学' SUBJECT, 90 SCORE FROM DUAL; -- ここは値が同じなので出力されない

なし

今日の授業で一番、理解しにくかったところです。
ID,SUBJECTは同じで、80だけ違うから80は結果に出るのが当たり前じゃないと思うかもしれませんが、ID,SUBJECT,SCOREが全部同じの場合が同じ集合として扱われました。
簡単に言いますと、レコード単位で比較します。

SELECT 1 ID, '数学' SUBJECT, 90 SCORE FROM DUAL MINUS
SELECT 1 ID, '数学' SUBJECT, 80 SCORE FROM DUAL

SCOREが異なりため、二つは全く違う集合として扱われます。　
最初のSELECT 1 ID, '数学' SUBJECT, 90 SCORE FROM DUALと
SELECT 1 ID, '数学' SUBJECT, 80 SCORE FROM DUALは積集合がないため、
自然に差集合もそのままになります。

この結果をSELECT 1 ID, '数学' SUBJECT, 90 SCORE FROM DUALに置換します。

SELECT 1 ID, '数学' SUBJECT, 90 SCORE FROM DUAL MINUS
SELECT 1 ID, '数学' SUBJECT, 80 SCORE FROM DUAL

A-Aみたいな形になってしまいますので、結果はなしになります。

演算子優先順位

OR -> AND -> NOT -> 比較演算子 -> 格好
低　　　　　　　　　　　　　　　　高

snackテーブルから、メーカーがオリオンで価格が1000円以下または2000円以上のスナックの
名前、価格、賞味期限を検索します。

SELECT s_name, s_price, s_exp
FROM snack
WHERE s_maker = 'オリオン' AND (s_price <= 1000 OR s_price >= 2000);

()がないと優先順位の問題でWHERE s_maker = 'オリオン'のみ主力されます。
ANDは＝より優先順位が低いからです。そのため、優先順位が低いところには格好を入れます。

GROUP BY

グループ化して、1つの結果を表示するします。

会社ごとのお菓子の平均価格、最小価格、最大価格
SELECT S_MAKER, AVG(S_PRICE), MIN(S_PRICE), MAX(S_PRICE) FROM SNACK GROUP BY S_MAKER;

s_maker avg     min    max
オリオン　1410  1200   3000
ロッテ    1800  500    3000
ヘテ      1900  1000   2500

ROLLUP

GROUP BY ROLLUP[グループ対象]という形に使われ、カラムに対する小計(SUBTOTAL)を出力します。

会社名、会社ごとのお菓子の合計価格とすべてのお菓子の合計価格
SELECT S_MAKER, SUM(S_PRICE) FROM SNACK GROUP BY ROLLUP(S_MAKER);

s_maker sum(s_price)
オリオン　5000
ロッテ    6000
ヘテ      7000
null      18000

この場合、NULLを処理するNVL2を活用します。

SELECT NVL2(S_MAKER, S_MAKER, '全体') S_MAKER, SUM(S_PRICE) FROM SNACK GROUP BY ROLLUP(S_MAKER);

s_maker sum(s_price)
オリオン　5000
ロッテ    6000
ヘテ      7000
全体      18000

DECODE

IF文のように、条件とTRUE,FALSEの場合の値を決める関数もあります。

DECODE(カラム, 条件, 条件に合う結果値, 条件に合わない結果値)
SELECT DECODE(S_MAKER, NULL, '全体', S_MAKER) S_MAKER, SUM(S_PRICE)
FROM SNACK GROUP BY ROLLUP(S_MAKER);

オリオン　5000
ロッテ    6000
ヘテ      7000
全体      18000

HAVING

GROUP BY句によって生成された結果の中から、条件に合致するデータのみを取得する際に使用します。HAVINGとWHEREの両方が条件を使用して、条件に合致するデータのみを取得する点が共通の特徴です。

文法	特徴
HAVING	グループに対する結果を制限
WHERE	条件を使用して結果を制限

会社ごとのお菓子の平均価格が2000ウォン以上の会社の名前とお菓子の平均価格を取得
SELECT s_maker, AVG(S_PRICE) FROM SNACK GROUP BY(S_MAKER) HAVING AVG(S_PRICE)>=2000;

ORDER BY :

結果を整列します。SELECT文の最後に付ける文法です。

ORDER BY [整列対象 ASC/DESC]

FROM SNACK WHERE S_MAKER IN('ゲテ','ロッテ')
GROUP BY (S_MAKER) 
HAVING AVG(S_PRICE)>=1800
ORDER BY AVG(S_PRICE) DESC;

サブクエリ（SUBQUERY）

SELECT文の中で再びSELECT文を使用する技術で、1つのSQL文内で別のSQL文がネストされたクエリを指します。現在のテーブル内の情報を別のテーブルから取得または加工する際に使用します。（後で学ぶJOINも同じです。）

主クエリ（MAIN QUERY, 外部クエリ）と副クエリ（SUB QUERY, 内部クエリ）で構成されます。

select s_name, s_price
from snack
where s_price < (
select avg(s_price)
from snack
);

平均価格よりも価格が低いお菓子の名前と価格情報を取得する文です。
1つのヒントは、メインクエリとサブクエリを分けて区別することです。
メインクエリのWHERE句でサブクエリの結果と比較する場合は、必ず比較演算子を使用します。

ORA-01427: single-row subquery returns more than one row
比較演算子に=がある場合は、単一の値しか取得できませんので、複数になる場合は、
=ではなくINで処理します。

最高価格はいくらですか？

select s_price from snack
where s_price =(
select max(s_price)
from snack
);

一番高価なお菓子の価格を取得する文

select s_name, s_maker, s_price from snack
where s_price =(
select max(s_price)
from snack
);

一番安価なお菓子を製造する会社はどこですか？

select s_maker from snack
where s_price =(
select min(s_price)
from snack
);

平均価格よりも高価なお菓子は何種類ありますか？

select count(*) from snack
where s_price >(
select avg(s_price)
from snack
);

賞味期限が一番長いお菓子の全情報

select * from snack where s_exp = (
select max(s_exp)
from snack
);

サブクエリ実習

お菓子メーカーテーブルを作成します => メーカー名、住所（都市）、従業員数の属性
お菓子テーブルに合わせてデータを入れてください！

CREATE TABLE maker(
m_name varchar2 (10 char) primary key,
m_address varchar2 (10 char) not null,
m_employee number(3) not null
);

insert into maker values ('オリオン','ソウル',100);
insert into maker values ('ヘテ','広州',50);
insert into maker values ('ソウル','釜山',30);
insert into maker values ('TOHATO','京畿',5);

従業員数が一番少ない会社が製造するお菓子の名前、価格を取得

SELECT s_name お菓子の名前, s_price 価格 FROM snack
WHERE s_maker = (
SELECT m_name FROM maker
WHERE m_employee=
(SELECT MIN(m_employee) FROM maker)
);

一番高価のお菓子を販売している会社のアドレス

SELECT m_address 住所 FROM maker
WHERE m_name IN (
SELECT s_maker FROM snack
WHERE s_price IN
(SELECT MAX(s_price) FROM snack)
);

ソウルにある会社が製造するお菓子の平均価格は？

SELECT AVG(s_price) 平均価格 FROM snack
WHERE s_maker IN (
SELECT m_name FROM maker
WHERE m_address = 'ソウル'
);

平均価格以上のお菓子を製造する会社の名前、位置を取得

SELECT m_name "会社名", m_address "位置" FROM maker
WHERE m_name IN (
SELECT s_maker FROM snack
WHERE s_price >
(SELECT AVG(s_price) FROM snack)
);

サブクエリ実習2

1. ドリンクテーブルとカフェテーブルを作成する

ドリンクテーブル：ドリンク名/価格/販売するカフェの名前
カフェテーブル：カフェ名/地域/席数
それぞれのテーブルでドリンク名とカフェ名は一意の値を持つ
2つのテーブルの関係を考慮する

CREATE TABLE cafe(
c_name varchar2(15 char) primary key,
c_address varchar2(5 char) not null,
c_space number(3) not null
);

CREATE TABLE drink(
d_name varchar2(15 char) primary key,
d_price number(5) not null,
d_c_name varchar2(15 char) not null,
constraint fk_d_c_name foreign key (d_c_name)
references cafe(c_name)
on delete cascade
);

2. 条件に合うテーブルを作成し、値を入れてください

Aカフェはソウルに位置し、席が100席で、(アメリカーノ,2000ウォン) (ラテ,3000ウォン)
(緑茶,2500ウォン)を販売しています

INSERT INTO cafe VALUES ('Aカフェ','ソウル',100);
INSERT INTO drink VALUES ('アメリカーノ',2000,'Aカフェ');
INSERT INTO drink VALUES ('ラテ',3000,'Aカフェ');
INSERT INTO drink VALUES ('緑茶',2500,'Aカフェ');

Bカフェは富川に位置し、席が80席で (紅茶,2500ウォン),(スムージー,3000ウォン)
(エスプレッソ,4000ウォン)を販売しています

INSERT INTO cafe VALUES ('Bカフェ','富川',80);
INSERT INTO drink VALUES ('紅茶',2500,'Bカフェ');
INSERT INTO drink VALUES ('スムージー',3000,'Bカフェ');
INSERT INTO drink VALUES ('エスプレッソ',4000,'Bカフェ');

3. 要求されたクエリ文を作成してください

平均価格よりも高価なドリンクは何種類ありますか？

SELECT COUNT(*) FROM DRINK WHERE D_PRICE>
(SELECT AVG(D_PRICE) FROM DRINK);

一番安いドリンクを販売するカフェの名前は？

SELECT c_name FROM CAFE WHERE c_name=
(SELECT d_c_name FROM DRINK WHERE d_price=
(SELECT MIN(D_PRICE) FROM DRINK)
);

ソウルにあるカフェで販売されるドリンクの平均価格は？

SELECT AVG(D_PRICE) FROM DRINK WHERE d_c_name=
(SELECT c_name FROM CAFE WHERE c_address ='ソウル');

席が90席以上のカフェで販売されるドリンクの名前は？

SELECT d_name FROM DRINK WHERE d_c_name IN
(SELECT c_name FROM CAFE WHERE c_space >= 90);

平均価格以下のドリンクの名前は？

SELECT d_name FROM DRINK WHERE d_price <=
(SELECT AVG(d_price) FROM DRINK);

一番高価なドリンクを販売するカフェはどこにありますか？

SELECT c_address FROM CAFE WHERE c_name =
(SELECT d_c_name FROM DRINK WHERE d_price =
(SELECT MAX(d_price) FROM DRINK)
);

最後に

最近、DB授業に入ってからとても大量の知識を学んでいます。
難易度はそこまで高くなかったので、難しいと思ったことがないですが、復習量を増えることで、自然に内容も長くなっちゃいますね！　😭