SQL練習1-SQLZoo 1-3章誤題整理

重点文法
concat, replace, mid, xor, round, left, right, <>
チュートリアル1 SELECT_names
住所:https://sqlzoo.net/wiki/SELEC...
表構造world(name,continent)name:国名continent:洲
Q & A 13.すべての首都とその国の名前を探し出して、首都は国家の名前が現れなければなりません.

SELECT capital,name
    FROM world
    WHERE capital LIKE concat('%',name,'%')

困難:concatの文法に詳しくない.CONCAT関数は2つの文字列を1つの文字列に接続できますが、2つ以上の文字については?

知識点:CONCAT(id,name,work_date)、パラメータは2つ以上

15.「Monaco-Ville」は合併した国の名前で、「Monaco」と延長語「-Ville」がつながっている.首都が国の名前の延長であるなど、国の名前とその延長語を表示します.SQL関数REPLACEまたはMIDを使用することができる.
使用MID

SELECT name,MID(capital,length(name)+1) as extend
    FROM world
    WHERE capital LIKE concat(name,'_%')

使用REPLACE

SELECT name, REPLACE(capital,name,'')
    FROM world
    WHERE capital LIKE concat(name,'_%')

知識点:

(1)MID():テキストフィールドから文字を抽出します.

構文:MID(column_name,start[,length])パラメータ
説明
column_name
必要です.文字を抽出するフィールド.
start
必要です.開始位置を指定します(開始値は1).
length
オプション.返される文字数.省略すると、MID()関数は残りのテキストを返します.

(2)REPLACE():文字列置換関数

構文:REPLACE(original，search，replace)パラメータ
説明
original
検索された文字列.
search
検索してreplaceで置換する文字列.searchが空の文字列の場合、元の文字列がそのまま返されます.
replace
この文字列はsearchを置換するために使用されます.replaceが空の文字列の場合、表示されたすべてのsearchが削除されます.

説明:文字列式1に表示されるすべての文字列式2の一致を文字列式3で置き換えます.新しい文字列を返します.パラメータがNULLの場合、この関数はNULLを返します.

2 SELECT from WORLD Tutorial
住所:https://sqlzoo.net/wiki/SELEC...
表構造world(name,continent,area,population,gdp)
Q & A 8.Exclusive OR (XOR). Show the countries that are big by area (more than 3 million) or big by population (more than 250 million) but not both. Show name, population and area.

SELECT name,population,area
    FROM world
    WHERE area>3000000 XOR population>250000000

知識点:XOR論理異或.二つは同時に成立することができず、同時に成立しないこともできず、その中の一つの条件しか成立しない.

9.Show the name and population in millions and the GDP in billions for the countries of the continent 'South America'. Use the ROUND function to show the values to two decimal places.For South America show population in millions and GDP in billions both to 2 decimal places.

SELECT name,round(population/1000000,2),ROUND(gdp/1000000000,2)
    FROM world
    WHERE continent ='South America'

知識点:ROUND関数は、数値フィールドを指定する小数点以下の桁数に丸めるために用いる.換算単位に用いることができる.

構文:ROUND(column_name,decimals)パラメータ
説明
column_name
必要です.端数処理するフィールド.
decimals
必要です.戻る小数点以下の桁数を指定します.
12.The capital of Sweden is Stockholm. Both words start with the letter 'S'. Show the name and the capital where the first letters of each match. Don't include countries where the name and the capital are the same word. • You can use the function LEFT to isolate the first character. • You can use <> as the NOT EQUALS operator.

SELECT name,capital
    FROM world
    WHERE  LEFT(name,1) =  LEFT(capital,1) 
    AND name<>capital

知識点:

LEFT(str,len) RIGHT(str,len) LEFT、RIGHT関数はstrの一番左、右のlen文字列

を返します.

<>:等しくない、比較演算子、その機能は!=と同じだが効率的に<>が高い.

13.Equatorial Guinea and Dominican Republic have all of the vowels (a e i o u) in the name. They don't count because they have more than one word in the name. Find the country that has all the vowels and no spaces in its name. • You can use the phrase name NOT LIKE '%a%' to exclude characters from your results. • The query shown misses countries like Bahamas and Belarus because they contain at least one 'a'.

SELECT name FROM world
    WHERE name LIKE '%a%'
      AND name LIKE '%e%'
      AND name LIKE '%i%'
      AND name LIKE '%o%'
      AND name LIKE '%u%'
      AND name NOT LIKE '% %'

3 SELECT from Nobel Tutorial
住所:https://sqlzoo.net/wiki/SELEC...
テーブル構造nobel(yr,subject,winner)
Q & A 14.The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1 . Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

SELECT winner, subject FROM nobel 
    WHERE yr=1984 
    ORDER BY subject IN ('Physics','Chemistry'),subject ,winner 

知識点:後の2つの式を除外します.これはグループソートです.

subjectin(xxx)が0のグループに分けてソートsubjectin(xxx)が1のグループに分けてソート結果を接続すると新しいソートテーブルとなる.