Exercise: World Lab (MySQL)

WORLD_MYSQL.md

World Lab (MySQL)

This lab requires the sample world database found here: https://dev.mysql.com/doc/index-other.html

Let's learn a few things about this crazy world!

Here's a model of what you now have loaded in the world database. The first row is the table name, the second is the primary key and finally the remaining are any additional attributes.

Exercises

• Using count, get the number of cities in the USA
• Find out what the population and average life expectancy for people in Argentina (ARG) is
• Using IS NOT NULL, ORDER BY, LIMIT, what country has the highest life expectancy?
• Using LEFT JOIN, ON, what is the capital of Spain (ESP)?
• Using LEFT JOIN, ON, list all the languages spoken in the 'Southeast Asia' region

BONUS

• Select 25 cities around the world that start with the letter 'F' in a single SQL query.

-- Using count, get the number of cities in the USA (274)

SELECT city.name, country.name, COUNT(*) FROM city, country
WHERE country.code=city.countryCode
AND country.name='United States';

-- Find out what the population and average life expectancy for people in Argentina (ARG) is (37032000, 75.10000)

SELECT population, AVG(lifeExpectancy) FROM country WHERE CODE='arg';

-- Using IS NOT NULL, ORDER BY, LIMIT, what country has the highest life expectancy? (Andorra, 83.5)

SELECT * FROM country;
SELECT NAME,lifeExpectancy FROM country ORDER BY lifeExpectancy DESC LIMIT 1 ;

-- Using LEFT JOIN, ON, what is the capital of Spain (ESP)? (Madrid)

SELECT city.name 
FROM city, country
WHERE country.code=city.countryCode
AND country.capital=city.ID
AND country.code='ITA';

SELECT city.name, country.name
FROM country
LEFT JOIN city
ON country.capital=city.ID
AND country.code=city.countryCode
WHERE country.code='ESP';

-- Using LEFT JOIN, ON, list all the languages spoken in the 'Southeast Asia' region (65)

SELECT LANGUAGE,region
FROM countrylanguage
LEFT JOIN country
ON countrylanguage.CountryCode=country.code
WHERE country.region='Southeast Asia';

-- Select 25 cities around the world that start with the letter 'F' in a single SQL query.

SELECT NAME FROM city WHERE NAME LIKE 'f%' ORDER BY NAME ASC LIMIT 25;

Thanks for this man. How do you get the life expectancy below 50??

Hey Dear,

You can try out this code for the life expectancy below 50

SELECT Name, LifeExpectancy
FROM country
WHERE LifeExpectancy > 50;

@majana17 #1 is incorrect unless I'm missing something (and I am new so I may be) - returning the city names and country names will give you 1 for the count of each row (and you'd need a GROUP BY for each)

To return 274, we can use:
SELECT COUNT(*) FROM city, country
WHERE country.code=city.countryCode
AND country.name='United States';

OR
SELECT COUNT(*), country.name FROM city, country
WHERE country.code=city.countryCode
AND country.name='United States';

Anytime the city.name is included, I get an error that a GROUP BY is needed. Then, a subquery or another approach would be needed for the USA total (again, unless I've missed something). Learning as much as I can!