Learn to use SQLite to understand the basic database schema and query operations.

To start SQLite, use command "sqlite3 [databasefile if there is one]".

You can find the documentation for SQL Lite at: https://www.sqlite.org. It's especially useful to get familiar with its Command Line Shell For SQLite.

In order to use SQLite, you should at least be able to do the following tasks:

1. Create a new database;

   linux$sqlite3 db_name
   

2. Create tables in the database;

   linux$sqlite3 db_name
   sqlite> create table Population (
               country_id int,
               country_name varchar(40),
               population int
           );
   

3. Download the csv data file, then import data from csv files into tables;

   sqlite> -- use the comma in csv file is treated as separator
   sqlite> .separator ,
   sqlite> .import population.csv Population
   

4. Try to query the data

   sqlite> -- turn on the header when display query result
   sqlite> .header on
   sqlite> -- use columns to show the query result
   sqlite> .mode column
   sqlite> -- find all the countries with population exceeds 100000000
   sqlite> select *
      ...> from population
      ...> where population > 100000000;
   

   And you should get result as shown:

   country_id  country_name  population
   ----------  ------------  ----------
   17          Bangladesh    168065920
   28          Brazil        212392717
   44          China         1420062022
   62          Egypt         101168745
   67          Ethiopia      110135635
   96          India         1368737513
   97          Indonesia     269536482
   105         Japan         126854745
   133         Mexico        132328035
   151         Nigeria       200962417
   157         Pakistan      204596442
   163         Philippines   108106310
   170         Russia        143895551
   219         U.S.          329093110
   

5. Try other queries or command line shell to get familiar with sqlite and simple data manipulation.