SQL Fundamentals

Most web applications rely on storing their data in a relational database, consisting of tables which are comprised of columns and rows. PostgreSQL, MySQL and SQLite are the most popular and established relational databases, and luckily, they have a LOT in common.

SQL Fundamentals

  1. Courses
  2. SQL Fundamentals
  3. Foundations of Relational Databases

Foundations of Relational Databases

Before we dive into our workshop project, we will spend some time to lay the foundation for relational databases and SQL. Learning whether a given task is best handled by your database or application layer is a big part of ensuring your apps perform well under heavy loads.

  • Foundations of Relational DatabasesWelcome and Tech Check

    We’ll get to know each other and ensure everyone is set up for the workshop project.

  • Foundations of Relational DatabasesRelational Algebra and Codd’s Relational Model

    In 1970, Edgar Codd invented a new way to model large, organized and shared piles of data using the expressive semantics provided by relational algebra. Today, virtually all relational databases are still based on these fundamental principles. We'll cover the conceptual models behind tables, columns, result sets and "joins".

  • Foundations of Relational DatabasesStructured Query Language

    Virtually all relational databases use some variant of a (mostly) declarative programming language called Structured Query Language (SQL) to perform operations. We’ll learn what SQL looks like, and try writing a few statements together.

  • Foundations of Relational DatabasesThree forms of SQL databases

    We’ll focus on a few types of databases, all of which fit our definition of “SQL Databases”.

    • Hosted databases exist as a completely independent system component, often running on their own server. PostgreSQL and MySQL are among the most popular hosted relational database products today.
    • Embedded databases are often packaged with an application instance, often as a file on disk. From the outside world, it’s hard or impossible to separate “app” from “database”. We'll be working extensively with SQLite - a very popular relational embedded database widely used in mobile, desktop and web applications.
    • Spreadsheets can also be regarded as databases. Although more limited than the other types of databases, a surprising amount of SQL syntax can be used to perform advanced queries and calculations! We'll be using the google visualization API, which allows us to “query” a google spreadsheet using a SQL-like syntax and get JSON back.

  • Foundations of Relational DatabasesDB Management Tools

    We’ll look at a few tools that will help us on our journey to learn more about SQL databases.