Accessを用いて,SQLのSELECT文を理解することを目的にします。
Access,SQL,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.キーフィールド名
売上表から,得意先コードと商品コードのフィールドだけを取り出した表を作りなさい。
得意先コード 商品コード 500 101 500 205 500 326 600 213 : : : : 700 213 500 205
一つの表(テーブル)から,指定した行(フィールド)だけによる新しい表を作る操作を射影といいます。射影はSELECTに続くフィールド名を指定すればよいのです。
入力テーブルは売上表→[FROM 売上表],得意先コードと商品コードのフィールドだけを取り出す→[SELECT 得意先コード,商品コード]なので,SQLは次のようになります。
SELECT 得意先コード,商品コード FROM 売上表;
これをAccessの「売上」データベースでのSQLクエリにコピーして,上の結果が得られることを確認しなさい。以下の例題も同様です。
売上表から,得意先コードが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句で与えます。
SELECT * FROM 売上表 WHERE 得意先コード=500;
売上表より,得意先コード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
射影と選択を組み合わせたものになります。
SELECT 月日,商品コード,数量 FROM 売上表 WHERE 得意先コード=500;
商品コード205の売上について,得意先コードと数量の表を作りなさい。
得意先コード 数量 500 20 700 20 500 20 600 10 500 20
例題1-3とほぼ同様ですので,自分でSQLを作成してください。 ☆
SELECT 得意先コード,数量 FROM 売上表 WHERE 商品コード=205;
商品区分コードが20以下の商品コードと商品名の表を作りなさい。 ☆
商品コード 商品名 101 目立テレビ 122 アニーテレビ 205 HALパソコン 213 MDBパソコン 248 不二パソコン ☆
SELECT 商品コード,商品名 FROM 商品表 WHERE 商品区分コード<=20;
商品名の一部分に「パソコン」という文字列を持つ商品の商品コードと商品名の表を作りなさい。
商品コード 商品名 205 HALパソコン 213 MDBパソコン 248 不二パソコン
文字列の一部に合致した文字列があるかを調べるのには,
商品名 LIKE "*パソコン*" ( * は半角)
のようにします。もし,先頭に「パソコン」があるかを調べるのなら,"パソコン*",末尾ならば "*パソコン" とします。
なお,Accessのときは * を用いますが,一般のSQLでは % を使います。
SELECT 商品コード,商品名 FROM 商品表 WHERE 商品名 LIKE "*パソコン*";
月日が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 でつなげます。
SELECT 月日,得意先コード,商品コード,数量 FROM 売上表 WHERE 月日 BETWEEN 1005 AND 1015 AND 得意先コード<>600 AND 商品コード IN (101,205);
例題1-7において、
SELECT 得意先コード,商品コード
(他の行は同じ) とすると、次の表が得られます。
得意先コード 商品コード 700 205 ① 500 101 ② 500 205 ③ 500 101 ④
②と④は全く同じ内容です。用途によっては、このような重複する行を排除したいことがあります。それには
SELECT DISTINCT 得意先コード,商品コード
とします。
得意先コード 商品コード 700 205 500 101 500 205
重複行の削除をしないときは
SELECT ALL 得意先コード,商品コード
としてもよいのですが、省略時は ALL になっているので、あえて記述する必要はありません。
複数の表にある共通のキー項目により,それらの表を一つの表にする操作を結合といいます。
商品表に商品区分名を加えた表を作りなさい。
商品表の商品区分コードと商品区分表の商品区分コードにより,それらの表を結合します。それをSQLでは,
WHERE 商品表.商品区分コード=商品区分表.商品区分コード
のように表現します。すなわち,結合も選択もWHERE句で記述するのですが,結合のときは,両辺がフィールド名であることが特徴です。
この結合により,例えば,商品表の商品コード101の商品区分コードは10ですが,商品区分表では商品区分コードが10の商品区分名は「テレビ」ですから,商品コード101の商品区分名は「テレビ」になります。同様に,商品コード122では,商品区分コードが20,それで商品区分名は「パソコン」になります。
SELECT 商品コード,商品名,商品表.商品区分コード,商品区分名,単価 FROM 商品表,商品区分表 WHERE 商品表.商品区分コード=商品区分表.商品区分コード;
SELECT句やWHERE句で,「商品表.商品区分コード」という表現をしています。これは単に「商品区分コード」とすると,商品表にも商品区分表にもそれがあるので,どちらかわからないので,「商品表の商品区分コード」とするために,「商品表.商品区分コード」としたのです。一般的には,テーブル名.フィールド名の形式になります。
商品コード205の売上について,年月,得意先コード,得意先名,数量の表を作りなさい。
月日 得意先コード 得意先名 数量 1001 500 コダマ電器 20 1009 500 コダマ電器 20 1020 500 コダマ電器 20 1017 600 リラックス 10 1006 700 淀川カメラ 20
得意先名以外の項目はすべて売上表にありますが,得意先名はありません。それは得意先表にあります。それで,売上表と得意先表を結合する必要があります。その共通キーになるのが得意先コードです。
SELECT 月日,売上表.得意先コード,得意先名,数量 FROM 売上表,得意先表 WHERE 売上表.得意先コード=得意先表.得意先コード AND 商品コード=205;
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;
売上表の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 はフィールド名を付け直すものだと覚えておいてください。
SELECT 月日,得意先名,商品名,数量,単価,数量*単価 AS 金額 FROM 売上表,得意先表,商品表 WHERE 売上表.得意先コード=得意先表.得意先コード AND 売上表.商品コード=商品表.商品コード AND 月日 BETWEEN 1005 AND 1010;
かなり複雑になってきました。わからなくなったら,次の方法を試してみるのも有効です(実務的には,テーブル数もフィールド数も数百個になりますので,このような方法を用いるのは不可能です)。
下の表は,横にここで取扱うすべてのフィールド名をならべ,縦にテーブル名をならべて,それぞれのテーブルが持つフィールドに〇を付けたものです。そして,最下段にこの例題で必要とするフィールドに〇を付けました。
上の表に次の手順を行います。
FROM には,赤のテーブル名を列挙します。
WHEREでの結合条件は,青い線で結んだ二つの〇の関係です。
一方のテーブル名.フィールド名=他方のテーブル名.フィールド名
となります。
10月5日から10月10日までの月日,得意先名,商品区分名,数量の表を作りなさい。
月日 得意先名 商品区分名 数量 1008 コダマ電器 テレビ 30 1006 淀川カメラ パソコン 20 1009 コダマ電器 パソコン 20 1007 淀川カメラ パソコン 30 1005 リラックス パソコン 10 1010 リラックス ケータイ 10
SELECT 月日,得意先名,商品区分名,数量 FROM 売上表,得意先表,商品表,商品区分表 WHERE 売上表.得意先コード=得意先表.得意先コード AND 売上表.商品コード=商品表.商品コード AND 商品表.商品区分コード=商品区分表.商品区分コード AND 月日 BETWEEN 1005 AND 1010;
例題2-4での方法で行ってみましょう。
したがって,FROMは,赤いテーブルの売上表,得意先表,商品表,商品区分表の4表であり,結合は,青線の
売上表.得意先コード=得意先表.得意先コード
売上表.商品コード=商品表.商品コード
商品表.商品区分コード=商品区分表.商品区分コード
の3つになります。
このように「見えない」テーブルが必要になることがあることに注意しましょう。
得意先「リラックス」への売上を,月日,商品名,金額の表にしなさい。 ☆
月日 商品名 金額 1004 MDBパソコン 5400 1005 不二パソコン 1500 1010 目立ケータイ 80 1011 MDBケータイ 240 1012 松川ケータイ 200 1017 HALパソコン 2000 ☆
SELECT 月日,商品名,数量*単価 AS 金額 FROM 売上表,商品表,得意先表 WHERE 売上表.商品コード=商品表.商品コード AND 売上表.得意先コード=得意先表.得意先コード AND 得意先名="リラックス";
商品区分名が「テレビ」の売上を,月日,得意先名,商品名,数量,金額の表にしなさい。 ☆
月日 得意先名 商品名 数量 金額 1001 コダマ電器 目立テレビ 10 400 1008 コダマ電器 目立テレビ 30 1200 1015 コダマ電器 目立テレビ 30 1200 1016 コダマ電器 アニーテレビ 10 500 1018 淀川カメラ アニーテレビ 20 1000 ☆
SELECT 月日,得意先名,商品名,数量,数量*単価 AS 金額 FROM 売上表,得意先表,商品表,商品区分表 WHERE 売上表.得意先コード=得意先表.得意先コード AND 売上表.商品コード=商品表.商品コード AND 商品表.商品区分コード=商品区分表.商品区分コード AND 商品区分名="テレビ";
売上表より,得意先コード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
SELECT 月日,商品コード,数量 FROM 売上表 WHERE 得意先コード=500 ORDER BY 商品コード;
売上表の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 得意先コード,商品コード;
売上表の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
SELECT 月日,得意先名,商品名,数量,数量*単価 AS 金額 FROM 売上表,得意先表,商品表 WHERE 売上表.得意先コード=得意先表.得意先コード AND 売上表.商品コード=商品表.商品コード AND 月日 BETWEEN 1005 AND 1010 ORDER BY 数量*単価 DESC;
集計の一般形式
SELECT ・・・ SUM(集計されるフィールド名) ・・・ : GROUP BY 大計フィールド名,中計フィールド名,・・・ 注意! SERECTに記述できるフィールドは,次のものだけ。 1 集計されるフィールド 2 GROUP BYに記述したフィールド そのため,得意先コードと得意先を同時に表示するのは困難 → GROUP BY では得意先コードを用いる SELECTでは,FIRST(得意先名)とする
売上表で,得意先コード別の数量合計の表を作りなさい。
得意先コード 数量合計 500 150 600 110 700 110
SELECT 得意先コード,SUM(数量) AS 数量合計 FROM 売上表 GROUP BY 得意先コード;
得意先コード別,商品コード別の数量合計の表を作りなさい。
得意先コード 商品コード 数量合計 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 得意先コード,商品コード;
得意先コード別の数量合計の表を作りなさい。フィールドは得意先名と数量合計にしなさい。
得意先名 数量合計 コダマ電器 150 リラックス 110 淀川カメラ 110
SELECT 得意先名,SUM(数量) AS 数量合計 FROM 売上表,得意先表 WHERE 売上表.得意先コード=得意先表.得意先コード GROUP BY 得意先名;
例題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 得意先表.得意先コード,得意先名;
得意先別の数量合計の表を作りなさい。フィールドは得意先コード,得意先名,数量合計にしなさい。
得意先コード 得意先名称 数量合計 500 コダマ電器 150 600 リラックス 110 700 淀川カメラ 110
SELECT 得意先表.得意先コード, FIRST(得意先名) AS 得意先名称, SUM(数量) AS 数量合計 FROM 売上表,得意先表 WHERE 売上表.得意先コード=得意先表.得意先コード GROUP BY 得意先表.得意先コード;
FIRST(得意先)というのは,集計のグループのなかで最初に出現した値ということで,これも一種の集計項目だとみなされます。
「AS 得意先名」とできると便利なのですが,そうすると合計をしたものが元のものになるような変なことになるので,同じ名称は付けられないのです。
商品別の数量合計を表にしなさい。フィールドは商品コード,商品名(商品名称とする),数量合計にしなさい。
商品コード 商品名称 数量合計 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 商品表.商品コード;
商品別の金額合計を表にしなさい。フィールドは商品コード,商品名(商品名称とする),金額合計にしなさい。
商品コード 商品名称 金額合計 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 商品表.商品コード;
商品区分別の金額合計の表を合計の高い順に表示しなさい。
商品区分名 金額合計 パソコン 32100 テレビ 4300 ケータイ 860
SELECT 商品区分名,SUM(数量*単価) AS 金額合計 FROM 売上表,商品表,商品区分表 WHERE 売上表.商品コード=商品表.商品コード AND 商品表.商品区分コード=商品区分表.商品区分コード GROUP BY 商品区分名 ORDER BY SUM(数量*単価) DESC;
得意先コード別商品コード別の数量合計が40以上の得意先コード,商品コード,数量合計の表を作りなさい。
得意先コード 商品コード 数量合計 500 101 70 500 205 60 700 213 40
SELECT 得意先コード,商品コード,SUM(数量) AS 数量合計 FROM 売上表 GROUP BY 得意先コード,商品コード HAVING SUM(数量)>=40;
もし,上のHAVING句がなければ,すべてのものが表示されてしまいます。「数量合計が40以上」のように,集計結果を検索条件にするのにHAVING句を用います。
商品別の金額合計が1000以上の商品名,金額合計の表を作りなさい。金額の大きい順に並べなさい。
商品名 金額合計 HALパソコン 18000 MDBパソコン 12600 目立テレビ 2800 不二パソコン 1500 アニーテレビ 1500
SELECT 商品名,SUM(数量*単価) AS 金額合計 FROM 売上表,商品表 WHERE 売上表.商品コード=商品表.商品コード GROUP BY 商品名 HAVING SUM(数量*単価)>=1000 ORDER BY SUM(数量*単価) DESC;
得意先コード別に売上件数,1回の売上での数量の最大値,最小値,平均値を表にしなさい。
得意先コード 件数 最大値 最小値 平均値 500 8 30 10 18.75 600 6 30 10 18.33 700 6 30 10 18.33
SELECT 得意先コード, COUNT(数量) AS 件数, MAX(数量) AS 最大値, MIN(数量) AS 最小値, AVG(数量) AS 平均値 FROM 売上表 GROUP BY 得意先コード;
吉田先生が担当している選択科目について,科目番号,科目名,単位を表にしなさい。
科目番号 科目名 単位 204 心理学 4 302 民法 2 304 社会論 4 ☆
SELECT 科目番号,科目名,単位 FROM 科目表,教員表 WHERE 科目表.教員番号=教員表.教員番号 AND 教員名="吉田先生";
現在履修中(成績が0)である学生名と科目名を表にしなさい。
学生名 科目名 安部愛子 情報論 安部愛子 貿易論 井上一郎 民法 江川絵美 企業論 小川音吉 数学 小林幸一 簿記 ☆
SELECT 学生名,科目名 FROM 履修表,学生表,科目表 WHERE 履修表.学生番号=学生表.学生番号 AND 履修表.科目番号=科目表.科目番号 AND 成績=0;
語学科目の単位を取得した(成績が3以上)の学生名を表にしなさい。
学生名 安部愛子 上田海彦 江川絵美 ☆
SELECT 学生名 FROM 履修表,学生表,科目表 WHERE 履修表.学生番号=学生表.学生番号 AND 履修表.科目番号=科目表.科目番号 AND 科目名="語学" AND 成績>=3;
学生番号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;
井上一郎が単位を取得した科目の科目番号,科目名,単位,成績を表にしなさい。
科目番号 科目名 単位 成績 203 経済学 4 5 204 心理学 4 4 303 企業論 4 4 306 貿易論 4 5 ☆
SELECT 科目表.科目番号,科目名,単位,成績 FROM 履修表,学生表,科目表 WHERE 履修表.学生番号=学生表.学生番号 AND 履修表.科目番号=科目表.科目番号 AND 学生名="井上一郎" AND 成績>=3;
現在履修中の科目を担当している教員の教員名,担当科目名,学生名の表を作りなさい。
教員名 科目名 学生名 湯川先生 情報論 安部愛子 渡辺先生 貿易論 安部愛子 吉田先生 民法 井上一郎 矢沢先生 企業論 江川絵美 湯川先生 数学 小川音吉 矢沢先生 簿記 小林幸一 ☆
SELECT 教員名,科目名,学生名 FROM 履修表,学生表,科目表,教員表 WHERE 履修表.学生番号=学生表.学生番号 AND 履修表.科目番号=科目表.科目番号 AND 科目表.教員番号=教員表.教員番号 AND 成績=0;
語学と貿易論のどちらかで成績が5の学生について学生番号,学生名,科目番号,科目名の表を作りなさい。科目番号の小さい順に並べなさい。
学生番号 学生名 科目番号 科目名 1011 安部愛子 201 語学 1012 井上一郎 306 貿易論 ☆
SELECT 学生表.学生番号,学生名,科目表.科目番号,科目名 FROM 履修表,学生表,科目表 WHERE 履修表.学生番号=学生表.学生番号 AND 履修表.科目番号=科目表.科目番号 AND 科目名 IN ("語学","貿易論") AND 成績=5;
担当科目の単位の合計の大きい順に,教員名と合計単位の表を作りなさい。
教員名 単位合計 矢沢先生 10 吉田先生 10 湯川先生 6 渡辺先生 6 ☆
SELECT 教員名,SUM(単位) AS 単位合計 FROM 科目表,教員表 WHERE 科目表.教員番号=教員表.教員番号 GROUP BY 教員名 ORDER BY SUM(単位) DESC;
取得した(成績が3以上)の単位の合計が高い順に,学生名,単位合計の表を作りなさい。
学生名 単位合計 安部愛子 18 上田海彦 16 井上一郎 16 小川音吉 4 剣持健二 4 加藤佳世 4 木村喜一 2 江川絵美 2 ☆
SELECT 学生名,SUM(単位) AS 単位合計 FROM 履修表,学生表,科目表 WHERE 履修表.学生番号=学生表.学生番号 AND 履修表.科目番号=科目表.科目番号 AND 成績>=3 GROUP BY 学生名 ORDER BY SUM(単位) DESC;
単位を取得した学生の数が多い順に科目名を表にしなさい。
科目名 学生数 心理学 4 社会論 3 語学 3 簿記 2 情報論 2 経済学 2 民法 1 貿易論 1 数学 1 企業論 1 ☆
SELECT 科目名,COUNT(学生番号) AS 学生数 FROM 履修表,科目表 WHERE 履修表.科目番号=科目表.科目番号 AND 成績>=3 GROUP BY 科目名 ORDER BY COUNT(学生番号) DESC;
担当科目で単位を取得した学生の数が多い順に教員名を表にしなさい。
教員名 学生数 吉田先生 8 矢沢先生 5 渡辺先生 4 湯川先生 3 ☆
SELECT 教員名,COUNT(学生番号) AS 学生数 FROM 履修表,科目表,教員表 WHERE 履修表.科目番号=科目表.科目番号 AND 科目表.教員番号=教員表.教員番号 AND 成績>=3 GROUP BY 教員名 ORDER BY COUNT(学生番号) DESC;
各科目につき,過去に履修した(成績≠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(成績);
必須科目の取得単位が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(単位);
取得した科目の成績×単位の合計が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