Network Computingwww.networkcomputing.com

Text and dates in SQL Server, are enclosed in single quotation marks, although double quotation marks may also be used. The date 3/27/2000 can be written as "3/27/2000" or '3/27/2000'. SQL Server will then properly interpret the delimiters to the proper format. Depending on how the data is used, SQL Server will either treat this as a date or as a string. In order to be consistent in SQL, we should stick with one delimiter. Throughout the book we will use single quotes for all strings and dates.

Different providers will implement their own rules for strings and dates. Access, for example, uses double quotes for strings and pound signs (#) for dates. Check with your DBMS to verify the correct delimiters for strings and dates.

Double quotations or brackets can be used when accessing tables or fields that have reserved words (words that have a particular function within SQL), contain special characters (characters that have a specific function within SQL), or contain spaces. Generally quotation marks are used, but SQL Server accepts brackets as well. As we're using a version of SQL Server (that comes on the CD with this book), we will be using brackets in our examples, but if you are using a different vendor product, please use double quotations for the SQL code examples here. For example, the following SQL statements are equivalent. Don't worry about what this statement actually does at the moment; we will soon be investigating this:

SELECT
 [Insert],
 [Update]
FROM
 [Employee Table]
SELECT
 "Insert",
 "Update"
FROM
 "Employee Table"

If you are writing a lot of comments or adding headers to your queries, then it is possible to use the block comment syntax of /* */. This will denote all of the text within the delimiters as comments, regardless of the number of lines. This is a quick way to comment blocks of statements or for headers (general information about the SQL code).

Both of the following are equivalent:

/*
Filename: GetEmployees.sql
 Author: Brian Matsik (OOCS)
 Date: 3/15/2000
 Purpose: This query returns all of the employees from 
   the employees table.
*/

-- Filename: GetEmployees.sql
-- Author: Brian Matsik (OOCS)
--  Date: 3/15/2000
-- Purpose: This query returns all of the employees from
--   the employees table.

Introducing SELECT

Let's take a look at the workhorse of all SQL queries, the SELECT statement.

The Basic Syntax

The SELECT statement is the most important keyword used in SQL statements. A SELECT statement is necessary in order to tell the database what data to return. The most basic SQL statement that we can write contains only two elements:

SELECT [field]
FROM [table]

The SELECT portion of the statement includes a list of fields that we would like to display. If we want to retrieve data from more than one field, then each field in the list must be separated by a comma, thus:

SELECT [field], [field2], [fieldn]
FROM [table]

Once we have defined the field list, we must provide the name of the table or tables that contain the fields. The table name follows the FROM clause.

There is a special field selector that you can use if you want all of the fields in a table: the * character. This will return every field in the order that they are defined within the structure of the table. It is fine to use this character for testing, but this should be avoided in a production environment since the use of the * is cumbersome and inefficient.

When we use the * the query processor must open the table and determine what the field list is before executing the statement. The other reason why we should avoid the * is that it makes reading a query much more difficult, since we would not typically know which fields are actually being returned, or their names. As we will see in later chapters, we must know the field names in order to calculate, sort, or filter information from your table.

Try it Out - Running a Simple Query

In order to execute a SQL statement, we must first open an appropriate interface. For SQL Server, this is called Query Analyzer, but if you are using another type of database, you will use a different interface that fits your particular DBMS.

1.  Launch Query Analyzer by going to Start - Programs - Microsoft SQL Server - Query Analyzer.

2.  Select the appropriate database from the drop-down list. In our case, we will be using the Northwind database:

SELECT FirstName, LastName
FROM Employees

4.  Execute the statement by pressing the F5 key or pressing the execute query button (the button showing the green triangular arrow). You should then see the following result:

If this is the first time that you have used Query Analyzer then there are a few things that you may have noticed. First, Query Analyzer uses color to delimit certain words or items. Reserved words are in blue or gray, strings and dates appear in red, and comments are green. Also, the bottom of the screen will show the results in a grid format (default) or as a plain text by pressing Ctrl+T. You can change back to the grid display by pressing Ctrl+D.

Remember that if you are using Oracle or MySQL's Command Prompt interface, you must include a semicolon at the end of the statement as in this example:

SELECT Firstname, LastName
FROM Employees;