Now, you will practice using the Command Line Interface (CLI) in SQLite3.

Download starter. cd into the sql-practice folder.

As you work, write down any questions you have for the upcoming discussion session.

Run sqlite3. You can begin with no database.

Tip: Notice the prompt changes to "sqlite>". That's how you know you will be able to run SQL statements and SQLite3 commands.

You can view the available commands for SQLite using .help. Run this now and look for the commands to

• View the list of tables
• View the table schema

Write down what you found. You'll need to use them in the next section.

Exit the CLI.

Tip: If you can't remember the keyboard shortcut you can look again at the built-in documentation to see if there is a command for exiting the CLI.

Start SQLite on a new database named example.db.

Run the command .read example.sql. This will execute the SQL in the example.sql file.

Repeat the command for viewing the tables in the database. You should see

samples

Repeat the command for viewing the schema of the database. You should see

CREATE TABLE samples (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
CREATE TABLE sqlite_sequence(name,seq);

Finally, run a simple query to see the data in the samples table by entering

SELECT * FROM samples;

This should show you

1|You found me!
2|Yippee!!
3|Yay!!!

The SQLite3 CLI has configuration options that you can set, one of which is to show the names of each of the columns in the rows of the query result.

Now, you should be able to see the names of the columns for any query result returned in the SQLite3 CLI.

Run the same query in the previous step

SELECT * FROM samples;

This should show you the results with the names of the columns as the first row

id|name
1|You found me!
2|Yippee!!
3|Yay!!!

Use Ctrl+d to exit the cli.

Now your prompt should return to normal, which probably means it ends with "$" or "%".

Congratulations! You have successfully used the SQLite3 CLI.