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.
Table of contents
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.
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.
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. :)
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.
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.
- 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. 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.
Hello Kris, and good afternoon from Lisbon, Portugal!
I have discovered your website on SQL Server and I just subscribed for the initial e-mail guide.
I have also created an account with Visual Studio Dev Essentials, as you suggested, and I have already downloaded and installed both SQL Server 2019 and SSMS 18 on my laptop.
Since the first lesson from the Getting Started page is about querying databases with the SELECT keyword, as a complete novice I think something is missing.
In order to follow your examples in this page, I think I have to have a database installed on my computer (either AdventureWorks or WideWorldImporters), am I correct? If so, how can I install the database you're using in this tutorial (which one is it)?
Thank you for your time and effort to bring this valuable content to the web, for free.
I hope to subscribe to the Fearless course if I can manage to understand the introductory part of your teachings.
Hi Kris. Thanks for putting this material together to help others understand better
Great stuff and much appreciated
Hi Kris… thanks for providing this resource.
how to find table structure in SQL Server Management Studio
it helps me alot.. thnx
I’m glad it helped!
Great stuff Kris, really appreciate what you do for newbies trying to catch up with SQL. Gracias!
I’m glad you’re liking the blog. If there are any topics you want covered let me know.
Nice Blog! Kindly add SQL Another types query details also!
I have found your statements educative and clear thank you.
Please give us some more topics,as yiu have discussed only select statement
Please check out https://www.essentialsql.com/getting-started/ to find even more topics!
This is great! Thank you for sharing your knowledge on SQL.
You are welcome! I’m glad you like the site.
The e-mail to gain access to the sample database and “Getting Started…” Guide did not appear. I checked the Bulk/Junk E-mail…not there? Can you send to me please. Thanks
The answer to Question 4 of the SQL SELECT exercises makes sense but kind of bugs me.
SELECT PurchaseOrderID, PurchaseOrderDetailID, UnitPrice, UnitPrice/2 AS HalfOff FROM Purchasing.PurchaseOrderDetail
but you never discuss why you use PurchaseOrderID or PurchaseOrderDetailID (although they make sense). I guess another way to say it is that it wasn’t clear to me what you wanted. Does this make any sense?
I made a slight change to the question to make it more clear. The idea is to get the 1/2 item price for each row in the table. I think the question made it sound like I was asking for the 1/2 price for the entire table. Given we’re looking at rows, it is natural to think about how to identify each row.
If you were a program or a person reading the results what other columns would you want to see so you could make sense of the newly calculated information?
At minimum you would want information to identify each row. At this stage the best information we have is the primary key, PurchaseOrderDetailID. Down the road when you learn about joins you’ll see that we can add other columns for other tables such as customer, product name, and so on.
OK. Makes sense. Thanks!