1. Declare yourself
In this lesson we'll look at the use of variables - why they are useful, how to create them and assign values to them.
We'll also look at creating temporary tables, which can help us carry out analysis tasks when we don't have access or permissions to other tables.
2. Variables
First of all, what do we mean by a variable? It's helpful to think of it as a placeholder for a specific value, of a specific data type.
Why is that useful? Well, it means we write less repetitive code.
Let's imagine our boss wants all the information from the artist table about AC/DC. That could happen, right?
So we write a query and give them the results.
Now they want to know about U2.
So we write another query.
Then they want to know about another artist. And another. Do we really want to write a new query each time?
A better way is to create a variable.
Variable names begin with the 'at' sign , so we could
perhaps call this variable "@my_artist".
Then, in the query, rather than writing the artist name, we refer to "@my_artist".
And then all we need to do is update the variable each time we run the query.
One query, easily modified, for repetitive use.
Let's see how to do it.
3. DECLARE
We use the DECLARE keyword to create a variable.
Then we type the variable name, beginning with the 'at' symbol.
Here are a couple of examples.
First, we can define a variable, @test_int, which is an integer.
Or, following on from the previous slide, we can create @my_artist, which has the varchar datatype, accepting anything up to 100 characters in length.
So that creates a variable, but how do we pass a value to it?
4. SET
In order to assign a value to a variable, we use the keyword 'SET', immediately after the DECLARE statement.
Here we see our previous integer variable, @test_int, which we have declared, and now SET to a value of 5 by typing SET, then the variable name, the equals sign, then the chosen value, in this case, 5.
Similarly, we can assign the value 'AC/DC' to our @artist variable as shown. Then we can refer to these variables in a WHERE clause.
5. Declaring more than one variable
Here's an example of declaring two variables, in this case and @my_artist, and @my_album.
We DECLARE them both first, then SET the values, and then finally we write our SELECT query.
We can see that in the WHERE clause, the artist should equal @my_artist, and the album should equal @my_album.
If we then need to change the artist and album combination, we simply update the SET section of the query, and run it again.
6. Temporary tables
Imagine we've written a query, joining information from different tables, with nicely aliased column names.
It would be great if this was a table that we could use for further work.
The good news is, we can create a temporary table.
And once we've created it, it's available for further querying for the remainder of our session.
How?
We write our SELECT query, but in between SELECT and FROM, we use the keyword INTO, and then type our desired table name, prefaced by the hash (#) sign.
The table will exist either until our connection or session ends, or we manually remove it with the 'DROP TABLE' statement.
7. Let's declare some variables!
OK, it's time to declare and set some variables.