SQL SELECT

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 SQL SELECT keyword; whereas, the table is preceded by FROM.

Table Naming

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

would error

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.

Want to learn More about SELECT? Check out my Getting Started with SQL Page.

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 SQL Select 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 SELECT 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 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 SELECT!

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.

SQL Distinct

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 SELECT 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.

Practical uses of SQL SELECT DISTINCT

I like to use DISTINCT when I’m exploring a new data set. It makes it easy to see if there are any variation or misspelling to look out.

I use this as part of my three steps to writing a query. Check out these steps I take to write complex queries.

In addition, if you have some raw data, and you’re looking to create reference or “lookup” tables, then using a SQL SELECT DISTINCT with queries is a great way to get the data you’ll insert into those tables.

What happens if SQL DISTINCT and TOP are in the Same Query?

Finding and Correcting SQL Select 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 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.

1. How can you find all the table names in the AdventureWorks2012 database?
2. What are two ways to get the names of all of the columns in the Person.Person table?
3. Select JobTitle and BirthDate  for all employees.
4. What would the UnitPrice of each PurchaseOrderDetail items be if there was a half off sale?

Answers are Here

Congratulations!  You just learned how to use the select command to query a database. Remember!  I want to remind you all that if you have other questions you want answered, then post a comment  I’m here to help you.

• SQL DISTINCT and TOP in Same Query
• SQL TOP
• An Ultimate Guide to Writing a SQL Query