GCIデータサイエンティスト育成講座
「GCIデータサイエンティスト育成講座」は、東京大学(松尾研究室)が開講している"実践型のデータサイエンティスト育成講座およびDeep Learning講座"で、演習パートのコンテンツがJupyterNoteBook形式で公開(CC-BY-NC-ND)されています。
Chapter8は「データベースとSQLの基礎」で、リレーショナルデータベース管理システムの操作方法を学習していきます。
日本語で学べる貴重で素晴らしい教材を公開いただいていることへの「いいね！」ボタンの代わりに、解いてみた解答を載せてみます。間違っているところがあったらご指摘ください。
Jupyter Notebook上でMariaDBを動作させる方法がわからなかったので、WSLにMySQLをインストールしてWSLのTerminal上でコマンド入力する形で進めました。
Chapter8 データベースとSQLの基礎
8.1 この章の概要
8.1.1 データベースとは
8.1.2 RDBMS
8.2 SQLの基礎
8.2.1 データベースとテーブルの作成
<練習問題 1>
セルを追加（左上にある＋を押下）して、今あるデータベースを表示させましょう。そして、新しいデータベース（TEST2など）を作って、そのデータベースを使うように選択しましょう。<練習問題 2>
練習問題１で作成、選択したデータベースに新しいテーブル「meibo2」を作成してください。作成後はテーブルが完成しているか、確認しましょう。<練習問題 3>
新しく作ったテーブルにデータを追加してみましょう。追加後は、確かにデータが入っているか確認してください。
/* 練習問題 1 */
create database TEST2;
use TEST2;
/* 練習問題 2 */
create table meibo2
(id int primary key, name varchar(20))
engine = MyISAM
default charset = utf8;
show tables;
/* 練習問題 3 */
insert into meibo2 (id, name) values (1, "Yamada");
select * from meibo2;
+-----------------+
| Tables_in_TEST2 |
+-----------------+
| meibo2 |
+-----------------+
1 row in set (0.00 sec)
+----+--------+
| id | name |
+----+--------+
| 1 | Yamada |
+----+--------+
1 row in set (0.00 sec)
8.2.2 データの検索と更新、削除、テーブルカラムの変更
[やってみよう]
上の条件式を色々と変更してデータを検索してみましょう。例えば、idが1以外の人や、nameがaで終わる人はどのように検索しますか？
select * from meibo where name like '%a';
+----+--------+
| id | name |
+----+--------+
| 1 | Yamada |
| 2 | Tanaka |
+----+--------+
2 rows in set (0.00 sec)
<練習問題 1>
TEST1のデータベースを選択して、上記のテーブルmeiboにて、id=4の人レコードを抽出してください。
select * from meibo where id=4;
+----+------+-------+------+
| id | name | class | age |
+----+------+-------+------+
| 4 | Kato | 2 | 15 |
+----+------+-------+------+
1 row in set (0.00 sec)
<練習問題 2>
上のmeiboテーブルでid=8の人のclassを7にアップデートしてください。select文でアップデートを確認できたら、id=8の人のclassを1に戻してください。
select * from meibo where id=8;
update meibo set class=7 where id=8;
select * from meibo where id=8;
update meibo set class=1 where id=8;
select * from meibo where id=8;
+----+------+-------+------+
| id | name | class | age |
+----+------+-------+------+
| 8 | Sato | 1 | 14 |
+----+------+-------+------+
1 row in set (0.00 sec)
+----+------+-------+------+
| id | name | class | age |
+----+------+-------+------+
| 8 | Sato | 7 | 14 |
+----+------+-------+------+
1 row in set (0.00 sec)
+----+------+-------+------+
| id | name | class | age |
+----+------+-------+------+
| 8 | Sato | 1 | 14 |
+----+------+-------+------+
1 row in set (0.00 sec)
<練習問題 3> (※必須問題：以降の問題で使います。)
上記と同じテーブルmeiboに新しい列heightという身長を追加してください。さらに、id=1から4までの人は150、id=5から6までの人は155、id=7から8までの人は160でアップデートしてください。
alter table meibo add height int;
update meibo set height=150 where id=1;
update meibo set height=150 where id=2;
update meibo set height=150 where id=3;
update meibo set height=150 where id=4;
update meibo set height=155 where id=5;
update meibo set height=155 where id=6;
update meibo set height=160 where id=7;
update meibo set height=160 where id=8;
select * from meibo;
+----+----------+-------+------+--------+
| id | name | class | age | height |
+----+----------+-------+------+--------+
| 1 | Yamada | 1 | 14 | 150 |
| 2 | Tanaka | 2 | 13 | 150 |
| 3 | Suzuki | 1 | 13 | 150 |
| 4 | Kato | 2 | 15 | 150 |
| 5 | Ito | 3 | 12 | 155 |
| 6 | Takeuchi | 2 | 16 | 155 |
| 7 | Kimura | 3 | 11 | 160 |
| 8 | Sato | 1 | 14 | 160 |
+----+----------+-------+------+--------+
8 rows in set (0.00 sec)
8.2.3 データの集計、演算、並び替え
<練習問題 1>
上記と同じmeiboテーブルで、異なる年齢は何種類でしょうか。
select count(distinct age) as ageCnt from meibo;
+--------+
| ageCnt |
+--------+
| 6 |
+--------+
1 row in set (0.00 sec)
<練習問題 2>
身長が一番小さい人、大きい人の身長をそれぞれ求めてください。
select min(height) as minHeight, max(height) as maxHeight from meibo;
+-----------+-----------+
| minHeight | maxHeight |
+-----------+-----------+
| 150 | 160 |
+-----------+-----------+
1 row in set (0.00 sec)
<練習問題 3>
身長が155以上で、classが3のレコードを抽出してください。
select * from meibo where height>=155 and class=3;
+----+--------+-------+------+--------+
| id | name | class | age | height |
+----+--------+-------+------+--------+
| 5 | Ito | 3 | 12 | 155 |
| 7 | Kimura | 3 | 11 | 160 |
+----+--------+-------+------+--------+
2 rows in set (0.00 sec)
8.2.4 グループごとの集計
<練習問題 1>
meiboテーブルにてクラスごとの平均身長を求めてみましょう。
select class, avg(height) as avgHeight from meibo group by class;
| class | avgHeight |
+-------+-----------+
| 1 | 153.3333 |
| 2 | 151.6667 |
| 3 | 157.5000 |
+-------+-----------+
3 rows in set (0.00 sec)
<練習問題 2>
上に加えて、クラスごとの人数、一番小さい人と、一番大きな人の身長もそれぞれのクラスで算出しましょう。
select class, avg(height) as avgHeight, min(height) as minHeight, max(height) as maxHeight from meibo group by class;
+-------+-----------+-----------+-----------+
| class | avgHeight | minHeight | maxHeight |
+-------+-----------+-----------+-----------+
| 1 | 153.3333 | 150 | 160 |
| 2 | 151.6667 | 150 | 155 |
| 3 | 157.5000 | 155 | 160 |
+-------+-----------+-----------+-----------+
3 rows in set (0.00 sec)
<練習問題 3>
年齢が13歳より上の人に絞って、上記と同じ項目を求めてください。
select class, avg(height) as avgHeight, min(height) as minHeight, max(height) as maxHeight from meibo where age>13 group by class;
+-------+-----------+-----------+-----------+
| class | avgHeight | minHeight | maxHeight |
+-------+-----------+-----------+-----------+
| 1 | 155.0000 | 150 | 160 |
| 2 | 152.5000 | 150 | 155 |
+-------+-----------+-----------+-----------+
2 rows in set (0.00 sec)
8.2.5 複数テーブルの利用
<練習問題 1>
新しくcardtbというテーブルを作成してください。ただし、カラムはid,point,money (すべてint)にしてください。さらに、以下のデータを挿入してください。
(id,point,money) = (1,100,1000),(2,NULL,60),(3,50,500),(4,30,600),(5,10,10),(6,NULL,40),(7,100,1000),(8,2000,100)
create table cardtb
(id int primary key, point int, money int)
engine = MyISAM
default charset = utf8;
insert into cardtb (id,point,money)
values (1,100,1000),(2,NULL,60),(3,50,500),(4,30,600),(5,10,10),(6,NULL,40),(7,100,1000),(8,2000,100);
select * from cardtb;
+----+-------+-------+
| id | point | money |
+----+-------+-------+
| 1 | 100 | 1000 |
| 2 | NULL | 60 |
| 3 | 50 | 500 |
| 4 | 30 | 600 |
| 5 | 10 | 10 |
| 6 | NULL | 40 |
| 7 | 100 | 1000 |
| 8 | 2000 | 100 |
+----+-------+-------+
8 rows in set (0.00 sec)
<練習問題 2>
meiboテーブルにidをキーとして、上記のテーブルを内部結合させてください。
select * from meibo as a join cardtb as b on a.id=b.id;
+----+----------+-------+------+--------+----+-------+-------+
| id | name | class | age | height | id | point | money |
+----+----------+-------+------+--------+----+-------+-------+
| 1 | Yamada | 1 | 14 | 150 | 1 | 100 | 1000 |
| 2 | Tanaka | 2 | 13 | 150 | 2 | NULL | 60 |
| 3 | Suzuki | 1 | 13 | 150 | 3 | 50 | 500 |
| 4 | Kato | 2 | 15 | 150 | 4 | 30 | 600 |
| 5 | Ito | 3 | 12 | 155 | 5 | 10 | 10 |
| 6 | Takeuchi | 2 | 16 | 155 | 6 | NULL | 40 |
| 7 | Kimura | 3 | 11 | 160 | 7 | 100 | 1000 |
| 8 | Sato | 1 | 14 | 160 | 8 | 2000 | 100 |
+----+----------+-------+------+--------+----+-------+-------+
8 rows in set (0.00 sec)
<練習問題 3>
meiboテーブルにidをキーとして、上記のテーブルを外部結合させてください。
/* 出力結果は練習問題 2と同じ */
select * from meibo as a left join cardtb as b on a.id=b.id;
/* select * from meibo as a right join cardtb as b on a.id=b.id; */
+----+----------+-------+------+--------+------+-------+-------+
| id | name | class | age | height | id | point | money |
+----+----------+-------+------+--------+------+-------+-------+
| 1 | Yamada | 1 | 14 | 150 | 1 | 100 | 1000 |
| 2 | Tanaka | 2 | 13 | 150 | 2 | NULL | 60 |
| 3 | Suzuki | 1 | 13 | 150 | 3 | 50 | 500 |
| 4 | Kato | 2 | 15 | 150 | 4 | 30 | 600 |
| 5 | Ito | 3 | 12 | 155 | 5 | 10 | 10 |
| 6 | Takeuchi | 2 | 16 | 155 | 6 | NULL | 40 |
| 7 | Kimura | 3 | 11 | 160 | 7 | 100 | 1000 |
| 8 | Sato | 1 | 14 | 160 | 8 | 2000 | 100 |
+----+----------+-------+------+--------+------+-------+-------+
8 rows in set (0.00 sec)
8.2.6 case文とサブクエリ
<練習問題 1>
meiboテーブルで、heightが155未満の場合は"below_155"、155ぴったりならば"equal_155"、155より大きいならば"over_155"と名付けて、テーブルを表示させてください。
select *
,case
when height<155 then "below_155"
when height=155 then "equal_155"
when height>155 then "over_155"
else "others"
end as heightLevel
from meibo;
+----+----------+-------+------+--------+-------------+
| id | name | class | age | height | heightLevel |
+----+----------+-------+------+--------+-------------+
| 1 | Yamada | 1 | 14 | 150 | below_155 |
| 2 | Tanaka | 2 | 13 | 150 | below_155 |
| 3 | Suzuki | 1 | 13 | 150 | below_155 |
| 4 | Kato | 2 | 15 | 150 | below_155 |
| 5 | Ito | 3 | 12 | 155 | equal_155 |
| 6 | Takeuchi | 2 | 16 | 155 | equal_155 |
| 7 | Kimura | 3 | 11 | 160 | over_155 |
| 8 | Sato | 1 | 14 | 160 | over_155 |
+----+----------+-------+------+--------+-------------+
8 rows in set (0.00 sec)
<練習問題 2>
上記のテーブルとサブクエリの考え方を利用して、それぞれのheightLevelの人数を求めてください。
select a.heightLevel, count(*) from
(select *
,case
when height<155 then "below_155"
when height=155 then "equal_155"
when height>155 then "over_155"
else "others"
end as heightLevel
from meibo) a group by a.heightLevel;
+-------------+----------+
| heightLevel | count(*) |
+-------------+----------+
| below_155 | 4 |
| equal_155 | 2 |
| over_155 | 2 |
+-------------+----------+
3 rows in set (0.00 sec)
<練習問題 3>
上記のテーブルとサブクエリの考え方を利用して、それぞれのheightLevelの平均年齢を求めてください。
select a.heightLevel, avg(age) from
(select *
,case
when height<155 then "below_155"
when height=155 then "equal_155"
when height>155 then "over_155"
else "others"
end as heightLevel
from meibo) a group by a.heightLevel;
+-------------+----------+
| heightLevel | avg(age) |
+-------------+----------+
| below_155 | 13.7500 |
| equal_155 | 14.0000 |
| over_155 | 12.5000 |
+-------------+----------+
3 rows in set (0.01 sec)
8.3 ビュー
8.3.1 ビュー
<練習問題 1>
テーブルmeibo2からidとnameを取り出して、ビューのv3を作成してみましょう。
create view v3 as select id, name from meibo2;
select * from v3;
+----+----------+
| id | name |
+----+----------+
| 1 | Yamada |
| 9 | Nagata |
| 10 | Sugino |
| 11 | Takayama |
| 12 | John |
+----+----------+
5 rows in set (0.00 sec)
<練習問題 2>
meiboテーブルとscoreテーブルを内部結合して、idとnameを取り出すビューv4を作成してみましょう。
create table TEST3 as select c.* from
(select a.* from meibo a join score b on a.id=b.id) c;
create view v4 as select id, name from TEST3;
select * from v4;
+----+----------+
| id | name |
+----+----------+
| 1 | Yamada |
| 2 | Tanaka |
| 3 | Suzuki |
| 5 | Ito |
| 6 | Takeuchi |
| 8 | Sato |
+----+----------+
6 rows in set (0.00 sec)
<練習問題 3>
上記で作成したテーブルに新しいデータを追加してみましょう。どうなりますか？
insert into TEST3 (id) values (11);
insert into TEST3 (id, name) values (12, "NANASHI");
select * from TEST3;
select * from v4;
+----+----------+-------+------+--------+
| id | name | class | age | height |
+----+----------+-------+------+--------+
| 1 | Yamada | 1 | 14 | 150 |
| 2 | Tanaka | 2 | 13 | 150 |
| 3 | Suzuki | 1 | 13 | 150 |
| 5 | Ito | 3 | 12 | 155 |
| 6 | Takeuchi | 2 | 16 | 155 |
| 8 | Sato | 1 | 14 | 160 |
| 11 | NULL | NULL | NULL | NULL |
| 12 | NANASHI | NULL | NULL | NULL |
+----+----------+-------+------+--------+
8 rows in set (0.00 sec)
+----+----------+
| id | name |
+----+----------+
| 1 | Yamada |
| 2 | Tanaka |
| 3 | Suzuki |
| 5 | Ito |
| 6 | Takeuchi |
| 8 | Sato |
| 11 | NULL |
| 12 | NANASHI |
+----+----------+
8 rows in set (0.00 sec)
8.4 総合問題
8.4.1 総合問題1
以下のデータベースを使って、後の問いに答えてください。(以下のURLにある注意事項等も参照ください。)
https://dev.mysql.com/doc/world-setup/en//// 中略 ///
(1) データベースworldを選択して、どのようなテーブルがあるか、またそれぞれどのようなカラムがあるか表示してください。
# ZDBというホストサーバーへのアクセス方法は知らないので…
# WSLの場合、Terminalから下記コマンドを入力
cd ~
wget http://downloads.mysql.com/docs/world.sql.gz
gzip -d world.sql.gz
mysql -h"localhost" -u root -p < world.sql
/* (1) */
use world;
show tables;
show columns from city;
show columns from country;
show columns from countrylanguage;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.01 sec)
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Continent | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO | | Asia | |
| Region | char(26) | NO | | | |
| SurfaceArea | decimal(10,2) | NO | | 0.00 | |
| IndepYear | smallint(6) | YES | | NULL | |
| Population | int(11) | NO | | 0 | |
| LifeExpectancy | decimal(3,1) | YES | | NULL | |
| GNP | decimal(10,2) | YES | | NULL | |
| GNPOld | decimal(10,2) | YES | | NULL | |
| LocalName | char(45) | NO | | | |
| GovernmentForm | char(45) | NO | | | |
| HeadOfState | char(60) | YES | | NULL | |
| Capital | int(11) | YES | | NULL | |
| Code2 | char(2) | NO | | | |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
15 rows in set (0.00 sec)
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3) | NO | PRI | | |
| Language | char(30) | NO | PRI | | |
| IsOfficial | enum('T','F') | NO | | F | |
| Percentage | decimal(4,1) | NO | | 0.0 | |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
(2) それぞれのテーブルについて、それぞれ5行ずつ表示させてください。どんなデータがあるか軽く確認してください。
/* (2) */
select * from city limit 5;
select * from country limit 5;
select * from countrylanguage limit 5;
+----+----------------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+---------------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
+----+----------------+-------------+---------------+------------+
5 rows in set (0.00 sec)
+------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+---------+-----------------------+----------------------------------------------+--------------------------+---------+-------+
| Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 |
+------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+---------+-----------------------+----------------------------------------------+--------------------------+---------+-------+
| ABW | Aruba | North America | Caribbean | 193.00 | NULL | 103000 | 78.4 | 828.00 | 793.00 | Aruba | Nonmetropolitan Territory of The Netherlands | Beatrix | 129 | AW |
| AFG | Afghanistan | Asia | Southern and Central Asia | 652090.00 | 1919 | 22720000 | 45.9 | 5976.00 | NULL | Afganistan/Afqanestan | Islamic Emirate | Mohammad Omar | 1 | AF |
| AGO | Angola | Africa | Central Africa | 1246700.00 | 1975 | 12878000 | 38.3 | 6648.00 | 7984.00 | Angola | Republic | José Eduardo dos Santos | 56 | AO |
| AIA | Anguilla | North America | Caribbean | 96.00 | NULL | 8000 | 76.1 | 63.20 | NULL | Anguilla | Dependent Territory of the UK | Elisabeth II | 62 | AI |
| ALB | Albania | Europe | Southern Europe | 28748.00 | 1912 | 3401200 | 71.6 | 3205.00 | 2500.00 | Shqipëria | Republic | Rexhep Mejdani | 34 | AL |
+------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+---------+-----------------------+----------------------------------------------+--------------------------+---------+-------+
5 rows in set (0.00 sec)
+-------------+------------+------------+------------+
| CountryCode | Language | IsOfficial | Percentage |
+-------------+------------+------------+------------+
| ABW | Dutch | T | 5.3 |
| ABW | English | F | 9.5 |
| ABW | Papiamento | F | 76.7 |
| ABW | Spanish | F | 7.4 |
| AFG | Balochi | F | 0.9 |
+-------------+------------+------------+------------+
5 rows in set (0.00 sec)
(3) cityテーブルについて、レコード数をカウントしてください。また、重複するデータがあるかどうか、チェックしてください。
/* (3) */
select count(*) from city;
select count(distinct id) from city;
select count(distinct name, countrycode, district) from city;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
1 row in set (0.00 sec)
+---------------------+
| count( distinct id) |
+---------------------+
| 4079 |
+---------------------+
1 row in set (0.00 sec)
+---------------------------------------------+
| count(distinct name, countrycode, district) |
+---------------------------------------------+
| 4078 |
+---------------------------------------------+
1 row in set (0.02 sec)
8.4.2 総合問題2
上記と同じデータベースを使って、以下の問いに答えてください。(1) countrylanguageテーブルについて、それぞれのCountryCodeを軸に、言語の数（テーブルにあるものだけ、重複なく）を計算してください。また、その結果をcityテーブルに紐付けて（joinして）、cityのカラム全てとその言語の数を表示してください。ただし、結果は5行のみ表示させるだけでよいです。
create view v1 as select CountryCode, count(distinct Language) from countrylanguage group by CountryCode;
select * from
city a
join
v1 b
on a.CountryCode = b.CountryCode
limit 5;
+-----+----------------+-------------+----------+------------+-------------+--------------------------+
| ID | Name | CountryCode | District | Population | CountryCode | count(distinct Language) |
+-----+----------------+-------------+----------+------------+-------------+--------------------------+
| 129 | Oranjestad | ABW | – | 29034 | ABW | 4 |
| 1 | Kabul | AFG | Kabol | 1780000 | AFG | 5 |
| 2 | Qandahar | AFG | Qandahar | 237500 | AFG | 5 |
| 3 | Herat | AFG | Herat | 186800 | AFG | 5 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | AFG | 5 |
+-----+----------------+-------------+----------+------------+-------------+--------------------------+
5 rows in set (0.00 sec)
(2) countrylanguageテーブルについて、それぞれのCountryCodeを軸に、Percentageを合計してください。ただし、その合計値が99.9を超えるものだけ表示させるようにしてください。
create view v2 as select CountryCode, sum(Percentage) from countrylanguage group by CountryCode;
select CountryCode, `sum(Percentage)` from v2 where `sum(Percentage)` > 99.9;
+-------------+-----------------+
| CountryCode | sum(Percentage) |
+-------------+-----------------+
| BHS | 100.0 |
| BMU | 100.0 |
| BTN | 100.0 |
| CHL | 100.0 |
| CPV | 100.0 |
| CRI | 100.0 |
| CUB | 100.0 |
| DMA | 100.0 |
| DOM | 100.0 |
| DZA | 100.0 |
| ECU | 100.0 |
| ESH | 100.0 |
| FRO | 100.0 |
| GRD | 100.0 |
| GRL | 100.0 |
| GUY | 100.0 |
| HTI | 100.0 |
| IRL | 100.0 |
| JPN | 100.0 |
| KNA | 100.0 |
| KOR | 100.0 |
| LCA | 100.0 |
| LSO | 100.0 |
| MDV | 100.0 |
| NLD | 101.0 |
| POL | 100.0 |
| PRK | 100.0 |
| PSE | 100.0 |
| RWA | 100.0 |
| SLV | 100.0 |
| SMR | 100.0 |
| TUV | 100.0 |
| WSM | 100.1 |
+-------------+-----------------+
33 rows in set (0.00 sec)
(3) cityテーブルについて、DistrictのイニシャルがAではじまるもの、Bではじまるもの、Cではじまるもの、その他(others)の4パターンに分けて、それぞれのレコード数をカウントしてください。
select a.Initial, count(a.Initial) from
(select *
,case
when left(Name, 1) = "A" then "Initial A"
when left(Name, 1) = "B" then "Initial B"
when left(Name, 1) = "C" then "Initial C"
else "others"
end as "Initial"
from city) a group by Initial;
+-----------+------------------+
| Initial | count(a.Initial) |
+-----------+------------------+
| Initial A | 260 |
| Initial B | 317 |
| Initial C | 281 |
| others | 3221 |
+-----------+------------------+
4 rows in set (0.02 sec)