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

Retrieving Data

The first thing we will learn is how to get data out of a database in a variety of ways. We will begin with the simplest possible queries; move on to filtering our result set; join tables together to retrieve the data we are interested as quickly and easily as possible.

  • Retrieving DataSELECTing a collection of data

    SELECT is the best and easiest way to begin working with a SQL database! But as we’ll see later on, it is by far the most complex type of query we’ll encounter.

  • Retrieving DataEXERCISE: Selecting Columns

    Selecting all columns in a table is generally inappropriate for a production app. We’ll explicitly pick which columns we need for several collections of data that our app needs, and witness the improved performance gained by making this simple change.

  • Retrieving DataFiltering via WHERE clauses

    It is often undesirable to work with all tuples or “rows” from a given table. Adding a WHERE clause to our SELECT query allows us to specify one or more criteria for filtering the result set, down to only what we are interested in.

  • Retrieving DataEXERCISE: Filtering via WHERE clauses

    We’ll add WHERE clauses to the collection queries for two pages on our app.

    • On the products list page, we’ll allow the user to filter by those products that need to be reordered, those that are discontinued, or the full list,
    • On the customer list page, we’ll add a rudimentary search field and use a LIKE clause to find matching rows.
  • Retrieving DataLunch

    Break for Lunch

  • Retrieving DataLIMITing and ORDERing the result set

    Particularly when working with large collections of data, it is important to be able to sort data the way we want and paginate or scroll through the results. We’ll learn how to use LIMIT and OFFSET to retrieve the records of interest, and ORDER BY to sort.

  • Retrieving DataEXERCISE: Sorting and Paging

    In our example app, the orders page has over 16,000 records. This is way too much data to show to users all at once. Even looking at an individual customer’s orders is a bit overwhelming. We’ll use the existing user interface for sorting and pagination, and modify the “orders list” and “customer orders list” queries as appropriate.