SQLの卒業課題問題集

あなたは、日本の某自動車メーカー の海外新規事業企画担当者です。

会社では、次世代の小型EVを海外市場に展開する計画があります。
あなたのミッションは、データベースを使って、進出候補国や販売拠点候補都市を調査することです。

※ところどころ研修中にお話ししていないSQL文法の知識が必要になるので、それを見極めて必要に応じてインターネットで調べたり講師に質問したりして問題を解くこと。

1. 検索

-- 1.1. まずは、進出候補国の情報を確認するため、country 表からすべての行のすべての列を取得しなさい。

<結果の例>

'ABW', 'Aruba', 'North America', 'Caribbean', '193.00', NULL, '103000', '78.4', '828.00', '793.00', 'Aruba', 'Nonmetropolitan Territory of The Netherlands', 'Beatrix', '129', 'AW'

-- 1.2. 市場規模を把握するため、country 表から国名(Name)と人口(Population)をすべて取得しなさい。

<結果の例>

'Aruba', '103000'

2. 算術演算子

-- 2.1. 各国の市場ポテンシャルを比較するため、国名(Name)と、人口(Population)とGNP(GNP)を掛け合わせた値を MarketPotential という別名で表示しなさい。

<結果の例>

Name, MarketPotential 'Aruba', '85284000.00' 'Afghanistan', '135774720000.00'

-- 2.2. 都市型EVの需要が高そうな国を探すため、国名(Name)と人口密度を PopulationDensity という別名で表示しなさい。

-- 計算式：人口(Population) ÷ 面積(SurfaceArea)

<結果の例>

name, PopulationDensity 'Aruba', '533.6788' 'Afghanistan', '34.8418'

3. 比較演算子

-- 3.1. 日本市場を基準として比較するため、国名(Name)が Japan のレコードを取得しなさい。

<結果の例>

'JPN', 'Japan', 'Asia', 'Eastern Asia', '377829.00', '-660', '126714000', '80.7', '3787042.00', '4192638.00', 'Nihon/Nippon', 'Constitutional Monarchy', 'Akihito', '1532', 'JP'

-- 3.2. 大規模市場の候補として、人口(Population)が2億人以上の国の国名(Name)と人口(Population)を取得しなさい。

<結果の例>

'China', '1277558000' 'Indonesia', '212107000' 'India', '1013662000' 'United States', '278357000'

-- 3.3. 中規模以上の市場として、人口(Population)が1億人以上1.5億人以下の国の国名(Name)と人口(Population)を取得しなさい。ただし、BETWEEN を使うこと。

<結果の例>

'Bangladesh', '129155000' 'Japan', '126714000' 'Nigeria', '111506000' 'Russian Federation', '146934000'

-- 3.4. 戦後に独立した国の市場を調査するため、独立年(IndepYear)が1947年または1955年の国の国名(Name)と独立年(IndepYear)を取得しなさい。

<結果の例>

'Germany', '1955' 'India', '1947' 'Pakistan', '1947'

-- 3.5. オセアニア地域の進出候補として、国コード(Code)が AUS または NZL の国の国名(Name)と独立年(IndepYear)を取得しなさい。

<結果の例>

'Australia', '1901' 'New Zealand', '1907'

-- 3.6. 政治体制や国家情報に欠損がある国を確認するため、首長(HeadOfState)が NULL の国の国名(Name)と独立年(IndepYear)を取得しなさい。

<結果の例>

'San Marino', '885'

-- 3.7. 国名(Name)が Ja で始まる国を調査対象として、国名(Name)とGNP(GNP)を取得しなさい。

<結果の例>

'Jamaica', '6871.00' 'Japan', '3787042.00'

-- 3.8. 国名(Name)にapaが含まれる国の名前(Name)と平均寿命(LifeExpectancy)を取得しなさい｡

<結果の例>

'Japan', '80.7'

-- 3.9. 国名(Name)がIで始まる4文字の国の名前(Name)とGNP(GNP)を取得しなさい｡

<結果の例>

'Iran', '195746.00' 'Iraq', '11500.00'

4. 論理演算子

-- 4.1. 人口が多く、かつ生活水準も高い国を候補にするため、人口(Population)が1億人以上、かつ平均寿命(LifeExpectancy)が80歳以上の国の国名(Name)、人口(Population)、平均寿命(LifeExpectancy)を取得しなさい。

<結果の例>

'Japan', '126714000', '80.7'

-- 4.2. 巨大市場または高寿命市場を候補にするため、人口(Population)が2億人以上、または平均寿命(LifeExpectancy)が80歳以上の国の国名(Name)、人口(Population)、平均寿命(LifeExpectancy)を取得しなさい。
<結果の例>

'Andorra', '78000', '83.5' 'China', '1277558000', '71.4' 'Indonesia', '212107000', '68.0'

-- 4.3. 国コード(Code)が AUS または NZL の国の国名(Name)と独立年(IndepYear)を取得しなさい。ただし、IN は使わず、論理和 OR を使うこと。

<結果の例>

'Australia', '1901' 'New Zealand', '1907'

-- 4.4. 国名(Name)が AまたはBで始まり､かつ大陸(Continent)がAで始まる国の名前(Name)と人口(Population)を取得しなさい｡　

<結果の例>

'Afghanistan', '22720000' 'Angola', '12878000'

-- 4.5. 大陸(Continent)がAで始まらない大陸を（重複なしに）取得しなさい｡　

<結果の例>

'North America' 'Europe' 'South America' 'Oceania'

5. 検索結果のソート

-- 5.1. 生活環境に課題がありそうな国を確認するため、平均寿命(LifeExpectancy)の小さい順に国名(Name)と平均寿命(LifeExpectancy)を取得しなさい。ただし、平均寿命(LifeExpectancy)が NULL のデータは除外すること。

<結果の例>

'Zambia', '37.2' 'Mozambique', '37.5'

-- 5.2. 高付加価値EVを販売しやすい可能性がある国を確認するため、平均寿命(LifeExpectancy)の大きい順に国名(Name)と平均寿命(LifeExpectancy)を取得しなさい。ただし、平均寿命(LifeExpectancy)が NULL のデータは除外すること。

<結果の例>

'Andorra', '83.5' 'Macao', '81.6'

-- 5.3. 歴史の古い国から順に市場調査を行うため、独立年(IndepYear)の古い順にすべての列を表示しなさい。ただし、独立年(IndepYear)が NULL のデータは除外すること。

<結果の例>

'CHN', 'China', 'Asia', 'Eastern Asia', '9572900.00', '-1523', '1277558000', '71.4', '982268.00', '917719.00', 'Zhongquo', 'People\'sRepublic', 'Jiang Zemin', '1891', 'CN' 'ETH', 'Ethiopia', 'Africa', 'Eastern Africa', '1104300.00', '-1000', '62565000', '45.2', '6353.00', '6180.00', 'YeItyop´iya', 'Republic', 'Negasso Gidada', '756', 'ET'

-- 5.4. 大陸ごとに市場を整理するため、大陸名(Continent)の昇順に並べ替え、大陸名(Continent)が同じ場合は国名(Name)の降順で、大陸名(Continent)と国名(Name)を取得しなさい。

<結果の例>

'Asia', 'Yemen' 'Asia', 'Vietnam' 'Asia', 'Uzbekistan'

-- 5.5. 独立年(IndepYear)が古い国の上位3件について、国名(Name)と独立年(IndepYear)を取得しなさい。ただし、独立年(IndepYear)が NULL のデータは除外すること。

<結果の例>

'China', '-1523' 'Ethiopia', '-1000' 'Japan', '-660'

-- 5.6. 人口上位国のうち、上位1〜5位は競争が激しいと考え、6位から10位の国を調査対象にする。人口(Population)の降順に並べ替え、6位から10位の5件について、国名(Name)と人口(Population)を取得しなさい。

<結果の例>

'Pakistan', '156483000' 'Russian Federation', '146934000' 'Bangladesh', '129155000'

-- 5.7. 経済成長している国を投資候補として探したい。GNP(GNP)が旧GNP(GNPOld)の1.5倍以上の国について、国名(Name)、GNP(GNP)、旧GNP(GNPOld)を取得し、GNP(GNP)の降順で表示しなさい。

<結果の例>

'Congo, The Democratic Republic of the', '6964.00', '2474.00' 'Estonia', '5328.00', '3371.00' 'Turkmenistan', '4397.00', '2000.00' 'Tajikistan', '1990.00', '1056.00'

6. 集約関数

-- 6.1. 調査対象となる国の数を確認するため、country 表のレコード件数を求めなさい。

<結果の例>

'239'

-- 6.2. 世界全体の生活水準のばらつきを確認するため、平均寿命(LifeExpectancy)の最大値、最小値、平均値を求めなさい。

<結果の例>

'83.5', '37.2', '66.48604'

-- 6.3. 世界全体の人口規模を確認するため、人口(Population)の合計を求め、TotalPopulation という別名で表示しなさい。

<結果の例>

TotalPopulation '6078749450'

-- 6.4. 人口(Population)と独立年(IndepYear)のデータ件数をそれぞれ求めなさい。

<結果の例>

count(Population), count(IndepYear) '239', '192'

-- 6.5. 6.4 の結果の件数に差が出た場合、その原因を説明しなさい。

7. 内部結合

7.1. 各国の首都を販売戦略上の重要拠点として確認するため、country 表と city 表を、country 表の首都(Capital)と city 表のID(ID)で内部結合し、国名(Name)と首都名(Name)を取得しなさい。

<結果の例>

'Aruba', 'Oranjestad' 'Afghanistan', 'Kabul'

-- 7.2. 人口の多い都市から販売拠点候補を探すため、country 表と city 表を国コードで内部結合し、国コード(Code)、国名(Name)、都市名(Name)、都市の人口(Population)を取得しなさい。なお、都市の人口(Population)の降順に並べ替えること。

<結果の例>

'IND', 'India', 'Mumbai (Bombay)', '10500000' 'KOR', 'South Korea', 'Seoul', '9981619' 'BRA', 'Brazil', 'São Paulo', '9968485'

8. レコードの挿入、更新、削除

-- 8.1. 架空のテスト販売都市として Beika 市を登録し、販売シミュレーションを行うことになった。city 表に以下の都市を挿入しなさい。

-- ID(ID)：4085
-- 都市名(Name)：Beika
-- 国コード(CountryCode)：JPN
-- 地域(District)：Tokyo
-- 人口(Population)：19138

<結果の例>

ID, Name, CountryCode, District, Population '4085', 'Beika', 'JPN', 'Tokyo', '19138'

-- 8.2. シミュレーション上、米花市で事件が発生し、人口(Population)が3人減少した。Beika 市の人口(Population)を3人減らしなさい。

<結果の例>

ID, Name, CountryCode, District, Population '4085', 'Beika', 'JPN', 'Tokyo', '19135'

-- 8.3. 上記の Beika 市の登録はテストデータだったため、物理削除しなさい。

【以下の問題は必須ではありませんので余裕のある場合に取り組んでください】

9. グループ化

-- 9.1. 大陸ごとの市場規模を把握するため、country 表から大陸(Continent)ごとの人口(Population)の合計を求め、大陸名(Continent)とともに表示しなさい。

<結果の例>

'North America', '482993000' 'Asia', '3705025700'

-- 9.2. 大陸ごとの経済規模を比較するため、country 表から大陸(Continent)ごとのGNP(GNP)の平均を求め、平均額の大きい順に表示しなさい。

<結果の例>

'North America', '261854.789189' 'Europe', '206497.065217'

-- 9.3. 販売・物流拠点として都市名の重複に注意する必要がある。city 表から都市名(Name)が重複している件数を取得し、duplicate_name_count という別名をつけ、都市名(Name)とともに表示しなさい。

-- なお、以下の条件を満たすこと。

-- ・重複件数が多い順に表示する
-- ・重複件数が同じ場合は都市名(Name)の昇順に表示する
-- ・重複していない都市名(Name)は表示しない。

<結果の例>

duplicate_name_count, name '4', 'San José' '3', 'Cambridge' '3', 'Córdoba' '3', 'Hamilton'

以上。