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.
SELECT name, popularity, followers
FROM artists
ORDER BY followers DESC
LIMIT 5;
name | popularity | followers |
---|---|---|
Drake | 96 | 8775792 |
Eminem | 90 | 7445304 |
The Weeknd | 95 | 5107305 |
Lil Wayne | 88 | 3546066 |
J. Cole | 87 | 2515149 |
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
Raspberry Pi
- Code - Sensor - Database
bash> sqlite3 chinook.db
sqlite> .headers on
sqlite> .mode column
sqlite> .tables
SELECT | Columns / values to retrieve |
FROM | What table to retrieve from |
WHERE | Boolean filters |
GROUP BY | Values to aggregate values on |
ORDER BY | Sort your results |
LIMIT | Limit the number of rows to return |
SELECT *
FROM table_name;
SELECT *
FROM tracks;
SELECT *
FROM tracks
LIMIT 5;
SELECT name, composer
FROM tracks
limit 5;
SELECT name
FROM tracks
WHERE composer = "Miles Davis";
SELECT COUNT(name), composer
FROM tracks
GROUP BY composer;
SELECT
COUNT(name) as count,
composer
FROM tracks
GROUP BY composer
ORDER BY count DESC
LIMIT 10;
SELECT
COUNT(name) as count,
AVG(Milliseconds),
composer
FROM tracks
GROUP BY composer
ORDER BY count DESC
LIMIT 10;
SELECT
tracks.Name AS track_name,
genres.Name AS genre_name
FROM tracks
LEFT JOIN genres ON genres.GenreId = tracks.GenreId;
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;
// sqlite> .import {file name} {table name}
sqlite> .import rap_caviar.csv rap_caviar