Web教材一覧データベース

SQL:SELECT文の演習

学習のポイント

Accessを用いて,SQLのSELECT文を理解することを目的にします。

キーワード

Access,SQL,SELECT


SELECT文の一般形式

SELECT フィールド名1,フィールド名2,・・・ 何を(射影)?
      または       (全フィールドを指定)
FROM  テーブル名1,テーブル名2,・・・   どこから?
WHERE 条件1 AND 条件2 ・・・・    どのような条件で?
                               (選択,結合)
GROUP BY フィールド名1,フィールド名2,・・・集計
HAVING 条件1 AND 条件2・・・       集計結果による条件
ORDER BY フィールド名1,フィールド名2,・・・[DESC]ソート

記述上の注意
 1 文の最後には;をつける。
 2 順序はこの順にする。SELECTとFROM以外は省略できる。
 3 予約語,空白,「,」「;」は必ず半角のこと。空白はいくつ並んでもよい。
 4 改行は任意。全体を1行に書いてもよい。

選択条件
 =,>,<,>=,<=,<>(≠の意味),LIKE "*文字列*"
 BETWEEN 〇〇 AND ××    (〇〇と××の間)
 IN (〇〇,××,△△)        (〇〇または××または△△)
 AND,OR

結合の一般形式
 テーブル1.キーフィールド名=テーブル2.キーフィールド名

1 一つの表からの検索

例題1-1(射影)

売上表から,得意先コードと商品コードのフィールドだけを取り出した表を作りなさい。

    得意先コード 商品コード
        500         101
        500         205
        500         326
        600         213
         :           :
         :           :
        700         213
        500         205

一つの表(テーブル)から,指定した行(フィールド)だけによる新しい表を作る操作を射影といいます。射影はSELECTに続くフィールド名を指定すればよいのです。
 入力テーブルは売上表→[FROM 売上表],得意先コードと商品コードのフィールドだけを取り出す→[SELECT 得意先コード,商品コード]なので,SQLは次のようになります。

これをAccessの「売上」データベースでのSQLクエリにコピーして,上の結果が得られることを確認しなさい。以下の例題も同様です。

例題1-2(選択)

売上表から,得意先コードが500のレコードだけを取り出した表を作りなさい。

    月日 得意先コード 商品コード 数量
    1001     500         101      10
    1001     500         205      20
    1001     500         326      10
    1008     500         101      30
    1009     500         205      20
    1015     500         101      30
    1016     500         122      10
    1020     500         205      20

一つの表から与えた条件に合致した行(レコード)を抽出して,新しい表を作る操作を選択といいます。選択はWHERE句で与えます。

例題1-3

売上表より,得意先コード500の得意先に売り上げたレコードを選択して,フィールドを月日,商品コード,数量とした表を作りなさい。

    月日 商品コード 数量
    1001     101     10
    1001     205     20
    1001     326     10
    1008     101     30
    1009     205     20
    1015     101     30
    1016     122     10
    1020     205     20

射影と選択を組み合わせたものになります。

例題1-4

商品コード205の売上について,得意先コードと数量の表を作りなさい。

    得意先コード 数量
       500        20
       700        20
       500        20
       600        10
       500        20

例題1-3とほぼ同様ですので,自分でSQLを作成してください。

SELECT 得意先コード,数量
FROM 売上表
WHERE 商品コード=205;

例題1-5

商品区分コードが20以下の商品コードと商品名の表を作りなさい。

    商品コード 商品名
      101      目立テレビ
      122      アニーテレビ
      205      HALパソコン
      213      MDBパソコン
      248      不二パソコン

SELECT 商品コード,商品名
FROM 商品表
WHERE 商品区分コード<=20;

例題1-6

商品名の一部分に「パソコン」という文字列を持つ商品の商品コードと商品名の表を作りなさい。

    商品コード 商品名
       205     HALパソコン
       213     MDBパソコン
       248     不二パソコン

文字列の一部に合致した文字列があるかを調べるのには,
    商品名 LIKE "*パソコン*"  ( * は半角)
のようにします。もし,先頭に「パソコン」があるかを調べるのなら,"パソコン*",末尾ならば "*パソコン" とします。
 なお,Accessのときは * を用いますが,一般のSQLでは % を使います。

例題1-7

月日が10月5日から10月15日の間に,得意先コード600以外の得意先に,商品コードが101または205の商品を売り上げたレコードを,月日,得意先コード,商品コード,数量の表にしなさい。

    月日 得意先コード 商品コード 数量
    1006     700        205       20
    1008     500        101       30
    1009     500        205       20
    1015     500        101       30

ここでは,いろいろな条件の与え方をあげました。「〇〇から××までの間」では BETWEEN を用います。「≠(~以外)」は <> で表します。「いくつかの値のどれかと合致する」のときは IN を用います。条件が複数ある場合は,AND でつなげます。

(注)ALL / DISTINCT

例題1-7において、
  SELECT 得意先コード,商品コード
(他の行は同じ) とすると、次の表が得られます。

    得意先コード 商品コード
        700        205       ①
        500        101       ②
        500        205       ③
        500        101       ④

②と④は全く同じ内容です。用途によっては、このような重複する行を排除したいことがあります。それには
  SELECT DISTINCT 得意先コード,商品コード
とします。

    得意先コード 商品コード
        700        205
        500        101
        500        205

重複行の削除をしないときは
  SELECT ALL 得意先コード,商品コード
としてもよいのですが、省略時は ALL になっているので、あえて記述する必要はありません。

2 複数の表からの検索

複数の表にある共通のキー項目により,それらの表を一つの表にする操作を結合といいます。

例題2-1(結合)

商品表に商品区分名を加えた表を作りなさい。

商品表の商品区分コードと商品区分表の商品区分コードにより,それらの表を結合します。それをSQLでは,
   WHERE 商品表.商品区分コード=商品区分表.商品区分コード
のように表現します。すなわち,結合も選択もWHERE句で記述するのですが,結合のときは,両辺がフィールド名であることが特徴です。

この結合により,例えば,商品表の商品コード101の商品区分コードは10ですが,商品区分表では商品区分コードが10の商品区分名は「テレビ」ですから,商品コード101の商品区分名は「テレビ」になります。同様に,商品コード122では,商品区分コードが20,それで商品区分名は「パソコン」になります。

SELECT句やWHERE句で,「商品表.商品区分コード」という表現をしています。これは単に「商品区分コード」とすると,商品表にも商品区分表にもそれがあるので,どちらかわからないので,「商品表の商品区分コード」とするために,「商品表.商品区分コード」としたのです。一般的には,テーブル名.フィールド名の形式になります。

例題2-2

商品コード205の売上について,年月,得意先コード,得意先名,数量の表を作りなさい。

    月日 得意先コード 得意先名    数量
    1001     500      コダマ電器     20
    1009     500      コダマ電器     20
    1020     500      コダマ電器     20
    1017     600      リラックス     10
    1006     700      淀川カメラ     20

得意先名以外の項目はすべて売上表にありますが,得意先名はありません。それは得意先表にあります。それで,売上表と得意先表を結合する必要があります。その共通キーになるのが得意先コードです。

例題2-3

10月5日から10月10日までのレコードを選択し,フィールドを月日,得意先名,商品名,数量,単価とした表を作りなさい。

    月日 得意先名  商品名     数量  単価
    1008 コダマ電器 目立テレビ       30    40
    1009 コダマ電器 HALパソコン   20   200
    1005 リラックス 不二パソコン     10   150
    1010 リラックス 目立ケータイ     10     8
    1006 淀川カメラ HALパソコン   20   200
    1007 淀川カメラ MDBパソコン   30   180

SELECT 月日,得意先名,商品名,数量,単価
FROM 売上表,得意先表,商品表
WHERE 売上表.得意先コード=得意先表.得意先コード
AND 売上表.商品コード=商品表.商品コード
AND 月日 BETWEEN 1005 AND 1010;

例題2-4

売上表の10月5日から10月10日までのレコードを選択し,フィールドを月日,得意先名,商品名,数量,単価,金額(数量×単価)とした表を作りなさい。

    月日 得意先名  商品名    数量  単価  金額
    1008 コダマ電器 目立テレビ      30    40  1200
    1009 コダマ電器 HALパソコン  20   200  4000
    1005 リラックス 不二パソコン    10   150  1500
    1010 リラックス 目立ケータイ    10     8    80
    1006 淀川カメラ HALパソコン  20   200  4000
    1007 淀川カメラ MDBパソコン  30   180  5400

ここでのポイントは,金額(数量×単価)です。SELECT句でフィールド名を「数量*単価」とすることにより,その計算をします。しかし,このままでは結果のフィールド名がAccessが付けた名前になってしまいます。「金額」というフィールド名にしたいときは,「数量*単価 AS 金額」とします。この AS はフィールド名を付け直すものだと覚えておいてください。

かなり複雑になってきました。わからなくなったら,次の方法を試してみるのも有効です(実務的には,テーブル数もフィールド数も数百個になりますので,このような方法を用いるのは不可能です)。
 下の表は,横にここで取扱うすべてのフィールド名をならべ,縦にテーブル名をならべて,それぞれのテーブルが持つフィールドに〇を付けたものです。そして,最下段にこの例題で必要とするフィールドに〇を付けました。

手順図

上の表に次の手順を行います。

  1. 最下段の〇から,その列の〇に向かって赤線を引き,その〇を赤くする。もし,2つ以上の〇がある場合は,赤〇を持つ行が最小になるように選択して,そちらの〇を赤くする。
  2. 赤〇がある行のテーブル名を赤くする。
  3. 2つの赤いテーブルの行で,同じ列に〇があるのを探して,その2つの〇を青線でつなぐ。
  4. すべての赤いテーブルがつなぐことができたら,完了する。
    例題2-4では,この段階で完了する。
  5. すべての赤いテーブルをつなぐことができないときは,黒いテーブルの行の〇を仲介して,上のようにして青線でつなぐ。そして,仲介したテーブル名を赤くする。これにより,すべての赤いテーブルがつながるはずである。

FROM には,赤のテーブル名を列挙します。
 WHEREでの結合条件は,青い線で結んだ二つの〇の関係です。
   一方のテーブル名.フィールド名=他方のテーブル名.フィールド名
となります。

例題2-5

10月5日から10月10日までの月日,得意先名,商品区分名,数量の表を作りなさい。

    月日 得意先名  商品区分名 数量
    1008 コダマ電器 テレビ       30
    1006 淀川カメラ パソコン     20
    1009 コダマ電器 パソコン     20
    1007 淀川カメラ パソコン     30
    1005 リラックス パソコン     10
    1010 リラックス ケータイ     10

例題2-4での方法で行ってみましょう。

  1. 最下段の〇から,その列の〇に向かって赤線を引き,その〇を赤くする。もし,2つ以上の〇がある場合は,赤〇を持つ行が最小になるように選択して,そちらの〇を赤くする。
  2. 赤〇がある行のテーブル名を赤くする。
  3. 2つの赤いテーブルの行で,同じ列に〇があるのを探して,その2つの〇を青線でつなぐ。
    ここまでで,下の左図ができる。
  4. すべての赤いテーブルがつなぐことができたら,完了する。
    ところがこの場合は,商品区分表が他の赤いテーブルと結合することができない。
  5. すべての赤いテーブルをつなぐことができないときは,黒いテーブルの行の〇を仲介して,上のようにして青線でつなぐ。そして,仲介したテーブル名を赤くする。これにより,すべての赤いテーブルがつながるはずである。
    この場合では,上の右図のように,商品表を仲介することにより,商品区分表-商品表-売上表として結合することができる。

したがって,FROMは,赤いテーブルの売上表,得意先表,商品表,商品区分表の4表であり,結合は,青線の
    売上表.得意先コード=得意先表.得意先コード     売上表.商品コード=商品表.商品コード     商品表.商品区分コード=商品区分表.商品区分コード の3つになります。
 このように「見えない」テーブルが必要になることがあることに注意しましょう。

例題2-6

得意先「リラックス」への売上を,月日,商品名,金額の表にしなさい。

    月日 商品名     金額
    1004 MDBパソコン 5400
    1005 不二パソコン   1500
    1010 目立ケータイ     80
    1011 MDBケータイ  240
    1012 松川ケータイ    200
    1017 HALパソコン 2000

SELECT 月日,商品名,数量*単価 AS 金額
FROM 売上表,商品表,得意先表
WHERE 売上表.商品コード=商品表.商品コード
AND 売上表.得意先コード=得意先表.得意先コード
AND 得意先名="リラックス";

例題2-7

商品区分名が「テレビ」の売上を,月日,得意先名,商品名,数量,金額の表にしなさい。

    月日 得意先名  商品名    数量   金額
    1001 コダマ電器 目立テレビ     10    400
    1008 コダマ電器 目立テレビ     30   1200
    1015 コダマ電器 目立テレビ     30   1200
    1016 コダマ電器 アニーテレビ   10    500
    1018 淀川カメラ アニーテレビ   20   1000

SELECT 月日,得意先名,商品名,数量,数量*単価 AS 金額
FROM 売上表,得意先表,商品表,商品区分表
WHERE 売上表.得意先コード=得意先表.得意先コード
AND 売上表.商品コード=商品表.商品コード
AND 商品表.商品区分コード=商品区分表.商品区分コード
AND 商品区分名="テレビ";

3 並べ替え(ORDER BY)

例題3-1

売上表より,得意先コード500の得意先に売り上げたレコードを選択して,フィールドを月日,商品コード,数量とし,商品コードの昇順に並べた表を作りなさい。

    月日 商品コード 数量
    1015    101       30
    1008    101       30
    1001    101       10
    1016    122       10
    1020    205       20
    1009    205       20
    1001    205       20
    1001    326       10

例題3-2

売上表の10月5日から10月10日までのレコードを選択し,フィールドを売上表と同じにして,得意先コードの昇順にして(第1順位),同じ得意先コードのときは商品コードの昇順(第2順位)に並べた(得意先コード,商品コード順という)表を作りなさい。

    月日 得意先コード 商品コード 数量
    1008      500       101        30
    1009      500       205        20
    1005      600       248        10
    1010      600       352        10
    1006      700       205        20
    1007      700       213        30

SELECT *
FROM 売上表
WHERE 月日 BETWEEN 1005 AND 1010
ORDER BY 得意先コード,商品コード;

例題3-3

売上表の10月5日から10月10日までのレコードを選択し,フィールドを月日,得意先名,商品名,数量,金額として,金額の大きい順に並べた表を作りなさい。

    月日 得意先名   商品名         数量   金額
    1007 淀川カメラ MDBパソコン   30   5400
    1009 コダマ電器 HALパソコン   20   4000
    1006 淀川カメラ HALパソコン   20   4000
    1005 リラックス 不二パソコン     10   1500
    1008 コダマ電器 目立テレビ       30   1200
    1010 リラックス 目立ケータイ     10     80

4 集計(SUMとGROUP BY)

集計の一般形式

SELECT  ・・・ SUM(集計されるフィールド名) ・・・
 :
GROUP BY 大計フィールド名,中計フィールド名,・・・

注意!
 SERECTに記述できるフィールドは,次のものだけ。
 1 集計されるフィールド
 2 GROUP BYに記述したフィールド
 そのため,得意先コードと得意先を同時に表示するのは困難
  → GROUP BY では得意先コードを用いる
    SELECTでは,FIRST(得意先名)とする

例題4-1

売上表で,得意先コード別の数量合計の表を作りなさい。

    得意先コード 数量合計
        500        150
        600        110
        700        110

例題4-2

得意先コード別,商品コード別の数量合計の表を作りなさい。

    得意先コード 商品コード 数量合計
        500         101        70
        500         122        10
        500         205        60
        500         326        10
        600         205        10
        600         213        30
        600         248        10
        600         326        20
        600         352        10
        600         386        30
        700         122        20
        700         205        20
        700         213        40
        700         352        20
        700         386        10

SELECT 得意先コード,商品コード,SUM(数量) AS 数量合計
FROM 売上表
GROUP BY 得意先コード,商品コード;

例題4-3

得意先コード別の数量合計の表を作りなさい。フィールドは得意先名と数量合計にしなさい。

    得意先名   数量合計
    コダマ電器    150
    リラックス    110
    淀川カメラ    110

例題4-3では,「得意先名が同じ得意先があると,一つに集計されてしまう」という大きな欠点があります。それで次の表のようにしたいのです。

    得意先コード 得意先名称 数量合計
         500     コダマ電器     150
         600     リラックス     110
         700     淀川カメラ     110

それで,次のSELECT文を考えたのですが,これらは誤りです。その理由を示しなさい。

ケース1
SELECT 得意先表.得意先コード,得意先名,SUM(数量) AS 数量合計
FROM 売上表,得意先表
WHERE 売上表.得意先コード=得意先表.得意先コード
GROUP BY 得意先名;

ケース2
SELECT 得意先表.得意先コード,得意先名,SUM(数量) AS 数量合計
FROM 売上表,得意先表
WHERE 売上表.得意先コード=得意先表.得意先コード
GROUP BY 得意先表.得意先コード;

ケース3
SELECT 得意先表.得意先コード,得意先名,SUM(数量) AS 数量合計
FROM 売上表,得意先表
WHERE 売上表.得意先コード=得意先表.得意先コード
GROUP BY 得意先表.得意先コード,得意先名;

例題4-4

得意先別の数量合計の表を作りなさい。フィールドは得意先コード,得意先名,数量合計にしなさい。

    得意先コード 得意先名称 数量合計
         500     コダマ電器     150
         600     リラックス     110
         700     淀川カメラ     110

FIRST(得意先)というのは,集計のグループのなかで最初に出現した値ということで,これも一種の集計項目だとみなされます。
 「AS 得意先名」とできると便利なのですが,そうすると合計をしたものが元のものになるような変なことになるので,同じ名称は付けられないのです。

例題4-5

商品別の数量合計を表にしなさい。フィールドは商品コード,商品名(商品名称とする),数量合計にしなさい。

    商品コード 商品名称   数量合計
        101    目立テレビ       70
        122    アニーテレビ     30
        205    HALパソコン   90
        213    MDBパソコン   70
        248    不二パソコン     10
        326    松川ケータイ     30
        352    目立ケータイ     30
        386    MDBケータイ   40

SELECT 商品表.商品コード,
       FIRST(商品名) AS 商品名称,
       SUM(数量) AS 数量合計
FROM 売上表,商品表
WHERE 売上表.商品コード=商品表.商品コード
GROUP BY 商品表.商品コード;

例題4-6

商品別の金額合計を表にしなさい。フィールドは商品コード,商品名(商品名称とする),金額合計にしなさい。

    商品コード 商品名称   金額合計
       101     目立テレビ       2800
       122     アニーテレビ     1500
       205     HALパソコン  18000
       213     MDBパソコン  12600
       248     不二パソコン     1500
       326     松川ケータイ      300
       352     目立ケータイ      240
       386     MDBケータイ    320

SELECT 商品表.商品コード,
       FIRST(商品名) AS 商品名称,
       SUM(数量*単価) AS 金額合計
FROM 売上表,商品表
WHERE 売上表.商品コード=商品表.商品コード
GROUP BY 商品表.商品コード;

例題4-7

商品区分別の金額合計の表を合計の高い順に表示しなさい。

    商品区分名 金額合計
    パソコン     32100
    テレビ        4300
  ケータイ       860

5 合計結果によるデータ選択(HAVING)

例題5-1

得意先コード別商品コード別の数量合計が40以上の得意先コード,商品コード,数量合計の表を作りなさい。

    得意先コード 商品コード 数量合計
        500         101         70
        500         205         60
        700         213         40

もし,上のHAVING句がなければ,すべてのものが表示されてしまいます。「数量合計が40以上」のように,集計結果を検索条件にするのにHAVING句を用います。

例題5-2

商品別の金額合計が1000以上の商品名,金額合計の表を作りなさい。金額の大きい順に並べなさい。

    商品名     金額合計
    HALパソコン   18000
    MDBパソコン   12600
    目立テレビ        2800
    不二パソコン      1500
    アニーテレビ      1500

6 その他

例題6-1(集計関数)

得意先コード別に売上件数,1回の売上での数量の最大値,最小値,平均値を表にしなさい。

    得意先コード 件数 最大値 最小値 平均値
        500        8    30     10    18.75
        600        6    30     10    18.33
        700        6    30     10    18.33

「授業」データベースによる演習

例題01

吉田先生が担当している選択科目について,科目番号,科目名,単位を表にしなさい。

    科目番号 科目名 単位
      204    心理学   4
      302    民法     2
      304    社会論   4

SELECT 科目番号,科目名,単位
FROM 科目表,教員表
WHERE 科目表.教員番号=教員表.教員番号
AND 教員名="吉田先生";

例題02

現在履修中(成績が0)である学生名と科目名を表にしなさい。

    学生名    科目名
    安部愛子   情報論
    安部愛子   貿易論
    井上一郎   民法
    江川絵美   企業論
    小川音吉   数学
    小林幸一   簿記

SELECT 学生名,科目名
FROM 履修表,学生表,科目表
WHERE 履修表.学生番号=学生表.学生番号
AND 履修表.科目番号=科目表.科目番号
AND 成績=0;

例題03

語学科目の単位を取得した(成績が3以上)の学生名を表にしなさい。

    学生名
    安部愛子
    上田海彦
    江川絵美

SELECT 学生名
FROM 履修表,学生表,科目表
WHERE 履修表.学生番号=学生表.学生番号
AND 履修表.科目番号=科目表.科目番号
AND 科目名="語学"
AND 成績>=3;

例題04

学生番号1011の学生が単位を取得(成績3以上)した科目の科目番号,科目名,単位,成績を表にしなさい。

    科目番号 科目名 単位 成績
      201    語学     2    5
      203    経済学   4    4
      204    心理学   4    3
      301    簿記     2    5
      302    民法     2    3
      304    社会論   4    4

SELECT 履修表.科目番号,科目名,単位,成績
FROM 履修表,科目表
WHERE 履修表.科目番号=科目表.科目番号
AND 学生番号=1011
AND 成績>=3;

例題05

井上一郎が単位を取得した科目の科目番号,科目名,単位,成績を表にしなさい。

    科目番号 科目名 単位 成績
      203    経済学   4    5
      204    心理学   4    4
      303    企業論   4    4
      306    貿易論   4    5

SELECT 科目表.科目番号,科目名,単位,成績
FROM 履修表,学生表,科目表
WHERE 履修表.学生番号=学生表.学生番号
AND 履修表.科目番号=科目表.科目番号
AND 学生名="井上一郎"
AND 成績>=3;

例題06

現在履修中の科目を担当している教員の教員名,担当科目名,学生名の表を作りなさい。

    教員名    科目名    学生名
    湯川先生  情報論    安部愛子
    渡辺先生  貿易論    安部愛子
    吉田先生  民法      井上一郎
    矢沢先生  企業論    江川絵美
    湯川先生  数学      小川音吉
    矢沢先生  簿記      小林幸一

SELECT 教員名,科目名,学生名
FROM 履修表,学生表,科目表,教員表
WHERE 履修表.学生番号=学生表.学生番号
AND 履修表.科目番号=科目表.科目番号
AND 科目表.教員番号=教員表.教員番号
AND 成績=0;

例題07

語学と貿易論のどちらかで成績が5の学生について学生番号,学生名,科目番号,科目名の表を作りなさい。科目番号の小さい順に並べなさい。

    学生番号 学生名  科目番号 科目名
      1011   安部愛子   201     語学
      1012   井上一郎   306     貿易論

SELECT 学生表.学生番号,学生名,科目表.科目番号,科目名
FROM  履修表,学生表,科目表
WHERE 履修表.学生番号=学生表.学生番号
AND 履修表.科目番号=科目表.科目番号
AND 科目名 IN ("語学","貿易論")
AND 成績=5;

例題08

担当科目の単位の合計の大きい順に,教員名と合計単位の表を作りなさい。

    教員名 単位合計
    矢沢先生    10
    吉田先生    10
    湯川先生     6
    渡辺先生     6

SELECT 教員名,SUM(単位) AS 単位合計
FROM 科目表,教員表
WHERE 科目表.教員番号=教員表.教員番号
GROUP BY 教員名
ORDER BY SUM(単位) DESC;

例題09

取得した(成績が3以上)の単位の合計が高い順に,学生名,単位合計の表を作りなさい。

    学生名 単位合計
    安部愛子   18
    上田海彦   16
    井上一郎   16
    小川音吉    4
    剣持健二    4
    加藤佳世    4
    木村喜一    2
    江川絵美    2

SELECT 学生名,SUM(単位) AS 単位合計
FROM 履修表,学生表,科目表
WHERE 履修表.学生番号=学生表.学生番号
AND 履修表.科目番号=科目表.科目番号
AND 成績>=3
GROUP BY 学生名
ORDER BY SUM(単位) DESC;

例題10

単位を取得した学生の数が多い順に科目名を表にしなさい。

    科目名 学生数
    心理学    4
    社会論    3
    語学      3
    簿記      2
    情報論    2
    経済学    2
    民法      1
    貿易論    1
    数学      1
    企業論    1

SELECT 科目名,COUNT(学生番号) AS 学生数
FROM 履修表,科目表
WHERE 履修表.科目番号=科目表.科目番号
AND 成績>=3
GROUP BY 科目名
ORDER BY COUNT(学生番号) DESC;

例題11

担当科目で単位を取得した学生の数が多い順に教員名を表にしなさい。

    教員名  学生数
    吉田先生    8
    矢沢先生    5
    渡辺先生    4
    湯川先生    3

SELECT 教員名,COUNT(学生番号) AS 学生数
FROM 履修表,科目表,教員表
WHERE 履修表.科目番号=科目表.科目番号
AND 科目表.教員番号=教員表.教員番号
AND 成績>=3
GROUP BY 教員名
ORDER BY COUNT(学生番号) DESC;

例題12

各科目につき,過去に履修した(成績≠0)成績の平均値を求め,平均値の低い順に科目名と成績平均値の表を作りなさい。

    科目名 成績平均値
    数学      2.50
    民法      3.00
    企業論    3.00
    社会論    3.67
    経済学    3.67
    語学      3.75
    簿記      4.00
    心理学    4.00
    情報論    4.00
    貿易論    5.00 

SELECT 科目名,AVG(成績) AS 成績平均値
FROM  履修表,科目表
WHERE 履修表.科目番号=科目表.科目番号
AND 成績>0
GROUP BY 科目名
ORDER BY AVG(成績);

例題13

必須科目の取得単位が4単位以下の学生番号,学生名,単位合計を,単位合計が少ない順に表示しなさい。ただし,0単位の者は除く。学生のなかには同姓同名があるかもしれない。

    学生番号 学生氏名 単位合計
      1012   井上一郎    2
      1011   安部愛子    2
      1023   工藤久美    4

SELECT 学生表.学生番号,FIRST(学生名) AS 学生氏名,SUM(単位) AS 単位合計
FROM 履修表,学生表,科目表
WHERE 履修表.学生番号=学生表.学生番号
AND 履修表.科目番号=科目表.科目番号
AND 科目区分="必須"
AND 成績 BETWEEN 1 AND 2
GROUP BY 学生表.学生番号
ORDER BY SUM(単位);

例題14

取得した科目の成績×単位の合計が20以上である学生を,合計の大きい順に学生番号,学生名(成績優良者とする),合計を表にしなさい。

    学生番号 成績優良者 合計
      1012   井上一郎    72
      1011   安部愛子    70
      1013   上田海彦    62
      1021   加藤佳世    20

SELECT 学生表.学生番号,FIRST(学生名) AS 成績優良者,SUM(成績*単位) AS 合計
FROM  履修表,科目表,学生表
WHERE 履修表.学生番号=学生表.学生番号
AND 履修表.科目番号=科目表.科目番号
AND 成績>=3
GROUP BY 学生表.学生番号
HAVING SUM(成績*単位)>=20
ORDER BY SUM(成績*単位) DESC;

参考URL:篠原光太郎「連載:SQL実践講座」
http://www.atmarkit.co.jp/fnetwork/rensai/sql01/sql1.html


本シリーズの目次へ