SQL Continued

Learning outcomes:

  • Demonstrate using sql with multiple filters
  • Use SQL with “distinct” and ordered by ascending and descending

Would you like to download my PowerPoint to follow along?

  • Table joins
    • 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
  • Examples of Table joins
    • 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
  • SQL filters
    • 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;
  • Adding in multiple options
    • 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;
  • SQL distinct
    • 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
  • SQL ascending and descending
    • 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;
    • How to Sort in SQL
  • Other functions with SQL
    • 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
      • Standard SQL says it's LN(x)
      • MSSQL says it's LOG(x)
      • MySQL says it can be either one
  • How SQL queries are processed

Suggested Activities and Discussion Topics:

Would you like to see some more classes? Click here