Skip to content

SQL 101 – SELECT

2005 July 2
tags: , ,
by Shannon Lowder

The first thing you need to know when learning SQL is how to get data out of a database.  This means learning the SELECT command.  Using this command will get the SQL server to return data to you. You can use this command to do some simple math, or to do the common “Hello World!” application you all learn the first day of a programming course.

SELECT 1 + 1
> 2
SELECT 'Hello World'
>Hello World

Keeping this in mind can become useful later, when you need to have the server return debugging statements to you during particularly long code writing sessions.  Usually you’ll need the SELECT command to look up a certain piece of data from a table.  The following examples are going to work from the AdventureWorks database.  You can install this database on your own instance of SQL, or you can contact me and I’ll give you access to my test server.  This is a new service I’m offering to anyone who wants to learn SQL!

In the AdventureWorks database, there’s a table used to store products created/sold by this company.  It’s called Production.Product.  (for now, let’s ignore the fact the table name has two parts…I’ll explain that later!)  In this table there are several columns, one of which is product’s name.  If you wanted to get a list of the name for every product in this table, you write the following command:

SELECT 
     Name 
FROM Production.Product

output

  Name
  -----------
  Cable Light Saber
  External Light Saber Washer 1
  Adjustable Race
  All-Purpose Light Saber Stand

This is just the first 4 items that showed up when I ran the query.  You may see different names appear in your list.  My data has been updated by myself, and other students accessing this database.

I’d like to point out the data returned is not ordered.  In later articles I’ll show you how to put the results into any order you may find useful.  But for now, Let’s stick to learning about the SELECT statement.

What if you wanted to know the name and the price?  In SQL you can list as many columns as you like, in a comma separated list.

SELECT
   Name, StandardCost
FROM Production.Products

output

  Name            StandardCost
  -----------     -----
  pencil          .25
  pen             .99
  paper          1.00
  Mountain Dew   1.25

This is the first 4 results I received.

Finally, if you want to return all the data of the table, you can do that too.  But I would like to point out using the following command on a table with lots of rows (thousands, or millions) could be very time and processor consuming.  Use this only if you know the number of records and columns will be small enough your server and connection can handle.  There are also many reasons NOT to use this, most of which have to do with the fact SQL is a shared resource… asking for all the data causes your server to work harder for you, and as a result, there are fewer resources left for other users.

Use the SELECT * queries sparingly!

SELECT *
FROM Production.Products

output

ProductID  Name                   ProductNumber  MakeFlag    FinishedGoodsFlag  Color  SafetyStockLevel ...
1          Adjustable Jedi Robe   AR-5381        0           0                  NULL   1000    
2          Bearing Crystal        BA-8327        0           0                  NULL   1000    
3          BB Crystal Bearing     BE-2349        1           0                  NULL   800    
4          Sith Crystal Bearings  BE-2908        0           0                  NULL   800    

There are more columns off to the right, but I’ve cleaned up the output to make it easier to read.

As always, if you have any questions, please let me know!  I’m here to help you understand SQL better.  Let me know how I can do that.  Play around with multiple variations

Previous: What is SQL? Next: SELECT, Filtering Results

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS