SQL-2 - note4recurrent @ ウィキ

• １－１
• VM_生徒基本情報を全列名検索してください。

select * from dbo.VM_生徒基本情報;

• １－２
• VM_生徒塾種情報を全列名検索してください。

select * from dbo.VM_生徒塾種情報;

• １－３
• VM_生徒受講情報を全列名検索してください。 select * from dbo.VM_生徒受講情報;

• １－４
• VM_組織マスタを全列名検索してください。 select * from dbo.VM_組織マスタ;

• １－５
• VM_クラスマスタを全列名検索してください。 select * from dbo.VM_クラスマスタ;

• １－６
• VM_生徒基本情報から、生徒コード「301～320」の
• 生徒を、全列名を検索してください。 select * from dbo.VM_生徒基本情報 where 生徒コード between 301 and 320;

• １－７
• VM_生徒塾種情報から、退塾の生徒を全列名検索してください。
• ＊状態区分は 1　:　塾生（塾に入塾した生徒）
• 2　:　塾外生（入塾していない生徒　※テストのみを受ける見込み客等）
• 3　:　プール生（次年度入塾予定の生徒）
• 4　:　退塾（入塾後、塾を辞めた生徒）
• 5　:　休塾（入塾後、様々な事情で一定期間塾をお休みする生徒）
• 6　:　既卒（入塾し、卒塾(浜学園は小1～小6まで）した生徒)
• 　　　 です。 select * from dbo.VM_生徒塾種情報 where 状態区分 = 4;

• ８
• M_生徒受講情報から、受講年度が　　 2014年　かつ、
• 講座コードが　 5100　 かつ、
• 教室コードが　 　 201　 かつ
• 現在受講中 の生徒を全列名検索してください。
• 「現在受講中」とは、開始日列がNULL以外、終了日列がNULLの状態を指します。 select * from dbo.VM_生徒受講情報 where 受講年度 = 2014 and 講座コード = 5100 and 教室コード = 201 and 開始日 is not null and 終了日 is null;

• １－９
• VM_組織マスタの塾種区分が1　かつ、組織タイプが3かつ、
• 組織KEY「850」「701」以外 の
• 列名：
• 　組織KEY → 教室コード、
• 組織名略称1 → 教室名、
• 組織名略称3を検索してください。 select 組織KEY as 教室コード,組織名略称1 as 教室名,組織名略称3 from dbo.VM_組織マスタ where 塾種区分 = 1 and 組織タイプ = 3 and 組織KEY not in (850,701);

• １－１０
• VM_クラスマスタの　教室コードが101　かつ、学年コードが6の
• 列名：クラスコード　　 →　クラス、
• クラス名称帳票用 →　クラス名称、
• 大クラスコード、
• クラスコード　 を検索してください

select クラスコード as クラス,クラス名称帳票用 as クラス名称, 大クラスコード,クラスコード from dbo.VM_クラスマスタ where 教室コード = 101 and 学年コード = 6;

• ２－１
• VM_生徒基本情報より、飛び級生の生徒を抽出してください。
• ※飛び級生は　生徒帳票用氏名に「＊」が付いています。ヒント：LIKE句 select * from dbo.VM_生徒基本情報 where 生徒帳票用氏名 like '%＊%';

• ２－２
• ２－１で抽出した生徒を、「女子」、次に「男子」の順に並べ替えて、さらに、
• 生徒氏名カナ_半角の昇順で表示してください。
• ※性別　１：男　２：女　 select * from dbo.VM_生徒基本情報 where 生徒帳票用氏名 like '%＊%' order by 性別 desc, 生徒氏名カナ_半角;

• ２－３
• VM_生徒塾種情報より、塾外生、退塾、既卒以外の生徒を抽出してください。
• [列名]　状態区分 ：生徒の状態
• （区分のコードは「001_1_課題１.doc」の１－７参照）
• 　　　　塾生コード ：塾生ｺｰﾄﾞ
• 所属教室コード ：教室コード
• ※塾種区分は’1’で指定してください。

select 状態区分 as 生徒の状態,塾生コード as 塾生ｺｰﾄﾞ , 所属教室コード as 教室コード from dbo.VM_生徒塾種情報 where 状態区分 not in (2,4,6) and 塾種区分 = 1 ;

• ２－４
• ２－３で抽出した生徒を、所属教室コードの昇順、状態区分の降順に並べ替えてください。
• [列名] 教室コード
• 生徒の状態
• 塾生ｺｰﾄﾞ select 所属教室コード as 教室コード,状態区分 as 生徒の状態,塾生コード as 塾生ｺｰﾄﾞ from dbo.VM_生徒塾種情報 where 状態区分 not in (2,4,6) and 塾種区分 = 1 order by 所属教室コード,状態区分 desc;

• ２－５
• ２－４で抽出した生徒を、所属教室単位の状態区分単位に集約してください。 select 所属教室コード as 教室コード,状態区分 as 生徒の状態,塾生コード as 塾生ｺｰﾄﾞ from dbo.VM_生徒塾種情報 where 状態区分 not in (2,4,6) and 塾種区分 = 1 group by 所属教室コード,状態区分,塾生コード order by 所属教室コード,状態区分 desc;

• ２－６
• VM_生徒基本情報を検索。生年月日　2001年4月2日以降、
• かつ、飛び級生（生徒帳票用氏名に「＊」が入っている生徒）の生徒を
• 性別降順、生徒コードの昇順の並びで、
• [列名] ‘飛び級生’　AS　塾種名称　
• 生徒帳票用氏名
• 生年月日
• 生徒コード

select '飛び級生'　AS　塾種名称,生徒帳票用氏名,生年月日,生徒コード from dbo.VM_生徒基本情報 where 生徒帳票用氏名 like '%＊%' and 生年月日 >= 2001-04-02 order by 性別 desc, 生徒コード;

• ２－７
• VT_テスト生徒得点情報を検索。第467回公開学力テストを受験者した５年生の中から、
• 教室コード降順、得点降順で全列名検索してください。
• [条件] 情報種別区分 ＝ 1(イベントテスト)
• PKEY ＝ 262(467回公開学力テスト)
• 　　　　　　　※Pkyeが何かを調べる場合は、VT_テスト情報ＨＤで検索。
• 科目コード ＝ 2(算数)
• 単元コード ＝ 2(算数)
• 　　　学年コード　　＝　　 5

select * from dbo.VT_テスト生徒得点情報 as a where 情報種別区分 = 1 and PKEY = 262 and 科目コード = 2 and 単元コード = 2 and

 　　　学年コード　=　 5

order by 教室コード desc , 得点 desc;

• ２－８
• VT_テスト生徒成績集計情報を検索。第467・468・469・470回公開学力テスト受験者、
• かつ、５年生、受験教室は西大寺(=401)、回数の昇順、で全列名検索してください。
• ※教室のコードを調べる場合は、VM_組織マスタで塾種区分=1、組織タイプ=3で参照。
• [条件] 情報種別区分 ＝ 1
• PKEY ＝ ※VT_テスト情報ＨＤより、情報種別区分=1、
• 　　　　　　　　　　　　　　　　　 　回数=(該当回数)で検索してください。
• テスト集計区分＝ 1
• 科計区分 ＝ 3
• 型NO ＝ 2
• 　　　学年コード　　＝　　 5
• 　　　受験教室コード＝　　 401

select * from dbo.VT_テスト生徒成績集計情報 as a where 情報種別区分 = 1 and テスト集計区分 = 1 and 科計区分 = 3 and 型NO = 2 and 学年コード = 5 and 受験教室コード = 401 and a.Pkey in (select Pkey from dbo.VT_テスト情報ＨＤ as k where 回数 in (467,468,469,470));

• ２－９
• ２－８の抽出結果を使用し、教室コードが西大寺教室(401)の生徒を、
• 回数単位、大クラスコード単位に集約し、回数の昇順、大クラスコードの昇順で並び替え、
• [列名] 集約教室　値＝‘西大寺教室’
• 回数
• 大クラスコード　：L_CLASS
• COUNT(*) AS 集計
• を抽出してください

select '西大寺教室' as 集約教室, 回数, 大クラスコード as L_CLASS ,count(*) as 集計　 from dbo.VT_テスト生徒成績集計情報 where 情報種別区分 = 1 and テスト集計区分 = 1 and 科計区分 = 3 and 型NO = 2 and 学年コード = 5 and 受験教室コード = 401 and Pkey in (select Pkey from dbo.VT_テスト情報ＨＤ where 回数 in (467,468,469,470)) group by 回数,大クラスコード order by 回数,大クラスコード

• ３－１
• 「VM_生徒塾種情報」テーブル使用
• 現在【塾生、塾外生、ﾌﾟｰﾙ生、退塾、休塾、既卒】それぞれの生徒の人数を検索して下さい。
• [列名] 状態区分、人数　の２つです。※状態区分については、課題１の７を参照。

• VM_生徒塾種情報から、退塾の生徒を全列名検索してください。
• ＊状態区分は 1　:　塾生（塾に入塾した生徒）
• 2　:　塾外生（入塾していない生徒　※テストのみを受ける見込み客等）
• 3　:　プール生（次年度入塾予定の生徒）
• 4　:　退塾（入塾後、塾を辞めた生徒）
• 5　:　休塾（入塾後、様々な事情で一定期間塾をお休みする生徒）
• 6　:　既卒（入塾し、卒塾(浜学園は小1～小6まで）した生徒)
• 　　　 です。

select 状態区分,count(*) as 人数 from dbo.VM_生徒塾種情報 where 状態区分 <> 0 group by 状態区分;

• ３－２
• ３－１で抽出したデータをさらに分かりやすくする為に、
• 状態区分　1　なら　塾生　というように、「状態名称」の[列名]を追加して下さい。

select 状態区分, case when 状態区分 = 1 then '塾生' when 状態区分 = 2 then '塾外生' when 状態区分 = 3 then 'プール生' when 状態区分 = 4 then '退塾' when 状態区分 = 5 then '休塾' when 状態区分 = 6 then '既卒' end as 状態区分名, count(*) as 件数 from dbo.VM_生徒塾種情報 where 状態区分 <> 0 group by 状態区分 order by 状態区分;

• ３－３
• 今現在の日付時刻を調べる、ＳＱＬ文を作成してください。
• 実行(Ctrl+E)した結果を、コメント欄に書いてください。（書き方自由）

SELECT getdate();

• 2022-11-02 14:01:13.923

• ３－４
• 「VT_テスト生徒得点情報」テーブル使用
• 生徒コード「 1392 」の生徒の2012年2月に受けた、公開学力テストの
• 受験科目の合計得点、平均点、最高点、最低点、を出力してください。
• ※年月は実施日開始日で抽出。
• 1392 は存在しなかったので、16225を使用（ほかの問題で使用した） select sum(得点) as 合計得点, avg(得点) as 平均点, max(得点) as 最高点, min(得点) as 最低点

  from dbo.VT_テスト生徒得点情報 as a
  where 	生徒コード = 16225 and
  year(実施日開始日) = 2012 and 
  month(実施日開始日) = 2  and
  

  a.得点 <> 999

  group by 生徒コード
  

• ３－５
• ３－４の生徒の平均を少数第２位で四捨五入してください。 select sum(得点) as 合計得点, round(avg(得点),2) as 平均点, max(得点) as 最高点, min(得点) as 最低点

  from dbo.VT_テスト生徒得点情報 as a
  where 	生徒コード = 16225 and
  year(実施日開始日) = 2012 and 
  month(実施日開始日) = 2 and
  

  得点<>999

  group by 生徒コード
  

• ３－６
• ３－４の応用編。下記の様に抽出して下さい。
• 受験教室 学年コード 受験者数 合計 平均 最高点 最低点
• 101 2 73 5460.0 74.794520 95.0 20.0

select 受験教室コード as 受験教室,学年コード,

count(*) as 受験者数, sum(得点) as 合計, avg(得点) as 平均, max(得点) as 最高点, min(得点) as 最低点

from dbo.VT_テスト生徒得点情報 as a
where  得点 <> 999 and

year(実施日開始日) = 2012 and month(実施日開始日) = 2

group by 受験教室コード,学年コード
order by 受験教室コード,学年コード

• ３－７
• [列名] 現在の日付時刻　
• 翌日の日付時刻　
• 昨日の日付時刻
• 現在の年　
• 現在の月
• 現在の日 を表示して下さい。

SELECT getdate() as 現在の日付時刻, getdate()+1 as 明日の日付時刻, getdate()-1 as 昨日の日付時刻, year(getdate()) as 現在の年, month(getdate()) as 現在の月, day(getdate()) as 現在の日

• ３－８
• [列名] ３日前の日付時刻 ３日後の日付時刻
• ３ヶ月前の日付時刻 ３ヶ月後の日付時刻
• ３年前の日付時刻 ３年後の日付時刻 select getdate()-3 as 昨日の日付時刻, dateadd(m,3,getdate()) '３ヶ月後の日付時刻', dateadd(yy,3,getdate()) as '３年後の日付時刻', year(dateadd(yy,-8,getdate())) as '8年前の年';

• ３－９
• 「生徒塾種情報」テーブル使用
• 所属教室コード単位、状態区分単位で集約してください。
• ※各教室コードはVM_組織マスタ(塾種区分=1かつ組織タイプ=3)を参照。
• ※状態区分はVM_生徒種マスタ参照。
• [条件]　所属教室＝西宮、上本町、四条烏丸、西大寺、名古屋、和歌山　のみ。
• [列名] 所属教室コード ：　教室コード
• ‘西宮’‘上本町’ … ：　教室名
• 状態区分 　 ：　状態
• ‘塾生’‘塾外生’ … ：　状態名称
• （関数を使用して） ：　生徒数

select a.所属教室コード as 教室コード , b.組織名略称１ as 教室名,a.状態区分 as 状態 ,c.名称 as 状態名称,count(a.生徒コード) as 生徒数

from dbo.VM_生徒塾種情報 as a

join VM_組織マスタ as b on a.所属教室コード = b.組織KEY join VM_生徒種マスタ as c on a.状態区分 = c.生徒種区分 where (b.組織名略称１ like '%西宮%' or b.組織名略称１ like '%上本町%'or b.組織名略称１ like '%四条烏丸%'or b.組織名略称１ like '%西大寺%'or b.組織名略称１ like '%名古屋教室%'or b.組織名略称１ like '%和歌山%') and b.塾種区分 = 1 and b.組織タイプ= 3 group by a.所属教室コード , b.組織名略称１,a.状態区分,c.名称 order by 教室コード,　状態 desc;

• ３－１０
• 「VM_組織マスタ」使用
• [条件]　塾種区分＝1　かつ　組織タイプ＝3 select cast(組織KEY as varchar) +' ： ' +組織名略称１ as 教室一覧 from dbo.VM_組織マスタ where 塾種区分 = 1　and　組織タイプ = 3 order by 組織KEY;

• ３－１１
• 「VM_生徒受講情報」テーブル使用
• [条件]　2014年度かつ、講座コード＝5401かつ、
• 三田教室, 天王寺教室, 草津教室の生徒を検索してください。
• ただし、年度の取得は関数を使うこと。

select cast(year(開始日) as varchar)+' 年度' as 受講年度,b.組織名略称１ as 教室名, '小５　選択社会'as 講座名,クラスコード,生徒コード from dbo.VM_生徒受講情報 as a join VM_組織マスタ as b on a.教室コード = b.組織KEY where (b.組織名略称１ like '%三田%' or b.組織名略称１ like '%天王寺%' or b.組織名略称１ like '%草津%' ) and b.塾種区分 = 1 and b.組織タイプ= 3 and year(開始日) = 2014 and 講座コード = 5401 order by a.教室コード, クラスコード desc , 生徒コード;

• －１２
• 「生徒基本情報」テーブル使用 飛び級生のみ一覧を表示
• 浜　花　子　＊　→　浜　花　子　★ 　置換してください。（生徒帳票用氏名を置換）
• 　　　※ＳＥＬＥＣＴ文で置換！！ＵＰＤＡＴＥでの更新はしないでください。

select 生徒コード,replace(生徒帳票用氏名,'*','★') as 生徒帳票用氏名,a.生徒氏名カナ_半角 from dbo.VM_生徒基本情報 as a where a.生徒帳票用氏名 like '%*%';

• ３－１３
• ３－１２で「★マーク」に変換した、ＳＱＬ文をさらに
• 浜　花　子　★　→　浜花子★　と置換してください。 select 生徒コード,replace(replace(生徒帳票用氏名,'*','★'),' ','') as 生徒帳票用氏名,a.生徒氏名カナ_半角 from dbo.VM_生徒基本情報 as a where a.生徒帳票用氏名 like '%*%';

• ３－１４
• ３－３で抽出した生徒の得点を、
• 合計……少数第１位までを非表示、文字列に変換
• 平均……少数第２位を四捨五入、少数第１位までを表示、文字列に変換
• 最高点…文字列に変換
• 最低点…文字列に変換

select cast(cast(sum(得点)as decimal) as varchar) as 文字列型_合計, cast(round(avg(得点),2)as decimal(3,1)) as 文字列型_平均, cast(max(得点) as varchar) as 文字列型_最高点, cast(min(得点) as varchar)as 文字列型_最低点

from dbo.VT_テスト生徒得点情報 as a
where 	生徒コード = 16225 and
year(実施日開始日) = 2012 and 
month(実施日開始日) = 2  and

得点 <> 999

group by 生徒コード

• ３－１５ @@@@@@@@@@@@@@@@@@@@@@
• 「生徒基本情報」使用
• 生徒帳票用氏名に「＊」が何文字目に含まれているか検索してください。
• [条件] 飛び級生のみ
• [列名] 生徒コード、生徒帳票用氏名、何文字目
• ※SELECT句で、CHARINDEXを利用します。空白もカウントされます。

select a.生徒コード, a.生徒帳票用氏名,CHARINDEX('*',a.生徒帳票用氏名) as 何文字目 from dbo.VM_生徒基本情報 as a where a.生徒帳票用氏名 like '%＊%';

• ３－１６
• 「講座マスタ」使用
• ６年生が受講する講座の内で、講座名称「最高レベル」「女子トップレベル」「Ｍレベル」の
• 講座のみを抽出し、それぞれ「最・女・Ｍ」の文字が何文字目から含まれているかを検索し下記の表を参考に作成してください。
• [条件] 削除区分 ＝ 0
• ※SELECT句や、WHERE句で、CHARINDEXを利用します。

select 講座コード,charindex('最',講座名称)as 最高レベル,charindex('女',講座名称)as 女子トップ, charindex('Ｍ',講座名称)as Ｍレベル,講座名称 from dbo.VM_講座マスタ where (講座名称 like '%最高レベル%' or 講座名称 like '%女子トップレベル%' or 講座名称 like '%Ｍレベル%') and 削除区分 = 0 ;

---

• ３－１７
• 「講座マスタ」使用
• ６年生が受講する講座のうち、削除区分が0で
• 　最高レベル特訓 　 最レ
• 女子トップレベル特訓 トップレ
• Ｍレベル特訓 Ｍレ
• 選択社会 社会
• 日曜志望校別特訓（日曜特訓） 日特
• 灘中合格特訓 灘中
• 日曜錬成特訓 日錬
• の７つの講座のみを抽出し、「大分類」の列名を追加し、上記の値を設定して下さい。
• 大分類名 講座名称 講座コード
• Ｍレ 小６　Ｍレベル特訓　算数　 6308
• Ｍレ 小６　Ｍレベル特訓難問解消 6309

select case when 講座名称 like '%最高レベル特訓%' then '最レ' when 講座名称 like '%女子トップレベル特訓%' then 'トップレ' when 講座名称 like '%Ｍレベル特訓%' then 'Ｍレ' when 講座名称 like '%選択社会%' then '社会' when 講座名称 like '%日曜志望校別特訓（日曜特訓）%' then '日特' when 講座名称 like '%灘中合格特訓%' then '灘中' when 講座名称 like '%日曜錬成特訓%' then '日練' end as 大分類名, 講座名称, 講座コード from dbo.VM_講座マスタ where (講座名称 like '%最高レベル特訓%' or 講座名称 like '%女子トップレベル特訓%' or 講座名称 like '%Ｍレベル特訓%' or 講座名称 like '%選択社会%' or 講座名称 like '%日曜志望校別特訓（日曜特訓）%' or 講座名称 like '%灘中合格特訓%' or 講座名称 like '%日曜錬成特訓%' ) and 削除区分 = 0 group by 講座名称,講座コード order by 大分類名

• ３－１８
• 「教室別講座マスタ」使用
• 西宮教室で、2012年以降に始まる全ての講座を検索し下記の様に表示して下さい。
• 　　　ただし、来年もこのソース（SQL文）を利用できるよう、日付の書き方で「年度=2012」
• などと、固定で数値や文字を記述せず、関数を使用します。
• データは2014年までしかないので、2022年に「来月開講」とすると結果はゼロ件となる。
• ９年前の翌月にした

select year(開始日)as 年度, 教室コード, 講座コード, SUBSTRING(cast(開始日 as varchar),7,4)+'/'+left(開始日,2)+ '/01～' as '９年前の翌月開始日' from dbo.VM_教室別講座マスタ as a join dbo.VM_組織マスタ as b on a.教室コード = b.組織KEY where 開始日 >= cast(year(dateadd(yy,-9,getdate())) as varchar )+'-'+cast(month (dateadd(mm,1,getdate())) as varchar)+'-01' and b.組織名略称２ like '西宮' and b.塾種区分 = 1 order by 開始日;

• ３－１９
• 「VT_テスト生徒成績集計情報」使用
• 第４７０回公開学力テスト、６年生の３科目合計の平均偏差値をランキングしてください。
• 　※Ｅ-Ｒ図【成績情報】より、赤字の列名はWHERE句に記述します。
• 　　ただし、今回は生徒コードを指定しないので、記述しなくてもよい。

select rank() over (order by avg(偏差値) desc ) as 順位 , 年度, 回数, 受験教室コード, cast(round(avg(偏差値),2) as decimal(3,1))as 平均偏差値, 生徒コード from dbo.VT_テスト生徒成績集計情報 where 回数 = 470 and 学年コード = 6 group by 年度,回数,受験教室コード,生徒コード;

• ３－２０
• 「テスト生徒成績集計情報」使用
• ２０１２年４月、公開学力テストを上本町で受験した生徒を学年単位、大クラス単位で、
• 下記の様に、ランキングして下さい。平均偏差値は２科目。

select rank() over (order by 学年コード, 大クラスコード, avg(偏差値) desc ) as 順位 , 年度,回数,学年コード,大クラスコード, cast(round(avg(偏差値),2) as decimal(3,1))as 平均偏差値,生徒コード from dbo.VT_テスト生徒成績集計情報 as a join dbo.VM_組織マスタ as b on a.教室コード = b.組織KEY where 回数　= 469 and b.組織名略称２ like '上本' and b.塾種区分 = 1 and 年度 = 2012 and month(実施日開始日) = 4 group by 年度,回数,学年コード,大クラスコード,生徒コード order by 学年コード,大クラスコード,平均偏差値 desc;

• ３－２１
• 「生徒受講情報」使用
• 本年度、４年生受講講座「一般コース」を受講の生徒を、大クラス単位で、
• 下記の様に、ナンバリング（採番）して下さい。

select ROW_NUMBER() OVER(partition by 大クラスコード ORDER BY 大クラスコード,生徒コード),大クラスコード,生徒コード, year(a.開始日) as 年 from dbo.VM_生徒受講情報 as a where year(a.開始日) = 2012 and 講座コード = 4101 and 受講学年コード = 4 group by 大クラスコード,生徒コード,開始日

select * from dbo.VM_生徒受講情報 as a where year(a.開始日) = 2012 and 講座コード = 4101 and 受講学年コード = 4;