SQL基础与进阶实战-优快云博客

SQL基础练习

一、在线练习
二、系统学习

一、在线练习

SQLzoo网站练习题：https://zh.sqlzoo.net/wiki/SELECT_basics/zh

1、Select basics

select basics

显示 Germany 的人口
select population from world where name = 'Germany';
备注：字符串应该在单引号中

查询面积为5,000,000 以上平方公里的国家,显示每个国家的名字和人均国内生产总值(gdp/population)
select name, gdp/population from world where area >= 5000000;
备注：用公式(population/area)計算出人均国内生产总值

顯示“Ireland 愛爾蘭”,“Iceland 冰島”,“Denmark 丹麥”的國家名稱和人口。
select name,population from world where name in ('Ireland', 'Iceland', 'Denmark');
备注：单词“IN”可以让我们检查一個項目是否在列表中

顯示面積為 200,000 及 250,000 之間的國家名稱和該國面積
select name, area from world where area between 200000 and 250000;
备注：between 允許範圍檢查 - 注意,這是包含性的

SELECT Name

找出以 Y 为首的国家
select name from world where name like 'Y%';
备注：你可以用where name like 'B%'來找出以 B 為開首的國家。
%是萬用字元,可以用代表任何字完。

找出以 Y 为结尾的国家
select name from world where name like '%Y';

找出所有國家,其名字包括字母x
`select name from world where name like ‘%x%’;

找出所有國家,其名字以 land 作結尾
select name from world where name like '%land';

找出所有國家,其名字以 C 作開始,ia 作結尾
select name from world where name like 'C%ia';

找出所有國家,其名字包括字母oo
select name from world where name like '%oo%';

找出所有國家,其名字包括三個或以上的a。
select name from world where name like '%a%a%a%';

找出所有國家,其名字以t作第二個字母
select name from world where name like '_t%';
select name from world where name like '_t%' order by name;
备注：用下划线_当作单一个字母的万用字元

找出所有國家,其名字都有兩個字母 o,被另外兩個字母相隔着。
elect name from world where name like '%o__o%';

找出所有國家,其名字都是 4 個字母的
select name from world where name like '____';

显示所有国家名字,其首都和国家名字是相同的
select name from world where name = capital;

顯示所有國家名字,其首都是國家名字加上”City”
select name from world where capital like concat(name, ' City');
**函数concat 可以用來合拼兩個或以上的字串

找出所有首都和其國家名字,而首都要有國家名字中出現
select capital,name from world where capital like concat('%',name,'%');

找出所有首都和其國家名字,而首都是國家名字的延伸。
你應顯示 Mexico City,因它比其國家名字 Mexico 長。
你不應顯示 Luxembourg,因它的首都和國家名相是相同的。
select name,capital from world where capital like concat(name,'_%') and capital != name;

“Monaco-Ville"是合併國家名字 “Monaco” 和延伸詞”-Ville".顯示國家名字，及其延伸詞，如首都是國家名字的延伸。
select name, replace(capital, name, '') as name1 from world where capital like concat(name, '_%');
备注：replace（f，s1，s2）返回字符串f，其中所有出现的s1被s2替换。

2、Select from World Tutorial

在这里插入图片描述

觀察運行一個簡單的SQL命令的結果
select name, continent, population from world;

顯示具有至少2億人口的國家名稱。 2億是200000000，有八個零
select name from world where population >= 200000000;

找出有至少200百萬(2億)人口的國家名稱，及人均國內生產總值。
select name, gdp/population from world where population >= 200000000;

顯示’South America’南美洲大陸的國家名字和以百萬為單位人口數。將人口population 除以一百萬(1000000)得可得到以百萬為單位人口數。
select name, population/1000000 from world where continent = 'South America';

顯示法國，德國，意大利(France, Germany, Italy)的國家名稱和人口
select name, population from world where name in ('France', 'Germany', 'Italy');

顯示包含單詞“United”為名稱的國家
select name from world where name like '%United%';

成為大國的兩種方式：如果它有3百萬平方公里以上的面積，或擁有250百萬(2.5億)以上人口。展示大國的名稱，人口和面積
select name,population,area from world where area > 3000000 or population > 250000000;

美國、印度和中國(USA, India, China)是人口又大，同時面積又大的國家。排除這些國家。顯示以人口或面積為大國的國家，但不能同時兩者。顯示國家名稱，人口和面積
select name,population,area from world where (area > 3000000 and population < 250000000) or (area < 3000000 and population > 250000000);

除以為1000000（6個零）是以百萬計。除以1000000000（9個零）是以十億計。使用 ROUND 函數來顯示的數值到小數點後兩位。對於南美顯示以百萬計人口，以十億計2位小數GDP
select name,round(population/1000000,2), round(gdp/1000000000,2) from world where continent = 'South America';

顯示國家有至少一個萬億元國內生產總值（萬億，也就是12個零）的人均國內生產總值。四捨五入這個值到最接近1000。顯示萬億元國家的人均國內生產總值，四捨五入到最近的$ 1000
select name,round(gdp/population/1000)*1000 from world where gdp >= 1000000000000;

所示的CASE语句用于在第三栏中用北美代替加勒比海地区。显示名称-以N开头的国家/地区，但要用大洋洲代替Australasia。
select name , (case when continent='Oceania' then 'Australasia' else continent end) as newName from world where name like 'N%';
备注：case 的用法：
> https://blog.youkuaiyun.com/rongtaoup/article/details/82183743

显示名称和大陆-但用欧亚大陆代替欧洲和亚洲；美国替代-北美，南美或加勒比海地区的每个国家/地区。显示以A或B开头的国家

select name,
      (case when continent='Asia' 
            then 'Eurasia'
            when continent='Europe' 
            then 'Eurasia'
            when continent = 'North America' 
            then 'America'
            when continent = 'South America' 
            then 'America'
            when continent = 'Caribbean' 
            then 'America'
            else continent
        end)as newName
from world where name like 'A%' or name like 'B%';

Put the continents right…
Oceania becomes Australasia
Countries in Eurasia and Turkey go to Europe/Asia
Caribbean islands starting with ‘B’ go to North America, other Caribbean islands go to South America
Show the name, the original continent and the new continent of all countries.

# 当前结果网站上提交不正确
select name,continent,
(case when continent = 'Oceania' then 'Australasia'
     when continent in ('Eurasia','Turkey') then 'Europe/Asia'            
     when continent = 'Caribbean' and name like 'B%' then 'North America'
     when continent = 'Caribbean' and name not like 'B%' then 'South America'
     else continent end) as newContinent
from world order by name;

3、Select from Nobel Tutorial

在这里插入图片描述

更改查詢以顯示1950年諾貝爾獎的獎項資料。

select yr, subject, winner
from nobel
where yr = 1950;

顯示誰贏得了1962年文學獎(Literature)

select winner
from  nobel
where yr = 1962
and subject = 'Literature';

顯示“愛因斯坦”(‘Albert Einstein’) 的獲獎年份和獎項

select yr, subject 
from nobel 
where winner = 'Albert Einstein';

顯示2000年及以後的和平獎(‘Peace’)得獎者。

select winner 
from nobel
where yr >= 2000 and subject = 'Peace';

顯示1980年至1989年(包含首尾)的文學獎(Literature)獲獎者所有細節（年，主題，獲獎者）。

select yr,subject,winner
from nobel
where yr>=1980 and yr <= 1989 and subject = 'Literature';

顯示總統獲勝者的所有細節：
西奧多•羅斯福 Theodore Roosevelt
伍德羅•威爾遜 Woodrow Wilson
吉米•卡特 Jimmy Carter

select * 
from nobel where 
winner in ('Theodore Roosevelt',
           'Woodrow Wilson',
           'Jimmy Carter');

顯示名字為John 的得獎者。 (注意:外國人名字(First name)在前，姓氏(Last name)在後)

select winner
from nobel
where winner like 'John%';

顯示1980年物理學(physics)獲獎者，及1984年化學獎(chemistry)獲得者。

select *
from nobel
where (yr = 1980 and subject = 'physics')
or (yr = 1984 and subject = 'chemistry');

查看1980年獲獎者，但不包括化學獎(Chemistry)和醫學獎(Medicine)

select *
from nobel
where subject not in ('Chemistry','Medicine') and yr = 1980;

顯示早期的醫學獎(Medicine)得獎者（1910之前，不包括1910），及近年文學獎(Literature)得獎者（2004年以後，包括2004年)

select *
from nobel
where (yr<1910 and subject = 'Medicine') or (yr >= 2004 and subject = 'Literature');

Find all details of the prize won by PETER GRÜNBERG

select * 
from nobel
where winner = 'PETER GRÜNBERG';

查找尤金•奧尼爾EUGENE O’NEILL得獎的所有細節 Find all details of the prize won by EUGENE O’NEILL

select * 
from nobel
where winner = 'EUGENE O''NEILL';

騎士列隊 Knights in order
列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者，然後同年再按名稱順序排列。

select winner,yr,subject
  from nobel
where winner like 'Sir%'
order by yr desc,winner asc;

显示1984年获奖者和主题（按主题和获奖者名称排序）；但最后列出化学和物理。

select winner, subject 
  from nobel
where yr=1984 
order by subject in ('Chemistry','Physics'),subject,winner asc;

在这里插入图片描述

4、Select within Select Tutorial

在这里插入图片描述

列出每個國家的名字 name，當中人口 population 是高於俄羅斯’Russia’的人口

select name
from world where population >
(select population from world where name = 'Russia');

列出歐州每國家的人均GDP，當中人均GDP要高於英國’United Kingdom’的數值。

select name
from world 
where gdp/population > (select gdp/population from world where name = 'United Kingdom') and continent

SQL练习

SQL基础练习

一、在线练习

1、Select basics

2、Select from World Tutorial

3、Select from Nobel Tutorial

4、Select within Select Tutorial