SQL Zoo

SELECT basics

1. SELECT population FROM world
   where name = ‘Germany’;
2. SELECT name, population FROM world
   WHERE name IN (‘Sweden ‘, ‘Norway’, ‘Denmark’);
3. SELECT name, area FROM world
   WHERE area BETWEEN 200000 AND 250000;

SELECT names

1. SELECT name FROM world
   WHERE name LIKE ‘Y%’
2. SELECT name FROM world
   WHERE name LIKE ‘%y’
3. SELECT name FROM world
   WHERE name LIKE ‘%x%’
4. SELECT name FROM world
   WHERE name LIKE ‘%land’
5. SELECT name FROM world
   WHERE name LIKE ‘C%ia’
6. SELECT name FROM world
   WHERE name LIKE ‘%oo%’
7. SELECT name FROM world
   WHERE name LIKE ‘%a%a%a%’
8. SELECT name FROM world
   WHERE name LIKE ‘_t%’
   ORDER BY name
9. SELECT name FROM world
   WHERE name LIKE ‘%o__o%’
10. SELECT name FROM world
    WHERE name LIKE ‘__‘
11. SELECT name FROM world
    WHERE name = capital;
12. SELECT name FROM world
    WHERE capital = concat(name, ‘ City’);
13. select capital, name from world
    where capital like concat(‘%’,name,’%’);
14. select capital, name from world
    where capital like concat(name, ‘_%’);
15. select name,replace(capital,name,”) from world
    where capital like concat(name,’_%’);

SELECT from WORLD

1. SELECT name, continent, population FROM world;
2. SELECT name FROM world
   WHERE population > 200000000;
3. select name,gdp/population from world
   where population > 200000000;
4. select name, population/1000000 from world
   where continent = ‘South America’
5. select name, population from world
   where name in (‘France’,’Germany’,’Italy’);
6. select name from world
   where name like ‘%United%’;
7. select name,population,area from world
   where area > 3000000 or population > 250000000;
8. SELECT name,population,area
   FROM world
   WHERE (area > 3000000 AND NOT population > 250000000) OR (NOT area > 3000000 AND population > 250000000);
9. select name,ROUND(population/1000000,2),ROUND(gdp/1000000000,2) from world
   where continent = ‘South America’;
10. select name,ROUND(gdp/population,-3)
    from world
    where gdp > 1000000000000;
11. SELECT name, capital
    FROM world
    WHERE LENGTH(name) = LENGTH(capital);
12. SELECT name, capital
    FROM world
    where LEFT(name,1) = LEFT(capital,1) and name <> capital;
13. SELECT name
    FROM world
    WHERE name LIKE ‘%a%’
    AND name LIKE ‘%i%’
    AND name LIKE ‘%u%’
    AND name LIKE ‘%e%’
    AND name LIKE ‘%o%’
    AND name NOT LIKE ‘% %’;

SELECT from Nobel Tutorial

1. SELECT yr, subject, winner
   FROM nobel
   WHERE yr = 1950
2. SELECT winner
   FROM nobel
   WHERE yr = 1962
   and subject = ‘literature’
3. select yr,subject from nobel
   where winner = ‘Albert Einstein’;
4. select winner from nobel
   where subject like ‘%peace%’
   and yr >= 2000;
5. select yr,subject,winner from nobel
   where subject = ‘literature’ and yr >=1980 and yr < 1990;
6. SELECT * FROM nobel
   WHERE winner IN (‘Theodore Roosevelt’,
   ‘Thomas Woodrow Wilson’,
   ‘Jimmy Carter’,
   ‘Barack Obama’);
7. select winner from nobel
   where winner like ‘John%’;
8. select yr,subject,winner from nobel
   where (subject = ‘physics’ and yr=1980) or (subject = ‘chemistry’ and yr =1984);
9. select yr,subject,winner from nobel
   where yr=1980 and subject != ‘chemistry’ and subject !=’medicine’;
10. select yr,subject,winner from nobel
    where (subject = ‘Medicine’ and yr < 1910) or (subject = ‘Literature’ and yr >= 2004);
11. select * from nobel
    where winner = ‘PETER GRÜNBERG’;
12. select * from nobel
    where winner = “EUGENE O’NEILL”;
13. select winner,yr,subject from nobel
    where winner like ‘Sir %’
    order by yr desc, winner;
14. SELECT winner, subject
    FROM nobel
    WHERE yr=1984
    ORDER BY subject in (‘chemistry’,’physics’),subject,winner;

SELECT within SELECT Tutorial

1. SELECT name FROM world
   WHERE population >
   (SELECT population FROM world
   WHERE name=’Russia’);
2. select name
   from world
   where continent = ‘Europe’
   and gdp/population > ( select gdp/population from world where name = ‘United Kingdom’);
3. select name,continent from world
   where continent in (select continent from world where name in (‘Argentina’,’Australia’))
   order by name;
4. select name,population from world
   where population > (select population from world where name = ‘United Kingdom’)
   and population < (select population from world where name = ‘Germany’);
5. select name,
   concat(round(100*population/(select population from world where name = ‘Germany’),0),’%’) as percentage
   from world
   where continent = ‘Europe’;
6. select name
   from world
   where gdp > ALL(select gdp from world where gdp > 0 and continent = ‘Europe’);
7. SELECT continent, name, area FROM world x
   WHERE area >= ALL
   (SELECT area FROM world y
   WHERE y.continent=x.continent
   AND area>0)
8. select continent, name from world x
   where name <= ALL(select name from world y
   where y.continent=x.continent);
9. select name, continent, population from world
   where continent IN (
   select continent from world group by continent having max(population) <= 25000000
   );
10. select x.name
11. , x.continent from world x
    where x.population > (select max(y.population) from world y
    where y.continent = x.continent and y.name != x.name) * 3;

SUM and COUNT

SELECT SUM(population)
FROM world

select distinct(continent) from world

select sum(gdp) from world where continent = ‘Africa’;

select count(name) from world where area > 1000000;

select sum(population) from world where name in (‘Estonia’, ‘Latvia’, ‘Lithuania’)

select continent, count(name) from world
group by continent

select continent, count(name) from world
where population > 10000000
group by continent

select continent from world
group by continent having sum(population) > 100000000