SQL Continued

Learning outcomes:

Data is organized in tables
Tables will have a primary key for each item/record that is unique
Joining tables is so that you can get data from two tables
There are different types of joins depending how you want your data presented
The relationship of the tables is based on the primary key and foreign key
Joins are typically done between primary and foreign keys, but can involve other values as well
To have a join, you need a related column otherwise the join won't know how to work

Let's say we have a table of books and a table of library patrons
From what we know about SQL so far we can look at the contents of each table individually and manipulate the data on each single table
Adding in joins allows us to look at combos, such as who borrowed a book or the borrowing history of each patron
SQL Join types explained visually

We can also use filters or groupings to get data out of the database
FILTER can show particular pieces of data
An introduction to the GROUP BY clause and FILTER modifier with samples to try right in your browser
One example of filtering data is using the following symbols: = != < >
For example you could see all books that cost less than $10, or more then $5 or cost equal to $9.99

An example of how this might look in a SQL command would be :

SELECT * FROM books_table WHERE price < 10;

SELECT * FROM books_table WHERE price = 9.99;

We can also use AND condition and the OR condition to get multiple options selected
AND condition and the OR condition explained with examples
For example, if you wanted to look at books that cost less than $10 AND were published in the last 10 years

An example of how this might look in a SQL command would be

SELECT * FROM books_table WHERE price < 10 AND publish_date > 2015;

We could also look for items that are unique, or distinct using SQL
This would allow us to look at data without duplication
Common use cases of SQL SELECT Distinct
Example 1: If we wanted to make sure we have unique library patrons, we could look for addresses to make sure we are only seeing unique addresses and no duplicates
Example 2: Looking at books we might want to make sure we are looking at a distinct or unique catalog of books, without duplication so we can take accurate inventory of what we might be missing

We can also easily organize our data
Asking the database to give results in any order or organizational pattern we want without changing the data can give us the ability to play with the data in more ways
SQL has the options of Ascending or Descending
These are denoted by ASC and DESC functions, These are used by saying ORDER BY

In SQL this might look like

SELECT * FROM books_table ORDER BY cost desc;

SQL has a number of other functions that are helpful as well
CAUTION: The type of SQL you're using affects what functions there are and how they are labelled
Some functions are there in all the common dialects of SQL and look the same
Here are some examples of what functions look like by dialect
For example, if you wanted to find Natural logarithm of x

Suggested Activities and Discussion Topics:

Would you like to see some more classes? Click here