Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@harutoman3

「SQL Bolt」の解答・解説（日本語訳）

Posted at

ブラウザ上でなんの準備もいらずにSQLの練習ができる神サイト、SQLBoltの解答・解説を日本語で書いていきます。

進め方

下記サイトに移動し、道なりに進めるだけでOKです。
実行ボタンやコマンドなどが存在せず、書いたさきから自動で実行されていくのが新鮮ですね。書くことだけに集中できるので素晴らしい。
ちなみに問題文の下の青文字の「Solution」をクリックすると答えが出てきます。

レッスンスタート

楽しんでいきましょう。SQLBoltはLessonとかExerciseとかが混同していますが、まあそこはフィーリングで。
あとSQLは小文字でも大文字でも関係なく動くので、疲れたら解答は小文字で書きます。実務などのオフィシャルな場ではだいたい大文字で書かれているみたいですが、なぜなのでしょう？

Lesson1

「検索せよ」は、「表示せよ」とか「列挙せよ」とかいう意味も含みます。Find の絶妙な訳を探し中。
Lesson1はSELECT句の基礎です。
「とりあえずテーブルの中身をみたい！」ってときに使います。

問題文の日本語訳

  1. 各映画の「Title」を検索せよ。
  2. 各映画の「Director」を検索せよ。
  3. 各映画の「Title」と「Director」を検索せよ。
  4. 各映画の「Title」と「Year」を検索せよ。
  5. 各映画のすべての情報を検索せよ。

解答

Task1
Task1の解答
SELECT Title FROM Movies;
Task2
Task2の解答
select director from movies;
Task3
Task3の解答
select title, director from movies;
Task4
Task4の解答
select title, year from movies;
Task5
Task5の解答
select * from movies;

解説

「SQLのSELECT句はこう書きます」というやつでした。とりあえずテーブルの中身を見たいときに実行することが多いです。「*」で全ての列をSELECTすることが出来ます。

Lesson2

Lesson2はWHERE文の基礎です。
WHEREは条件でフィルタリングしたいときに使います。

問題文の日本語訳

  1. 「Id」が6の行の映画を検索せよ。
  2. 「Year」が2000~2010の間の映画を検索せよ。（意訳）
  3. 「Year」が2000~2010の間ではない映画を検索せよ。（意訳）
  4. 映画を5つ、公開年度が早い順に検索せよ。（あまりにも意訳、この通りにやると少し難しい）

解答

Task1
Task1の解答
select * from movies
where id = 6;
Task2
Task2の解答
select * from movies
where 2000 <= year and year <= 2010;
Task3
Task3の解答
select * from movies
where not (2000 <= year and year <=2010);
Task4
Task4の解答
select * from movies
order by year
limit 5;

解説

「SQLのWHERE文はこう書きます」というやつでした。Task4は悪問です。SQLBoltはSQLiteなので、TOP文が使えないですね。

Lesson3

Lesson3はWHERE句の練習の続きです。文字列が登場します。

問題文の日本語訳

  1. Toy Storyシリーズの映画を検索せよ。
  2. John Lasseterが監督した映画を検索せよ。
  3. John Lasseter以外の人間が監督した映画を検索せよ。
  4. 「Title」がWALL-となっている映画を検索せよ。

解答

Task1
Task1の解答
select * from Movies
where Title like '%Toy Story%';
Task2
Task2の解答
select * from Movies
where Director = 'John Lasseter';
Task3
Task3の解答
select * from Movies
where Director != 'John Lasseter';
Task4
Task4の解答
select * from movies
where Title like '%WALL-%';

解説

WHERE文の文字列検索の練習です。いろいろな書き方があります。

Lesson4

Lesson4はORDER句の練習です。
並び順を変えたりします。

問題文の日本語訳

  1. 「Director」を重複のないように、アルファベット順で列挙せよ。
  2. 映画を最新のものから順に4つ列挙せよ。
  3. アルファベット順に並べて上から5つ列挙せよ。
  4. (上を受け）次の5つを列挙せよ。

解答

Task1
Task1の解答
select distinct Director from Movies
order by Director;
Task2
Task2の解答
select * from movies
order by Year desc
limit 4;
Task3
Task3の解答
select * from movies
order by Title
limit 5;
Task4
Task4の解答
select * from movies
order by Title
limit 5 offset 5;

解説

「ORDER BY {列名}」で、列名の昇順に並び替えることができます。その後に「DESC」をつけると降順になります。

Review-1

ここは復習のコーナーです。

問題文の日本語訳

  1. カナダの都市とその人口を列挙せよ。
  2. アメリカのすべての都市を、北から南の順になるように並べよ。
  3. シカゴより西に位置するすべての都市を、西から東の順になるように並べよ。
  4. メキシコの都市で人口が多い上位2つを列挙せよ。
  5. アメリカの都市で、人口が3番目、4番目に多いものを列挙せよ。

解答

Task1
Task1の解答
select city, population from north_american_cities
where Country = 'Canada';
Task2
Task2の解答
select * from North_american_cities
where country = "United States"
order by latitude DESC;
Task3
Task3の解答
select * from North_american_cities
where Longitude < (select Longitude from north_american_cities where City = 'Chicago')
order by Longitude;
Task4
Task4の解答
select * from North_american_cities
where Country = 'Mexico'
order by Population desc
limit 2;
Task5
Task5の解答
select * from North_american_cities
where Country = 'United States'
order by Population desc
limit 2 offset 2;

解説

Task3は複雑です（元サイトの解答は少しずるしてる）。
WHERE句で比較したい値をテーブルからSELECTして使うことが出来ます。
この段階ではやや発展的なので「へ～」くらいで良いかもしれません。

一旦休憩

お疲れ様でした。Part1終了といったところでございます。
次からはテーブルが複数出てきたりします。

Lesson6

Lesson6は内部結合、すなわちJOIN(=INNER JOIN)句の練習です。
JOINは複数のテーブルを結合するときに使います。

問題文の日本語訳

  1. 各映画のDomestic_sales(国内売上)、International_sales(海外売上)を、Titleと併せて表示せよ。
  2. 国内売上より海外売上の方が大きい映画を検索せよ。
  3. 各映画をレーティングの降順に並べよ。

解答

Task1
Task1の解答
select Title, Domestic_sales, International_sales
from Movies
join Boxoffice 
on Movies.Id = Boxoffice.Movie_id;
Task2
Task2の解答
select Title, Domestic_sales, International_sales
from Movies
join Boxoffice 
on Movies.Id = Boxoffice.Movie_id
where Domestic_sales < International_sales;
Task3
Task3の解答
select Title, Rating
from Movies
join Boxoffice 
on Movies.Id = Boxoffice.Movie_id
order by Rating desc;

解説

  1. 内部結合「JOIN(INNER JOIN)」によるテーブルの結合の基本的な書き方です。「ON」以降で結合する際にキーの役割を果たす列を指定します。
  2. 内部結合によって作られたテーブルに対してWHERE句を使用することができます。
  3. ORDER BYも使えます。

Lesson7

Lesson7は外部結合の練習です。思うままに書いてみて出力を眺め、どんな風に結合されているのかを確認すると良いと思います。

問題文の日本語訳

  1. 従業員がいる建物名を検索せよ（重複無し）。
  2. 建物名とそのキャパシティを検索せよ。
  3. 建物名(Building_name)とその建物に属する従業員の役割(Role)の組み合わせを重複のないように列挙せよ。ただし、従業員がいない建物についても、役割を空欄にして表示させること。（かなり意訳）

解答

Task1
Task1の解答
SELECT distinct building FROM employees;
Task2
Task2の解答
SELECT * FROM Buildings;
Task3
Task3の解答
select distinct Building_name, Role 
from Buildings
left join Employees
on Buildings.Building_name = Employees.Building;

解説

  1. これはひっかけです。「ふふっw」となっておきましょう。
  2. 笑いすら起きません。outer joinの練習したい気持ちが高まります。
  3. LEFT JOINの基礎です。左のテーブルを基準にして、右のテーブルをくっつける感じです。なので右のテーブルからやってきた列には空欄が含まれることもあります。

Lesson8

Lesson8はテーブル上で空欄になっているデータを扱う練習です。

問題文の日本語訳

  1. 建物に割り当てられていない全ての従業員の名前(NAME)と役割(ROLE)を検索せよ。
  2. 従業員がいない建物の名前(Building_name)を検索せよ。

解答

Task1
Task1の解答
select Name, Role 
from Employees
where Building is null
Task2
Task2の解答
select distinct Building_name
from Buildings
left join Employees
on Buildings.Building_name = Employees.Building
where Name is null;

解説

  1. nullはこんな風に判定します。
  2. Buildingsテーブルを基準にして、建物名をキーにEmployeesテーブルを結合します。すると、従業員がいない建物、つまり、Employeesテーブルに登場しない建物については、Employeesの情報が空の状態でレコードが生成されます。それを選べばOKです。

Lesson9

Lesson9は計算を含むSQLクエリの練習です。

問題文の日本語訳

  1. 全ての映画のタイトルと、その映画の売上の合計を100万ドル単位で表示した値を列挙せよ。
  2. 全ての映画のタイトルと、その映画のレーティングをパーセント表記したものを列挙せよ。
  3. 偶数年に公開された映画を列挙せよ。

解答

Task1
Task1の解答
select Title, (Domestic_sales + International_sales)/1000000
from Movies
  join Boxoffice 
    on Movies.Id = Boxoffice.Movie_id;
Task2
Task2の解答
SELECT title, rating * 10 AS rating_percent
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id;
Task3
Task3の解答
select * from Movies
where Year%2 =0;

解説

  1. 計算した値を表示したいときは露骨のその式を書けばOKです。
  2. レーティング×10がパーセントって常識なのか？答え見なきゃわからん。それは置いといて、AS {列名}とすることで、出力されるテーブルの列名を指定することが出来ます。
  3. SELECTするもの以外に対しても計算式を入れることが出来ます。

Lesson10

Lesson10は集計関数の練習です。いろいろあります。

問題文の日本語訳

  1. 最長の雇用年数(Years_employed)を検索せよ。
  2. それぞれの役割(Role)に対し、その役割を担う従業員の雇用年数の平均を計算して表示せよ。
  3. それぞれの建物(Building)に対し、そこに属する従業員の雇用年数の合計を計算して表示せよ。

解答

Task1
Task1の解答
SELECT MAX(Years_employed) FROM Employees;
Task2
Task2の解答
SELECT Role, AVG(Years_employed) FROM Employees
GROUP BY Role;
Task3
Task3の解答
SELECT Role, AVG(Years_employed) FROM Employees
GROUP BY Role;

解説

  1. SELECTした列名をMAX()の引数にすると、その列の最大値にあたるレコードだけを抽出できます。
  2. SELECTした列名をAVG()の引数にすると、その列の平均値を取得出来ます。さらにGROUP BY {列名}とすると、その列でグルーピングできます。
  3. SUM()もあります。

Lesson11

Lesson11も集計関数です。ちょっと難しくなります。

問題文の日本語訳

  1. 役割(Role)がArtistである従業員の人数を出力せよ。
  2. 役割(Role)ごとの従業員数を出力せよ。
  3. エンジニアの雇用年数の合計を出力せよ。

解答

Task1
Task1の解答
select role, count(*) from Employees
where Role = 'Artist';
Task2
Task2の解答
select Role, count(*) from Employees
group by Role;
Task3
Task3の解答
select Role, sum(Years_employed) from Employees
where Role = 'Engineer';

解説

  1. COUNT()でレコードの行数をカウント出来ます。かっこの中身には「*」「{列名}」「distinct {列名}」などいろいろ入れられます。詳細はwebで。
  2. GROUP BY {列名}で、その列にいる値でグループ化出来ます。
  3. Years_employedの合計を、Roleでフィルタリングした状態で表示しています。

Lesson12

問題文の日本語訳

  1. 監督ごとの映画の本数を検索せよ。
  2. 監督ごとに、映画の売上げの合計を検索せよ。

解答

Task1
Task1の解答
select Director, count(*) 
from Movies
group by Director;
Task2
Task2の解答
select Director, sum(Domestic_sales + International_sales) as total_sales
from Movies
join Boxoffice
on Movies.Id = Boxoffice.Movie_id
group by Director;

解説

  1. count(*)を監督ごとにグルーピングすればOK。
  2. 結合と集計関数とGROUP BYの合わせ技です。まずは監督と映画の売上を紐付けるためにテーブルを結合し、それに対して集計関数とグループ化を適用するイメージです。

Lesson13

Lesson13はレコード(行)の追加です。

問題文の日本語訳

  1. Moviesテーブルに「Toy Story 4」を追加せよ。(Title以外は適当でOK)
  2. Boxofficeテーブルに「TOy Story 4」に関する情報を示すレコードを追加せよ。ただし、Rating=8.7, Domestic_sales = 340000000, International_sales = 270000000とする。

解答

Task1
Task1の解答
INSERT INTO Movies
VALUES(4, 'Toy Story 4', 'Harutoman3', 2024, 4);
Task2
Task2の解答
INSERT INTO Boxoffice
VALUES(4, 8.7, 340000000, 270000000);

解説

  1. こう書けばレコードを追加出来ます。
  2. Movies_idはToy Story 4のものと同じにしましょう。

Lesson14

Lesson14はレコードの更新です。

問題文の日本語訳

  1. 「A Bug'a Life」の監督(Director)が間違っている。「John Lasseter」に修正せよ。
  2. 「Toy Story 2」の公開年(Year)が間違っている。1999に修正せよ。
  3. 「Toy Story 8」のタイトル(Title)と監督(Director)が間違っている。それぞれ「Toy Story 3」、「Lee Unkrich」に修正せよ。

解答

Task1
Task1の解答
UPDATE Movies
SET Director = "John Lasseter"
WHERE Title = "A Bug's Life";
Task2
Task2の解答
UPDATE Movies
SET Year = 1999
WHERE Title = "Toy Story 2";
Task3
Task3の解答
update Movies
set Title = 'Toy Story 3', Director = 'Lee Unkrich'
where Title = 'Toy Story 8';

解説

  1. UPDATEの基礎的な書き方はこれです。WHEREで行を指定しないと全ての行が更新されてしまいます。
  2. はい。
  3. 複数箇所変更した場合は「,」区切りで並べれば良いです。

Lesson15

Lesson15は行の削除です。

問題文の日本語訳

  1. このテーブルは大きすぎる。出版年(Year)が2005年より前のレコードを全て削除せよ。
  2. 「Andrew Stanton」がスタジオを去るので、彼が監督(Director)になっているレコードを全て削除せよ。

解答

Task1
Task1の解答
DELETE FROM Movies
WHERE Year < 2005;
Task2
Task2の解答
delete from Movies
where Director = 'Andrew Stanton';

解説

  1. DELETEの基礎です。
  2. はい。

Lesson16

Lesson16はテーブルの作成です。

問題文の日本語訳

  1. 「Database」という名のテーブルを作成せよ。ただし列は以下の通りとする。
  • Name:データベースの名前を表す文字列
  • Version:データベースのバージョンを表す浮動小数点
  • Download_count:ダウンロードされた回数を表す整数

解答

Task1
Task1の解答
CREATE TABLE Database (
    Name TEXT,
    Version FLOAT,
    Download_count INTEGER
);

解説

1.こう書けばテーブルが作られます。

Lesson17

Lesson17はテーブルの変更です。レコードの削除や追加、更新なく、テーブル列そのものを変更します。

問題文の日本語訳

  1. 各映画が公開されたときのアスペクト比を保存するために、列「Aspect_ratio」をFLOAT型で追加せよ。
  2. TEXT型の列「Language」を追加せよ。ただしデフォルト値は「English」とする。

解答

Task1
Task1の解答
ALTER TABLE Movies
ADD COLUMN Aspect_ratio FLOAT;
Task2
Task2の解答
ALTER TABLE Movies
ADD COLUMN Language TEXT DEFAULT "English";

解説

  1. こう書けば追加出来ます。
  2. デフォルト値を設定できます。

Lesson18

Lesson18ではテーブルそのものを削除するクエリを習います。
いよいよおしまいですね。さみしいです。噛み締めていきましょう。

問題文の日本語訳

  1. Moviesテーブルを削除せよ。
  2. Boxofficeテーブルを削除せよ。

解答

Task1
Task1の解答
DROP TABLE Movies;
Task2
Task2の解答
DROP TABLE IF EXISTS Movies;

解説

  1. はい。これで消せます。
  2. 丁寧に、あんぜんに書くとこうです。

終わり

お疲れ様でした。全レッスン修了です。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?