Getting Started with SQL Server: 1. Simple SELECT Queries

Introduction

In this series of lessons, you’re going to learn how to query Microsoft SQL Server using the SQL Select statement. Once you have read this blog and viewed the series of videos, you’ll be able to:

• Identify all the tables in a SQL Server Database using SQL Server Management Studio
• Understand how to list a table’s columns
• Use the SQL Select statement to view columns, create text and mathematical results, and distinct values
• Learn some techniques to debug your commands and fix mistakes

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 Persons the table. The columns come after the 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].

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

This displays a query window to the right. You can type anything 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 shown 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 company name and city 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 DBAs feel it is 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 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 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 of whether 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 customer cities and states 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's 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 all PurchaseOrderDetail be if there was a half off sale?

The answers are revealed in our weekly newsletter.

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.