2005年04月17日

[ テクニカルエンジニア(データベース)/H17 ]

H17本試験午後I問3(会員管理システムのSQL文)

H17本試験 午後I 問3に関するコメント用

会員管理システムのSQL文

設問1(1) SQL文の穴埋め
a:COUNT(*)
b:GROUP BY 会員区分, 利用区分
c:ON 会員種別.会員区分 = 現会員.会員区分 AND 利用区分.利用区分 = 現会員.利用区分
 ※USING でも可?
d:利用年月日 BETWEEN
e:入会年月日 BETWEEN
f:退会年月日 BETWEEN
g:会員種別名

設問1(2) 外部結合の理由

設問1(3) 新規要件に対応するための列追加
テーブル名:会員
列の内容:親会員の会員番号

設問2(1) 集計結果の穴埋め

設問2(2) COALESCE関数の目的

Posted by g@kko at 2005/04/17 13:40 | 個別記事表示 | コメントを見る (28) |
この記事をLicWikiに埋め込む:
コメント
2 :9:05/04/17 17:05:03 [RES]

今年のSQLは何が出たのか?


3 :lei:05/04/17 18:16:29 [RES]

えーと、問3は会員管理におけるSQL文問題。
難易度は至って普通だと思います。
最初は帳票出力のためのSQL穴埋め問題。サブクエリーで集計してとか言う感じ。

SQLは自信あるのでこれはなんとかなったかな?

しかし、久しぶりに見ましたね、COALESCE関数。
わたしは、Oracle使いなのでCOALESCEは別の意味なんですよね・・・。
MS仕様のSQLは大の苦手・・・。 OUTER JOINとかいまだにぜーんぜんわからん。(SQL99はあまり知らない)


4 :natsuki:05/04/17 18:21:36 [RES]

確かに、後半のクロス集計の SQL は、僕は久しぶりどころか、初めて見ました・・・(@_@;)

う~ん、勉強不足を痛感・・・。


5 :前スレ973:05/04/17 19:15:26 [RES]

COALESCEなんて見たことも聞いたこともないよ~。
苦し紛れに「集計期間内に年齢が変化した会員の利用状況を集計開始時点で集計するため」って書いちゃったよ。
COALESCEミスですな。


6 :lei:05/04/17 19:21:21 [RES]

SQL ServerではCOALESCE
OracleではNVL ってことだよね?
OracleのCoalesceは、管理用の部分で使うキーワードの一つ。知らない人のほうが多いと思うけど。
他のDBMSは・・・、知らない。

っていうかCOALESCEのところはなんて書いたっけな? 多分利用履歴に存在しない年代、性別の場合、和がNULLになるからそれを回避するとか書いたような気がする。


7 :前スレ973:05/04/17 19:40:34 [RES]

SQLの関数なんてノーマークだったよ。素人にはきついよね。

LEFT OUTER JOIN の結合条件は、USINGを使って書いた。これは、ONよりスマートでしょ?


8 :lei:05/04/17 19:50:13 [RES]

へぇー、USINGをそこで使えるんですか??

実はあの空欄は何が入るんだ?? と最後まで考えちゃいましたが、問題文を見て、ピンと来ちゃいました。

情報処理試験のSQLはACCESSを勉強すれば関数も自然と身につきますけど、まあ普通は持ってないか。私は、数年前に業務でSQL Serverをちょっとだけ使ったので、それで覚えていましたけど。


9 :前スレ973:05/04/17 21:06:27 [RES]

USING と ON については、
http://tedb.gasakura.net/archives/entry/000705.phpを参照。
Cに突っ込むことについては、たぶん、あっていると思う。


10 :まるまさ:05/04/18 17:45:23 [RES]

はじめまして、テスト前数週間で発見してから毎日見てます。

私もCはUSINGだと思ったんですが、
項目名をカッコでくくるの忘れたよ・・・。orz


11 :9:05/04/18 18:13:29 [RES]

>>9
おおーアイテク模試的中じゃんw(´・∀・`)w
しかもここでそのガッコの突っ込みも見てれば確実


12 :前スレ973:05/04/18 19:16:31 [RES]

まあ、普通にONでも良かったんだけど・・・。


13 :おみおみ:05/04/18 20:58:25 [RES]

設問1(1)c iTacの答えは「ON」

斜線の四角の部分はテーブル名が異なるだけで内容は同じなので省略したというのがこの問題のミソなんですけどね。


14 :前スレ973:05/04/18 21:12:11 [RES]

iTacは一体どうなってるんだ?
ONだけじゃ足りないだろう。


15 :お悔やみ:05/04/18 21:43:47 [RES]

>14
確かに足らない。

あと、kは4であってる?0では?

ハンドルネームに飽きてきたので改名してみました。
縁起は良くなさそうだけど。


16 :前スレ973:05/04/18 22:07:21 [RES]

>>15
>あと、kは4であってる?0では?

う~ん、kはB3を集計したものだから、退館時刻が17:00より後ってことは、
利用履歴の3行目、4行目、6行目、8行目で4でいいのでは?


17 :前スレ973:05/04/18 22:09:57 [RES]

あっ、30代女か、俺勝手に、合計出してたよ。
でも、期せずしてiとjは一緒。


18 :沈没・・・:05/04/19 08:58:04 [RES]

>利用履歴の3行目、4行目、6行目、8行目で4でいいのでは?

最終的にグルーピングした結果を書くんじゃなかったっけ?


19 :おくやみ:05/04/19 20:30:49 [RES]

kは0でいいですよね?

あとhは一見会員数を算出するように見えますが、
A表とB表作って外部結合した時点で
30代女性のタプルは4つ。A.会員番号も4つ。
COUNTでDISTINCTしてないので答えは4じゃないでしょうか。

作成者がDISTINCT付け忘れたんじゃないかなと思ってます。


20 :おくやみ:05/04/19 20:48:18 [RES]

>19
ただこういう形の外部結合始めてみるんですよね。
この問題でいうと会員表のあるタプルの会員番号に一致するタプルが利用履歴表に複数ある場合もあるんですよね。(妙な日本語ですみません)

私は左外部結合なら左のテーブルの会員番号はNULLになることはないと思うのですが、どうなんでしょうかね。
資料がなくてちょっと自信がなくなってきました。


21 :DB01:05/04/19 21:42:40 [RES]

>>19
実際に問題文と同じテーブルとSQLを作成して実行してみたら?
そしたらGROUP BY と LEFT OUTER JOIN の意味も分かると思うし。


22 :DB素人:05/04/20 09:27:21 [RES]

どこかの模範解答だと、2、1、4、4ってなってますが、
この3つ目は、4じゃなくって、3じゃないですかね?

確か、1200以上、1700以下のレコードは4件で、
その内、3件が30台女のレコードだったような・・・

SQLは年代、性別毎にグルーピングするようになっていますし。


23 :ABC:05/04/20 16:42:20 [RES]

TACの模範解答で採点したら、50%~55%・・・

完全に沈没です。


24 :9:05/04/20 16:54:32 [RES]

>>23
ABC分析よm9(´・A・`)
まだ55%なら可能性あるヽ(`Д´;)ノ


25 :ABC:05/04/20 17:58:39 [RES]

皆、そんなにできなかったんですか?

それより、TACの解答、所々で疑問が・・・午後1問2なんて、
あれが正解なのかな???


26 :9:05/04/20 18:05:20 [RES]

逆に聞くが6割越えた奴っている(´・ω・)?
>>25
考えてもみいm9(´・A・`)6割越えた人なんてあまりいない。どう考えても半分以上の人が6割越えたとは思えない。午後1で4~5割の人を拾うとするなら、どうしたって足切りラインは下がる
俺の推測からすると、60%とかにすると午後1突破者は2割くらいしかいなくなるw
なんせあのガッコですら大苦戦したくらいだからな(`Д´;)

しっかし俺があれほど基礎理論最後にやれと言ってるにもかかわらず、ホント順番通り最初に取り掛かる奴が多いのな
だから問1でドつぼに嵌ったのが多かったんだろうな


27 :ABC:05/04/21 09:01:55 [RES]

>考えてもみいm9(´・A・`)6割越えた人なんてあまりいない。

昨年は8割、9割なんてのがゴロゴロいたけど、
今年は6割でも厳しいかもね。(うまく解けば7割くらい取れたと思うけど。)

でも、50%とかで通過させるとは思えないんだけど。。。

最近、情処の掲示板が少なくなってるから、皆の平均的な出来がわからないんだよね。


28 :9:05/04/21 09:35:09 [RES]

>>27
俺がこれだけ言ってるにもかかわらず、おまえはよほど落ちたいらしいなm9(´・A・`#)
じゃあいいよ確実に足切りABC分析にも入らないDランクだw

> 昨年は8割、9割なんてのがゴロゴロいたけど、
(゚Д゚)ハァ?むちゃくちゃな憶測やめてもらえないか?俺は67%で700超えたが、すると8割9割となるとそれこそ750越えてくるよな
去年の発表で750越えた奴なんていくらいるわけ?

そういや去年の午後2問2でも50%でも受かると豪語し続けたにもかかわらず、必死にそれじゃ落ちると意固地に否定し続けた奴らがいたな
蓋を開けてみたら予想通り(プゲラwww


29 :ABC:05/04/21 12:56:26 [RES]

>28
>俺がこれだけ言ってるにもかかわらず、おまえはよほど落ちたいらしいなm9(´・A・`#)

あんた何様?採点官?

>じゃあいいよ確実に足切りABC分析にも入らないDランクだw

俺、一応、高度2科目保持者です。(昨年はDB落ちたけど。)
自己採点も厳しいですから、最終的にはあなたより点数も高いとは思いますよ。ただ、あなたみたいに甘くは考えてないだけです。合否は別としてね。

>(゚Д゚)ハァ?むちゃくちゃな憶測やめてもらえないか?俺は67%で700超えたが、すると8割9割となるとそれこそ750越えてくるよな

あの簡単な問題で、67%が700のわけないでしょ。
公開された解答で採点し、80%が720くらいですよ。
まあ、選択問題によっても変動するかもしれないけどね。