Hi. I’m Jason Roth. In the previous videos,
we installed SQL Server 2008 and began using SQL Server Management Studio.
In this video, I’m going to show you how to write basic database queries in SQL Server
2008. I’m also going to provide some tips and
tricks along the way. Let’s start by launching SSMS.
On the “Start Menu”, expand “Microsoft SQL Server 2008 R2”, and then click “SQL
Server Management Studio”. Type the name of your SQL Server instance,
and then click Connect. In our first video, “What is a Relational
Database?”, we introduced a fictional database that contained experiment data.
This database has three tables: Experiements, Runs, and RunValues.
Recall that relational data is often separated or “normalized” into distinct but related
entities as we see here. A query is just a question about the data.
For example, your manager or professor might ask a simple question, such as,
“What experiments does this database track?” To answer this question, let’s create a
query. On the toolbar, click the “New Query”
button. In the query editor, type: SELECT * FROM dbo.Experiments
The language used here is called Transact-SQL (or T-SQL for short).
The most basic T-SQL query has two main parts: 1. The SELECT keyword, followed by the requested
columns. In this case * indicates all columns. 2. And the FROM keyword, followed by the table
that contains the data. The Experiments table here is prefaced by
“dbo”, which is the schema that owns the table.
This is clearly shown in the Tables list in Object Explorer.
Now, press the Execute button on the toolbar. We get a very common error: Invalid object
name. I can see the Experiments table in the Object Explorer, so why is it invalid?
In this case, I’m not targeting the correct database.
If we look at the toolbar, we can see that this query window is targeting the built-in
“master” database. I’m going to change this to “ExperimentData”.
Note that you could also accomplish this through one additional line in the query window:
Use ExperimentData Run the query again, and you now see the results
window. It shows that there are three Experiments in the Experiments table.
Let’s look at a few other things you can do with queries. The WHERE clause allows you
to filter the results. First, I’ll paste a copy of the existing
query, and then I’ll add the clause: WHERE Experiment=‘Exp1’
Note that strings use single-quotes in T-SQL. Run the query. This time there are results
from both queries. But the second query returned only one row,
where the Experiment name is equal to ‘Exp1’. I’d like to point out two helpful tips for
running queries. First, I find it easier to press F5 to run
the queries instead of using the mouse, and I’ll do that throughout the rest of this
video. Also, if you highlight text in the query window,
only that text will run. This is an incredibly useful feature of the query editor.
Let’s now quickly go through several other query variations.
Instead of using the asterisk to specify all columns, you can specify specific columns.
You can use the ORDER BY clause to order the results using one or more columns.
Specify either DESC for descending order or ASC for ascending order.
You can compose new columns from the returned data.
For example, here I concatenate the experiment name and description and name the result “DerivedColumn”.
Also note the brackets around the Description column.
Description is a recognized keyword, so the brackets tell the database engine to treat
it as a column name instead. Brackets are also necessary for names with
spaces or other special characters. Now we need to introduce two more complex
query concepts: Aggregations and Joins. Both of these are best defined with examples.
Here are the actual temperature readings from various experiment runs.
We can see here that each run can have more than one temperature reading.
What if I want the average temperature for each run. To do that I need to aggregate the
data, which combines the results, based on the RunId.
To aggregate the data, we need to use explicit column names.
We’ll select the RunId and then use the AVG function to average the values for each
run. Finally, we need to add a GROUP BY clause
to indicate that the results should be grouped by the RunId.
So instead of three rows with a RunId of 1, there will be one row and the average of all
values with that RunId. AVG is just one of many aggregate functions.
The GROUP BY clause and aggregate functions can help you to analyze your data in many
different ways. Joins are also explained best with an example.
Joins can return combined results from multiple tables.
This is important in a relational database. For example, here are the results from two
separate queries. The first returns the experiments. The second returns the runs. You can see that
the Runs refer to experiments by the ExperimentId column.
But what if I want to have a single query that returns runs along with the associated
experiment name. In this case a join is required.
We’re interested in adding Experiment information to the Runs query, so let’s start by selecting
from the Runs table. When using joins, you should alias the tables
to be able to more easily refer to the columns of each table.
In this query, I’ll alias the Runs table as ‘R’ and then prefix the Runs column
with ‘R’ period. To add information from the Experiments table,
I’ll use the INNER JOIN operator. Note that I first alias the Experiments table as ‘E’.
I then specify that the ExperimentId column in the Experiments table relates to the ExperimentId
column in the Runs table. The final step is to add the E.Experiment
column to the column list. When I press F5 to run this query, I see that
the Experiment name is now added to the Run results.
Obviously, I can’t cover all of Transact-SQL in several minutes. So I’d like to use the
rest of this time to share some learning tips. First, when you’re experimenting with increasingly
complex queries, construct it one piece at a time.
We already started doing this with the join example. Let’s do more.
I’ll add the MIN aggregate function to determine the minimum start time for each Experiment.
Run the query to make sure that it worked. And then I’ll add an ORDER BY clause to
sort the results by the minimum start time. Again, run the query to see the effect of
that change. Because I test each change, I’m able to
more easily and accurately construct a complex query.
Now, let’s say you finish watching this video and completely forget how to write a
query. I highly recommend leveraging SSMS to help
you both learn and remember Transact-SQL. Let me show you one way.
In Object Explorer, right-click on the Experiments table, select “Script Table as”, click
“SELECT to…”, and then click “New Query Editor Window”.
It brings up the basic select syntax that you can edit and run.
This technique also provides script examples for other types of queries that we didn’t
have time to cover, such INSERT and UPDATE statements.
My final tip is to point you to the numerous examples in the documentation.
Open your favorite search engine. Search for: Transact-SQL SELECT site:msdn.microsoft.com
One of the first links should be the Transact-SQL documentation for the SELECT statement.
A link to examples is at the bottom of the topic. For many people, using Transact-SQL
examples is the fastest way to learn. More information and resources can be found
on http://learningsqlserver.wordpress.com. For example, to create the ExperimentData
database used in this video, you can select the “Video Series” link,
and then click the link “ExperimentData Database Script”.
Copy the script and run it in a New Query window in SSMS. This creates the database
and adds the data. At that point, you can try all of the queries
in this video and experiment with other variations. I hope this has been a helpful introduction
to using queries. Look for more videos on learning SQL Server
coming soon. Thanks for watching!!