DataBase vol.13

• 復習テスト

問題1

社員テーブル(Employees)と、売上テーブル(Sales)から社員の2007年分の売上総数を
その社員ごとのIDと名前とともに表示しなさい。

SELECT
        e.EmployeeID,
        e.EmployeeName,
        COUNT(Quantity)
FROM
        Employees AS e,
        Sales AS s
WHERE
        e.EmployeeID = s.EmployeeID
GROUP BY
        s.EmployeeID
;

回答

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

• まず、なんでCOUNTしているのだろう。自分は。
• あと、期間が書かれていない。

問題2

部署移動が１回以上あった社員をIDと名前とともに表示しなさい。

SELECT
        e.EmployeeID AS "社員ID",
        e.EmployeeName AS "社員名",
        COUNT(b.DepartmentID) AS "移動回数"
FROM
        Employees AS e
         JOIN
        BelongTo AS b
         ON (e.EmployeeID = b.EmployeeID)
         JOIN
        Departments AS d
         ON (b.DepartmentID=d.DepartmentID)
GROUP BY
        b.DepartmentID
HAVING
        COUNT(b.DepartmentID) > 1
;

回答

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

問題3

在職中の社員について2006年度に支払われた給料合計を社員毎に表示しなさい。
ただし2006年度に在籍していなかった社員の給料合計は0として表示する。

SELECT
        e.EmployeeName AS "社員名",
        SUM(
          CASE
              WHEN s.EmployeeID IS NULL THEN 0
              ELSE s.Amount
          END
         ) AS "給料合計"
FROM
        Employees AS e
         JOIN
        Salary AS s
         ON (e.EmployeeID=s.EmployeeID)
WHERE
        s.PayDate BETWEEN "2006-01-01" AND "2007-01-01"
GROUP BY
        e.EmployeeID
;

回答

SELECT
        e.EmployeeID,
        e.EmployeeName AS "社員名",
        SUM(
          CASE
              WHEN s.Amount IS NULL THEN 0
              ELSE s.Amount
          END
         ) AS "給料合計"
FROM
        Employees AS e
         LEFT OUTER JOIN
        Salary AS s
         ON e.EmployeeID=s.EmployeeID AND '2006-01-01' <= s.PayDate AND s.PayDate < '2007-01-01'
GROUP BY
        e.EmployeeID
;

問題4

2007年度顧客別売上ランキングを表示しなさい。
ただし、表示の際は企業種別(個人/法人)と企業名をつなげて表示しなさい。
(例:法人ディノ) ※売上のない顧客は対象外とする。

SELECT
       SUM(p.Price) AS "売上",
       CONCAT(cc.CustomerClassName,c.CustomerName) AS "お客様名"
FROM
       Sales AS s,
       Customers AS c,
       CustomerClasses AS cc,
       Products AS p
WHERE
       s.CustomerID = c.CustomerID
       AND
       c.CustomerClassID = cc.CustomerClassID
       AND
       s.ProductID = p.ProductID
       AND
       s.SaleDate BETWEEN "2007-01-01" AND "2008-01-01"
GROUP BY
       s.ProductID
ORDER BY
       SUM(p.Price) DESC
;

回答

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

• 数量が入っていない。
• やけに安いからおかしいと思ったらそりゃそうだ。

問題5

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

回答

SELECT
        c.CategoryID,
        c.CategoryName,
        CEIL(AVG(s.Quantity*p.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
;