2月5日(金)2コマ目

今日、やったこと

シーケンス確認演習

シーケンスの確認演習の解答例


テーブル作成

ジャンルマスタ

create table ジャンルマスタ(

ID number(2),

ジャンル名 nvarchar2(64) not null,

constraint pk_ジャンル primary key(ID)

);

商品マスタ

create table 商品マスタ(

ID number(3),

商品名 nvarchar2(64) not null,

ジャンルID number(2),

定価 number(5),

constraint pk_商品 primary key(ID),

constraint fk_商品_ジャンル foreign key(ジャンルID) references ジャンルマスタ(ID),

constraint chk_商品定価 check(定価>=0)

);

顧客マスタ

create table 顧客マスタ(

ID number(4),

氏名 nvarchar2(64) not null,

constraint pk_顧客 primary key(ID)

);

売上データ

create table 売上データ(

ID number(5),

顧客ID number(4),

売上日 date,

constraint pk_売上データ primary key(ID),

constraint fk_売上_顧客 foreign key(顧客ID) references 顧客マスタ(ID)

);

売上詳細データ

create table 売上詳細データ(

売上ID number(5),

商品ID number(3),

数量 number(4),

constraint pk_売上詳細データ primary key(売上ID, 商品ID),

constraint fk_売上詳細_ID foreign key(売上ID) references 売上データ(ID),

constraint fk_売上詳細_商品 foreign key(商品ID) references 商品マスタ(ID),

constraint chk_売上数量 check(数量 >= 0)

);


シーケンス作成

SEQ_グループID

create sequence SEQ_グループID 

  start with 1

  increment by 1

  minvalue 1

  maxvalue 99

  nocycle;

または

create sequence SEQ_グループID 

  maxvalue 99;

SEQ_商品ID

create sequence SEQ_商品ID 

  start with 1

  increment by 1

  minvalue 1

  maxvalue 999

  nocycle;

または

create sequence SEQ_商品ID 

  maxvalue 999;

SEQ_顧客ID

create sequence SEQ_顧客ID 

  start with 1

  increment by 1

  minvalue 1

  maxvalue 9999

  nocycle;

または

create sequence SEQ_顧客ID 

  maxvalue 9999;

SEQ_売上ID

create sequence SEQ_売上ID 

  start with 1

  increment by 1

  minvalue 1

  maxvalue 9999

  nocycle;

または

create sequence SEQ_売上ID 

  maxvalue 9999;


データ登録

ジャンルマスタ、商品マスタ

--- ジャンル名：フィナンシェ

insert into ジャンルマスタ(ID, ジャンル名) values(SEQ_グループID.nextval, 'フィナンシェ');

--- 商品名：フィナンシェ

insert into 商品マスタ(ID, 商品名, ジャンルID, 定価) 

  values(SEQ_商品ID.nextval, 'フィナンシェ', SEQ_グループID.currval, 140);

--- 商品名：ショコラフィナンシェ

insert into 商品マスタ(ID, 商品名, ジャンルID, 定価) 

  values(SEQ_商品ID.nextval, 'ショコラフィナンシェ', SEQ_グループID.currval, 170);


--- ジャンル名：ケーキ

insert into ジャンルマスタ(ID, ジャンル名) values(SEQ_グループID.nextval, 'ケーキ');

--- 商品名：キャラメルフロマージュ

insert into 商品マスタ(ID, 商品名, ジャンルID, 定価) 

  values(SEQ_商品ID.nextval, 'キャラメルフロマージュ', SEQ_グループID.currval, 600);

--- 商品名：ザッハトルテ

insert into 商品マスタ(ID, 商品名, ジャンルID, 定価) 

  values(SEQ_商品ID.nextval, 'ザッハトルテ', SEQ_グループID.currval, 1200);

--- 商品名：モンブラン

insert into 商品マスタ(ID, 商品名, ジャンルID, 定価) 

  values(SEQ_商品ID.nextval, 'モンブラン', SEQ_グループID.currval, 600);


--- ジャンル名：クッキー

insert into ジャンルマスタ(ID, ジャンル名) values(SEQ_グループID.nextval, 'クッキー');

--- 商品名：サブレ3枚入り

insert into 商品マスタ(ID, 商品名, ジャンルID, 定価) 

  values(SEQ_商品ID.nextval, 'サブレ3枚入', SEQ_グループID.currval, 260);

--- 商品名：ビスキュイ5枚入り

insert into 商品マスタ(ID, 商品名, ジャンルID, 定価) 

  values(SEQ_商品ID.nextval, 'ビスキュイ5枚入', SEQ_グループID.currval, 550);

顧客マスタ

insert into 顧客マスタ(ID, 氏名) values(SEQ_顧客ID.nextval, '太郎');

insert into 顧客マスタ(ID, 氏名) values(SEQ_顧客ID.nextval, '次郎');

売上データ、売上詳細データ

--- １件目

--- 売上データ

insert into 売上データ(ID, 顧客ID, 売上日) 

  values(SEQ_売上ID.nextval, (select ID FROM 顧客マスタ WHERE 氏名='太郎'), '2021-01-20');

--- 売上詳細データ

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='フィナンシェ'), 1);

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='ショコラフィナンシェ'), 2);

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='ビスキュイ5枚入'), 5);


--- ２件目

--- 売上データ

insert into 売上データ(ID, 顧客ID, 売上日) 

  values(SEQ_売上ID.nextval, (select ID FROM 顧客マスタ WHERE 氏名='次郎'), '2021-01-20');

--- 売上詳細データ

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='ショコラフィナンシェ'), 4);

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='キャラメルフロマージュ'), 4);

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='ザッハトルテ'), 1);

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='サブレ3枚入'), 2);


--- ３件目

--- 売上データ

insert into 売上データ(ID, 顧客ID, 売上日) 

  values(SEQ_売上ID.nextval, (select ID FROM 顧客マスタ WHERE 氏名='太郎'), '2021-01-21');

--- 売上詳細データ

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='ザッハトルテ'), 2);

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='サブレ3枚入'), 3);


--- ４件目

--- 売上データ

insert into 売上データ(ID, 顧客ID, 売上日) 

  values(SEQ_売上ID.nextval, (select ID FROM 顧客マスタ WHERE 氏名='次郎'), '2021-01-21');

--- 売上詳細データ

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='フィナンシェ'), 3);

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='ショコラフィナンシェ'), 2);

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='モンブラン'), 2);


--- ５件目

--- 売上データ

insert into 売上データ(ID, 顧客ID, 売上日) 

  values(SEQ_売上ID.nextval, (select ID FROM 顧客マスタ WHERE 氏名='次郎'), '2021-01-21');

--- 売上詳細データ

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='フィナンシェ'), 6);

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='ザッハトルテ'), 1);

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='モンブラン'), 3);


--- ６件目

--- 売上データ

insert into 売上データ(ID, 顧客ID, 売上日) 

  values(SEQ_売上ID.nextval, (select ID FROM 顧客マスタ WHERE 氏名='太郎'), '2021-01-22');

--- 売上詳細データ

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='ショコラフィナンシェ'), 5);

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='キャラメルフロマージュ'), 2);

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='サブレ3枚入'), 2);


--- ７件目

--- 売上データ

insert into 売上データ(ID, 顧客ID, 売上日) 

  values(SEQ_売上ID.nextval, (select ID FROM 顧客マスタ WHERE 氏名='太郎'), '2021-01-23');

--- 売上詳細データ

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='ショコラフィナンシェ'), 3,);

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='サブレ3枚入'), 1);

insert into 売上詳細データ(売上ID, 商品ID, 数量) 

  values(SEQ_売上ID.currval, (select ID FROM 商品マスタ WHERE 商品名='ビスキュイ5枚入'), 1);


ビュー作成

ジャンル別売上金額

create or replace view ジャンル別売上金額(ジャンル名, 売上金額) as 

select ジャンル名, sum(定価*数量) 

from ジャンルマスタ, 商品マスタ, 売上詳細データ

where ジャンルマスタ.ID=商品マスタ.ジャンルID AND 商品マスタ.ID=売上詳細データ.商品ID

group by ジャンル名;

売上一覧

create or replace view 売上一覧(顧客名, 売上日, 売上金額, 商品数) as

select 氏名, 売上日, sum(定価*数量), sum(数量)

from 商品マスタ, 顧客マスタ, 売上データ, 売上詳細データ

where 売上データ.ID=売上詳細データ.売上ID and 商品マスタ.ID=売上詳細データ.商品ID and 

    顧客マスタ.ID=売上データ.顧客ID

group by 売上データ.ID, 氏名, 売上日

order by 売上日, sum(定価*数量);

最高売上数商品データ

create or replace view 最高売上数商品データ(商品名, ジャンル名, 売上数量) as

select 商品名, ジャンル名, sum(数量) AS 売上数量

from ジャンルマスタ, 商品マスタ, 売上データ, 売上詳細データ

where ジャンルマスタ.ID=商品マスタ.ジャンルID AND 売上データ.ID=売上詳細データ.売上ID AND 

    商品マスタ.ID=売上詳細データ.商品ID

group by 商品名, ジャンル名

having sum(数量) = 

(select max(売上数量) 

from (

  select sum(数量) AS 売上数量

  from 売上詳細データ

  group by 商品ID

  )

);

最高売上金額商品データ

create or replace view 最高売上金額商品データ(商品名, ジャンル名, 売上金額) as

select 商品名, ジャンル名, sum(定価*数量) AS 売上金額

from ジャンルマスタ, 商品マスタ, 売上データ, 売上詳細データ

where ジャンルマスタ.ID=商品マスタ.ジャンルID AND 売上データ.ID=売上詳細データ.売上ID AND

    商品マスタ.ID=売上詳細データ.商品ID

group by 商品名, ジャンル名

having sum(定価*数量) = 

(select max(売上金額) 

from (

  select sum(定価*数量) AS 売上金額

  from 商品マスタ, 売上詳細データ

  where 商品マスタ.ID=売上詳細データ.商品ID

  group by 商品名

  )

);

最高売上金額データ

create or replace view 最高売上金額データ(商品名, ジャンル名, 数量) as

select 商品名, ジャンル名, 数量

from ジャンルマスタ, 商品マスタ, 売上データ, 売上詳細データ

where ジャンルマスタ.ID=商品マスタ.ジャンルID AND 売上データ.ID=売上詳細データ.売上ID 

    AND 商品マスタ.ID=売上詳細データ.商品ID AND 

売上データ.ID = (

  select 売上詳細データ.売上ID 

  from 商品マスタ, 売上詳細データ

  where 商品マスタ.ID=売上詳細データ.商品ID

  group by 売上詳細データ.売上ID

  having sum(定価*数量) = 

  (select max(売上金額) 

    from (

      select 売上詳細データ.売上ID, sum(定価*数量) AS 売上金額

      from 商品マスタ, 売上詳細データ

      where 商品マスタ.ID=売上詳細データ.商品ID

      group by 売上詳細データ.売上ID

    )

  )

);


コメント

新しいコメントは書き込めません。

このブログの人気の投稿

1月15日(金)2コマ目

今日、やったこと ビューのおさらい課題　その１、その２ 今日の課題 解答例をあげておきます。 ビューのおさらい課題　その１ テーブル作成 商品マスタ create table 商品マスタ( ID number(2), 商品名  nvarchar2(64) not null, 定価 number(5), constraint pk_商品 primary key(ID), constraint chk_商品定価 check(定価>=0) ); 顧客マスタ create table 顧客マスタ( ID number(2), 氏名 nvarchar2(64) not null, constraint pk_顧客 primary key(ID) ); 売上データ create table 売上データ( ID number(4), 商品ID number(2), 顧客ID number(2), 数量 number(4), 売上日 date, constraint pk_売上データ primary key(ID, 商品ID), constraint fk_売上_商品 foreign key(商品ID) references 商品マスタ(ID), constraint fk_売上_顧客 foreign key(顧客ID) references 顧客マスタ(ID), constraint chk_売上数量 check(数量 >= 0) ); ビュー作成 商品別販売回数 create or replace view 商品別販売回数(商品名, 販売回数) as  select 商品名, count(*)  from 商品マスタ, 売上データ where 商品マスタ.ID=売上データ.商品ID group by 商品名; 顧客別商品購入回数 create or replace view 顧客別商品購入回数(顧客名, 商品名, 購入回数) as  select 氏名, 商品名, count(*) from 商品マスタ, 顧客マスタ, 売上データ where 商品マスタ.ID=売上データ.商品ID and 顧客マスタ.ID=売上データ.顧客ID group by 氏名, 商品名 order by 氏名, 商品名; 商品別販売...
続きを読む

11月27日(金)2コマ目

イメージ
今日、やったこと オラクルのデータディクショナリ SQLのDDLの基本パターン ビュー 今日のホワイトボード オラクルのデータディクショナリ オラクルはどんなテーブルがある、とかどんなビューがあるといった情報を管理するためのテーブル（のようなモノ）を持っています。これをデータディクショナリと呼びます。 データディクショナリは結構たくさんありますが、名前にパターンがあります。 図　オラクルのデータディクショナリ このパターンをおさえておけば、とっさのときに対応しやすいです。 SQLのDDLの基本パターン 今まで扱ったSQLは以下の2種類に分類できます。 DML Data Manipulation Languageの略。 データ操作を行うSELECT、INSERT、UPDATE、DELETEです。 DDL Data Definition Languageの略。 テーブルやビューなどデータベースオブジェクトの生成、変更、削除を行うCREATE、ALTER、DROPです。 DDLも基本パターンがあります。 図　SQLのDDL基本パターン ビュー ビューの作成、変更 ビューの変更を行うALTER VIEWはありません。 かわりにCREATE OR REPLACE VIEWで既存ビューの置き換えができます。 図　CREATE VIEWとCREATE OR REPLACE VIEW ビューの列に別名を設定 ビューの列名は無指定時はテーブルの列名がそのまま引き継がれます。 別名をつけたい場合は下図のようにビュー名のあとに（）で指定します。 図　ビューの列に別名をつける ビュー作成 先週作った商品一覧ビューを変更しました。 〇商品一覧ビューに「税込み価格」列を追加 create or replace view 商品一覧 as select 商品名, ジャンル名, 価格...
続きを読む

1月29日(金)2コマ目

イメージ
今日、やったこと シーケンス 今日のホワイトボード シーケンスとは ID列のように重複してはいけない列の値を生成する仕組み。 図　シーケンス シーケンスから採番する シーケンス名.nextval で採番できる。 直近に採番した値は シーケンス名.currval で取得できる。 シーケンスを作る 図　シーケンスを作る SQLは以下の構文。 create sequence シーケンス名 start with 初期値 increment by 増分 maxvalue 最大値 minvalue 最小値 cycle または nocylce cache または nocache ; 各オプションは以下のとおり。 〇start with 初期値 一番最初に採番される値。 〇increment by 増分 採番するたびに増える値。 負の値にすると、デクリメントするシーケンスになる。 たとえば、start with 1 increment by -1にすると、 1 => 0 => -1 => -2　と採番される。 〇maxvalue 最大値 採番される最大値。最大値は初期値以上、かつ最小値を超える値。 未指定時は最大値は昇順の場合、10の28乗-1、降順の場合は-1。 〇minvalue 最小値 採番される最小値。最小値は初期値以下、かつ最大値未満の値。 未指定時は最大値は昇順の場合、1、降順の場合は-(10の27乗 - 1)。 〇cycle 採番値が最小値と最大値で循環する。 シーケンス採番値が昇順の場合、最大値に達すると次は最小値が採番される。 降順の場合は、最小値に達すると、次は最大値が採番される。 〇nocycle デフォルトはこちら。 循環しない。 〇cache 高速に採番するためにあらかじめメモリ上に採番しておく数を指定。 〇nocache メモリ上に事前採番をしない。デフォルトはこちら。 作成済みシーケンスの変更 変更できるのは、増分(increment by)、最大値(maxvalue)、最小値(minvalue)、キャッシュ(cache)。 SQLは以下のとおり。 alter sequence シーケンス名 　変更対象項目名　設定値 ; 作成済みシーケンスの削除 SQLは以下のとおり。 drop sequence シーケンス名;  
続きを読む