Zenn
🚀

ITスクール DAY31 Oracle JOIN

2023/08/23に公開
初心者
Oracle
tech

はじめに

今日の午前は、サブQuery、SELECT復習を行いました。
以前、勉強したものの復習でしたが、記事を作成しながら、復習していましたので、簡単でした。以前の記事にある文法の繰り返しなので、そちらに対しては省略したいと思います。

SELECT [distinct] [c_name],[c_name as othername], [Operator],[aggregate function(集計関数],
from [table_name]
where [conditional statement]
group by [grouping target]
having [function-inclusive condition(関数包含条件)]
order by [ASC/DESC (昇順 / 降順)] --defaultはASC(Ascend)

こちらの仕組みを覚えておくことと、WHERE,FROMみたいな句は一つ以上入れないことだけ注意しましょう。

また、句によって、ANDで対応できる句と,で対応できるものもありますので、両方試した方がいいと思います。

ORDER BY m_price AND m_name DESC;(X)
ORDER BY m_price　, m_name DESC; (O)

JOIN

JOINするテーブル作成

複数のテーブルを一時的に合わせることです。

まず、FRANCHISE、CEO、MENUというテーブルを作成し、
FRANCHISEテーブルはf_ceoという外来キーを持ち、
CEOテーブルのc_noというプライマリーキーと繋がります。
MENUテーブルは、m_f_noという外来キーを持ち、
FRANCHISEのプライマリーキーf_no0と繋がります。

CEOとFRANCHISEは1：N関係、
FRANCHISEとMENUも1：N関係になります。
　　 　
上下関係はMENU　➡　FRANCHISE　➡　CEOみたいな関係性だと思います。

FRANCHISE,CEO,MENU TABLES
CREATE TABLE FRANCHISE(
    f_no   NUMBER(4) PRIMARY KEY,
    f_name VARCHAR2(15 CHAR) NOT NULL,
    f_location VARCHAR2(10 CHAR) NOT NULL,
    f_ceo NUMBER(3) NOT NULL,   --FORIGN KEY
    f_seat NUMBER(3) NOT NULL
);

CREATE SEQUENCE FRANCHISE_SEQ;

INSERT INTO FRANCHISE VALUES(FRANCHISE_SEQ.NEXTVAL,'홍콩반점','서초',1,100);
INSERT INTO FRANCHISE VALUES(FRANCHISE_SEQ.NEXTVAL,'홍콩반점','종로',2,80);
INSERT INTO FRANCHISE VALUES(FRANCHISE_SEQ.NEXTVAL,'한신포차','강남',3,150);
INSERT INTO FRANCHISE VALUES(FRANCHISE_SEQ.NEXTVAL,'구로포차','구로',4,130);


CREATE TABLE CEO(
    c_no NUMBER(3) PRIMARY KEY,
    c_name VARCHAR2(15 CHAR) NOT NULL,
    c_birthday DATE NOT NULL,
    c_gender CHAR(2 CHAR) NOT NULL
    CONSTRAINT OwnerInfo_c_gender CHECK(c_gender IN('남자','여자'))
);

CREATE SEQUENCE CEO_SEQ;


INSERT INTO CEO VALUES(CEO_SEQ.NEXTVAL,'홍길동',
TO_DATE('1990-03-01','YYYY-MM-DD'),'남자');
INSERT INTO CEO VALUES(CEO_SEQ.NEXTVAL,'김길동',
TO_DATE('1992-02-01','YYYY-MM-DD'),'여자');
INSERT INTO CEO VALUES(CEO_SEQ.NEXTVAL,'홍길동',
TO_DATE('1991-12-12','YYYY-MM-DD'),'여자');
INSERT INTO CEO VALUES(CEO_SEQ.NEXTVAL,'최길동',
TO_DATE('1989-07-14','YYYY-MM-DD'),'남자');

SELECT * FROM CEO;
   
CREATE TABLE MENU(     
     m_no NUMBER(3) PRIMARY KEY, --메뉴 번호
     m_name VARCHAR(20 CHAR) NOT NULL,
     m_price NUMBER(5) NOT NULL,
     m_f_no NUMBER(3) NOT NULL  --메뉴 파는 식당 번호
 );
 
CREATE SEQUENCE MENU_SEQ;   
    
-- 1 :홍콩반점 서초 
-- 2 :홍콩반점 종로 
-- 3 : 한신포차 강남 
-- 4 :구로포차 구로

INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'짜장면',5000,1);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'불닭발',12000,3);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'짬뽕',6000,1);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'볶음밥',7000,1);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'군만두',6000,1);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'쟁반짜장',10000,1);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'찹쌀탕수육',12000,1);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'짜장면',5000,2);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'짬뽕',6000,2);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'짬뽕밥',7000,2);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'고추짜장',6000,2);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'칭따오맥주',4500,2);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'사이다',2000,2);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'계란찜',5000,3);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'닭똥집',15000,3);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'닭발',10000,3);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'조개탕',12000,3);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'오뎅탕',13000,3);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'계란말이',8000,4);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'치즈계란말이',11000,4);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'계란찜',4000,4);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'홍합탕',11000,4);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'감자전',10000,4);
INSERT INTO MENU VALUES(MENU_SEQ.NEXTVAL,'골뱅이무침',8000,4);

JOIN

FRANCHISEとCEOを合わせたテーブルを一時的に作成するため、JOINを活用します。
今は、CEOとFRANCHISEは1：1関係なので、二つの合わせたテーブルは4つのレコードになるはずです。

SELECT * FROM franchise,ceo;

1	홍콩반점	서초	1	100	1	홍길동	1990-03-01 00:00:00	남자
1	홍콩반점	서초	1	100	2	김길동	1992-02-01 00:00:00	여자
1	홍콩반점	서초	1	100	3	홍길동	1991-12-12 00:00:00	여자
1	홍콩반점	서초	1	100	4	최길동	1989-07-14 00:00:00	남자
2	홍콩반점	종로	2	80	1	홍길동	1990-03-01 00:00:00	남자
2	홍콩반점	종로	2	80	2	김길동	1992-02-01 00:00:00	여자
2	홍콩반점	종로	2	80	3	홍길동	1991-12-12 00:00:00	여자
2	홍콩반점	종로	2	80	4	최길동	1989-07-14 00:00:00	남자

本来なら、店の数は4つで、オナーもそれぞれなので、4つのレコードが必要ですが、データーがぐちゃぐちゃにになりました。
JOINの最後のプロセとして、WHEREを通して、キーとキーの関係で範囲を縮むことが必須です。

SELECT * FROM franchise, ceo where franchise.f_ceo = c_no;

1	홍콩반점	서초	1	100	1	홍길동	1990-03-01 00:00:00	남자
2	홍콩반점	종로	2	80	2	김길동	1992-02-01 00:00:00	여자
3	한신포차	강남	3	150	3	홍길동	1991-12-12 00:00:00	여자
4	구로포차	구로	4	130	4	최길동	1989-07-14 00:00:00	남자

実習

1. 全てのレストラン名、支店名、オーナー名、オーナーの誕生日を取得

SELECT f_name, f_location,c_name,c_birthday 
FROM franchise,ceo 
WHERE franchise.f_ceo=ceo.c_no;

2. 全てのメニュー名、価格、レストラン名、支店名を取得

SELECT m_name, m_price, f_name, f_location 
FROM franchise,menu
WHERE franchise.f_ceo= menu.m_f_no;

3. 座席数が50席以上のレストランのメニュー名、価格、レストラン名、支店名、座席数を取得

SELECT m_name, m_price, f_name, f_location 
FROM franchise,menu
WHERE franchise.f_ceo = menu.m_f_no 
AND franchise.f_seat>=50;

4. 座席数が最も多いレストランのメニュー名、価格、レストラン名、支店名、座席数を取得

SELECT m_name, m_price, f_name, f_location 
FROM franchise,menu
WHERE franchise.f_ceo = menu.m_f_no 
AND franchise.f_seat =
(SELECT MAX(franchise.f_seat) FROM franchise);

5. メニュー名、価格、レストラン名、支店名、オーナー名、誕生日の全情報を取得

SELECT m_name,m_price, f_name, f_location, c_name, c_birthday
FROM menu,franchise,ceo
WHERE franchise.f_ceo = menu.m_f_no 
AND menu.m_f_no = ceo.c_no;

6.最年長のオーナーの店のメニュー名、価格、レストラン名、支店名、オーナー名、誕生日を取得 -> メニュー名をアルファベット順、レストラン名をアルファベット順でソート

SELECT m_name, m_price, f_name, f_location, c_name, c_birthday
FROM menu, franchise, ceo
WHERE franchise.f_ceo = menu.m_f_no 
AND menu.m_f_no = ceo.c_no
AND ceo.c_birthday = 
(SELECT MAX(c_birthday) FROM ceo)
ORDER BY m_name, f_name;

ページング処理

データの数をページごとに分割して表示したいというニーズがあります。
どのように実現すれば良いでしょうか？

MENUテーブルにはM_NOというプライマリーキーが存在します。
これはSEQUNCEで処理されており、INSERTに失敗しても連番は増加されるリスクがあります。
例えば、一つのレコードを削除した場合は、連番が欠けることも考えられますね。
M_NOが厳密に1、2、3、4と続かない場合、どのような方法が考えられるでしょうか？

その場合は、ROWNUMという仮想フィードを利用し、データーを分割します。
このような処理過程をページング処理と呼びます。

ROWNUMの特徴

1.　SELECTするたびに整数の数字（１から）が自動的に割り当てられます。
2.　"*"とは同時に使用できません。
3.　ORDER BYより前に割り当てられます。
4.　必ず1から順に検索する必要があります。

実習

メニュー名をアルファベット順に並べ替えてMENUテーブルのすべての情報を表示

SELECT ROWNUM, M_NO, M_NAME, M_PRICE, M_F_NO FROM MENU ORDER BY m_name;

メニュー名をアルファベット順に並べ替えてMENUテーブルのすべての情報を表示

SELECT ROWNUM, M_NO, M_NAME, M_PRICE
  FROM(                        -- FROM内にサブクエリを挿入する場合
  SELECT M_NO, M_NAME, M_PRICE -- インラインビューと呼ばれます（インラインビュー）
  FROM MENU ORDER BY m_name    -- クエリ内でテーブルのように使用
                                -- クエリ終了後に消える！
  );

メニュー名をアルファベット順に並べ替えて、ROWNUMが2〜4のMENUテーブルのすべての情報を表示

SELECT * --ROWNUM,RN,M_NO,M_NAME,M_PRICE
FROM
(
SELECT ROWNUM RN, m_no, m_name, m_price, m_f_no
 FROM(
 SELECT m_no, m_name, m_price, m_f_no 
 FROM MENU ORDER BY m_name
 ) 
 ) 
 WHERE RN BETWEEN 2 AND 4;

複合問題１

〜길동の名前を持つオーナーの店
メニュー名、価格、レストラン名、支店名、オーナー名
価格を降順に => メニュー名をアルファベット順に
3〜8番のみ取得

SELECT ROWNUM, RN, m_name, m_price, f_name, f_location, c_name
FROM
(
   SELECT ROWNUM RN, m_name, m_price, f_name, f_location, c_name
   FROM(
        SELECT m_name, m_price, f_name, f_location, c_name
        FROM menu, franchise, ceo
        WHERE m_f_no = f_ceo 
        AND f_ceo = c_no
        AND c_name IN
        (SELECT c_name FROM ceo WHERE c_name LIKE '%길동')
        ORDER BY m_price DESC, m_name
       )
) 
WHERE RN BETWEEN 3 AND 8;

複合問題2

'김'で始まるオーナーの店
メニュー名、価格、オーナー名、性別
価格を昇順にソート
2〜3番のみ取得

SELECT ROWNUM, RN, m_name, m_price, c_name, c_gender
   FROM
   ( 
   SELECT ROWNUM RN, m_name, m_price, c_name, c_gender
     FROM( 
        SELECT m_name, m_price, c_name, c_gender
        FROM menu, ceo
        WHERE m_f_no = c_no
        AND c_name LIKE '김%'
        ORDER BY m_price
         )
    )
    WHERE RN BETWEEN 2 AND 3;

Discussion