This guide provides a step-by-step walkthrough of SQL commands using SQLite, covering examples with country data and IMDB data. It includes instructions on how to create databases, import data, perform queries, and optimize performance using indexes.
Open the SQLite3 command-line interface in VSCode Terminal:
> cd data
> sqlite3
SQLite version 3.46.1 2024-08-13 09:16:08 (UTF-16 console I/O)
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
To exit the SQLite3 command-line interface, type .quit:
sqlite3> .quit
Note: If you receive an error stating 'sqlite3' is not recognized ..., try running ./sqlite3 instead.
Create a new database named countries.db (make sure you're in the data folder):
> sqlite3 countries.db
Within the SQLite prompt, set the mode to CSV and import data from countries.csv into a table named countries:
sqlite> .mode csv
sqlite> .import countries.csv countries
View the structure of your database and tables:
sqlite> .schema
Retrieve all records from the countries table:
SELECT * FROM countries;
List all capital cities:
SELECT capital FROM countries;
Identify the highest population among all countries:
SELECT MAX(population) FROM countries; -- Something seems not right.
If you need to start over, you can drop the existing table and recreate it:
DROP TABLE countries;
CREATE TABLE countries(code TEXT, name TEXT, continent TEXT, population INTEGER, capital TEXT);
Set the mode to CSV and import the data again:
.mode csv
.import countries.csv countries
If the CSV headers were imported as data, delete the first row:
DELETE FROM countries WHERE code = 'code';
Now you can find correct maximum population
SELECT MAX(population) FROM countries;
Enable headers and set the output mode to column for better readability:
.headers on
.mode column
Display the data with the new settings:
SELECT * FROM countries;
Get the total count of countries:
SELECT COUNT(*) FROM countries;
List all continents represented in the table:
SELECT continent FROM countries;
-- Better
SELECT DISTINCT(continent) FROM countries;
Count the number of distinct continents:
--- How many:
SELECT COUNT(DISTINCT(continent)) FROM countries;
Find the number of countries in Asia:
SELECT COUNT(*) FROM countries WHERE continent = 'Asia';
Get the number of countries per continent:
SELECT continent, COUNT(*) as total
FROM countries
GROUP BY continent
ORDER BY total;
To sort the results from highest to lowest:
SELECT continent, COUNT(*) as total
FROM countries
GROUP BY continent
ORDER BY total DESC;
Retrieve the continent with the highest number of countries:
SELECT continent, COUNT(*) as total
FROM countries
GROUP BY continent
ORDER BY total DESC
LIMIT 1;
Find countries whose names start with 'U':
SELECT name
FROM countries
WHERE name LIKE 'U%';
Find countries where the third letter is 'i':
SELECT name
FROM countries
WHERE name LIKE '__i%'; -- third letter is i
Add a new country to the table:
INSERT INTO countries (code, name, continent) VALUES('CA', 'Canada', 'North America');
Update the capital of Canada:
UPDATE countries
SET capital='Ottawa'
WHERE name='Canada'; -- What if I forget this WHERE clause?
Warning: Omitting the WHERE clause will update the capital for all countries.
Remove Canada from the table:
DELETE FROM countries
WHERE name='Canada';
Warning: Omitting the WHERE clause will delete all records from the table.
To undo the last change (insert, update, or delete):
BEGIN; -- Start a transaction
DELETE FROM countries WHERE name='Canada'; -- Delete Canada
SELECT * FROM countries WHERE name='Canada'; -- Verify the change
ROLLBACK; -- Undo the last change
If you're satisfied with the change, you can commit it:
BEGIN; -- Start a transaction
DELETE FROM countries WHERE name='Canada'; -- Delete Canada
SELECT * FROM countries WHERE name='Canada'; -- Verify the change
COMMIT; -- Save the change
To learn more about transactions, refer to the SQLite documentation.
Open the shows.db database:
> sqlite3 shows.db
Within the SQLite prompt, check the schema and set output formatting:
.schema
.headers on
.mode column
Retrieve records from the shows table:
SELECT * FROM shows;
SELECT * FROM shows LIMIT 10;
shows, genres, and stars tablesList the first 10 records from the stars table (which contains foreign keys):
SELECT * FROM stars LIMIT 10;
Find all shows categorized under 'Comedy':
SELECT * FROM genres WHERE genre = 'Comedy';
Retrieve details of a specific show by ID:
SELECT * FROM shows WHERE id = 65283;
Get show_ids for comedies:
SELECT show_id FROM genres WHERE genre = 'Comedy' LIMIT 10;
List titles of all shows that are comedies:
SELECT title
FROM shows
WHERE id IN (
SELECT show_id
FROM genres
WHERE genre = 'Comedy'
)
ORDER BY title;
Find people whose names start with 'Steve':
SELECT * FROM people WHERE name LIKE 'Steve %';
List titles of shows starring 'Steve Carell':
SELECT title
FROM shows
WHERE id IN (
SELECT show_id
FROM stars
WHERE person_id = (
SELECT id
FROM people
WHERE name = 'Steve Carell'
)
);
Retrieve all information about 'The Office' by joining shows and genres:
SELECT *
FROM shows
JOIN genres ON shows.id = genres.show_id
WHERE title = 'The Office';
Join shows and ratings to get rating information for 'The Office':
SELECT *
FROM shows
JOIN ratings ON shows.id = ratings.show_id
WHERE title = 'The Office';
Explicit JOIN:
SELECT title
FROM people
JOIN stars ON people.id = stars.person_id
JOIN shows ON stars.show_id = shows.id
WHERE name = 'Steve Carell';
Implicit JOIN:
SELECT title
FROM people, stars, shows
WHERE people.id = stars.person_id
AND stars.show_id = shows.id
AND name = 'Steve Carell';
Enable the timer to measure query execution time:
.timer ON
Run a query to select shows with the title 'The Office':
SELECT * FROM shows WHERE title = 'The Office';
Create an index on the title column in the shows table:
CREATE INDEX title_index ON shows (title);
Run the query again to see improved performance:
SELECT * FROM shows WHERE title = 'The Office';
Before creating indexes:
SELECT title
FROM people
JOIN stars ON people.id = stars.person_id
JOIN shows ON stars.show_id = shows.id
WHERE name = 'Steve Carell';
Create indexes to optimize the query:
CREATE INDEX person_index ON stars (person_id);
CREATE INDEX show_index ON stars (show_id);
CREATE INDEX name_index ON people (name);
Run the query again to see performance improvements:
SELECT title
FROM people
JOIN stars ON people.id = stars.person_id
JOIN shows ON stars.show_id = shows.id
WHERE name = 'Steve Carell';