SQLの問題集

以下の問題は､以下のworldデータベースに関してのものです。

※ところどころ研修中にお話ししていない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.country表から､国名と国の総GNPを求めTotalGNPという名前で表示しなさい。(TotalGNP:Population×GNP)

<結果の例>

Name, TotalGNP 'Aruba', '85284000.00' 'Afghanistan', '135774720000.00'

-- 2.2.country表から､国名と人口密度を求めPopulationDensityという名前で表示しなさい。(PopulationDensity:Population÷SurfaceArea)

<結果の例>

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

3.比較演算子

-- 3.1.country表から､国の名前(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.country表から､人口(Population)が2億人以上の国の名前(Name)と人口(Population)を取得しなさい｡

<結果の例>

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

-- 3.3.country表から､人口(Population)が1億人以上1.5億人以下の国の名前(Name)と人口(Population)を取得しなさい｡ただし､BETWEENキーワードを使うこと。

<結果の例>

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

-- 3.4.country表から､独立年(IndepYear)が1947年または1955年の国の名前(Name)と独立年(IndepYear)を取得しなさい｡

<結果の例>

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

-- 3.5.country表から､国コード(Code)が NZLまたはAUSの国の名前(Name)と独立年(IndepYear)を取得しなさい｡

<結果の例>

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

-- 3.6.country表から､首長(HeadOfState)がNULLの国の名前(Name)と独立年(IndepYear)を取得しなさい｡

<結果の例>

'San Marino', '885'

-- 3.7.country表から､国名(Name)がJaで始まる国の名前(Name)とGNP(GNP)を取得しなさい｡

<結果の例>

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

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

<結果の例>

'Japan', '80.7'

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

<結果の例>

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

4.論理演算子

-- 4.1.country表から､人口(Population)が1億人以上かつ平均寿命(LifeExpectancy)が80歳以上の国の名前(Name)と人口(Population)と平均寿命(LifeExpectancy)を取得しなさい｡

<結果の例>

'Japan', '126714000', '80.7'

-- 4.2.country表から､人口(Population)が2億人以上または平均寿命(LifeExpectancy)が80歳以上の国の名前と人口と平均寿命を取得しなさい｡
<結果の例>

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

-- 4.3.country表から､国コード(Code)が NZLまたはAUSの国の名前(Name)と独立年(IndepYear)を取得しなさい｡　ただし､論理和を使うこと｡

<結果の例>

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

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

<結果の例>

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

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

<結果の例>

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

5.検索結果のソート

-- 5.1.country表から平均寿命(LifeExpectancy)の小さい順に国の名前(Name)と平均寿命(LifeExpectancy)を取得しなさい。ただし、平均寿命がNULLのデータを除外すること。

<結果の例>

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

-- 5.2.country表から平均寿命(LifeExpectancy)の大きい順に国の名前(Name)と平均寿命(LifeExpectancy)を取得しなさい。ただし､平均寿命がNULLのデータを除外すること。

<結果の例>

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

-- 5.3.country表から独立年(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.country表から､大陸名（Continent）の昇順に並べ替え、大陸名が同じ場合は、国名（Name）の降順で並べ替えて、大陸名と国名を取得しなさい。

<結果の例>

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

-- 5.5.country表から､独立年（IndepYear）の昇順に並べ替え、上位3つの国名（Name）と独立年（IndepYear）を表示しなさい。ただし、独立年がNULLのデータを除外すること。

<結果の例>

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

-- 5.6.country表から､人口（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の降順で表示しなさい。

<結果の例>

'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.country表から各国の平均寿命(LifeExpectancy)の最大値､最小値､平均を求めなさい｡

<結果の例>

'83.5', '37.2', '66.48604'

-- 6.3.country表から人口(Population)の合計を求め、TotalPopulationという別名で表示しなさい｡

<結果の例>

TotalPopulation '6078749450'

-- 6.4.country表から人口(Population)と独立年(IndepYear)のデータの件数を求めなさい｡

<結果の例>

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

問:この結果の差は何が原因か答えなさい。

7.内部結合

-- 7.1.country表とcity表をcountry表の首都(Capital)とcity表のIDで結合させた内部結合で､country表の国名(Name)とcity表の市名を取得しなさい。

<結果の例>

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

-- 7.2.country表とcity表を国コード(CountryCode)で結合させた内部結合で､country表の国コード(Code)と国名(Name)､city表の市名(Name)と市の人口(Population)を取得し,市の人口の降順にならべかえなさい。

<結果の例>

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

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

-- 8.1.city表の最後に以下の都市を挿入しなさい。

<結果の例>

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

-- 8.2.米花市で殺人事件発生、人口を3人減らしなさい。

<結果の例>

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

-- 8.3.上記米花市のレコードの登録は間違いでした。物理削除しなさい。

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

9.グループ化

-- 9.1.country表から大陸(Continent)ごとの人口(Population)の合計を求め､大陸名とともに表示しなさい｡

<結果の例>

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

-- 9.2.country表から大陸(Continent)ごとのGNP(GNP)の平均を求め､平均額の大きい順に大陸名(Continent)とともに表示しなさい｡

<結果の例>

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

-- 9.3.city表から都市名(Name)が重複する件数を取得し、duplicate_name_countと名付け都市名とともに重複件数の降順に表示しなさい｡なお、重複件数が同じ場合は都市名の昇順に表示させること。

<結果の例>

duplicate_name_count, name '3', 'Cambridge' '3', 'Córdoba' '3', 'Hamilton'

10.単一行副問い合わせ

-- 10.1.country表から､人口が最大の国名(Name)と人口(Population)を取得しなさい。

<結果の例>

'China', '1277558000'

-- 10.2.country表から､人口が平均より多い国の名前(Name)と人口(Population)を取得しなさい。

<結果の例>

'Argentina', '37032000' 'Bangladesh', '129155000'

11.複数行副問い合わせ

-- 11.1．city表から人口(Population)が7,000,000人以上の都市の国コード（CountryCode）を取得し、country表からその都市がある国の国名（Name）を取得しなさい。（副問合せを使うこと）

＜結果の例＞

Brazil' 'China' 'United Kingdom'

-- 11.2．countrylanguage表から言語（Language）で日本語（Japanese）を話す人がいる国の国コード（CountryCode）を取得し、country表からその都市がある国の名前(Name)を取得しなさい。（副問合せを使うこと）

＜結果の例＞

'Brazil' 'Guam' 'Japan' 'United States'

-- 11.3．上記の10.2から更に絞り込みを行い日本語が公用語（IsOfficial）になっている国を取得しなさい。

＜結果の例＞

'Japan'

以上。