Zenn
Closed24

達人に学ぶSQL徹底指南書第2版の練習問題

SQL
DB
#
RDB
むらむーむらむー

練習問題1-1

自分の回答

テーブル作成
create table Greatests(id varchar(128), x integer, y integer, z integer);

insert into Greatests(id, x, y, z) values
    ("A", 1, 2, 3),
    ("B", 5, 5, 2),
    ("C", 4, 7, 1),
    ("D", 3, 3, 8);
xとyの最大値取得
select
    id, 
    (case when x > y then x else y end) as greatest
from
    Greatests;
x,y,zから最大値
select
    id, 
    (case
        when x > y and x > z then x
        when y > z then y 
        else z
    end) as greatest
from
    Greatests;

回答との比較

WHENの中でCASE文も使うことができる

ただ、これは解説にもあるけど可読性は良くないなあ。。

行変換してMAX関数使うという手もある。

むらむーむらむー

練習問題1-2

自分の回答

テーブル作成
create table PopTbl2(pref_name varchar(128), sex integer, population integer);

insert into PopTbl2 values
    ("徳島", 1, 60),
    ("徳島", 2, 40),
    ("香川", 1, 100),
    ("香川", 2, 100),
    ("愛媛", 1, 100),
    ("愛媛", 2, 50),
    ("高知", 1, 100),
    ("高知", 2, 100),
    ("福岡", 1, 100),
    ("福岡", 2, 200),
    ("佐賀", 1, 20),
    ("佐賀", 2, 80),
    ("長崎", 1, 125),
    ("長崎", 2, 125),
    ("東京", 1, 250),
    ("東京", 2, 150);
クロス表の作成
select
    case sex when 1 then "男" else "女" end as 性別,
    sum(population) as 全国,
    sum(case pref_name when "徳島" then population else 0 end) as 徳島,
    sum(case pref_name when "香川" then population else 0 end) as 香川,
    sum(case pref_name when "愛媛" then population else 0 end) as 愛媛,
    sum(case pref_name when "高知" then population else 0 end) as 高知,
    sum(case pref_name
            when "徳島" then population
            when "香川" then population
            when "愛媛" then population
            when "高知" then population
            else 0
        end) as 四国（再掲）
from PopTbl2
group by 性別;

回答との比較

INを使うのは頭から抜けてた。。

最初、ORでやろうとしたけど

sum(case pref_name when "徳島" or "香川" or "愛媛" or "高知" then population else 0 end)

こんな書き方して、うまくいかなかった。単純CASE式と検索CASE式がごちゃごちゃになってしまっていた。。

むらむーむらむー

練習問題1-3

自分の回答

ソート順の条件分岐
select id
from Greatests 
order by
    case id
        when "B" then 1
        when "A" then 2
        when "D" then 3
        when "C" then 4
    end;

回答との比較

  • SELECT句にソート列として定義する方法もある
  • ただし、非推奨
    • 最初からソート用の列をテーブルに持たせた方がいい
    • SQLの仕事の基本はデータ検索
むらむーむらむー

練習問題2-1

テーブル作成
create table ServerLoadSample(
    server varchar(128),
    sample_date date,
    load_val integer
);

insert into ServerLoadSample values
    ("A", "2018-02-01", 1024),
    ("A", "2018-02-02", 2366),
    ("A", "2018-02-05", 2366),
    ("A", "2018-02-07", 985),
    ("A", "2018-02-08", 780),
    ("A", "2018-02-12", 1000),
    ("B", "2018-02-01", 54),
    ("B", "2018-02-02", 39008),
    ("B", "2018-02-03", 2900),
    ("B", "2018-02-04", 556),
    ("B", "2018-02-05", 12600),
    ("B", "2018-02-06", 7309),
    ("C", "2018-02-01", 1000),
    ("C", "2018-02-07", 2000),
    ("C", "2018-02-16", 500);
問題のSELECT文
select
    server,
    sample_date,
    sum(load_val) over () as sum_load
from ServerLoadSample;

予想

  • load_valと同じ値がsum_loadになる
  • カーソルの位置を移動させていないから

結果

server	sample_date	sum_load
A	2018-02-01	74448
A	2018-02-02	74448
A	2018-02-05	74448
A	2018-02-07	74448
A	2018-02-08	74448
A	2018-02-12	74448
B	2018-02-01	74448
B	2018-02-02	74448
B	2018-02-03	74448
B	2018-02-04	74448
B	2018-02-05	74448
B	2018-02-06	74448
C	2018-02-01	74448
C	2018-02-07	74448
C	2018-02-16	74448

全部の合計だったかあ。。

1行か、合計のどちらかになるかなとは思ってけど、オプションがないと全部が1つのパーティションの対象になるのか。

むらむーむらむー

練習問題2-2

問題のSELECT文
select
    server,
    sample_date,
    sum(load_val) over (partition by server) as sum_load
from ServerLoadSample;

予想

  • Aの行ならAの合計値、Bの行ならBの合計値、Cの行ならCの合計値が表示される
  • serverの種類の単位でパーティションが区切られるから

結果

server	sample_date	sum_load
A	2018-02-01	8521
A	2018-02-02	8521
A	2018-02-05	8521
A	2018-02-07	8521
A	2018-02-08	8521
A	2018-02-12	8521
B	2018-02-01	62427
B	2018-02-02	62427
B	2018-02-03	62427
B	2018-02-04	62427
B	2018-02-05	62427
B	2018-02-06	62427
C	2018-02-01	3500
C	2018-02-07	3500
C	2018-02-16	3500

予想通り♪

むらむーむらむー

ウィンドウ関数、今まで全然使ってこなかったから、使えそうな機会は積極的に使っていきたいな。

むらむーむらむー

練習問題 3-1

テーブル作成
create table Products(
    name varchar(128),
    price integer
);

insert into Products values
    ("りんご", 100),
    ("みかん", 50),
    ("バナナ", 80);
    
select * from Products;

自分の回答

重複組み合わせ
select
    P1.name as name_1,
    P2.name as name_2
from Products as P1
    inner join Products as P2 on P1.name >= P2.name;

回答と同じ♪

むらむーむらむー

練習問題 3-2

テーブル作成
create table Products(
    name varchar(128),
    price integer
);

insert into Products values
    ("りんご", 100),
    ("みかん", 50),
    ("みかん", 50),
    ("みかん", 50),
    ("バナナ", 80);
Productsテーブルにtmp_idを付与
select
    name,
    price,
    count(*) over tmp_id_w as tmp_id
from Products
window tmp_id_w as (order by name rows between unbounded preceding and current row);
自分の回答
delete P1 from (
    select name, price, count(*) over (order by name rows between unbounded preceding and current row) as tmp_id
    from Products
) as P1
where P1.tmp_id < (
    select name, price, count(*) over (order by name rows between unbounded preceding and current row) as tmp_id
    from Products P2
    where P1.name = P2.name and P1.price = P2.price
);

こんな感じのSQLを考えたけど、The target table P1 of the DELETE is not updatableとなってしまった。。

降参して回答との比較

  • create tableで実テーブル作ってた。。この考えが漏れてた。。
  • row_number()関数を使えばよかった、この関数知らなかった。。
  • ウィンドウ関数は partition by を使えばよかった
むらむーむらむー

演習問題 4-1

MySQLをよく使うので、MySQLのORDER BY句のソート順

https://dev.mysql.com/doc/refman/8.0/ja/working-with-null.html

ORDER BY を実行する場合、NULL 値は ORDER BY ... ASC では最初に表示され、ORDER BY ... DESC では最後に表示されます。

試しに、以下のSQLの実行してみた

order by の確認
create table Test(id integer, title varchar(100));
insert into Test(id, title) values(1, "Hello");
insert into Test(id, title) values(1, "");
insert into Test(id, title) values(1, null);
insert into Test(id, title) values(1, "World");
select * from Test order by title;
結果
id	title
1	NULL
1	
1	Hello
1	World
むらむーむらむー

演習問題 4-3

https://dev.mysql.com/doc/refman/8.0/ja/comparison-operators.html#function_coalesce

リストの最初の非 NULL 値を返します。非 NULL 値がない場合は、NULL を返します。

COALESCE() の戻り型は、引数型の集計型です。

例
SELECT COALESCE("Hello","", null, "World");
-> Hello

https://dev.mysql.com/doc/refman/8.0/ja/flow-control-functions.html#function_nullif

expr1 = expr2 が true の場合は NULL を返し、それ以外の場合は expr1 を返します。 これは、CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END と同じです。

むらむーむらむー

演習問題 5-1

テーブル作成
create table ArrayTbl(id char(1), i integer, val integer);

insert into ArrayTbl values
    ("A", 1, null),
    ("A", 2, null),
    ("A", 3, null),
    ("A", 4, null),
    ("A", 5, null),
    ("A", 6, null),
    ("A", 7, null),
    ("A", 8, null),
    ("A", 9, null),
    ("A", 10, null),
    ("B", 1, 3),
    ("B", 2, null),
    ("B", 3, null),
    ("B", 4, null),
    ("B", 5, null),
    ("B", 6, null),
    ("B", 7, null),
    ("B", 8, null),
    ("B", 9, null),
    ("B", 10, null),
    ("C", 1, 1),
    ("C", 2, 1),
    ("C", 3, 1),
    ("C", 4, 1),
    ("C", 5, 1),
    ("C", 6, 1),
    ("C", 7, 1),
    ("C", 8, 1),
    ("C", 9, 1),
    ("C", 10, 1);
自分の回答
select distinct AT1.id
from ArrayTbl AT1
where not exists (
    select AT2.val
    from ArrayTbl AT2
    where AT1.id = AT2.id
        and (AT2.val <> 1 or AT2.val is null)
);

回答とほぼ同じ♪

havingやallだと簡潔に書けるんだなあ。。

むらむーむらむー

演習問題 5-2

自分の回答
select distinct P1.project_id
from Projects P1
where "完了" = all (
    select P2.status
    from Projects P2
    where P1.project_id = P2.project_id
    and P2.step_nbr <= 1
)
and "待機" = all (
    select P3.status
    from Projects P3
    where P1.project_id = P3.project_id
    and P3.step_nbr > 1
);
  • 回答との比較
    • サブクエリのcaseで、step_nber <= 1 and status = "完了"のときと、step_nber > 1 and status = "待機"のときをそれぞれ定義してあげればよかった
    • 回答の方がサブクエリ1つだけで済むから、こっちのほうが良いね
むらむーむらむー

演習問題 5-3

select N1.num as prime_number
from Numbers N1
where N1.num > 1
and not exists (
    select N2.num
    from Numbers N2
    where N1.num > N2.num
    and N1.num % N2.num = 0
);

これでいけるかなと思ったけど、結果は何も返らず。。

回答と比較したら、サブクエリで、N2.num > 1するのが抜けていた。。

むらむーむらむー

演習問題 6-1

my answer
select
    case
        when count(*) = max(seq) then '歯抜けなし'
        else '歯抜けあり'
    end as gap
from SeqTbl;

回答との比較

  • UNION ALL使うのは思い浮かばなかった
むらむーむらむー

演習問題 6-2

全員が9月中に提出済みの学部（経済学部のみ）

create table
create table Students(student_id integer, dpt varchar(16), sbmt_date date);
insert into Students values
    (100, "理学部", "2018-10-10"),
    (101, "理学部", "2018-09-22"),
    (102, "文学部", null),
    (103, "文学部", "2018-09-10"),
    (200, "文学部", "2018-09-22"),
    (201, "工学部", null),
    (202, "経済学部", "2018-09-25");
my answer
select dpt
from Students
group by dpt
having count(*) = sum(
    case
        when sbmt_date < "2018-10-01" then 1
        else 0
    end
);

回答との比較

  • between使って9月に限定する
  • extract使って、年、月をそれぞれ確認する方法だと、日付気にせず確認できる
むらむーむらむー

演習問題 6-3

create table
create table Items(item char(16));
insert into Items values ("ビール"),("紙オムツ"),("自転車");

create table ShopItems(shop char(16), item char(16));
insert into ShopItems values
    ("仙台", "ビール"),
    ("仙台", "紙オムツ"),
    ("仙台", "自転車"),
    ("仙台", "カーテン"),
    ("東京", "ビール"),
    ("東京", "紙オムツ"),
    ("東京", "自転車"),
    ("大阪", "テレビ"),
    ("大阪", "紙オムツ"),
    ("大阪", "自転車");
my answer
select
    SI.shop,
    count(I.item) as my_item_cnt,
    (select count(*) from Items) - count(I.item) as diff_cnt
from ShopItems SI left join Items I on SI.item = I.item
group by SI.shop;

回答との比較

  • INNER JOINを使った方がよかった
むらむーむらむー

演習問題 7-1

setup
create table Accounts(prc_date date, prc_amt integer);
insert into Accounts values
    ("2018-10-26", 12000),
    ("2018-10-28", 2500),
    ("2018-10-31", -15000),
    ("2018-11-03", 34000),
    ("2018-11-04", -5000),
    ("2018-11-06", 7200),
    ("2018-11-11", 11000);
my answer
select
    A1.prc_date,
    A1.prc_amt,
    (
        select avg(A2.prc_amt) from (
            select A3.prc_amt
            from Accounts A3
            where A1.prc_date >= A3.prc_date 
            order by A3.prc_date desc limit 3
        ) A2
    ) prc_avg
from Accounts A1;

回答との比較

  • betweenでA2とA1の期間をずらして、それが３以下のものを取得していた、なるほど
むらむーむらむー

演習問題 7-2

my answer (window)
select
    prc_date,
    prc_amt,
    (case
        when (count(prc_amt) over w) < 3 then null
        else avg(prc_amt) over w
    end)
from Accounts
window w as (
    order by prc_date
    rows between 2 preceding and current row
);
my answer (sub query)
select
    A1.prc_date,
    A1.prc_amt,
    (
        select
            case
                when count(A2.prc_amt) < 3 then null
                else avg(A2.prc_amt)
            end
        from (
            select A3.prc_amt
            from Accounts A3
            where A1.prc_date >= A3.prc_date
            order by A3.prc_date desc limit 3
        ) A2
    ) prc_avg
from Accounts A1;

回答との比較

  • window関数の方はfromの中で平均とcountを取得するようにしていた。なるほど。
  • 相関サブクエリの方はhavingを使っていた
むらむーむらむー

演習問題 8-1

setup
create table TblAge(age_class integer, age_range varchar(16));
insert into TblAge values(1, "21~30歳"),(2, "31~40歳"),(3, "41~50歳");

create table TblSex(sex_cd char(8), sec varchar(16));
insert into TblSex values("m", "男"),("f", "女");

create table TblPop(pref_name char(8), age_class integer, sex_cd char(8), population integer);
insert into TblPop values
    ("秋田", 1, "m", 400),("秋田", 3, "m", 1000),("秋田", 1, "f", 800),("秋田", 3, "f", 1000),
    ("青森", 1, "m", 700),("青森", 1, "f", 500),("青森", 3, "f", 800),
    ("東京", 1, "m", 900),("東京", 1, "f", 1500),("東京", 3, "f", 1200),
    ("千葉", 1, "m", 900),("千葉", 1, "f", 1000),("千葉", 3, "f", 900);
my answer
select
    age_class,
    sex_cd,
    sum(
        case
            when pref_name in ("青森", "秋田") then population
            else null
        end
    ) as pop_tohoku,
    sum(
        case
            when pref_name in ("東京", "千葉") then population
            else null
        end
    ) as pop_kanto
from (
    select
        TblAge.age_class as age_class,
        TblSex.sex_cd as sex_cd,
        TblPop.pref_name as pref_name,
        TblPop.population as population
    from TblAge
        cross join TblSex
        left outer join TblPop
            on TblAge.age_class = TblPop.age_class
            and TblSex.sex_cd = TblPop.sex_cd
) as DATA
group by age_class, sex_cd;

回答と一緒♪

むらむーむらむー

演習問題 8-2

setup
create table Personal(employee char(16), child_1 char(16), child_2 char(16), child_3 char(16));
insert into Personal values
    ("赤井", "一郎", "二郎", "三郎"),
    ("工藤", "春子", "夏子", null),
    ("鈴木", "夏子", null, null),
    ("吉田", null, null, null);

create view Children(child)
as (
    select child_1 as child from Personal
    union all
    select child_2 as child from Personal
    union all
    select child_3 as child from Personal
);
my answer
select
    A.employee,
    sum(
        case
            when A.child is null then 0
            else 1
        end
    ) child_cnt
from (
    select distinct EMP.employee, Children.child
    from Personal EMP
        left outer join Children
            on Children.child in (EMP.child_1, EMP.child_2, EMP.child_3)
) A
group by A.employee;

回答との比較

  • count(Children.child)にすれば良かったのか。。
  • distinctしたテーブル使わないと重複行が集計されてしまうような。。？
    • viewを作るときにunion allしちゃってたからだった。。
むらむーむらむー

演習問題 8-3

setup
create table Class_A(id integer primary key, name varchar(16));
insert into Class_A values
    (1, "田中"),
    (2, "鈴木"),
    (3, "伊集院");

create table Class_B(id integer primary key, name varchar(16));
insert into Class_B values
    (1, "田中"),
    (2, "内海"),
    (4, "西園寺");
my answer
insert into Class_A(id, name)
    select Class_B.id, Class_B.name
    from Class_B
on duplicate key update Class_A.name = Class_B.name;

回答との比較

  • MERGE 使えって書いてあったのを無視してinsert on duplicate key にしてしまっていた。。
  • MERGE構文知らなかった
むらむーむらむー

演習問題 9-1

setup
create table tbl_A(
    key char(16) primary key,
    col_1 integer,
    col_2 integer,
    col_3 integer;
insert into tbl_A values
    ("A", 2, 3, 4),
    ("B", 0, 7, 9),
    ("C", 5, 1, 6);
    
create table tbl_B(
    key char(16) primary key,
    col_1 integer,
    col_2 integer,
    col_3 integer;
insert into tbl_A values
    ("A", 2, 3, 4),
    ("B", 0, 7, 9),
    ("C", 5, 1, 6);
my answer
select count(*) as union_row_cnt
from (
    select * from tbl_A
    union
    select * from tbl_B
) TMP
union all
select count(*) as union_row_cnt from tbl_A
union all
select count(*) as union_row_cnt from tbl_B;

回答との比較

  • case使って、各テーブルのcountと一致しているか判定しているのか。なるほど。
このスクラップは12日前にクローズされました