The SELECT and FROM Clauses

SELECT and FROM

The first clause in any query is the SELECT clause. The SELECT clause contains either a list of the columns you want returned from the query separated by a comma, or the wildcard *. The second clause in the query is the FROM clause. The FROM clause indicates against which table to run the query.

The wildcard * when used after SELECT means that all the columns in the table should be returned and they are presented in the order in which they are found in the original table.

The query to see everything in the Intakes table would look like:

SELECT *
  FROM austin_animal_center_intakes

And the first seven rows of the result would look like this:

Okay, we got a report with all of the data in the table by running a SELECT * query. But what if we didn’t want all of that information? What if we only wanted to see what type of animal, which sex, what age, and what condition they were in on intake? That query would look like this:

SELECT animal_type,
       sex_upon_intake,
       age_upon_intake,
       intake_condition
  FROM austin_animal_center_intakes

And the first several rows of the results would render as:

SELECT AS

Another way you can change the presentation of the data–gussy it up, if you will–is to replace the column names in the dataset with something more readable or meaningful to your purpose. In the last example, our query returned data on the type, sex, age and condition of the animals. If we wanted to rename the columns to match those names in our results we would select the same columns, but we would use the keyword AS after each column name followed by the column name we would like to see. AS introduces the column name you would like to see in the results of a query.

To get the column names written the way we want, we would write the query thusly:

SELECT animal_type AS Animal,
       sex_upon_intake AS Sex,
       age_upon_intake AS Age,
       intake_condition AS Condition
  FROM austin_animal_center_intakes

And the table we’d get back would look like:

If instead of replacing the column names with one-word titles we wanted to just take out the underscores, we’d need to write the query using backticks:

SELECT animal_type AS `animal type`,
       sex_upon_intake AS `sex upon intake`,
       age_upon_intake AS `age upon intake`,
       intake_condition AS `intake condition`
  FROM austin_animal_center_intakes

Resulting in:

Adding capitalization to our changes would yield:

SELECT animal_type AS `Animal Type`,
       sex_upon_intake AS `Sex Upon Intake`,
       age_upon_intake AS `Age Upon Intake`,
       intake_condition AS `Intake Condition`
  FROM austin_animal_center_intakes

And the resulting table would look like:

SELECT DISTINCT

The dataset we are using for our examples is very large. There were 54,724 animals taken in by the Austin Texas Animal Center in the almost three years covered by the dataset. If you wanted to know the different types of animals that were taken in (were they all dogs and cats, e.g.,) you could scan through all the data, or you could use the nifty little modifier for the select clause called DISTINCT. DISTINCT is used in a SELECT clause to return unique combinations of data across all the columns returned by the query.

The query for distinct animal types would be written like this:

SELECT DISTINCT animal_type
  FROM austin_animal_center_intakes

Returning:

If you wanted to show only the unique animal types, but you want to see the columns for age and sex as well you might construct a query like this:

SELECT DISTINCT animal_type,
       sex_upon_intake,
       age_upon_intake
  FROM austin_animal_center_intakes

It is a legitimate query so you wouldn’t get an error, but the resulting table would not have distinct values for just animal type. Instead the values considered to be distinct would be each combination of type, sex, and age. The DISTINCT qualifier refers to a unique row of data, not a unique column. This is what the first few rows of the results would look like:

For this dataset the number of records (rows) returned was reduced to 539 of the original 75,947.

Exercises

Exercise 1

Write a query against the allergies table that returns all the records.

Try query

Exercise 2

Write a query against the allergies table to return the columns start, stop and description.

Try query

Exercise 3

Write a query against the allergies table that shows a list of every allergy with each one only showing up once.

Try query