SQL练习

题目来源：http://zh.sqlzoo.net/

SELECT基础操作

world表

1、查询所有国家名字，条件是首都名是国家名加上' City'。

SELECT NAME FROM WORLD
WHERE CAPTIAL = CONCAT(NAME,'CITY');

2、找出所有首都和国家名字，首都名要有国家名出现。

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

3、找出所有首都和国家名，首都是国家名的延伸。

SELECT name,name FROM world
WHERE capital LIKE CONCAT('%',name,'%') AND capital != name;

4、显示国家名字，及其延伸词，如首都是国家名字的延伸。

SELECT name,replace(capital,name,'') FROM world
WHERE capital LIKE CONCAT(name,'%_')

SELECT from WORLD Tutorial

1、找出至少有2亿人口的国家名称和人均国内生产总值。

SELECT name,gdp/population FROM world
WHERE population >= 200000000

2、查询南美洲国家的名字和人口数(单位为百万)。

SELECT name,population/1000000 FROM world
WHERE continent='South America'

3、

SELECT name,population FROM world
WHERE name in ('France','Germany','Italy')

4、

SELECT name FROM world
WHERE name LIKE '%United%'

5、

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

6、

SELECT name,population,area FROM world
WHERE (area > 3000000 AND population < 250000000) OR (area < 3000000 AND population > 250000000)

7、

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

8、

SELECT name,ROUND(gdp/population,-3) FROM world
WHERE gdp>1000000000000

9、将N开头的国家中continent为Oceania的用Australasia替换。

SELECT name,
       CASE WHEN continent='Oceania' THEN 'Australasia'
            ELSE continent END
FROM world
WHERE name LIKE 'N%'

10、

SELECT name,
(CASE WHEN continent IN ('Europe','Asia') THEN 'Eurasia'
WHEN continent IN ('South America','North America','Caribbean') THEN 'America'
ELSE continent END) AS continent
FROM world
WHERE name LIKE 'A%' OR name LIKE 'B%'

11、

SELECT name,continent,
CASE WHEN continent='Oceania' 
           THEN 'Australasia'
           WHEN continent IN ('Eurasia','Turkey') 
           THEN 'Europe/Asia'
           WHEN continent='Caribbean' 
           THEN CASE WHEN name LIKE 'B%' 
                               THEN 'North America'
                               ELSE 'South America' 
                               END
           ELSE continent
           END
FROM world
ORDER BY name;

SELECT FROM NOBEL TUTORIAL

1、

SELECT yr,subject FROM nobel
WHERE winner='Albert Einstein';

2、

SELECT winner FROM nobel
WHERE yr >= 2000 AND subject='Peace';

3、

SELECT * FROM nobel
WHERE yr BETWEEN 1980 AND 1989 AND subject='Literature';

4、

SELECT * FROM nobel
 WHERE winner IN ('Theodore Roosevelt',
                  'Woodrow Wilson',
                  'Jimmy Carter')

5、

SELECT winner FROM nobel
WHERE winner LIKE 'John%';

6、

SELECT * FROM nobel
WHERE (subject='physics' AND yr=1980)
OR (subject='chemistry' AND yr=1984);

7、

SELECT * FROM nobel
WHERE yr=1980 AND subject NOT IN ('Chemistry','Medicine');

8、

SELECT * FROM nobel
WHERE (yr<1910 AND subject='Medicine')
OR (yr>=2004 AND subject='Literature');

9、

SELECT * FROM nobel
WHERE winner='PETER GRÜNBERG';

10、

SELECT * FROM nobel
WHERE winner='EUGENE O\'NEILL';

11、

SELECT winner,yr,subject FROM nobel
WHERE winner LIKE 'Sir%'
ORDER BY yr DESC,winner;

12、

找出1984年获奖的详细信息，根据奖项和获奖者名字进行排序，将物理奖和化学奖放在最后，

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

13、

SELECT winner FROM nobel
WHERE winner LIKE 'C%' AND winner LIKE '%n'

14、

SELECT COUNT(subject) FROM nobel
WHERE subject = 'Chemistry'
AND yr BETWEEN 1950 and 1960

15、

SELECT COUNT(DISTINCT yr) FROM nobel
WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject = 'Medicine')

16、