One of the purposes of SQL is to retrieve information stored in a database. This is called querying. SQL allows us to ask the database a question and it returns a result set with data relevant to the question. Today, we are going to continue to work with our cats table.
Select is used whenever you want to query data. Let's say we were only interested in taking the data from two of the columns. We write the names of the two columns, separated by a comma.
AS is a keyword in SQL that lets you rename a column or table using an alias. The name must be written in quotes. Below, we are going to change adoption_status to Adoption Status.
Keep in mind that AS does not rename the column in the table. The alias only appears in the result.
DISTINCT is used to return unique values in the output. It filters out duplicate values from a specified column. Let's go over a couple of examples:
might return something like this:
When we add DISTINCT before the column name,
the result would be:
The WHERE clause filters the result to only include rows where the following condition is true. Let’s take a look at an example:
This is saying, select all the rows from cats where the value of adoption_status is equal to “Adopted”.
LIKE is great when you want to compare similar values. It is used with the WHERE clause.
Let’s say we had two cats with similar names, “Kitty” and “K1tty”. We would want a query that shows us all the cats that start with “K” and all the cats that end with “tty”. Take a look at the example below to see how we would do that:
The “_” means you can substitute ant character here without breaking the pattern. This would return the rows of both cats.
The % sign can also be used with LIKE. The below example will return cats whose name starts with “K”.
“K%” matches all cats that start with “K”, “%k” matches all cats that end with “k”, and “%k%” matches all cats that contain the letter “k”.
Unknown values are indicated by NULL. We can’t test for NULL values with comparison operators so we use IS NULL and IS NOT NULL. If we wanted to filter all cats with a name, we would write this:
Between is used with the WHERE clause to filter the result set within a certain range. It accepts two values that are either numbers, text, or dates. If we wanted to see all the cats that are between the ages of 1 and 5(inclusive), this would be our code:
AND and OR
Occasionally, we are going to want to combine multiply conditions in a WHERE clause. Here is what our code would look like if we wanted to find the cats where their ages are between 1 and 5(inclusive) and where their adoption_status is equal to “Not Adopted”:
With AND, both conditions have to be true for the row to be included in the result.
OR is similar to AND except OR displays a row when any of the conditions are true.
ORDER BY allows us to sort the results alphabetically or numerically. This helps data more readable.
If I wanted to sort all the cats by their names from A-Z, I would write:
If I wanted to sort all the cats by age from oldest to youngest, I would write:
DESC is a keyword used to sort the results in descending order (high to low or Z-A). Asc is a keyword used to sort the results from ascending order (low to high or A-Z).
If you are using ORDER BY with WHERE, ORDER BY always goes after WHERE.
Most SQL tables contain hundreds of thousands of records, so we have to use LIMIT. LIMIT is a clause that lets you specify the maximum number of rows the result will have. Take a look at the example below:
Here, we are saying the result set can’t have more than 5 rows. Remember, LIMIT can only go at the end of a query.
We are talking about our last and final query(yay!!!). CASE allows us to create different outputs. It's like the SQL version of if-then logic. Let’s say we want to condense cat ages into three levels:
- if the cat's age is below 1, then it is a kitten
- if the cat's age is between 1 and 3, then it is an adolescent
- if the cat's age is anything else, then it is an adult.
Let’s put this into action!
WHEN tests a condition, and THEN gives us the string if the condition is true.
Thank you for reading my blog post and joining me on my #100daysofcode challenge!