note4recurrent @ ウィキ

SQL-

最終更新：2022年11月04日 13:27

匿名ユーザー

- view

だれでも歓迎！編集

１－１
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_クラスマスタ;

１－６
生徒コード列が「301～320」の条件を満たす、
VM_生徒基本情報の全ての列を取得して下さい。

select * from dbo.VM_生徒基本情報 where 生徒コード between 301 and 320;

１－７
退塾の生徒（状態区分列が3）の条件を満たす、
VM_生徒塾種情報の全ての列を取得して下さい。

select * from dbo.VM_生徒塾種情報 where 状態区分 = 3;

１－８
受講年度列が2011かつ、
講座コード列が5100かつ、
教室コード列が201の条件を満たす、
VM_生徒受講情報の全ての列を取得して下さい。

select * from dbo.VM_生徒受講情報 where 受講年度= 2011 and 講座コード = 5100 and 教室コード = 201 ;

１－９
現在受講中である条件を満たす、
VM_生徒受講情報の全ての列を取得して下さい。
「現在受講中」とは、開始日列がNULL以外、終了日列がNULLの状態を指します。

select * from dbo.VM_生徒受講情報 where 開始日 is not NULL and 終了日 is NULL;

１－１０
塾種区分列が1かつ、組織タイプ列が3かつ、
組織KEY列が「850」「701」以外の条件を満たす、
VM_組織マスタの全ての列を取得して下さい。
但し、組織KEY列の条件は「IN」または「NOT IN」を使用して下さい。

select * from dbo.VM_組織マスタ where 組織KEY not in (850,701) and 塾種区分 = 1 and 組織タイプ = 3;

１－１１
塾種区分列が1かつ、組織タイプ列が3かつ、
組織KEY列が「850」「701」以外の条件を満たす、
VM_組織マスタの組織KEY列、組織名略称1列、組織名略称2列、組織名略称3列を取得して下さい。

select 組織KEY,組織名略称1,組織名略称2,組織名略称3 from dbo.VM_組織マスタ where 組織KEY not in (850,701) and 塾種区分 = 1 and 組織タイプ = 3;

１－１２
塾種区分列が1かつ、組織タイプ列が3かつ、
組織KEY列が「850」「701」以外の条件を満たす、
VM_組織マスタの組織KEY列、組織名略称1列、組織名略称2列、組織名略称3列を取得して下さい。
但し、組織KEY列は「教室コード」、組織名略称1列は「教室名」に変更して取得して下さい。

select 組織KEY as 教室コード,

組織名略称1 as 教室名, 組織名略称2 , 組織名略称3 from dbo.VM_組織マスタ where 組織KEY not in (850,701) and 塾種区分 = 1 and 組織タイプ = 3;

１－１３
教室コード列が101かつ、学年コード列が6の条件を満たす、
VM_クラスマスタのクラスコード列、クラス名称帳票用列、大クラスコード列を取得して下さい。
但し、クラスコード列は「クラス」、クラス名称帳票用列は「クラス名称」に変更して取得して下さい。

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

１－１４
飛び級生である、VM_生徒基本情報の全ての列を取得して下さい。
ここで、「飛び級生」は生徒帳票用氏名列に「*」が付いている状態を指します。

select * from dbo.VM_生徒基本情報 where 生徒帳票用氏名 = '*';

１－１５
性別列が「女子」（2）「男子」（1）の順、さらに生徒氏名カナ_半角列の昇順で
VM_生徒基本情報の全ての列を取得して下さい。

select * from dbo.VM_生徒基本情報 order by 性別 desc ,生徒氏名カナ_半角;

１－１６
VM_生徒基本情報の件数を取得して下さい。

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

１－１７
１性別が「女子」（2）の条件を満たす、VM_生徒基本情報の件数を取得して下さい。

select count(*) from dbo.VM_生徒基本情報 where 性別 = 2;

１－１８
VM_生徒基本情報の郵便番号1列を取得して下さい。 select 郵便番号1 from dbo.VM_生徒基本情報;

１－１９
VM_生徒基本情報の郵便番号1列をDISTINCT句を付けて取得して下さい。 select distinct 郵便番号1 from dbo.VM_生徒基本情報;

１－２０
VM_生徒基本情報の郵便番号1列をGROUP BY句を付けて取得して下さい。 select 郵便番号1 from dbo.VM_生徒基本情報 group by 郵便番号1;

１－２１
VM_生徒基本情報の郵便番号1列、郵便番号2列を取得して下さい。 select 郵便番号1,郵便番号2 from dbo.VM_生徒基本情報;

１－２２
VM_生徒基本情報の郵便番号1列、郵便番号2列をDISTINCT句を付けて取得して下さい。 select distinct 郵便番号1, 郵便番号2 from dbo.VM_生徒基本情報;

１－２３
VM_生徒基本情報の郵便番号1列、郵便番号2列をGROUP BY句を付けて取得して下さい。 select 郵便番号1,郵便番号2 from dbo.VM_生徒基本情報 group by 郵便番号1 , 郵便番号2;

１－２４
VM_生徒基本情報の郵便番号1列の値ごとの件数を取得して下さい。 select count(郵便番号1) from dbo.VM_生徒基本情報 group by 郵便番号1;

１－２４
VM_生徒基本情報の郵便番号1列の最大値を取得して下さい。 select max(郵便番号1) from dbo.VM_生徒基本情報

１－２５
VM_生徒基本情報の郵便番号1列の最小値を取得して下さい。 select min(郵便番号1) from dbo.VM_生徒基本情報

２－１
VM_学校マスタの全ての列を取得して下さい。 select * from dbo.VM_学校マスタ;

２－２
年度列が2014の条件を満たす、VM_学校マスタの全ての列を取得して下さい。

select * from dbo.VM_学校マスタ where 年度 = 2014;

２－３

年度列が2014かつ、男女区分列が1（男子校）の条件を満たす、VM_学校マスタの全ての列を取得して下さい。

select * from dbo.VM_学校マスタ where 年度 = 2014 and 男女区分 = 1;

２－４

年度列が2014かつ、男女区分列が2（女子校）の条件を満たす、VM_学校マスタの全ての列を取得して下さい。

select * from dbo.VM_学校マスタ where 年度 = 2014 and 男女区分 = 2;

２－５

年度列が2014かつ、男女区分列が3（共学校）の条件を満たす、VM_学校マスタの全ての列を取得して下さい。

select * from dbo.VM_学校マスタ where 年度 = 2014 and 男女区分 = 3;

２－６

年度列が2014かつ、男女区分列が2（女子校）かつ、
都道府県コード列が27（大阪府）の条件を満たす、VM_学校マスタの全ての列を取得して下さい。

select * from dbo.VM_学校マスタ where 年度 = 2014 and 男女区分 = 2 and 都道府県コード = 27;

２－７

年度列が2014かつ、男女区分列が2（女子校）かつ、
都道府県コード列が27（大阪府）かつ、
住所1列に「大阪市」を含む条件を満たす、VM_学校マスタの全ての列を取得して下さい。

select * from dbo.VM_学校マスタ where 年度 = 2014 and 男女区分 = 2 and 都道府県コード = 27 and 住所1 like '%大阪市%';

２－８

年度列が2014かつ、男女区分列が2（女子校）かつ、都道府県コード列が27（大阪府）かつ、
住所1列に「大阪市」を含むかつ、住所1列に「東大阪市」を含まない条件を満たす、
VM_学校マスタの全ての列を取得して下さい。

select * from dbo.VM_学校マスタ where 年度 = 2014 and 男女区分 = 2 and 都道府県コード = 27 and 住所1 like '%大阪市%' and 住所1 not like '%東大阪市%';

２－９

VM_個人別月謝情報の全ての列を取得して下さい。 select * from dbo.VM_個人別月謝情報;

２－１０

生徒コード列が16の条件を満たす、VM_個人別月謝情報の全ての列を取得して下さい。

select * from dbo.VM_個人別月謝情報 where 生徒コード = 16225;

２－１１

生徒コード列が16の条件を満たす、VM_個人別月謝情報の合計金額（金額列の合計）を取得して下さい。

select sum(金額) from dbo.VM_個人別月謝情報 where 生徒コード = 16225;

２－１２

生徒コード列が16の条件を満たす、VM_個人別月謝情報の年度ごとの（年度別の値ごとの）
合計金額（金額列の合計）を取得して下さい。

select 年度,sum(金額) from dbo.VM_個人別月謝情報 where 生徒コード = 16225 group by 年度;

２－１３

２－１１と２－１２の抽出結果をUNION句を使用して一覧表示して下さい。
但し、２－１１の年度列は「合計」と表示して下さい。

２－１１と２－１２の抽出結果をUNION句を使用して一覧表示して下さい。但し、２－１１の年度列は「合計」と表示して下さい。 select str(年度),sum(金額) from dbo.VM_個人別月謝情報 where 生徒コード = 16225 group by 年度 union select '合計' as 年度 ,sum(金額)as 合計 from dbo.VM_個人別月謝情報 where 生徒コード = 16225;

２－１４

VM_生徒塾種情報の状態区分ごとの件数を取得して下さい。
但し、状態区分ごとの状態名をCASE句を使用して以下の例のように表示して下さい。

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 状態区分;

２－１５

生徒コード列が16225かつ、年度列が2013かつ、
回数列が483を満たすVT_テスト生徒得点情報の全ての列を取得して下さい。

select * from dbo.VT_テスト生徒得点情報 where 生徒コード = 16225 and 回数 = 483;

２－１６

生徒コード列が16225かつ、年度列が2013かつ、回数列が483を満たす
VT_テスト生徒得点情報の得点列の合計と得点列の平均を取得して下さい。

select sum(得点)as 合計,avg(得点) as 平均 from dbo.VT_テスト生徒得点情報 where 生徒コード = 16225 and 年度 = 2013 and 回数 = 483;

２－１７

年度列が2013かつ、回数列が483を満たすVT_テスト生徒得点情報の教室コード列、
学年コード列ごとの得点列の件数、得点列の合計、得点列の平均、得点列の最大値、
得点列の最小値を取得して下さい。

select 教室コード, 学年コード, count(得点)as 件数, sum(得点) as 合計, avg(得点)as 平均, max(得点)as 最高, min(得点)as 最低

from dbo.VT_テスト生徒得点情報

group by 教室コード,学年コード order by 学年コード, 教室コード;

２－１８

VM_生徒基本情報の生徒帳票用氏名列と、VM_学校マスタの学校名を取得して下さい。
但し、VM_学校マスタの年度列は2014、VM_生徒基本情報の在籍校コードと
VM_学校マスタの学校コードをつないで下さい。

select child.生徒帳票用氏名,school.学校名 from dbo.VM_生徒基本情報 as child join dbo.VM_学校マスタ as school on child.在籍校コード = school.学校コード where school.年度 = 2014;

２－１９

VM_組織マスタの組織KEY列、組織名列、上位組織KEY列、
上位組織KEYの組織名（上位組織名）を取得して下さい。
但し、上位組織KEYが0の組織は抽出対象から除外して下さい。

select a.組織KEY, a.組織名,a.上位組織KEY,b.組織名 from dbo.VM_組織マスタ as a join dbo.VM_組織マスタ as b on a.上位組織KEY = b.組織KEY where a.上位組織KEY <>0 order by a.組織KEY;

２－２０

VM_講座マスタには講座コード列と翌年度講座コード列があります。
講座コード列が1100（小１　一般コース）のデータから、
翌年度講座コード列に該当する講座コード列のデータを
再帰のSQLを使用して取得し、小１から小６のデータを表示して下さい。（ヒント：「SQL 再帰」で検索）

with r as (select * from dbo.VM_講座マスタ where 講座コード = 1100 union all select dbo.VM_講座マスタ.* from dbo.VM_講座マスタ, r where r.翌年度講座コード = dbo.VM_講座マスタ.講座コード) select * from r;

https://www.letitride.jp/entry/2020/06/15/161826

３－１

VM_ユーザーマスタのユーザーIDには「ka-takemori」や「t-murotani」のように
「名前の先頭1文字または2文字-苗字」の形でデータが登録されています
（中には「syssekiguchi」のように途中にハイフンが入っていないデータもありますが無視して下さい）。
VM_ユーザーマスタのユーザーIDのハイフンとハイフンより前の文字を除去、すなわち、苗字のみの文字列に置き換えて、同じ苗字の件数の多い順、ユーザーIDの昇順に並び替えて表示して下さい。

select count(sub.uid)as 件数,uid from (select SUBSTRING(ユーザーID,charindex('-',ユーザーid)+1,50) as uid from dbo.VM_ユーザーマスタ) as sub group by sub.uid order by 件数 desc, sub.uid;

３－２
　VM_学校マスタには学校の情報が年度別に登録されています。
このうち、年度が2014で、都道府県別で学校コードが一番小さい学校のVM_学校マスタの全ての列を
都道府県コードの小さい順に表示して下さい。
但し、都道府県コードは1から47までの範囲に絞って下さい。 select * from dbo.VM_学校マスタ where 学校コード in (select min(学校コード) from dbo.VM_学校マスタ where 都道府県コード between 1 and 47
```
group by 都道府県コード) 
and dbo.VM_学校マスタ.年度　= 2014
```
order by 都道府県コード

３－３

VM_銀行マスタの情報が、VM_銀行支店マスタには支店の情報が登録されています。
　銀行コードごとの支店数をVM_銀行支店マスタから取得し、
支店数が50以上の銀行コード、銀行名称全角、支店数を支店数の多い順に表示して下さい。
但し、VM_銀行マスタとVM_銀行支店マスタの結合はINNER JOINを使用して下さい。

select c.銀行コード,c.銀行名称全角,count(c.銀行名称全角) as 支店数 from (select a.銀行コード, a.銀行名称全角 from dbo.VM_銀行マスタ as a inner join dbo.VM_銀行支店マスタ as b on a.銀行コード = b.銀行コード) as c where c.銀行名称全角 <> '' group by c.銀行コード,c.銀行名称全角 having count(c.銀行名称全角) >= 50 order by 支店数 desc;

３－４

　VM_個人別月謝情報には生徒ごとの月謝の情報が登録されています。
　生徒ごとに年度単位の金額の合計を集計し、年度ごとに最も多くの
金額を支払っている生徒の生徒コードと金額の合計を年度の昇順で表示して下さい。

with z as( select * from (select 年度,生徒コード,sum(金額)as 金額 from dbo.VM_個人別月謝情報 group by 生徒コード, 年度) b where 金額 in (select max(a.金額)as 金額 from (select 年度,生徒コード,sum(金額)as 金額 from dbo.VM_個人別月謝情報 group by 生徒コード, 年度) as a group by a.年度)) select y.* from (select 年度,max(金額) as 金額 from z group by 年度) as x inner join z as y on y.年度 = x.年度 and y.金額 = x.金額 order by y.年度

３－５

　VM_住所マスタには住所の情報が登録されています。
　大阪市や神戸市のように、区を持つ市は市区町村名が「〇〇市〇〇区」で登録されていますが、
VM_住所マスタを使用して、区を持つ市を区数の多い順に表示して下さい。

select left(b.市区町村名,charindex('市',b.市区町村名)) as 市名,count(b.市区町村名)as 区数 from (select a.市区町村名 from dbo.VM_住所マスタ as a where charindex('区',a.市区町村名) > 0 and charindex('市',a.市区町村名) >0 group by a.市区町村名) as b group by left(b.市区町村名,charindex('市',b.市区町村名)) order by 区数 desc

３－６

　VM_生徒志望校情報には生徒ごとの志望校の情報が登録されています。
　VM_生徒志望校情報の志望校ごとの合計（志望数）を集計し、
志望校とVM_学校マスタとの学校コードと紐付けて志望数の多い上位10校の学校コード、
学校名、志望数を表示して下さい。また、学校名については年度が2014のものを表示して下さい。

select top(10) c.志望校 as 学校コード, c.学校名 , count(c.学校名) as 件数 from(select a.志望校 , b.学校名 from dbo.VM_生徒志望校情報 as a inner join dbo.VM_学校マスタ as b on a.志望校 = b.学校コード where b.年度 =2014) as c group by c.志望校, c.学校名 order by 件数 desc

タグ：

+ タグ編集