You will learn how to query Microsoft SQL Server using the SQL SELECT statement in this series of lessons. Once you have read this lesson you’ll be able to:
- Identify all tables in a SQL Server SSMS (SQL Server Management Studio) Database.
- Understand how to list a table’s columns.
- To view columns, create text and mathematical results and set distinct values, use the SQL SELECT statement.
- Learn some techniques to debug your commands and fix mistakes.
Important! Please follow the examples in your database and do them. If you haven’t already done so, sign up for my Guide to Getting Started with SQL Server. You can receive guidance on downloading the free software and sample database.
Understanding Your Database
A simple select statement consists of two parts. The first part describes what columns we want to view and the second part which table we’re viewing. A select statement looks like:
SELECT LastName FROM Person.Person
In this example LastName is the column and Person the table. The columns come after the SELECT keyword; whereas, the table is preceded by FROM.
You may be wondering why the Person table is referred to as Person. Person in the above statement. This has to do with the way objects are organized on the server. The database can be thought of as a series of nested containers. The containers are the:
- Server/Instance – The instance of SQL Server running on the computer.
- Databases – One or more databases created on the instance.
- Schemas – A way to logically group tables and other database objects.
The fully qualified name for a table is [Server/Instance].[DatabaseName].[Schema].[TableName]. So if we really wanted to be explicit we could use something like
SELECT LastName FROM [KAWLAPTOP\SQLEXPRESS2014].[AdventureWorks2012].[Person].[Person]
to do a query. Luckily we typically write queries within the context of one database so we only need to specify the Schema and TableName as so:
SELECT LastName FROM Person.Person
But what about the brackets  ? Those are used if your table has a space in the name. So
SELECT Last Name FROM Person.Person Table
SELECT [Last Name] FROM Person.[Person Table]
Is OK. My advice? If you get to name your own tables, don’t use spaces, brackets are ugly! If you must, do as the Oracle folks do and use underscores. Person_Table is much easier to read than [Person Table], personally I prefer PersonTable.
Using the Object Explorer
In order to write queries you need to know what tables and columns are available in your databases. When using SSMS (SQL Server Management Studio) you can simply expand the database’s tables folder to list all the tables found in your database. Once you find a table you would like to know more about simply click on the table name to expand it, and then the columns folder to list all the columns.
Running Queries in SSMS
Before we get too far let me show you how to run a query in SSMS. It is actually really easy. Once you have launched SQL Server Management Studio, you’ll want to select your database. Then click the New Query button located in the top tool bar. This displays a query window to the right. You can type any thing you want in this window, as it is basically a text editor. Cut and paste or type in a SQL statement, then click Execute to run the statement. The results are show in the bottom portion of the window and any messages, such as errors, show in the messages tab.
Select Multiple Columns
To select more than one column, just separate them with a comma. For instance,
SELECT FirstName, LastName FROM Person.Person
returns both the first name and last name as a result. In case you wondering you can mix the case when writing queries. That is
SELECT FirstName, LastName FROM Person.Person
Works just as well as
select FirstName, LastName from Person.Person
Tip! A common convention is to capitalize all the keywords. Some DBA’s feel is it easier to read commands written this way. Personally I don’t do that, but for this blog I WILL TRY. :)
Rename SQL Columns
You can rename the columns that result from your select statements. To do this use the AS statement like so:
SELECT LastName as SirName FROM Person.Person
This displays the result as “SirName” rather than “LastName.” This is a nice feature from a vanity point of view, but it also comes in handy when we want to return a calculate result and need to give it a name. For instance, suppose you wanted to return the LastName in all upper case. You would could write
SELECT UPPER(LastName) AS SirName FROM Customers
UPPER is known as a function. SQL Server has many functions you can use to perform many types of calculations. We will look into all of them later, but for now, know that you can string several functions together to create a complex expression to output the data how you need to do so.
Complex Column Expressions
Our employee table contains separate fields for first name and last name. Suppose the HR department needs to see a full name in upper case. How would you do this? Simple! You could write
SELECT UPPER(FirstName) + ' ' + UPPER(LastName) AS FullName FROM Person.Person
This statement may look complicated, but once we break it down, you’ll see it’s just made up of a bunch of simple elements.
- As you just learned, UPPER is used to return the upper case of a column.
- The + tells SQL to combine two values together.
Anything in single quotes (‘) is literally displayed as is. In our case the ‘ ‘ means to output a single space. If you read our expression, in English, it would read as “Take the uppercase of the FirstName combine it with a space and then add the uppercase of LastName to it.”
Do Math with SQL!
You can also get SQL to do math tricks for you. It can do some pretty complicated arithmetic if you allow it to do so. For today, I’ll show you how to multiply two numbers, but you can just as easily add, subtract, or divide. Later we’ll make it more involved so you can relive your Algebra II days. :) Here are some common math operators you can use in SQL:
- * Multiply
- / Divide
- + Add
- – Subtract
We’ll use the OrderDetails table and calculate the total price by multiplying the unit price by quantity.
SELECT UnitPrice, OrderQty, UnitPrice * OrderQty AS Total FROM Purchasing.PurchaseOrderDetail
The bit ” UnitPrice * OrderQty” says to multiply UnitPrice by Quantity.
Be Distinct and Debug
So far we have used the select statement to retrieve all the records in a table regardless if some values repeat or not. If you wish, you can use the DISTINCT keyword to remove duplicates from your results. For instance if you wanted to just return a unique list of employees’ titles you would use this SQL statement:
SELECT DISTINCT JobTitle FROM HumanResources.Employee
The DISTINCT statement also works on more than one column. If you wanted, you could get a distinct listing of job titles and genders by typing
SELECT DISTINCT JobTitle, Gender FROM HumanResources.Employee
!Trivia: The ALL keyword is DISTINCT’s counterpart and is the default behavior. That is why we don’t write it into our statements.
Finding and Correcting Errors
Sooner or later you’ll mistype a select statement into the command line and the computer will yell at you — “Syntax Error!” or “Invalid Column Name” What to do? Just remember that the select statement is made up of two main parts: the SELECT and the FROM portion. In general, the database first looks to see if these keywords are present, and if they are not it triggers a Syntax error.
This is just a fancy phrase for “you have misspelled a keyword or have it out of order” (e.g. you have the Distinct before Select as in DISTINCT SELECT). Once the keywords are checked, the statement is inspected to make sure you have specified a table. Once the table is known it can check the columns. So the order is to first check for keywords, then for tables, and lastly for correct columns. Whenever an error is displayed read it carefully. Chances are you misspelled something. The computer will tell you what you misspelled in quotes; it your job to then find the misspelling and correct it.
SQL Select Exercises
It’s important to practice! Use the sample database to answer these questions.
- How can you find all the table names in the AdventureWorks2012 database?
- What are two ways to get the names of all of the columns in the Person.Person table?
- Select JobTitle and BirthDate for all employees.
- What would the UnitPrice of each PurchaseOrderDetail items be if there was a half off sale?
Congratulations! You just learned how to use the select command to query a database. More tutorials are to follow, and in these we’ll dive into some of the areas we glossed over today such as mathematical operators and functions. Remember! I want to remind you all that if you have other questions you want answered, then post a comment or tweet me. I’m here to help you.