Listing More Than One Column

So far, I’ve only shown examples with a single column and table. To add more columns to your results, add them to the column list after the SELECT. Each column in the list is separated by a comma. So, for example, to list the title_id and title columns from the same titles table that you’ve been using, use this SQL statement:

     select title_id, title
     from titles

     title_id title
     -------- -------------                         -------------------
   BU1032  The Busy Executive’s Database Guide
   BU1111  Cooking with Computers: Surreptitious Balance Sheets
   BU2075  You Can Combat Computer Stress!
   BU7832  Straight Talk About Computers
   MC2222  Silicon Valley Gastronomic Treats
   MC3021  The Gourmet Microwave
   MC3026  The Psychology of Computer Cooking
   PC1035  But Is It User Friendly?
   PC8888  Secrets of Silicon Valley
   PC9999  Net Etiquette
   PS1372  Computer Phobic AND Non-Phobic Individuals: Behavior Variations
   PS2091  Is Anger the Enemy?
   PS2106  Life Without Fear
   PS3333  Prolonged Data Deprivation: Four Case Studies
   PS7777  Emotional Security: A New Algorithm
   TC3218  Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
   TC4203  Fifty Years in Buckingham Palace Kitchens
   TC7777  Sushi, Anyone?

   (18 row(s) affected)

This statement selects two columns from the titles table as well as all of the rows. The columns show up in the result set in the same order as they are listed in the SELECT list. If you wanted to list the title first, you would type

select title, title_id…

There is only one table in the table list. If you wanted information from more than one table (a list of titles plus the names of the authors who wrote them, for example), you would list all the tables that you needed to access in the table list, separated by commas, after the FROM clause. You would also need to perform a join in order to define the relationships between the tables. I talk about joins on Day 7.

---

Note:  At the end of each result set is a message from the server telling you how many rows were affected by your query. To turn this off, execute this query:

set nocount on

---

Simplified Syntax:

     select <column list>
     from <table list>

Altering Data in the Result Set

If I want to reduce my high-powered SQL Server to a simple calculator, I can do so:

     select 5 * 5

     ------
     25

I can also perform mathematical operations on columns appearing in the select list. The titles table contains a price column that lists the prices of the books. If I wanted to see the total price of a book after taxes, which in my area is 8 percent, I could use this query:

     select  title_id, price * 1.08
     from    titles

     title_id
     --------                 ---------------------------
     BU1032       21.589200
     BU1111       12.906000
     BU2075       3.229200
     BU7832       21.589200
     MC2222       21.589200
     MC3021       3.229200
     MC3026        (null)
     PC1035       24.786000
     PC8888       21.600000
     PC9999        (null)
     PS1372       23.317200
     PS2091       11.826000
     PS2106       7.560000
     PS3333       21.589200
     PS7777       8.629200
     TC3218       22.626000
     TC4203       12.906000
     TC7777       16.189200

     (18 row(s) affected)

This result set lists the title ID in the first column and the price times 1.08 in the second column, which is a calculation of the tax amount where I live. Some columns list (NULL) as a result, which means “the value for this row is unknown.” Null is discussed in more detail tomorrow, in Day 3.

Also, notice that the second column doesn’t have a name at the top. Since an operation was performed on the column (I multiplied the price contained in the column by 1.08), the column in the result set does not inherit the name of the column in the titles table. If I want to give it a name, I have to use a column alias.