Computational Setup

SQLite and SQLTools

Installation

  • Installation

  • Course Workspace

  • Chinook Database

  • Connect with SQLTools

  • First SQL Queries

SQL and Relational Databases

SQL (Structured Query Language) is a language used to work with relational databases.

With SQL, we can:

  • Retrieve data from tables
  • Clean and summarize data
  • Manage data stored in a relational database

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 Positron

Positron is a data science IDE built on Code OSS. We can use it as an editor for SQL files and extend it with database tools.

Download Positron

  • Organize database project files
  • Write .sql scripts
  • Connect to SQLite databases
  • Run SQL queries
  • View query results

Install VS Code

Visual Studio Code is a general-purpose code editor that supports database workflows through extensions.

Download VS Code

Install VSCodium

VSCodium is an open-source build of VS Code with Microsoft telemetry disabled.

VSCodium

Install SQLTools

In Positron, VS Code or VS Codium, open the Extensions panel.

Search for and install:

  1. SQLTools (mtxr.sqltools)
  2. SQLTools SQLite (mtxr.sqltools-driver-sqlite)

SQLTools provides the database connection interface. The SQLite driver allows SQLTools to connect specifically to SQLite database files.

SQLTools Documentation

Course Workspace

  • Installation

  • Course Workspace

  • Chinook Database

  • Connect with SQLTools

  • First SQL Queries

Workspaces

A workspace is a project folder containing the files needed for a data-management project.

A good workflow is:

  1. Create one folder for the course.
  2. Create a data folder for database files.
  3. Create a sql folder for saved SQL scripts.
  4. Open the course folder in Positron or VS Code.
  5. Connect SQLTools to the SQLite database file.
  6. Save your SQL work in .sql files.

Course Project Files

A simple course folder might look like:

data-management-course/
├── data/
│   └── chinook.db
├── sql/
│   ├── practice.sql
│   └── queries.sql
├── notes/
└── assignments/

Chinook Database

  • Installation

  • Course Workspace

  • Chinook Database

  • Connect with SQLTools

  • 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

For this course, you may rename it:

chinook.db

Place it in your project’s data folder.

Open Chinook from the Terminal

From your course project folder, run:

sqlite3 data/chinook.db

If the database opens successfully, you should see a SQLite prompt similar to:

sqlite>

Useful SQLite Commands

SQLite includes special dot commands for working with the database shell.

Try:

.tables

This displays the tables in the database.

To inspect the structure of the Customer table:

.schema Customer

To exit SQLite:

.quit

SQLite Commands

Warning

SQLite dot commands such as .tables and .schema are commands for the SQLite shell. They are not SQL statements and should not be included inside SQLTools query scripts.

Connect with SQLTools

  • Installation

  • Course Workspace

  • Chinook Database

  • Connect with SQLTools

  • First SQL Queries

Create a SQLite Connection

In VS Code or Positron:

  1. Open the Command Palette.
  2. Search for SQLTools: Add New Connection.
  3. Select SQLite as the database driver.
  4. Choose the Chinook database file in your data folder.
  5. Give the connection a name, such as Chinook.
  6. Test and save the connection.

You can now use SQLTools to browse tables and run SQL statements.

Create a SQL File

Inside the sql folder, create a file named:

practice.sql

Use this file to save your SQL statements.

A SQL statement usually ends with a semicolon:

SELECT *
FROM Customer;

First SQL Queries

  • Installation

  • Course Workspace

  • Chinook Database

  • Connect with SQLTools

  • 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;