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.