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.
- 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
- Select 25 cities around the world that start with the letter 'F' in a single SQL query.
@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!