Definitions

Databases are a centralized place to store and retrieve data.

SQL database: A set of tables with defined relationships and data types.

SQL (Structured Query Language): A language for interacting with databases. Most commonly used to retrieve data.

Schema: The structure of a database. The schema defines the tables, the fields in each table, and the relationships between fields and tables.

SQL client: A tool used to connect and access a database. Can be GUI or CLI.

Example schema

Source

Example table

Example query

SELECT name, popularity, followers 
FROM artists 
ORDER BY followers DESC 
LIMIT 5;

Design

Common Flavors

Client - Server

App with SQL Client

Clients

Web API

- Phone app
- ​Desktop app
- ​Web client

- ​CLI

SQL Server

Internet

Server

Clients

- App
- Database

Web API

- Phone app
- ​Desktop app
- ​Web client

- ​CLI

Embedded

Raspberry Pi

- Code
- Sensor
- Database

Embedded

Let's connect!

bash> sqlite3 chinook.db

sqlite> .headers on
sqlite> .mode column
sqlite> .tables

Our Schema

Source

Writing Queries

Bare minimum query

SELECT *
FROM table_name;

Select, From

SELECT *
FROM tracks;

Limit

SELECT *
FROM tracks
LIMIT 5;

Select

SELECT name, composer
FROM tracks
limit 5;

Where

SELECT name
FROM tracks
WHERE composer = "Miles Davis";

Count, Group by

SELECT COUNT(name), composer 
FROM tracks 
GROUP BY composer;

As, Order by

SELECT 
    COUNT(name) as count, 
    composer 
FROM tracks 
GROUP BY composer
ORDER BY count DESC
LIMIT 10;

Multiple Aggregations

SELECT 
    COUNT(name) as count,
    AVG(Milliseconds),
    composer 
FROM tracks 
GROUP BY composer
ORDER BY count DESC
LIMIT 10;

Joins

Joins let us combine data in multiple tables.

Syntax for a join

SELECT 
    tracks.Name AS track_name, 
    genres.Name AS genre_name 
FROM tracks 
LEFT JOIN genres ON genres.GenreId = tracks.GenreId;

Left Join (enrich)

• All rows in the left table
• Columns from right table are added

Inner Join (filtering)

• Only that match are preserved
• All other rows are dropped from both tables

SELECT 
    tracks.Name AS track_name, 
    genres.Name AS genre_name 
FROM tracks 
LEFT JOIN genres ON genres.GenreId = tracks.GenreId;

SELECT 
    AVG(Milliseconds) as avg_millis, 
    genres.Name AS genre_name 
FROM tracks 
LEFT JOIN genres ON genres.GenreId = tracks.GenreId
GROUP BY genre_name
ORDER BY avg_millis DESC
LIMIT 10;

What genres have the longest tracks?

Loading New Data

Load a CSV file

// sqlite> .import {file name} {table name}

sqlite> .import rap_caviar.csv rap_caviar

What should my first query be?