In the previous post, we installed SSMS and discussed some of the things that SQL can do. Let’s jump straight into writing our first query. Right-click on the Northwind database in the Object Explorer and click on New Query.
This will open a blank query window for you to type your code into.
The SELECT Statement
The SELECT statement is the basic operation that is used to pull data from a table. Type the following code into the query window and press F5 or click the execute button.
SELECT 'Hello World'
This is what you should see in the results window. In this statement we are selecting the constant string ‘Hello World’ rather than specifying a table to pull data from. In the bottom right corner, we can see the number of rows returned and the time that it took to run the query.
Now let’s try to pull some data from an actual table. Let’s try running this next command.
SELECT * FROM Employees
You should see something like this. Let’s break down what we are seeing.
Along the top of each column we have a column name such as EmployeeID, LastName, FirstName, and so on. The columns are defined by a table object and will be the same for every row (regardless of if there is data for that column and row or not!) So what exactly is a table?
A table is a database object that stores data in a specific format. It consists of horizontal rows and vertical columns. If you have a background in object oriented programming, you could think of the columns of being like a class and the rows like an instance of that class. Let’s go back to our original query.
SELECT * FROM Employees
We know now what SELECT means and that the FROM keyword is specifying the table that the data is being pulled from. What does the * mean? The * means that we will be selecting all columns from the specified table. What if we don’t want every column? If this is the case, then you can specify which columns to pull by replacing the * with only the column names that you want to see. Multiple column names will need to be separated by commas. Let’s try it out:
SELECT LastName, FirstName, City, Region FROM Employees
When we run the code this is what we get.
So we still get the same amount of rows but now we only see the columns that we specified. Great! But what is that highlighted entry that says NULL? NULL simply represents a missing value for a row. Pretty easy so far right? Feel free to run select statements on the other tables in the database to get a feel for what kind of data that we will be looking at. As a refresher, you can see the other tables in the database by opening the object explorer, expanding the database, and then expanding the tables folder as shown.
Ordering Results With ORDER BY
One additional useful clause that can be applied to a is the ORDER BY clause. This allows you to specify the order that your results are displayed in. Suppose that we want the result set from the previous query to be ordered by the LastName field. Here is what you would do
SELECT LastName, FirstName, City, Region FROM Employees ORDER BY LastName
If you run this query you can see that the results will now be ordered by LastName. You can also chain multiple columns together to provide secondary sorting options
SELECT LastName, FirstName, City, Region FROM Employees ORDER BY LastName, City
Now the result set will be sorted first by LastName and then by City.
In the next post, I will be covering how to filter data returned by a SELECT statement by using the WHERE clause.