You can do math with SQL! Find out how to use the basic operators, and explore some of the more advanced commands.
- [Instructor] SQL supports various ways of doing math, and using mathematical functions on values and records. The most basic way of doing math in SQL is with a select statement and a basic operation. SQL supports the standard arithmetic operations, addition, subtraction, multiplication, and division, and also the modulo operator. Mathematical operations are assumed to be integers unless one or more terms is expressed in floating point, so you may get unexpected results if you don't pay attention to that. Let's take a quick look at doing some addition with integers.
I'll write select, four plus two and a semicolon. And then I'll run the statement and here I get the result of six. I can change this to one divided by three and I get zero. One divided by three in the integer space is zero, but one divided by three point zero, a floating point value, is what we would expect, 0.333 and so on. The precision of the result is still limited, though, so it's a good idea not to rely on SQL math for financial or scientific applications where high precision is necessary.
We can also use comparison operators and ask logical questions and there are functions available to find the minimum, maximum, sum, and other aspects of numeric fields. For example, we can ask if three is greater than two. The result here is one, the logical expression for true. Is two greater than three? Zero, false. Is three equal to three? True.
Is five not equal to three? That's also a true statement. We can use these operators to help us return information from the database as well. I'll write a statement that asks for a first name and a quiz score from our people table and I'll ask for the records which have a quiz score of more than 70. So I'll write select, first name, quiz points, from people, where quiz points are greater than 70.
I'll run that and I have 551 rows. Or if we wanted to include the people whose score is exactly 70, we could change the query to greater than or equal to. And I've picked up a few people, 657 rows have been returned now. And if we wanted to find out what the maximum and minimum scores on the quiz were, we could use the max and min functions. I'll write select, max, and in parentheses I'll put the field that I'm interested in, in this case, quiz points.
And also use the minimum function with min and in parentheses there I'll put the same field, quiz points. And here I can see the maximum score on my quiz was 100, and the minimum score is 10. If we wanted to figure out what the total number of points earned in our quiz was, we could use the sum function. I'll replace this part of my select clause and instead use the sum function.
Inside the parentheses I'll use the field that I want to sum up and this time I'll spell it correctly and it looks like we have a total of 68,740 points. That's a lot of points. Let's take a moment and combine some of what we know so far. It's nice to have all of this information but it's better to get some kind of insights out of it so let's put together some helpful information. Let's build a little report that shows the breakdown of how many people are on each team, the total points earned by each team, and each team's average score.
Because we're breaking down the data by team, we know that we want to use a group by clause on the team field, so I'll start kind of backwards with that. I'll write select, from people, group by team. And then to get the team name, we can add team in here in the select clause. And adding in the other fields, let's start with a count function, with a star because we just need a number of rows and because this is working with the group by clause, this count will apply to each team, not the whole table.
I can run this here and see how that works. If I took off the group by, I would just get a count of the entire table, 1,000. But I'll undo and put that group by team back. And then let's add in the total points that each team got, with sum quiz points. And now for the average, which is the sum of items divided by the number of items and we'll use parentheses to group the expression together.
I'll move to the next line here. And start with a set of parentheses. We want the sum of the quiz points and divide it by the count of the number of records like we have in the previous columns. And because that's fairly long, I'll alias this as average. Good, I can see that we have a clear winner, both in points and average score, good for green team.
- Name the predicate of the following statement: SELECT EyeColor, Age FROM Student WHERE FirstName = 'Tim' ORDER BY LastName ASC;
- Explain what to use to enforce the order in which an expression must be evaluated if the WHERE clause contains multiple expressions to evaluate.
- Identify the best option to join two tables in a database to be able to display data from both.
- List a data type that is not numeric.
- Determine the result of running the following statement on a table containing columns col_1 and col_2:
- INSERT INTO Box (col_1, col_2) VALUES ('A', 'B'), ('A', 'B'), ('A', 'B'), ('A', 'B');
- Determine the best approach of deleting Jon Ramirez (ID 3452) from a Student table.