Previously we looked at how to set up a database, create a table, and insert data into the table using SQL statements. Once you have a database containing data the next step is to be able to retrieve data, and to retrieve exactly the cross-section of data relevant to your needs. We briefly mentioned how to access all the data in a table, so we’ll start by looking at that statement one more time. Here is how you would write a query to return all data from the Customers table we created in the previous article:
SELECT * FROM Customers;
But when we don’t want everything in table we can use additional keywords to narrow the focus of our query. For instance, maybe we only want the names of our customers and we don’t need the phone numbers or e-mail. In this case we can specify the columns we want to return:
SELECT FirstName, LastName
FROM Customers;
Now it will return only the first and last names of the customers. Another example, here we only want the last name and phone number of each customer:
SELECT LastName, Phone
FROM Customers;
This will select only the specified columns, but what about rows? Typically a database will have hundreds if not thousands of rows and one of the first things you’ll want to do is pair it down to only the rows relevant to your work. There are many ways you can go about this, as well as ways to organize the data. We’ll start simple, and try to retrieve a single row, in this case the data for a specific customer. To do this we’ll use the WHERE clause to specify a condition:
SELECT *
FROM Customers
WHERE FirstName = ‘Ben’;
This will only return rows where the FirstName column contains the string “Ben”. In our table there is only one Ben, but maybe we don’t don’t know that, and we need to make sure we get the right one, so we’ll specify both with the operator AND:
SELECT *
FROM Customers
WHERE FirstName = ‘Ben’ AND LastName = ‘Jones’;
Now it will only return rows where FirstName is “Ben” and LastName is “Jones”. Similarly you can use other operators like OR and NOT to write conditions. Sometimes you’ll only want a range of data, where a numeric value is within a certain range. This turns out to be very simple to accomplish with the keyword BETWEEN. Let’s say we want to pull up all customers who are at least 18, but no older than 21:
SELECT *
FROM Customers
WHERE Age BETWEEN 18 AND 21;
Notice that the BETWEEN operator is inclusive, which means that it includes the numbers specified. You can also arrange the results according to the values in a certain column by using the keyword ORDER BY. As you might expect using ORDER BY on a numeric column will order the rows in ascending order by the values in that column, that is smallest to largest. If you want it sorted in descending order instead you can follow it with DESC to order it from largest to smallest. If you use ORDER BY with a column containing Strings it will arrange them in alphabetical order, or reverse alphabetical order if you use DESC. Here’s how the statement looks:
SELECT *
FROM Customers
ORDER BY LastName;
Two more handy things to know about are the MIN() and MAX() functions. As you might have guessed, these are for returning the minimum or maximum of a selected column. They are typically used in the SELECT part of the statement:
SELECT MAX(Age)
FROM Customers;
This will return the maximum value from the age column, or if we use MIN() it will return the minimum. You can use these and other operators and statements in different combinations to retrieve exactly the data you want from the tables in your database. These become even more powerful when combined with Joining, which we’ll discuss next time.
Top comments (0)