２．選択のバリエーション

以下のテーブルをもとに、演習を進める。

[ sale ]

sale_id sale_date product_name product_count product_price product_total customer is_delivery note
101 2007/4/1 りんご 20 140 2800 A商店 false (NULL)
102 2007/4/2 いちご 15 400 6000 B商店 true (NULL)
103 2007/5/4 いちご 2 400 800 A商店 false (NULL)
104 2007/5/12 いちじく 32 600 19200 A商店パリ支店 true 空輸
xinon-dnai.jp 2007/5/20 マンゴー 18 260 4680 C商店 false (NULL)
106 2007/6/7 りんご 3 140 420 A商店 false (NULL)
107 2007/7/1 メロン 12 3500 42000 W商店 true のしをつける
108 2007/7/7 七夕セット 3 1780 5340 C商店 false (NULL)
109 2007/7/10 マンゴー 10 260 2600 B商店 false (NULL)
110 2007/8/1 常夏セット 6 2300 13800 海の家 false (NULL)

２－１

5000〜10000円のデータを確認 − 数値の範囲指定

金額が5,000円以上10,000円未満のデータについて、販売日、商品名、合計金額、販売先を表示する。
（書き方その1）

sale_date product_name product_total customer
2007/4/2 いちご 6000 B商店
2007/7/7 七夕セット 5340 C商店

キーワード：where、and

【サンプルソースコード】

2-1.sql

/* 2-1．5000〜10000円のデータを確認 − 数値の範囲指定 */ /* 　「product_total >= 5000 and < 10000」のような書き方はNG */ /* 　andで区切ったそれぞれが、きちんとした条件式になっている必要がある */ select sale_date, product_name, product_total, customer from sale where product_total >= 5000 and product_total < 10000;

２－２

5000〜10000円のデータを確認2 − 数値の範囲指定2

金額が5,000円以上10,000円未満のデータについて、販売日、商品名、合計金額、販売先を表示する。
（書き方その2）

sale_date product_name product_total customer
2007/4/2 いちご 6000 B商店
2007/7/7 七夕セット 5340 C商店

キーワード：where、between 〜 and

【サンプルソースコード】

2-2.sql

/* 2-2．5000〜10000円のデータを確認2 − 数値の範囲指定2 */ /* 　between 〜 andでは、指定した数値を「含む」ことに注意すること */ select sale_date, product_name, product_total, customer from sale where product_total between 5000 and 9999;

２－３

メロンとマンゴーのデータを確認 − 同じ列での複数条件

「マンゴー」と「メロン」の両方の販売データ（販売日、商品名、合計金額、販売先）を表示する。
（書き方その1）

sale_date product_name product_total customer
2007/5/20 マンゴー 4680 C商店
2007/7/1 メロン 42000 W商店
2007/7/10 マンゴー 2600 B商店

キーワード：where、or

【サンプルソースコード】

2-3.sql

/* 2-3．メロンとマンゴーのデータを確認 − 同じ列での複数条件 */ /* 　「product_name='マンゴー' or 'メロン'」のような書き方はNG */ /* 　orで区切ったそれぞれが、きちんとした条件式になっている必要がある */ select sale_date, product_name, product_total, customer from sale where product_name = 'マンゴー' or product_name = 'メロン';

２－４

B商店で5000円以上のデータを確認 − 違う列での複数条件

販売先が「B商店」で合計金額が「5000円以上」の販売データ（販売日、商品名、合計金額、販売先）を表示する。

sale_date product_name product_total customer
2007/4/2 いちご 6000 B商店

キーワード：where、and

【サンプルソースコード】

2-4.sql

/* 2-4．B商店で5000円以上のデータを確認 − 違う列での複数条件 */ select sale_date, product_name, product_total, customer from sale where customer = 'B商店' and product_total >= 5000;

２－５

配送が必要なデータを確認 − 論理型データの条件

配送が必要な販売データ（販売日、商品名、合計金額、販売先）を表示する。

sale_date product_name product_total customer
2007/4/2 いちご 6000 B商店
2007/5/12 いちじく 19200 A商店パリ支店
2007/7/1 メロン 42000 W商店

キーワード：where

【サンプルソースコード】

2-5.sql

/* 2-5．配送が必要なデータを確認 − 論理型データの条件 */ /* 　論理型データの条件の書き方は、使うDBによって違うこともあるので注意 */ select sale_date, product_name, product_total, customer from sale where is_delivery = true;

２－６

特記事項のあるデータを確認 − nullデータの条件

備考欄に何か書いてある販売データ（販売日、商品名、合計金額、販売先）を表示する。

sale_date product_name product_total customer
2007/5/12 いちじく 19200 A商店パリ支店
2007/7/1 メロン 42000 W商店

キーワード：where、null、not

【サンプルソースコード】

2-6.sql

/* 2-6．特記事項のあるデータを確認 − nullデータの条件 */ /* 　nullは、「何も入っていない『からっぽ』の状態」を表す */ /* 　notは、次に続く条件を否定する */ select sale_date, product_name, product_total, customer, note from sale where not note is null;

２－７

メロンとマンゴーのデータを確認2 − 同じ列での複数条件2

「マンゴー」と「メロン」の両方の販売データ（販売日、商品名、合計金額、販売先）を表示する。
（書き方その2）

sale_date product_name product_total customer
2007/5/20 マンゴー 4680 C商店
2007/7/1 メロン 42000 W商店
2007/7/10 マンゴー 2600 B商店

キーワード：in

【サンプルソースコード】

2-7.sql

/* 2-7．メロンとマンゴーのデータを確認2 − 同じ列での複数条件2 */ /* 　inのカッコの中には、いくつ並べてもいい（カンマでつなげる） */ select sale_date, product_name, product_total, customer from sale where product_name in ('マンゴー', 'メロン');

２－８

B商店以外の販売データを確認 − 否定条件

B商店以外の販売データ（販売日、商品名、合計金額、販売先）を表示する。
（書き方その1）

sale_date product_name product_total customer
2007/4/1 りんご 2800 A商店
2007/5/4 いちご 800 A商店
2007/5/12 いちじく 19200 A商店パリ支店
2007/5/20 マンゴー 4680 C商店
2007/6/7 りんご 420 A商店
2007/7/1 メロン 42000 W商店
2007/7/7 七夕セット 5340 C商店
2007/8/1 常夏セット 13800 海の家

キーワード：where、<>

【サンプルソースコード】

2-8.sql

/* 2-8．B商店以外の販売データを確認 − 否定条件 */ /* 「<>」は、「not equal」を表す演算子 */ select sale_date, product_name, product_total, customer from sale where customer <> 'B商店';

２－９

B商店以外の販売データを確認2 − 否定条件2

B商店以外の販売データ（販売日、商品名、合計金額、販売先）を表示する。
（書き方その2）

sale_date product_name product_total customer
2007/4/1 りんご 2800 A商店
2007/5/4 いちご 800 A商店
2007/5/12 いちじく 19200 A商店パリ支店
2007/5/20 マンゴー 4680 C商店
2007/6/7 りんご 420 A商店
2007/7/1 メロン 42000 W商店
2007/7/7 七夕セット 5340 C商店
2007/8/1 常夏セット 13800 海の家

キーワード：where、not

【サンプルソースコード】

2-9.sql

/* 2-9．B商店以外の販売データを確認2 − 否定条件2 */ /* 　notは、次に続く条件を否定する */ select sale_date, product_name, product_total, customer from sale where not (customer = 'B商店');

２－１０

商品リストの並べ替え

重複しない商品リスト（商品名、単価）を、単価が安いもの順に表示する。

product_name product_price
りんご 140
マンゴー 260
いちご 400
いちじく 600
七夕セット 1780
常夏セット 2300
メロン 3500

キーワード：distinct、order by

【サンプルソースコード】

2-10.sql

/* 2-10．商品リストの並べ替え */ /* 　重複しない商品リスト（商品名、単価）を、単価が安いもの順に表示する */ select distinct product_name, product_price from sale order by product_price;

２－１１

単価と個数で合計計算 − 計算式の利用

［合計金額］列を使わずに、［単価］列と［個数］列を使って合計を計算し、販売データ（商品名、個数、単価、計算で求められる合計金額）を表示する。
計算から求める合計金額は、仮に［total］という列名として表示する。

product_name product_count product_price total
りんご 20 140 2800
いちご 15 400 6000
いちご 2 400 800
いちじく 32 600 19200
マンゴー 18 260 4680
りんご 3 140 420
メロン 12 3500 42000
七夕セット 3 1780 5340
マンゴー 10 260 2600
常夏セット 6 2300 13800

キーワード：演算式

【サンプルソースコード】

2-11.sql

/* 2-11．単価と個数で合計計算 − 計算式の利用 */ /* 　［合計金額］列を使わずに、［単価］列と［個数］列を使って合計を計算する */ /* 　計算から求める列に、別名を付ける */ select product_name, product_count, product_price, product_count*product_price as total from sale;

２－１２

5月分のデータを確認 − 日付の範囲指定

5月分の販売データ（販売日、商品名、合計金額、販売先）を表示する。
（書き方その1）

sale_date product_name product_total customer
2007/5/4 いちご 800 A商店
2007/5/12 いちじく 19200 A商店パリ支店
2007/5/20 マンゴー 4680 C商店

キーワード：where、and

【サンプルソースコード】

2-12.sql

/* 2-12．5月分のデータを確認 − 日付の範囲指定 */ /* 　年月日すべてを指定しないといけないので、「○月○日〜○月○日」とする */ select sale_date, product_name, product_total, customer from sale where sale_date >= '2007/5/1' and sale_date <= '2007/5/31';

課題

スポーツクラブの会員データがある。
以下のテーブルをもとに、仕様を満たすようなSQL文を作成する。

[ member ]

member_id regist_date member_name birthday member_gender class member_fee area lesson
M101 2005/2/10 秋田きりん 1978/5/4 男性 マスター 10000 上野 スカッシュ
N511 2006/8/15 長野となかい 1980/10/9 男性 ナイト 8000 上野 (NULL)
M340 2006/8/15 山口ぱんだ 1976/4/28 女性 マスター 10000 上野 ジャズダンス
H028 2006/10/10 長崎らっこ 1979/7/16 男性 ホリデー 7200 品川 スイミング
M502 2007/1/20 石川うさぎ 1976/5/21 女性 マスター 10000 千葉 (NULL)
N113 2007/1/31 岡山ふくろう 1965/12/30 男性 ナイト 8000 上野 (NULL)
D327 2007/3/6 富山ひよこ 1986/4/23 女性 デイ 6500 千葉 (NULL)
N280 2007/4/1 香川ひらめ 1972/6/24 男性 ナイト 8000 品川 (NULL)
H335 2007/4/18 宮崎まんぼう 1968/3/12 男性 ホリデー 7200 品川 (NULL)
M211 2007/4/28 福岡かまきり 1980/10/8 男性 マスター 10000 千葉 ジャズダンス

　

課題2-1．上野店で、スカッシュのレッスンを受けている人を表示する。

※member_id, member_name, area, lesson を表示すること

[結果表示]

member_id member_name area lesson
M101 秋田きりん 上野 スカッシュ

課題2-2．2007年以降に入会した男性を表示する。

※member_id, regist_date, member_name, member_genderを表示すること

[結果表示]

member_id regist_date member_name member_gender
H335 2007-04-18 宮崎まんぼう 男性
M211 2007-04-28 福岡かまきり 男性
N113 2007-01-31 岡山ふくろう 男性
N280 2007-04-01 香川ひらめ 男性

課題2-3． 2006年に入会した人を表示する。

※member_id, regist_date, member_name を表示すること

[結果表示]

member_id regist_date member_name
H028 2006-10-10 長崎らっこ
M340 2006-08-15 山口ぱんだ
N511 2006-08-15 長野となかい

課題2-4． 女性で、何かレッスンを受けている人を表示する。

※member_id, member_name, member_gender, lesson を表示すること

[結果表示]

member_id member_name member_gender lesson
M340 山口ぱんだ 女性 ジャズダンス

課題2-5． 平日昼間に来られる人を表示する。

※「ナイト会員」は、夜帯しか利用できない会員種別。「ホリデー会員」は、休日しか利用できない会員種別。
※「ナイト会員」以外・ホリデー会員以外、という形で条件式を作成してください
※member_id, member_name, class を表示すること

[結果表示]

member_id member_name class
D327 富山ひよこ デイ
M101 秋田きりん マスター
M211 福岡かまきり マスター
M340 山口ぱんだ マスター
M502 石川うさぎ マスター

課題2-6． 男性の人数を数える。

※男性の人数だけ表示できればよい

[結果表示]

男性人数
7

課題2-7． 女性の人数を数える。

※女性の人数だけ表示できればよい

[結果表示]

女性人数
3

課題2-8. マスター会員を、年齢の高い順に表示する。

※member_id, member_name, birthday, class を表示すること

[結果表示]

member_id member_name birthday class
M340 山口ぱんだ 1976-04-28 マスター
M502 石川うさぎ 1976-05-21 マスター
M101 秋田きりん 1978-05-04 マスター
M211 福岡かまきり 1980-10-08 マスター

Copyright © Xincor miXell Co., Ltd. All rights reserved

results matching ""

    No results matching ""