If you’re reading this series for the first time, you’ll want to start with our introductory post. It explains everything you need to know about this series.
In Part 1 of this series, we covered some basic terminology.
In today’s post, we move on to create a couple simple queries using SELECT statements.
But first, you need to know these four terms:
SELECT specifies the list of columns you want to include in your output.
FROM specifies the list of tables you need to access
WHERE limits the output based on the values you specify
ORDER determines how to sort the output.
A Simple Query
Let’s start with a very simple query:
SELECT * FROM dbo.Products
If we enter this query in SSRS, we get the following output:
This is the simplest possible query in SQL. But even so it contains several mistakes:
- We didn’t specify any columns for output
We asked for everything, and we should never do this in the real world. Database tables can and do change over time. When we use the wild card, the output will change when the table changes. And this can cause reports to blow up.
- We didn’t limit output with a WHERE clause
With no limiting WHERE clause, this query selects every record in the table. Which is fine when you only have 77 records (as in the Northwind Products table we’re using). But it’s not fine when you have several million records in your sales table.
- We didn’t sort the output with an ORDER clause
The output looks like it’s sorted on ProductID, so what’s the issue? Unless you state it, order is NOT Guaranteed. If you want a particular order, you must specify it.
So, let’s fix the mistakes. Let’s start by specifying four columns:
Now, let’s select records using a WHERE clause where CategoryID = 1.
In the bottom right hand corner, we can see that SQL Server only returned 12 rows. The first query returned 77 rows.
When exploring data for the first time, sometimes it’s hard to figure out how to limit output when you don’t know what the data contains. In that case, we have another option for limiting data: The TOP function.
Below, instead of using a WHERE clause, we specify the number of records we want to return with TOP.
Even with no WHERE clause, SQL server only returned five records.
For one final example of a simple select, let’s sort the records using ORDER by ProductName:
Now, ProductID 39 comes before ProductID 38 because “chartreuse” comes before “cote” in alphabetical order.
In our next post in this series, we’ll get acquainted with a single table join.