Hands-On Activity:
SQL: Introduction to Databases with SQL

Set-Up

1. Open https://sqliteonline.com/
2. On the bottom right corner, click on the Book Icon, then go to the SYNTAX tab. In the Example Databases, click on CHINOOK
3. Once the dataset loads, the left sidebar should list all of the available tables under Chinook_Sqlite.sqlite:

Album · Artist · Customer · Employee · Genre · Invoice · InvoiceLine · MediaType · Playlist · PlaylistTrack · Track

4. Confirm it worked by running:

SELECT * FROM Customer;

If you see rows of customer data, you’re ready to go.

Note on SQL style: SQL keywords (SELECT, FROM, WHERE, etc.) are case-insensitive, but it’s good practice to write them in ALL CAPS and to spell table/column names exactly as they appear in the schema (e.g. CustomerId, not customerid).

Activity 1: Basic Queries

1. SELECT

Purpose: Choose which columns (attributes) to return from a table.

— Select all columns with *

SELECT *
FROM Genre;

— Select specific columns by naming them

SELECT FirstName, LastName, Email
FROM Employee;

— Select only the unique values of a column with DISTINCT

SELECT DISTINCT Country
FROM Customer;

2. Table/Column Aliasing (AS)

Purpose: Rename a column or table for readability, especially useful once we start combining tables.

— 3 ways to alias: with AS, with quotes, and with no quotes at all

SELECT FirstName AS 'First Name', LastName 'Last Name', PostalCode Zip
FROM Customer;

Use quotes around an alias whenever it contains a space, so SQL doesn’t mistake it for a second column.

3. ORDER BY

Purpose: Sort the result set by one or more columns.

— Sort by last name, descending

SELECT FirstName, LastName
FROM Customer
ORDER BY LastName DESC;

— Sort by multiple columns

SELECT *
FROM Invoice
ORDER BY InvoiceDate DESC, Total ASC;

• Default sort order is ASC (ascending) — use DESC to reverse it.

4. LIMIT

Purpose: Cap the number of rows returned — handy for previewing a large table or grabbing a “top N.”

— Get the 10 most expensive invoices

SELECT InvoiceId, CustomerId, Total
FROM Invoice
ORDER BY Total DESC
LIMIT 10;

Summary

Practice 1

All of the activities below are based on the Chinook database.

Practice Activity 1.1 We are generating a mailing list to send promotional material to our customers. Query a list of full names (first and last) and mailing addresses for all of our customers. To mail something, we need their street address, city, state, country, and postal code.

Answer:

SELECT FirstName, LastName, Address, City, State, Country, PostalCode
FROM Customer;

Practice Activity 1.2 Generate a list of invoices in order of least total amount to greatest total amount. Feel free to use SELECT *.

Answer:

SELECT *
FROM Invoice
ORDER BY Total ASC;

Practice Activity 1.3 Generate a list of the 40 longest tracks in milliseconds. Include the name of the track and its runtime in milliseconds.

Answer:

SELECT Name, Milliseconds
FROM Track
ORDER BY Milliseconds DESC
LIMIT 40;

Chapter Exercise Get a list of the top 10 most-tenured employees (i.e. the ones who were hired the earliest). Display their name, title, supervisor, and the date they were hired.

Challenge: Name the columns something reasonable and meaningful, where applicable.

Answer:

SELECT FirstName, LastName, Title, ReportsTo AS SupervisorId, HireDate
FROM Employee
ORDER BY HireDate ASC
LIMIT 10;

Stretch: swap ReportsTo AS SupervisorId for a self-join on Employee (see Practice Activity 1.2) to show the supervisor’s actual name instead of their ID.

Activity 2: Conditionals (WHERE)

1. Equality & Pattern Matching (=, LIKE, <>)

Purpose: Filter rows where a column exactly — or approximately — matches a value.

— = checks for exact, case-sensitive equality

SELECT FirstName, LastName, Phone, Country
FROM Customer
WHERE Country = 'Brazil';

— LIKE is case-insensitive and more flexible

SELECT FirstName, LastName, Phone, Country
FROM Customer
WHERE Country LIKE 'brazil';

— % is a wildcard for “any characters” — useful when you’re not sure of exact spelling

SELECT FirstName, LastName, Phone, Country
FROM Customer
WHERE Country LIKE '%Czech%';

— <> means “not equal to”

SELECT *
FROM Invoice
WHERE BillingCountry <> 'USA';

In general, prefer LIKE over = for text — it’s case-insensitive and supports wildcards.

2. Comparison Operators (<, >, <=, >=)

Purpose: Compare numbers, dates, or text alphabetically.

SELECT *
FROM Invoice
WHERE Total < 4;

3. NULL Values (IS NULL / IS NOT NULL)

Purpose: NULL represents missing data. You can’t compare it with = — you must use IS NULL or IS NOT NULL.

SELECT *
FROM Invoice
WHERE BillingState IS NOT NULL;

4. Combining Conditions (AND, OR, parentheses)

Purpose: Build more powerful filters by combining multiple conditions.

• AND — both conditions must be true
• OR — at least one condition must be true
• Parentheses () group conditions so they evaluate together

SELECT *
FROM Invoice
WHERE BillingCountry LIKE 'USA'
AND Total >= 4;

— Parentheses control evaluation order

SELECT *
FROM Invoice
WHERE (Total < 1 OR InvoiceDate < '2012-01-01')
AND BillingCountry NOT LIKE 'USA';

5. The IN Operator

Purpose: Shorthand for matching a column against a list of values — avoids writing a long chain of ORs.

— Verbose version

SELECT *
FROM Customer
WHERE State LIKE 'CA' OR State LIKE 'OR' OR State LIKE 'WA';

— Same result, using IN

SELECT *
FROM Customer
WHERE State IN ('CA', 'OR', 'WA');

6. The BETWEEN Operator

Purpose: Find rows within an inclusive range — another shorthand for chained comparisons.

SELECT *
FROM Invoice
WHERE Total BETWEEN 5 AND 10;

Summary

Practice 2

All of the activities below are based on the Chinook database.

Practice Activity 2.1 Return a list of tracks composed by Wolfgang Amadeus Mozart. Include the track name and the composer name.

Challenge: What if you only knew his last name?

Answer:

SELECT Name, Composer
FROM Track
WHERE Composer = 'Wolfgang Amadeus Mozart';

Challenge:

SELECT Name, Composer
FROM Track
WHERE Composer LIKE '%Mozart%';

Practice Activity 2.2 The music store has decided to split the team into two floors by last name, in alphabetical order — people whose last name begins with A–M are on one floor, and N–Z on another.

Write a query to find the employees whose last names begin with A–M. Include their first and last name, and keep the result in alphabetical order by last name.

Answer:

SELECT FirstName, LastName
FROM Employee
WHERE LastName < 'N'
ORDER BY LastName ASC;

Practice Activity 2.3 Return a list of customers who are not associated with a company (in other words, they don’t have a value in the Company column).

Answer:

SELECT *
FROM Customer
WHERE Company IS NULL;

Practice Activity 2.4 To foster company spirit, HR believes it’s important for supervisors to send cards to their direct reports on their birthday. An HR analyst wrote a query to report the data, but forgot to specify whose name was the employee’s and whose was the supervisor’s.

Rename the columns to specify which is the employee and which is the supervisor. (Hint: this previews the WHERE concept.)

Answer:

SELECT e.EmployeeId,
      e.FirstName AS "Employee First Name",
      e.LastName  AS "Employee Last Name",
      s.FirstName AS "Supervisor First Name",
      s.LastName  AS "Supervisor Last Name",
      e.BirthDate,
      e.Email
FROM Employee e, Employee s
WHERE e.ReportsTo = s.EmployeeId;

Chapter Exercise Write a query that obtains invoices with an amount greater than $15, that took place in either the USA or Canada, or from the years 2000–2010.

Challenge: Exclude all invoices that took place in the state of California (CA).

Answer:

SELECT *
FROM Invoice
WHERE Total > 15
AND (BillingCountry IN ('USA', 'Canada')
    OR InvoiceDate BETWEEN '2000-01-01' AND '2010-12-31');

Challenge:

SELECT *
FROM Invoice
WHERE Total > 15
AND (BillingCountry IN ('USA', 'Canada')
    OR InvoiceDate BETWEEN '2000-01-01' AND '2010-12-31')
AND BillingState <> 'CA';

Practice Activity 1.2 To foster company spirit, HR believes it’s important for supervisors to send cards to their direct reports on their birthday. An HR analyst wrote a query to report the data, but forgot to specify whose name was the employee’s and whose was the supervisor’s.

Rename the columns to specify which is the employee and which is the supervisor. (Hint: this previews the WHERE concept.)

Answer:

SELECT e.EmployeeId,
      e.FirstName AS "Employee First Name",
      e.LastName  AS "Employee Last Name",
      s.FirstName AS "Supervisor First Name",
      s.LastName  AS "Supervisor Last Name",
      e.BirthDate,
      e.Email
FROM Employee e, Employee s
WHERE e.ReportsTo = s.EmployeeId;

Activity 3: Aggregates, Functions, and Arithmetic

1. GROUP BY

Purpose: Condense many rows into one summary row per group, so we can run aggregate calculations on each group.

SELECT CustomerId
FROM Invoice
GROUP BY CustomerId;

On its own, GROUP BY just collapses each group down to one row. It becomes genuinely useful once paired with an aggregate function.

2. Aggregate Functions (SUM, COUNT, AVG, MIN, MAX)

Purpose: Calculate a single summary value — a total, a count, an average — across each group.

Count:

SELECT CustomerId, COUNT(InvoiceId) AS NumOfInvoices
FROM Invoice
GROUP BY CustomerId;

Sum:

SELECT CustomerId, SUM(Total) AS TotalSpent
FROM Invoice
GROUP BY CustomerId;

Average:

SELECT CustomerId, AVG(Total) AS AverageInvoice
FROM Invoice
GROUP BY CustomerId;

Minimum:

SELECT CustomerId, MIN(Total) AS SmallestInvoice
FROM Invoice
GROUP BY CustomerId;

Maximum:

SELECT CustomerId, MAX(Total) AS LargestInvoice
FROM Invoice
GROUP BY CustomerId;

3. Arithmetic Operators

Purpose: Perform math on numeric columns directly inside a query.

SELECT Name, UnitPrice, UnitPrice - 0.10 AS DiscountedPrice
FROM Track;

4. String Concatenation (||)

Purpose: Combine (concatenate) two pieces of text into one. || doesn’t add spaces automatically — add them yourself.

SELECT (FirstName || ' ' || LastName) AS FullName
FROM Customer;

Summary

GROUP BY condenses rows into groups so aggregate functions (AVG, SUM, COUNT, MIN, MAX) can summarize each one. Arithmetic operators (+ – * /) and the concatenation operator (||) let you compute new values directly in a query.

Practice 3

All of the activities below are based on the Chinook database.

Practice Activity 3.1 Return a list of composers and the average runtime for their tracks, in milliseconds.

Answer:

SELECT Composer, AVG(Milliseconds) AS AvgRuntimeMs
FROM Track
GROUP BY Composer;

Practice Activity 3.2 Due to rampant inflation, the price of each track is being raised by $0.50. Return a list of tracks and their new prices. The track name should be formatted like this: “[Track Name] by [Composer]”.

Answer:

SELECT (Name || ' by ' || Composer) AS TrackInfo,
      UnitPrice + 0.50 AS NewPrice
FROM Track;

Chapter Exercise Write a query that lists composers and the number of tracks that composer has written.

Answer:

SELECT Composer, COUNT(TrackId) AS NumTracks
FROM Track
GROUP BY Composer;

Activity 4: Multi-Table Queries (JOINs)

1. Primary & Foreign Keys

Purpose: Understand how tables relate to one another before combining them.

A primary key uniquely identifies each row in its own table (e.g. Customer.CustomerId). A foreign key is a column in one table that references a primary key in another (e.g. Invoice.CustomerId points back to Customer.CustomerId). These columns often — but not always — share a name.

2. INNER JOIN

Purpose: Combine rows from two tables, keeping only the rows where the key matches in both tables.

— List of invoices and the customer who purchased each one

SELECT i.InvoiceId, i.CustomerId, c.FirstName, c.LastName, i.Total
FROM Invoice i
INNER JOIN Customer c ON i.CustomerId = c.CustomerId
ORDER BY i.InvoiceId;

SELECT i.InvoiceId, i.CustomerId, c.FirstName, c.LastName, i.Total
FROM Invoice I, Customer c
WHERE i.CustomerId = c.CustomerId
ORDER BY i.InvoiceId;

3. LEFT OUTER JOIN

Purpose: Keep every row from the left-hand table, whether or not it has a match in the right-hand table. Useful for finding rows with no matching data — like tracks that have never been purchased.

— Find all tracks that have never appeared on an invoice

SELECT t.TrackId, t.Name, il.InvoiceId
FROM Track t
LEFT OUTER JOIN InvoiceLine il ON t.TrackId = il.TrackId
WHERE il.InvoiceId IS NULL;

Summary

An INNER JOIN returns only the rows with a match in both tables. A LEFT OUTER JOIN keeps every row from the left table, filling in NULL where there’s no match on the right — which is exactly how you find “orphan” rows with no related data.

Practice 4

All of the activities below are based on the Chinook database.

Chapter Exercise The music store wants to generate a catalogue of the music they sell, containing the track name, artist name, album name, and price.

Challenge: List the genre along with the rest of the information.

Answer:

SELECT t.Name AS TrackName, ar.Name AS ArtistName, al.Title AS AlbumName, t.UnitPrice
FROM Track t
JOIN Album al ON t.AlbumId = al.AlbumId
JOIN Artist ar ON al.ArtistId = ar.ArtistId;

Challenge:

SELECT t.Name AS TrackName, ar.Name AS ArtistName, al.Title AS AlbumName,
      g.Name AS Genre, t.UnitPrice
FROM Track t
JOIN Album al ON t.AlbumId = al.AlbumId
JOIN Artist ar ON al.ArtistId = ar.ArtistId
JOIN Genre g ON t.GenreId = g.GenreId;

Syntax Cheat Sheet