exercise-SQLite-setup-and-first-steps.md

Practice: SQLite setup & first steps

Iteration 1 - Setup:

Follow the instructions in the unit "Installations: Intro to SQL with SQLite " to install SQLite and DB Browser for SQLite

Note:

• You'll find instructions for each operating system: Windows / Mac / Linux.
• If you use Windows → for DB Browser for SQLite, use Method 3 or Method 1.

Iteration 2 - Verify your installations:

a. Verify that "SQLite" is correctly installed:

• Run this command: sqlite3 --version

b. Verify that "DB Browser for SQLite" is correctly installed:

• Open the application in your operating system and confirm that it launches successfully.

Bonus I - Explore some SQL Statements:

If you have completed the installations, you can start exploring some SQL commands, following the cheat sheet below.

For example, you can:

• Create a database & a table (see the sections 'Create a database' + 'Create a table')
• Add some data (see the section 'Insert data in a table')
• Get all the entries an a table (see the section 'Read')

📌 SQLite cheat-sheet

Bonus II - Explore some SQL Statements:

If you have the time and you're up for a bigger challenge, you can try to solve the tasks below:

1. Create the database file animals_db.sqlite3 by connecting to it → Run this command sqlite3 animals_db.sqlite3 (creates the file if it doesn't exist)
2. Create a table animals with columns id, name, species, age.
3. Insert 3 rows of sample animal data.
4. Run .tables to confirm the table exists.
5. Run .schema animals to view the table structure.
6. Select all rows from animals.
7. Select only animals older than a certain age.
8. Update one animal's age.
9. Delete one row.
10. Add a new column (e.g. weight) using ALTER TABLE.
11. Export query results to CSV using .mode csv and .output.
12. Quit the session with .quit and reconnect to confirm data persisted.