Introduction to Databases

Class 4 - Joins & More

Welcome!

Girl Develop It is here to provide affordable and accessible programs to learn software through mentorship and hands-on instruction.

Some "rules"

  • We are here for you!
  • Every question is important
  • Help each other
  • Have fun

Overview

  • SQL Joins
  • Bonus Material (time permitting):
    • Functions
    • Indexes
    • Views
    • Triggers
    • Transactions

Joining Tables

Finally, the real power of SQL!

The SQL JOIN clause combines records from two or more tables in a database. You will primarly use two types of joins, with some variations on these.

  • Inner Join
  • Outer Join

Our Tables

To illustrate these joins, let's say we have TableA and TableB. We want to join these tables in a variety of ways.

TableA                    TableB
=================          =================
id    name                 id     name
=================          =================
1     Pirate               1      Rutabaga
2     Monkey               2      Pirate
3     Ninja                3      Darth Vader
4     Spaghetti            4      Ninja

INNER JOIN

  • The Inner Join is the most common type of join.
  • It's so common that it's the default join type for SQLite.
  • Selects only records that match both of the tables you are joining.
TableA                    TableB
=================          =================
id    name                 id     name
=================          =================
1     Pirate               1      Rutabaga
2     Monkey               2      Pirate
3     Ninja                3      Darth Vader
4     Spaghetti            4      Ninja

Inner Join

Selects only the records that match both TableA and TableB

SELECT * from TableA
INNER JOIN TableB
ON TableA.name = TableB.name;

=================          =================
id    name                 id     name
=================          =================
1     Pirate               2      Pirate
3     Ninja                4      Ninja

Let's Develop It!

Write a SQL Query that returns the following from the Chinook Database.

  • Album Id
  • Album Title
  • Artist Name

Solution

SELECT Album.AlbumId, Album.Title as AlbumTitle,
    Artist.Name as ArtistName
FROM Album
JOIN Artist
    ON Album.ArtistId = Artist.ArtistId;

Let's Develop It!

Write a SQL Query that returns the following from the Chinook Database.

  • Artist Name
  • Album Title
  • Track Name
  • Genre Name
  • Composer
  • Unit Price

Solution

SELECT Artist.Name, Album.Title as AlbumTitle,
    Track.Name as TrackName, Genre.Name,
    Track.Composer, Track.UnitPrice
FROM Album
JOIN Artist
    ON Album.ArtistId = Artist.ArtistId
JOIN Track
    ON Track.AlbumId = Album.AlbumId
JOIN Genre
    ON Genre.GenreId = Track.GenreId;

FULL OUTER JOIN

  • The Full Outer Join produces all sets of records in TableA and TableB.
  • Records from both sides are included, where available.
  • If there is no matching record, the missing side will have a null value.
  • Note: Full Outer Joins are not supported by SQLite
TableA                    TableB
=================          =================
id    name                 id     name
=================          =================
1     Pirate               1      Rutabaga
2     Monkey               2      Pirate
3     Ninja                3      Darth Vader
4     Spaghetti            4      Ninja

FULL OUTER JOIN

Selects all sets of records from TableA and TableB.

SELECT * from TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name;

=================          =================
id    name                 id     name
=================          =================
1     Pirate               2      Pirate
2     Monkey	             null   null
3     Ninja                4      Ninja
4     Spaghetti            null   null
null  null                 1      Rutabaga
null  null                 3      Darth Vader

LEFT OUTER JOIN

  • The Left Outer Join produces all records from TableA, with matching records from TableB if they are available.
  • If there is no matching record in TableB, the right side will have a null value.
TableA                    TableB
=================          =================
id    name                 id     name
=================          =================
1     Pirate               1      Rutabaga
2     Monkey               2      Pirate
3     Ninja                3      Darth Vader
4     Spaghetti            4      Ninja

LEFT OUTER JOIN

Selects all sets of records from TableA and TableB.

SELECT * from TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name;

=================          =================
id    name                 id     name
=================          =================
1     Pirate               2      Pirate
2     Monkey	             null   null
3     Ninja                4      Ninja
4     Spaghetti            null   null

Records in TableA, but not TableB

  • Let's say we want to know all of the records in TableA that don't also appear in TableB.
  • We can do this by adding a WHERE clause to our left outer join.
TableA                    TableB
=================          =================
id    name                 id     name
=================          =================
1     Pirate               1      Rutabaga
2     Monkey               2      Pirate
3     Ninja                3      Darth Vader
4     Spaghetti            4      Ninja

Records in TableA, but not TableB

Remembere that the left outer join gave us null values for the right side if there was no corresponding record in TableB. We can now use this information to isolate the records we want.

SELECT * from TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null;

=================          =================
id    name                 id     name
=================          =================
2     Monkey	          null   null
4     Spaghetti            null   null

Records in TableA OR TableB, but not in both

  • Let's say we want to know all of the records in TableA or TableB, but want to exclude records that appear in both.
  • We can do this by adding a WHERE clause to a full outer join.
TableA                    TableB
=================          =================
id    name                 id     name
=================          =================
1     Pirate               1      Rutabaga
2     Monkey               2      Pirate
3     Ninja                3      Darth Vader
4     Spaghetti            4      Ninja

Records in TableA OR TableB, but not in both

This will give us records unique to TableA OR TableB.

SELECT * from TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null;

=================          =================
id    name                 id     name
=================          =================
2     Monkey	          null   null
4     Spaghetti            null   null
null  null                 1      Rutabaga
null  null                 3      Darth Vader

Let's Develop It!

Write a SQL query that returns album and track information for tracks where the composer information is missing.

Solution

SELECT * from Album
JOIN Track
    ON Track.AlbumId = Album.AlbumId
WHERE Track.Composer IS null;

Ordering Data

Data from a SELECT statement can be ordered with the ORDER BY clause.

SELECT * from Invoice
ORDER BY InvoiceDate DESC;

The results will be ordered from newest to oldest. Following the keyword with ASC gives us the reverse order.

Group By

Data can also be grouped by identical values using the GROUP BY clause.

SELECT * from Invoice
GROUP BY CustomerId;

This query will return all invoices grouped by customer.

Functions

SQLite has three types of functions:

  • Core functions
  • Aggregate functions
  • Date & Time functions


Functions give you built-in functionality that allow you to do more with your data.

Core Functions

sqlite_version() Returns the version of the SQLite library
random() Returns a pseudo-random integer
abs() Returns the absolute value of a numerical argument
max() Returns the maximum value
min() Returns the minimum value
upper() Converts characters into upper case
lower() Converts characters into lower case
length() Returns the length of a string of text
total_changes() Returns the number of row changes since the current database connection was opened.

Aggregate Functions

count(*) Returns the number of rows matching the query
count(id) Returns the number of rows matching the query where the given column is not null
count(DISTINCT col) Using DISTINCT with count returns the unique count
avg() Returns the average value of all non-null values
sum() Returns the sum of all non-null values

Date & Time Functions

date('now') Returns the current date (Example: 2014-01-01)
datetime('now') Returns the current date and time (Example: 2014-01-01 13:12:01)
time('now') Return the current time (Example: 13:12:01)

Date & Time Modifiers

You can pass one or more modifiers to the date(), time() and datetime() functions.

sqlite> SELECT date('now', '2 months');
2014-07-29

Months and days can be passed as a modifier to add to the current date.

sqlite> SELECT date('now', '-55 days');
2014-04-04

Likewise, you can subtract months and days.

Date & Time Modifiers

Other modifiers include:

'start of year' Returns the start of the year
'start of day' Returns the start of the day (00:00:00)
'weekday 0' Advances to to the next date where the day of the week matches the number passed, where 0 is Sunday, 1 is Monday, ..., Saturday is 6.

Date & Time Modifiers

What is the first Monday of next year?

sqlite> SELECT datetime('now', '12 months', 'start of year',
   ...> 'weekday 1');
2015-01-05 00:00:00

Let's Develop It!

Write SQL queries to answer the following questions about the Chinook data.

  • If you bought one copy of every track, how much would you spend?
  • What is the average track price in our store?
  • How many genres of music are represented by tracks sold in our store?
  • How many artists have albums in our store?

Solutions

  • If you bought one copy of every track, how much would you spend?
    sqlite> SELECT sum(UnitPrice) from Track;
3680.969999999704
  • What is the average track price in our store?
    sqlite> SELECT avg(UnitPrice) as 'Average Price' FROM Track;
1.0508050242648312

Solutions

  • How many genres of music are represented by tracks sold in our store?
    sqlite> SELECT count(DISTINCT GenreId) from Track;
25
  • How many artists have albums in our store?
    sqlite> SELECT count(DISTINCT ArtistId) from Album;
204

Advanced Topics

Indexes

Indexes are a special lookup table that an RDBMS uses to speed up data retrieval.

We can think of an index in a database as being similar to an index in the back of a book.

sqlite> CREATE INDEX salary_index ON COMPANY (salary);

You can list all of the indexes on a TableBy using the .indices TABLE command. 

sqlite> .indices Employee
IFK_EmployeeReportsTo
IPK_Employee

Advanced Topics

Views

Views are a specific look at data from one or more tables.

You can think of a view as the results of a query that can be accessed as a virtual table

A view is not a part of the physical schema of a database, and its values are computed dynamically from the data in the database.

sqlite> CREATE VIEW cheap_tracks AS SELECT * FROM Track WHERE UnitPrice <= 0.99;

Advanced Topics

Triggers

Triggers are database operations that are automatically performed when a specific event occurs in your database.

Among other things, triggers often used to insert records into log tables when changes are made to data.

Creating triggers is beyond the scope of this class, but know they exist.

Advanced Topics

Transactions

A transaction is an atomic unit of database operations.

The effect of a transaction is that either all of the SQL statements in a transaction are committed to the database, or all of them are rolled back (no changes).

A transaction begins with the BEGIN TRANSACTION statement, followed by COMMIT or ROLLBACK.

Transactions are beyond the scope of this class, but good to learn if you are dealing with a group of changes that are "all or nothing".

Resources

Questions?

?