Skip to content

Instantly share code, notes, and snippets.

@majana17

majana17/WORLD.md

Forked from mdang/WORLD.md
Created April 30, 2021 07:10
Show Gist options
  • Save majana17/a4baa54aa1a26010277d0237fa959b0b to your computer and use it in GitHub Desktop.
Save majana17/a4baa54aa1a26010277d0237fa959b0b to your computer and use it in GitHub Desktop.
Download ZIP
Lab: World Database
Raw
WORLD.md

World Lab

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

  1. Start by downloading and copying the contents of the worlds sample database file here
  2. Open up your terminal, then type in psql at the prompt to start the Postgres REPL
  3. Run the command create database world;
  4. Switch to the new database by typing in \c world
  5. Paste the contents of the SQL file you copied from step 1

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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment