Skip to content

Latest commit

 

History

History
146 lines (101 loc) · 4.44 KB

2-SELECT-from-World.md

File metadata and controls

146 lines (101 loc) · 4.44 KB

Questions Index

Ques 1. Show the name, continent and population of all countries

SELECT name, continent, population
FROM world

Ques 2. Show the name for the countries that have a population of at least 200 million

SELECT name
FROM world
WHERE population >= 200000000

Ques 3. Give the name and the per capita GDP for those countries with a population of at least 200 million

SELECT name, (gdp/population) AS "per capita GDP"
FROM world
WHERE population >= 200000000

Ques 4. Show the name and population in millions for the countries of the continent 'South America'

SELECT name, population/1000000 AS "population(in millions)"
FROM world
WHERE continent = 'South America'

Ques 5. Show the name and population for France, Germany, Italy

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

Ques 6. Show the countries which have a name that includes the word 'United'

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

Ques 7. Show the countries that are big by area or big by population. Show name, population and area

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

Ques 8. 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

a. Using XOR operator

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

b. Without using XOR operator

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

Ques 9. Show the name and population in millions and the GDP in billions for the countries of the continent 'South America'

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

Ques 10. Show the name and per-capita GDP for those countries with a GDP of at least one trillion

SELECT name, ROUND((gdp / population), -3) AS 'per capita gdp'
FROM world
WHERE gdp >= 1000000000000

Ques 11. Show the name and capital where the name and the capital have the same number of characters

SELECT name, capital
FROM world
WHERE LENGTH(name) = LENGTH(capital)

Ques 12. 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

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

Ques 13. Find the country that has all the vowels and no spaces in its name

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