Summary

You learned the basic syntax of the SELECT statement today, which is the only way to retrieve data from a SQL Server. You learned how to get a set of columns from a table, and how to sort the rows of data coming back with the ORDER BY predicate.

You saw how to get help from the system on tables and the columns they contain. You got a little information about errors and messages, and you learned how to handle some of the more common ones.

Q&A

Q Is there a limit to the number of columns I can list in a single select statement?

A SQL does not impose a limit, but the most columns that a table can have is 255.

Q What’s the difference between a raiserror and a print, and when would I use them?

A RAISERROR is used to indicate an exception. When things go wrong, RAISERRORS give the user an idea about what happened, and how to fix it. RAISERROR allows you to specify an error number, severity, and state. PRINT is used to give supplemental information. You can use PRINT to confirm that things succeeded.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you’ve learned. Try to understand the quiz and exercise answers before continuing on to the next day’s lesson. Answers are provided in the appendix.

Quiz

1.  What is the smallest number of columns possible in a result set? Smallest number of rows?

2.  What is the minimum number of tables you may specify in a SELECT?

3.  What predicate sorts data in a result set?

4.  What system stored procedure will give a list of all objects in the current database? How can this same procedure be used to describe the properties of columns in a table?

5.  Identify the column and table names in the following SELECT statements:

     select   au_fname,  au_lname
     from     authors

     select   title_id,  title
     from     titles

     select   *
     from     titles

Exercises

1.  Retrieve a list of all titles from the titles table.

2.  Now, retrieve the same list, but this time, sort it. Sort it forwards, then use a different query to sort it backwards.

3.  Select all of the data in the publishers table.

4.  Find a table in the pubs database that hasn’t been used in the workshop. (Hint : What stored procedure can you use to list available objects?) Select data from just the first column in the table.