BYU-Idaho Online Learning
Video Transcript
Single table
[One speaker.]
[Any necessary descriptions.]
[On screen: Retrieving Data from a Single Table
CIT 111]
Narrator: [Screen reads: Retrieving Data from a Single Table.]
Let's look at the five basic clauses of the Select statement. Select, from, where, order by, and limit. Select is really the only one that is required, but they do need to be used in this order as they are shown here for them to work properly. Let's look at what each one of them does.
[On screen: 5 Clauses of the Select Statement
Here we see a query using all five of the clauses [image of a query is shown]. Below the query, we see the result set or the data that is returned when the query runs. [On screen: SELECT describes the column in the result set] “Select” describes the columns in the result set or values that will be returned when queried. This is where you will list all the fields or columns that you want to show up in the result set. You separate each column with a comma. Here we want first name and last name to show up.
[On screen: FROM what table are you getting the data from] “From” tells what table you're getting the data from. This is always required if you're getting data from a database table. In this example we want the data to come from the artists table.
[On screen: WHERE filters the rows, only those that match the condition] “Where” is where you can place criteria for what data you want from the table to filter the rows down to only those that match a condition that you state. Here we only want names of artists from Italy.
[On screen: ORDER BY how to sort rows] “Order by” will sort the rows in the order you specify, like alphabetizing or sorting biggest to smallest or smallest to biggest, etc. Our results will be alphabetized by last name A to Z.
[On screen: LIMIT the number of rows to return] “Limit” tells how many rows of the result are set to show. Here we are limiting the result set to the first two rows that are returned. So we see our results that shows two artists: Leonardo DaVinci and Michelangelo. Their first and last names from the artists table and they both are from Italy and they are sorted by last name alphabetically and only two rows show up.
[On screen: 4 Ways to Code Column Specifications with SELECT
Let's look more closely at the “select” clause. There are different ways to code columns with the “select.” You can reference all the columns of a table with an asterisk or list column names like we saw in our example. You can also use calculated columns and functions as part of the “select” clause.
[On screen: SELECT * FROM artist. Table is shown] When you use the asterisks, every column in the table will be returned. It is okay to use an asterisk this way but once your project is developed and in production, you should probably avoid using it. [On screen: SELECT fname, lname FROM artist. Table with fname and lname is shown] You can also list each column separated with commas. Then only those columns listed will show up in the result set.
[On screen: SELECT fname, lname, dod-dob AS age_at_death
FROM artist.
Table is shown.]
You can show calculations in the Select clauses “where” as well.
Here we are using the date of death minus the date of birth to get the age of the artist at their death. Please note here that usually a date year would have a date datatype, but these years are only numbers. Year data type values can't be before 1970 and since many of these artists were born or died before 1970, we're not using the year date datatype here. So to subtract dates properly you should use different date functions that we'll go over in a later lesson.
Here also note the “as” keyword is followed with an alias name for the column header. If we left out this “as” and the alias name, then the column header would have just been whatever the calculation was. In our case, it would have said DoD - DOB. So, the alias is a nice way to make sure we have a more meaningful column header in our result set.
[On screen: Arithmetic Operators
Table is shown with Operator, Name, and Precedence.]
The Select statement calculations recognize all of these with arithmetic operators. If you end up using more than one operator in a single calculation, be aware of the order just like we learned in elementary school, there's an order of precedence. The third column here shows the precedence from left to right as the calculation is processed. So for example, multiplication, division, and modulus would be done first from left to right and then addition and subtraction.
[On screen: SELECT CONCAT (fname, ‘ ‘, lname, ‘ was ‘, dod - dob, ‘ years old at death’) AS “Death Age”
FROM artist.
Table is shown.]
Functions can also be a part of the Select clause. Here is an example of a function called concat that takes groups of strings and in this case a calculation and joins them together. There are many different types of functions and we'll learn about these functions in a different lesson, but the point here is that functions can be used in the Select statement.
[On screen: Image of two queries and titled as DISTINCT.] If you end up having a query with a result set that has repeated values, you can use the distinct keyword to eliminate the duplicate rows included in the result set. [Image of a query is shown] Here's a query getting all of the countries of the artists and if they're local or not we can see that some of the result set rows are duplicates. [Image of a query is shown] Here is a query, the same query, with a keyword distinct after the Select keyword. Now you see the distinct prevents duplicate rows from being included in the result sets.
[On screen: WHERE. The WHERE clause used with a SELECT statement filters the rows in the base table so only the rows you need are retrieved.] The “where” clause will filter the rows from the result set according to the conditions that you give. This enables you to see just the rows that you need. Comparison operators are used in the “where” clause.
[On screen: COMPARISON OPERATORS
= Equal
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
<> Not equal
!= Not equal]
Comparison operators do just what they say: they help you compare one value or stream to another. Are they equal? Is one bigger or comes later in the alphabet or smaller? Or are they not equal to each other? Then, according to the results of the conditions, only those rows in the result set will show up. Let's take a look at these comparison operators in action.
[Image of a query is shown with an equal symbol next to it] In this first example, using the equal sign, the “where” clause reads where the country is equal to Italy. So only the artist's first and last name will show up if the country is equal to Italy. Italy is another attribute in each row of the artists table. Even though we don't show the column in the Select statement we can still filter using country. So Leonardo and Michelangelo are both from Italy.
[Two images of a query are shown with a greater than symbol next to one and a greater than or equal to symbol next to the other.] Let's look at the greater than and less than sign. In the example on the left, the “where” clause states where DOB, or date of birth, is greater than 1606. So only the artist will show up if their date of birth is greater than or after the year 1606. The example on the right adds the equal sign to the greater than sign. Now you can see all the artists whose date of birth is after or on the year 1606. So Rembrandt who was born in 1606 now shows up as well. Before it was only those born after 1606.
[Two images of a query are shown with a less than symbol next to one and a less than or equal to symbol next to the other.] On the left here if we use the less than sign you're seeing show everyone that was born before 1606. With the less than an equal sign on the right, we're saying show everyone that was born before or on 1606. So again, Rembrandt will show up.
[Image of a query is shown with both “not equal” symbols next to it.] The less than and the greater than sign used together or the exclamation and equal sign together means not equal to.
Our where clause here says, where date of birth is not equal to 1606. So here we want everyone who was not born in 1606, which is everyone except Rembrandt, because he was born in 1606, so he does not show up.
[On screen: AND, OR, and NOT
AND
OR
NOT
You can combine multiple operators in different ways to build your results down even further. When you use the “and” between two different conditions, both conditions have to be true for it to show up in the result set.
So now the where clause states “where country is equal to Italy and last name is equal to DaVinci.” Not only does the artist have to be from Italy, he also has to have the last name DaVinci. Both those conditions have to be true. So “and” will filter down really narrowly to get just the results that match both conditions. Only Leonardo or only DaVinci has the last name that matches and is from Italy. So he will be the only one in the results set.
When you use “or” between the same two conditions or similar conditions now either of the conditions can be true for it to show up in the result set. Everyone from Italy or those with the last name Picasso will show up. So now our filter is not as narrow. Everyone from Italy and anyone with the last name Mocoso will show up in the result set.
All right, you can also reverse or negate your condition with “not.” This will then show everyone who is not from Italy. There is also an order of precedence with and or and not as well. The nots will be evaluated first, then the ands and then the or’s. If you want to change the order that they're evaluated and you would just use parentheses around those that you want to have done first, just like in math.
[On screen: IN, BETWEEN, LIKE, REGEXP and IS NULL.] There are even more operators that can be used with the where clause to filter down your results. In, between, like, and regular expression are some. With the “in” operators a test expression is compared to a list of expressions in the “in'' phrase. If the test expression is equal to one of the expressions in the list, the row will be part of the result set. Here we see that if country is equal to France or Italy, the raw will show up in the result set. It's the same as if we had said where country is equal to Italy or country is equal to France in a compound operator, but the “in'' phrase makes the syntax a little simpler.
The “between” operator when used in a where clause will allow you to compare a test expression with a range of values. If the value falls within the range, the row will be included in the result set.
Here are a few examples of how that might work. In the example on the left if the date of birth lands in the range between year 1500 to the year 1900, it will show up in the result set. The example on the right shows that you can also use it alphabetically. If the last name alphabetically lands between the letters H and the letter W, then it will be included. Something to note here with alphabetical ranges, if we had a name of Williams or Watters, they would also not be included because W is always less than WA and so on. So if you wanted to include any name that started with W you'd have to put the range between H and X or H and W Z or something like that.
The “like” operator matches specific simple string patterns to a test expression. In the first one you are saying if the last name starts with van and the percentage sign it's saying that the van can be followed by any number of characters after it. This matches two different last names and then “go” and then “range” show up. Another symbol that can be used with like is the underscore character. Instead of any number of characters like the percent sign the underscore represents any single character. So the percent sign and the underscore characters are like wildcard characters that can represent anything; the percent sign being any number of characters the underscore any single character.
The Regexp, or regular expression operator, allows you to create much more complex string patterns to test expressions. There are many regular expression symbols that can be used, here are just a few. The top-left example is doing the same thing that the like example did, getting all the last names that begin with van. The caret symbol this time represents the beginning of a stream. The next one down gets all the first names that end in T. The dollar sign represents the end of a stream.
The last example is getting any first name that has an “en” or an “an'' anywhere in it. So Vincent has an “en”, Rembrandt has an “an“, and so forth. There are lots of regular expression symbols but we only want to introduce it here and let you know it's available, but not teach regular expressions in depth in this course.
You can even find “all nulls” with “is null” or all rows that don't have nulls with “is not null.” The example on the left is showing all rows that have a null for the middle name. The other is showing all the artists that do have a middle name. The middle name is “not null” or “not empty.” Okay we're getting there. We have two more the five clauses to go, there's lots of information here.
[On screen: ORDER BY specifies the sort order for the rows in the result set.] Okay, order by. Order by specifies the sword order for the rows in the result set. Most of the time you use a column name to tell how you want it sorted. [On screen: Sort by last name. Image of a query is shown.] In this example the results are alphabetized by last name. The default is to sort in ascending order. If you want to reverse the order to descending, you have to use the deSC keyword.
[On screen: Sorting by last name descending. Image of a query is shown.]
This one shows the same query but with a keyword descending deSC added. Now the order is reversed. Those later in the alphabet show up first.
[On screen: Sorting on multiple columns. Image of a query is shown.] You can even sort on multiple columns. This is sorted by country and then within country by date of birth.
[On screen: LIMIT. The maximum number or rows to returned.] The limit Clause specifies the maximum number of rows that will be returned. Even if there were hundreds of rows returned, if you said limit five, then only the first five rows would be returned. [Two images of queries shown labeled without LIMIT and with LIMIT] Here on the left is a query one with all the rows that fit the criteria of the where. On the right if we add “limit for” at the end, then only four rows would be returned. So again you might have thousands of results but you only need to see the top ten or something like that, that is when “limit” would come in handy. So there we have the common clauses of getting data from a single table
[End of video.]