SELECT names-Pattern Matching Strings

1. The % is a wild-card it can match any characters
Q. Find the country that start with Y
A. SELECT name FROM world
   WHERE name LIKE 'Y%'
2.
Q. Find the countries that end with y
A. SELECT name FROM world
   WHERE name LIKE '%y'

3. Luxembourg has an x - so does one other country. List them both.
Q.Find the countries that contain the letter x
A.SELECT name FROM world
  WHERE name LIKE '%x%'

4. Iceland, Switzerland end with land - but are there others?
Q. Find the countries that end with land
A. SELECT name FROM world
   WHERE name LIKE '%land'

5. Columbia starts with a C and ends with ia - there are two more like this.
Q. Find the countries that start with C and end with ia
A. SELECT name FROM world
   WHERE name LIKE 'C%ia'

6. Greece has a double e - who has a double o?
Q. Find the country that has oo in the name
A. SELECT name FROM world
   WHERE name LIKE '%oo%'

7. Bahamas has three a - who else?
Q. Find the countries that have three or more a in the name
A. SELECT name FROM world
   WHERE name LIKE '%a%a%a%'

8. India and Angola have an n as the second character. You can use the underscore as a single character wildcard.
Q. Find the countries that have "t" as the second character.
A. SELECT name FROM world
   WHERE name LIKE '_t%'
   ORDER BY name

9. Lesotho and Moldova both have two o characters separated by two other characters.
Q. Find the countries that have two "o" characters separated by two others.
A. SELECT name FROM world
   WHERE name LIKE '%o__o%'

10. Cuba and Togo have four characters names.
Q. Find the countries that have exactly four characters.
A. SELECT name FROM world
   WHERE name LIKE '____'

11. The capital of Luxembourg is Luxembourg. Show all the countries where the capital is the same as the name of the country
Q. Find the country where the name is the capital city.
A. SELECT name
   FROM world
   WHERE name LIKE capital

12.The capital of Mexico is Mexico City. Show all the countries where the capital has the country together with the word "City".
  THE CONCAT FUNCTION
Q. Find the country where the capital is the country plus "City".
A. SELECT name
   FROM world
   WHERE capital LIKE '%City'


13.
Q. Find the capital and the name where the capital includes the name of the country.
A. SELECT capital, name
   FROM world
   WHERE capital LIKE concat('%', name, '%')

14. You should include Mexico City as it is longer than Mexico. You should not include Luxembourg as the capital is the same as the country.
Q. Find the capital and the name where the capital is an extension of name of the country.
A. SELECT capital, name
   FROM world
   WHERE capital LIKE concat('%', name, '%') AND
   NOT capital LIKE name

15. You can use the SQL function REPLACE.
Q. Show the name and the extension where the capital is an extension of name of the country.
A. SELECT name, REPLACE(capital,name,'')
   FROM world
   WHERE capital LIKE concat(name, '%') AND
   NOT capital LIKE name

## Introduction
1.Observe the result of running this SQL command to show the name, continent and population of all countries.
Q.
A. SELECT name, continent, population FROM world

2. How to use WHERE to filter records
Q. Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.
A. SELECT name FROM world
   WHERE population > 200000000

3. PER CAPITA GDP. per capita GDP is the GDP divided by the population GDP/population
Q. Give the name and the per capita GDP for those countries with a population of at least 200 million.
A. SELECT name, gdp/population
   FROM world
   WHERE population > 200000000

4. In Millions
Q. Show the name and population in millions for the countries of the continent 'South America'. Divide the population by 1000000 to get population in millions.
A. SELECT name, population/1000000
   FROM world
   WHERE continent = 'South America'

5. Show Name and Population
Q. Show the name and population for France, Germany, Italy
A. SELECT name, population
   FROM world
   WHERE name IN ('France', 'Germany', 'Italy')

6. Includes a word
Q. Show the countries which have a name that includes the word 'United'
A. SELECT name
   FROM world
   WHERE name LIKE '%united%'

7. Two ways to be big (OR): Show the countries that are big by area or big by population. Show name, population and area.
Q. Two ways to be big: A country is big if it has an area of more than 3 million sq km or it has a population of more than 250 million.
A. SELECT name, population, area
   FROM world
   WHERE area > 3000000 OR population > 250000000

8. EXCLUSIVE | One or the other (but not both)
Q. Exclusive OR (XOR). Show the countries that are big by area or big by population but not both. Show name, population and area.
A. SELECT name, population, area
   FROM world
   WHERE (area > 3000000 AND population < 250000000)
   OR (population > 250000000 AND area < 3000000)

9. Rounding | 2 decimal places | Divide by 1000000 (6 zeros) for millions. Divide by 1000000000 (9 zeros) for billions.
Q. 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.
A. SELECT name, ROUND(population/1000000, 2), ROUND(gdp/1000000000, 2)
   FROM world
   WHERE continent = 'South America'

10. Trillion Dollar economics | Show per-capita GDP for the trillion dollar countries to the nearest $1000.
Q. Show the name and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000.
A. SELECT name, ROUND((gdp/population),-3)
   FROM world
   WHERE gdp >= 1000000000000

11. LENGTH | Name and capital have the same length
Q. Show the name and capital where the name and the capital have the same number of characters.
A. SELECT name, capital
   FROM world
   WHERE LENGTH(name) = LENGTH(capital)

12. LEFT | <> Not equals opeartor | Matching name and capital
Q. 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.
A. SELECT name, capital
   FROM world
   WHERE LEFT(name,1) = LEFT(capital,1)
   AND name <> capital

13. name NOT LIKE '%a%' | All the vowels
Q. Find the country that has all the vowels and no spaces in its name. 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.
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 '% %'