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 |