DB(テスト),HTML - なんちゃってエンジニア

■DB(テスト)
■HTML(ブログアプリ制作)
今日も１日は、あっという間に過ぎた。
今日の午前中の集中力は半端無かった。
■DBテスト
Q1.社員テーブル(Employees)と、売上テーブル(Sales)から社員の2007年分の売り上げ総数をその社員ごとのIDと名前とともに表示しなさい。

[自分の解答]

SELECT
  EmployeeName
 ,E.EmployeeID
 ,SUM(Quantity)
FROM
  Employees AS E
 ,Sales AS S
WHERE
  E.EmployeeID = S.EmployeeID
  AND
  SaleDate LIKE "2007%"
GROUP BY
  EmployeeID
;

[模範解答]

SELECT
  e.EmployeeID
 ,e.EmployeeName
 ,SUM(Quantity)
FROM
  Sales AS s
 ,Employees AS e
WHERE
  s.EmployeeID = e.EmployeeID
  AND
  '2007-01-01' <= s.SaleDate
  AND
  s.SaleDate < '2008-01-01'
GROUP BY
  s.EmployeeID
;

自分の解答は2007年度という条件の作りかたが、無理矢理になってしまった。SaleDateが文字列ならばいいけど、ちょい邪道なやり方をしてしまった。

Q2.部署移動が１回以上あった社員をIDと名前とともに表示しなさい
[自分の解答]

SELECT
 　B.EmployeeID
　,EmployeeName
FROM
  BelongTo AS B
 ,Employees AS E
WHERE
  B.EmployeeID = E.EmployeeID
GROUP BY
  B.EmployeeID
HAVING
  COUNT(EndDate) >=1
;

[模範解答]

SELECT
  e.EmployeeID,
  e.EmployeeName
FROM
  Employees e,
  BelongTo b
WHERE
  e.EmployeeID = b.EmployeeID
GROUP BY
  e.EmployeeID
HAVING
 COUNT(e.EmployeeID) >1
;

１回以上という条件を、私の解答では COUNT(EndDate) >=1 として、EndDateそのものの数を数えているのに対して、模範解答では、COUNT(e.EmployeeID) >1 として部署移動が１回以上あったというのをEmployeeIDが２行以上ある人で絞り込みをしている。

Q3.在職中の社員について2006年度に在職していなかった社員の給与合計は０として表示する。
[自分の解答]

SELECT
  E.EmployeeName AS "社員名"
 ,SUM(Amount) AS "給料合計"
 FROM
   Employees AS E
    LEFT JOIN
  Salary AS S
    ON (E.EmployeeID = S.EmployeeID)
 GROUP BY
   E.EmployeeID
;

[模範解答]

SELECT
  E.EmployeeName AS "社員名"
 ,SUM(
     CASE
          WHEN S.Amount IS NULL THEN 0
          ELSE S.Amount
     END
    ) AS "給料合計"
FROM
  Employees AS E
    LEFT JOIN
  Salary AS S
    ON (E.EmployeeID = S.EmployeeID)
GROUP BY
 E.EmployeeID
;

この自分の解答では、2006年に在職していなかった給与合計が０として表示されない。LEFT JOIN ONを子分に条件をつけたい場合は、FROM句の中で指定する必要がある。WHEREを使うと、先に削られてしまうので2006年支払いはなかったが在職していた人まで削られてしまう。

Q4.2007年度顧客別売上ランキングを表示しなさい。ただし、表示の際は企業種別と企業名をつけて表示しなさい（例：法人ディノ）※売上のない企業は対象外とする
[模範解答]

SELECT
  CONCAT(cc.CustomerClassName, c.CustomerName),
  SUM(p.Price * s.Quantity) AS '売上'
FROM
  Sales s,
  Customers c,
  CustomerClasses cc,
  Products p
WHERE
  s.CustomerID =c.CustomerID AND
  c.CustomerClassID = cc.CustomerClassID AND
  s.ProductID = p.ProductID AND
  '2007-01-01' <= s.SaleDate < '2008-01-01'
GROUP BY
  s.CustomerID
ORDER BY
  SUM(p.Price * Quantity) DESC
;

４問目は時間がなくて解けませんでした。
Q5.個人事業主の顧客向けに販売された商品について、商品カテゴリ別平均売上を高い順に表示せよ。ただし、売上が計上されていない場合は０として扱う。また、商品カテゴリ別平均売上の小数点以下は切り上げとする。

SELECT
  c.CategoryID,
  c.CategoryName,
  CEIL(AVG(s.Quantity * Price)) AS "商品売上平均"
FROM
  Categories c
    LEFT JOIN
  Products p ON c.CategoryID = p.CategoryID
    LEFT JOIN
  Sales s ON p.ProductID =s.ProductID
GROUP BY
  c.CategoryID
;

５問目も時間がなくて解けませんでした。
５問目は、小数点以下の切り下げに「CEIL関数」を使うところが新しい項目でした。

[テストを受けた時の私の実況中継プレイバック]
まず、全問題にさらっと目を通す…うむ、難しそうだ…。
さぁ、一問目を解いてみよう…SELECT,FROM……。
解きおえて、さぁ実行してみる…。
エラーが出る…値がないって言ってるな…ん？なんで？あれ？
…………、とりあえずもう一回実行してみる…エラー。
よって、時間もあるので次の問題にいこう!!
次の問題を解き始める。解いた。実行する。
またしてもエラー!!………
よって、時間もあるので次の問題にいこう!!
次の問題を解き始める。解いた。実行する。
またしてもエラー!!
………どうしよう…。
とりあえず４、５問目は問題読んでもピンとこなかったので、
わかりそうな１、２、３問目を先につぶすか……。

ってな感じで問題を解き進め…時間切れゲームオーバー。

今日のテストは、教科書を見てもよかったし、ネットで答えを調べることも許されていました。今日のテストは、ただ理解度や応用力を試すだけではなく、わからないものにぶつかった時に一人の力で対処できるかという適応力を試されていたようです。

これはDBにおけることだけでは無いけど、わからないことを調べる力が私にはまだあまりないと思う。もっとネットとか色んなものを駆使できたら、もっと自分が楽になるんだろうなって思います。しかし、なんにせよネットというのは情報量が多すぎて、うわぁってなる時もあります。
でも、もっと調べる力をつけられたらいいな。

■最後のHTML
□ブログアプリの作成

今日でHTMLの講義が最後だなんて、授業が終わって数時間過ぎて知りました。HTMLは始め、文字の羅列ばかりで、それを書くという作業がキーボードになれていなかった私にとってとても苦痛で、あまり好きにはなれませんでした。しかし、今日は、やっと最後になってHTMLっておもしろいって思えました。というのも、ブログを作りHTMLで書き始めているのですが、昨日まではタイトルとちょっとしたリストが載ってるだけの、薄っぺらのページだったのが、リンクが付き、テキストを書く場所もあって、リンク先にも飛べて…といったように、自分がHTMLを書くことで、ページに色んな項目が増えていくことが当たり前なのに、なんか今日はやたらにそれが嬉しかったです。