More than 3 years have passed since last update.

【技術書まとめ】『改訂3版すらすらと手が動くようになる SQL書き方ドリル WEB+DB PRESS plus』を読んだまとめ

Posted at 2021-01-25

感想

ネコのイラストがかわいいが内容は結構ガッチリしている。ドリルの穴埋め形式なのでそこまで詰まってしまうことはなかった。本当にさっぱりわからんときは答えもあるので。応用問題は普通に答えを見て書きました。

Amazonのレビューがあまり良くないが、SQLは理論の本が多く結局理解しても実際手が動かないことが多かったので、ドリル形式でなれるところがいい本だと思った。

参考になったところ

グループ化を行う場合、選択リストで許可されるのは、グループ化のキーとなる列名か、集合関数のみ

SELECT
SUM( A.Quantity ) AS 合計数量
, B.PrefecturalID
, MAX( C.PrefecturalName ) AS 県名
FROM
  Sales AS A
    JOIN
  Customers AS B
    ON A.CustomerID = B.CustomerID
      JOIN
    Prefecturals AS C
      ON B.PrefecturalID = C.PrefecturalID
GROUP BY
  B.PrefecturalID
;

JOINを使わないでサブクエリでひっぱってくる


SELECT
  EmployeeID
, EmployeeName
FROM
Employees
WHERE
  EmployeeID IN
  (
SELECT
EmployeeID
FROM
Salary
GROUP BY
EmployeeID
   HAVING
     MAX( Amount ) >= 300000
) ;

SELECT
SaleID
, Quantity
, CustomerID ,(
   SELECT
     CustomerName
   FROM
     Customers
   WHERE
     CustomerID = Sales.CustomerID
) AS 顧客名 FROM
Sales
WHERE
  Quantity >= 100
;

結合をJOINとWHEREで書き分ける

JOIN

SELECT
  A.Quantity
, B.CustomerName
, C.ProductName
, D.EmployeeName
FROM
  Sales AS A
    JOIN
  Customers AS B
    ON A.CustomerID = B.CustomerID
      JOIN
    Products AS C
      ON A.ProductID = C.ProductID
        JOIN
      Employees AS D
        ON A.EmployeeID = D.EmployeeID
WHERE
  A.Quantity >= 200
;

WHERE

SELECT
  A.Quantity
, B.CustomerName
, C.ProductName
, D.EmployeeName
FROM
  Sales AS A
, Customers B
, Products C
, Employees D
WHERE
  A.Quantity >= 200
  AND
  A.CustomerID = B.CustomerID
  AND
  A.ProductID = C.ProductID
  AND
  A.EmployeeID = D.EmployeeID
;

自己結合を使ってセット価格を出す

select
	p1.ProductName as 商品名1
	, p2.ProductName as 商品名2
	, ( p1.Price + p2.Price) as セット価格
from
	Products as p1
		JOIN
	Products as p2
		ON p1.ProductID < p2.ProductID
		AND p1.CategoryID <> p2.CategoryID
where
	( p1.Price + p2.Price) > 2500
;

この結合条件の不等号｢ON ～ < ～｣は、一度出現したレコードを2度と出さないため。この不等号｢<｣を否定演算子｢<>｣にすると、同じ組み合わせが何度も出てくる。

商品別の平均販売数量よりも多く売れている日を教えてくれ

相関サブクエリを使う


select
	p.ProductName
	, s1.SaleDate
from
	Sales AS s1
		JOIN
	Products AS p
		ON s1.ProductID = p.ProductID
where
	s1.Quantity > (
			select
				AVG(Quantity)
			from
				Sales AS s2
			where
				s1.ProductID = s2.ProductID
			)
order by
	p.ProductID, s1.SaleDate DESC
;

二つ以上の条件で絞りこんでどちらも表示する時はUNIONが使える

テーブルSalesの中から、CustomerClassID=2（個人）の顧客でQuantityが10以上のデータと、CustomerClassID=1（法人）の顧客でQuantityが100以上のデータをUNION ALLで1つにまとめなさい。SaleID、ProductID、Quantity、CustomerClassID、CustomerNameの各項目を表示しなさい（第3章その8第5問、その9第5問、その10第4問、その10第5問の結果と比較しなさい）。

select
	A.SaleID
	, A.ProductID
	, A.Quantity
	, B.CustomerClassID
	, B.CustomerName
from
	Sales as A
	JOIN
	(
		select
			CustomerID
			, CustomerClassID
			, CustomerName
		from
			Customers
		where
			CustomerClassID = 2
	) as B
	ON A.CustomerID = B.CustomerID
where
	Quantity >= 10
UNION ALL
select
	A.SaleID
	, A.ProductID
	, A.Quantity
	, B.CustomerClassID
	, B.CustomerName
from
	Sales as A
	JOIN
	Customers as B
	ON A.CustomerID = B.CustomerID
where
	B.CustomerClassID = 1
	and
	A.Quantity >= 100
;

更新前の値を使って更新する


UPDATE
    Products
SET
    ProductName = '<'||ProductName||'>'
;

売り上げ個数が100個を超える商品を1%値上げする（相関サブクエリを使って）

update
	Products
set 
  Price = Price * 1.01
where
  exists
    (
      select
        'X'
      from
        Sales as s
      where
        s.ProductID = Products.ProductID
      group by
        s.ProductID
      having
        sum(s.Quantity) > 100
    )
;

支払日を2007年度として、入社年度との差で1年ごとに1000円ボーナスに上乗せ

update
    Salary
set
    Amount = Amount + (
                       select
                           (2007 - e.HireFiscalYear) * 1000
                       from
                           Employees as e
                       where
                           Salary.EmployeeID = e.EmployeeID
                      )
where
    PayDate = '2008-02-14'
    and
    exists
          (
           select
               'X'
           from
               Employees as e
           where
               Salary.EmployeeID = e.EmployeeID
          )
;

相関サブクエリで合計個数を持ってくる

販売個数の累計が500個以上の商品について、ProductsテーブルのProductNameを次の文字列連結を使って修正しなさい。『'n個も売れてるヒット商品！' || ProductName』なお、n個の「n」には実際の販売個数の累計値をセットしなさい。


update
Products
set
ProductName = 
(
select
SUM(Quantity)
from
Sales
where
Products.ProductID = Sales.ProductID
)
|| '個も売れてるヒット商品！'
|| ProductName
where
(
select
SUM(s.Quantity)
from
Sales as s
where
Products.ProductID = s.ProductId
) >= 500
;

商品別・月別販売額一覧の作成

商品別に、月ごとの販売額の合計を一覧にして出しなさい。ただし、商品のカテゴリIDが1または3または9のものだけで構いません。合計が5000円以下のものも表示する必要はありません。なお、最近のものから月別に並べてください。
(出力項目：ProductID、ProductName、年月、販売合計金額；
出力順：ProductID、ProductName、年月（降順）)

SELECT
  p.ProductID
p.Price AS 販売金額
  , p.ProductName
, 年月
, SUM( s.Quantity * p.Price ) AS 販売合計金額 FROM
( SELECT
    ProductID
  , SUBSTR(CAST( SaleDate AS VARCHAR ), 1, 7)
  , Quantity
  FROM
Sales ) AS s
    JOIN
  Products AS p
    ON s.ProductID = p.ProductID
WHERE
  p.CategoryID IN (1, 3, 9)
GROUP BY
  p.ProductID
, p.ProductName , 年月
HAVING
  SUM( s.Quantity * p.Price ) > 5000
ORDER BY
  p.ProductID
, p.ProductName
, 年月 DESC
;

部門別・月別の給与

部門別・月別の給与の平均を一覧にして出しなさい。なお、2007年に支払われたものだけで構いません。
ただし部門は、給与支払日（PayDate）にその社員が所属していた部門としてください。
(出力項目：DepartmentID、DepartmentName、年月、平均給与；
出力順：DepartmentID、DepartmentName、年月)

select
	d.DepartmentID
	, d.DepartmentName
	, 年月
	, avg(Amount) as 平均給与
from
	(
	select
		EmployeeID
		, Paydate
		, substr(cast( PayDate as varchar), 1, 7) as 年月
		, Amount
	from
		Salary
	where
		substr(cast(PayDate as varchar), 1, 4) = '2007'
	) as s
		join
	BelongTo as b
		on s.EmployeeID = b.EmployeeID
		and s.PayDate >= b.StartDate
		and s.PayDate < case
					when b.EndDate is null then '9999-12-31'
					else b.EndDate
				end
			join
		Departments as d
			on b.DepartmentID = d.DepartmentID
group by
	d.DepartmentID
	, d.DepartmentName
	, 年月
order by
	d.DepartmentID
	, d.DepartmentName
	, 年月
;

月別・カテゴリ別（クロス集計)

月別・カテゴリ別の合計販売額を一覧にして出しなさい。なお、各月ごとに1行にまとめて、カテゴリは1から10までIDごとに集計して横に並べて表示しなさい。
(出力項目：年月、Ct1、Ct2、Ct3、Ct4、Ct5、Ct6、Ct7、Ct8、Ct9、Ct10；
出力順：年月)


select
	年月
	, sum(case
			when p.CategoryID = 1 then s.Quantity * p.Price
			else 0
		end) as Ct1
        , sum(case
			when p.CategoryID = 2 then s.Quantity * p.Price
			else 0
		end) as Ct1
from
	(
		select
			ProductID
			, substr(cast( SaleDate as varchar), 1, 7) as 年月
			, Quantity
		from
			Sales
	) as s
		join
	Products as p
		on s.ProductID = p.ProductID
group by
	年月
order by
	年月
;

商品別3ヶ月販売推移表

商品別に、2007年6月から8月までのそれぞれの月の販売額の推移を出しなさい。なお、7月と8月は前月に対して、増加・変化なし・減少のそれぞれについて、↑・→・↓でそれぞれ表現しなさい。
(出力項目：ProductID、ProductName、6月販売金額、7月販売金額、対6月増減、8月販売金額、対7月増減；
出力順：ProductID)

select
	ProductID
	, ProductName
	, "6月販売金額"
	, "7月販売金額"
	, case
		when "6月販売金額" < "7月販売金額" then '↑'
		when "6月販売金額" = "7月販売金額" then '→'
		else '↓'
	end as 対6月増減
	, "8月販売金額"
	, case
		when "7月販売金額" < "8月販売金額" then '↑'
		when "7月販売金額" = "8月販売金額" then '→'
		else '↓'
	end as 対7月増減
from
	(
	select
		p.ProductID
		, p.ProductName
		, sum(
			case when s.SaleDate is null then 0
				when substr(cast(s.SaleDate as varchar), 1, 7) = '2007-06'
					then s.Quantity * p.Price
				else 0
			end
			) as "6月販売金額"
		, sum(
			case when s.SaleDate is null then 0
				when substr(cast(s.SaleDate as varchar), 1, 7) = '2007-07'
					then s.Quantity * p.Price
				else 0
			end
			) as "7月販売金額"
		, sum(
			case when s.SaleDate is null then 0
				when substr(cast(s.SaleDate as varchar), 1, 7) = '2007-08'
					then s.Quantity * p.Price
				else 0
			end
			) as "8月販売金額"
	from
		Products as p
			left outer join
		Sales as s
			on p.ProductID = s.ProductID
	group by
		p.ProductID
		, p.ProductName
	)
order by
	ProductID

;

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

【技術書まとめ】『改訂3版 すらすらと手が動くようになる SQL書き方ドリル WEB+DB PRESS plus』を読んだまとめ

感想