Computational Setup

SQLite and DB Browser for SQLite

Installation

  • Installation

  • Chinook Database

  • Open the Database in DB Browser

  • First SQL Queries

What is SQLite?

SQLite is a lightweight relational database management system. Unlike many database systems, SQLite does not require a separate database server. A complete database can be stored in a single file, such as:

chinook.db

This makes SQLite useful for:

  • Learning SQL
  • Small and medium-sized applications
  • Local data storage
  • Prototyping
  • Reproducible data projects

SQLite

Install DB Browser for SQLite

DB Browser for SQLite is a free, open-source graphical application for working with SQLite database files.

Use it to:

  • Open a SQLite database without a server
  • Browse tables and records
  • Examine a table’s structure
  • Write and run SQL statements
  • Save SQL scripts and export query results

Download DB Browser for SQLite

After installation, launch DB Browser for SQLite as a separate application.

Chinook Database

  • Installation

  • Chinook Database

  • Open the Database in DB Browser

  • First SQL Queries

The Chinook Example Database

For examples in this course, we will use the Chinook sample database.

Chinook represents a digital media store and contains related tables for:

  • Customers
  • Employees
  • Invoices
  • Invoice items
  • Tracks
  • Albums
  • Artists
  • Genres
  • Playlists

Chinook Database

Download Chinook for SQLite

Download the SQLite version of the Chinook database from the project releases:

Chinook Releases

The SQLite database file is named similar to:

Chinook_Sqlite.sqlite

Open the Database in DB Browser

  • Installation

  • Chinook Database

  • Open the Database in DB Browser

  • First SQL Queries

Open the Chinook Database

In DB Browser for SQLite:

  1. Select Open Database from the toolbar.
  2. Navigate to the course project folder.
  3. Open chinook.sqlite.
  4. Confirm that the Database Structure tab lists tables such as Customer, Album, and Track.

DB Browser opens the database file directly, so you do not need to create or save a connection profile.

Explore the Database

Use the main tabs in DB Browser:

  • Database Structure: View tables, columns, data types, indexes, and relationships.
  • Browse Data: Select a table and inspect its records.
  • Execute SQL: Write and run SQL statements.

To inspect customer records without writing a query, open Browse Data and select Customer from the Table menu.

Write and Save a SQL Script

  1. Open the Execute SQL tab.
  2. Enter a SQL statement in the SQL editor.
  3. Select Execute all (the play button) to run it.
  4. Save the script as:
sql/practice.sql

Use Open SQL file when you want to continue working with a saved script.

A SQL statement usually ends with a semicolon:

SELECT *
FROM Customer;

First SQL Queries

  • Installation

  • Chinook Database

  • Open the Database in DB Browser

  • First SQL Queries

View Data from a Table

The SELECT statement retrieves data from a database table.

SELECT *
FROM Customer;

* means to return all columns.

Select Specific Columns

Usually, we only select the columns needed for an analysis.

SELECT CustomerId,
       FirstName,
       LastName,
       Country
FROM Customer;

This makes the query output easier to read and reduces unnecessary data retrieval.

Limit Query Results

Use LIMIT when you only want to inspect a small number of rows.

SELECT CustomerId,
       FirstName,
       LastName,
       Country
FROM Customer
LIMIT 10;