You've done a good job getting to this point! Now that you've gotten a taste of how to write a basic query, you need to practice writing queries that solve actual problems.

SELECT query

SELECT column, another_column, … FROM mytable WHERE _condition(s)_ ORDER BY column ASC/DESC LIMIT num_limit OFFSET num_offset;

Exercise

In the exercise below, you will be working with a different table. This table instead contains information about a few of the most populous cities of North America 1 ("Wikipedia: North American cities by population") including their population and geo-spatial location in the world.

Did you know?

Positive latitudes correspond to the northern hemisphere, and positive longitudes correspond to the eastern hemisphere. Since North America is north of the equator and west of the prime meridian, all of the cities in the list have positive latitudes and negative longitudes.

Try and write some queries to find the information requested in the tasks below. You may have to use a different combination of clauses in your query for each task. Once you're done, continue onto the next lesson to learn about queries that span multiple tables.

1. List all the Canadian cities and their populations
	1. SELECT * FROM north_american_cities WHERE Country = 'Canada';
	2. SELECT City FROM north_american_cities WHERE Country = 'Canada';
2. Order all the cities in the United States by their latitude from north to south
	1. SELECT * FROM north_american_cities WHERE Country = 'United States' ORDER BY Latitude DESC;
	2. SELECT City FROM north_american_cities WHERE Country = 'United States' ORDER BY Latitude DESC;
3. List all the cities west of Chicago, ordered from west to east
	1. SELECT * FROM north_american_cities WHERE Longitude < '-87.629798' ORDER BY Longitude;
	2. SELECT City FROM north_american_cities WHERE Longitude < '-87.629798' ORDER BY Longitude;
4. List the two largest cities in Mexico (by population)
	1. SELECT * FROM north_american_cities WHERE Country = 'Mexico' ORDER BY Population DESC LIMIT 2;
	2. SELECT City FROM north_american_cities WHERE Country = 'Mexico' ORDER BY Population DESC LIMIT 2;
5. List the third and fourth largest cities (by population) in the United States and their population
	1. SELECT * FROM north_american_cities WHERE Country = 'United States' ORDER BY Population DESC LIMIT 2 OFFSET 2;
	2. SELECT City FROM north_american_cities WHERE Country = 'United States' ORDER BY Population DESC LIMIT 2 OFFSET 2;

Review 1 — Tasks

1. List all the Canadian cities and their populations ✓
2. Order all the cities in the United States by their latitude from north to south ✓
3. List all the cities west of Chicago, ordered from west to east ✓
4. List the two largest cities in Mexico (by population) ✓
5. List the third and fourth largest cities (by population) in the United States and their population ✓

The current SQLBolt lesson, "SQL Review: Simple SELECT Queries," provides a review and practice exercises focused on creating basic SELECT queries in SQL.

Key points:

• Syntax Overview: Shows the general structure for a SELECT query, including clauses for selecting columns, specifying a table, adding conditions, ordering, and setting limits or offsets.
• Exercise Table: Features a sample table called north_american_cities, listing several major North American cities with their country, population, latitude, and longitude.
• Practice Tasks: Contains five tasks for the user to complete using SQL queries:
  1. List all Canadian cities and their populations.
  2. Order all US cities by latitude from north to south.
  3. List all cities west of Chicago, ordered west to east.
  4. List the two largest Mexican cities by population.
  5. List the third and fourth largest US cities by population.
• Navigation: Offers links to the previous lesson (on filtering and sorting query results) and the next lesson (on multi-table queries with JOINs).
• Extras: Contains a reference note on latitude and longitude related to North America, as well as donation and sharing options for supporting the site.[1]

1