Ships from: Bay, AR
Usually ships in 1-2 business days
| SELECT statement | Joining tables |
| Subquery | Using subqueries |
| Aggregate | Qualifying object names |
| Union | Retrieving text and images |
| SQL |
Techniques you'll need to master:
| Specifying rows | Outer join |
| Using functions | Inner join |
| Using the like keyword | Cross join |
| Formatting and sorting data | Result set |
| Grouping results | Derived table |
Storing data in a database does you no good unless there is some way to retrieve the data. Not surprisingly, Microsoft SQL Server uses Structured Query Language (SQL) to access data stored in databases. The SQL SELECT statement is used to retrieve data.
Alert: SQL is an ANSI standard database access layer. One of its main purposes is to hide the underlying data structures from users, thereby making it simpler for users to manipulate the data. SQL Server has extensions to ANSI SQL called Transact-SQL(TSQL).
Note: Table and column names can be configured to be either case sensitive or case insensitive. SQL keywords are always case insensitive. With SQL Server 7, the names can have a bracket around them, such as '[object_name]'. The following code example shows a SELECT statement written both with and without brackets around column and table names.SELECT [d].[discounttype] FROM [dbo].[discount] d
— Performs the same select as
SELECT d.discounttype FROM dbo.discount d
In the upcoming sections, we'll take a closer look at the SELECT statement's seven clauses. To keep our presentation orderly, we'll look at each clause in the order in which the clause should appear in a SELECT statement. Therefore, the first clause we'll look at is the select list.
SELECT [ ALL | DISTINCT ] [ TOP n [PERCENT] [ WITH TIES] ] select_list
The clause provides a way to specify which columns should be included in the SELECT statement's result set. Columns are returned in the order in which they appear in the select list clause.
When you use a SELECT statement, the select list returns a virtual table of results (a result set). This table can contain constants, columns, functions, and derived columns. For example, the following code snippet returns a constant value ('constant value'), a database column (Column1), a function (@@SERVERNAME), and a derived column (Column2+Column2):
SELECT 'constant value', Columnl, @@SERVERNAME, Column2+Column2 FROM MyTable
To specify all columns in a table, you can either list each column or use an asterisk (*) in the select list. When you use an asterisk, all of the columns in the table will be returned. You can specify an * along with constraints, columns, and derived columns in the result set. The following example shows how to use an * in SELECT statements:
SELECT * FROM MyTable
SELECT * Columnl+Column2 FROM MyTable
By default, SELECT statements return all rows, including duplicate rows. To explicitly ask for all rows, include the ALL keyword before the select list. The ALL keyword does not ever have to be used, it is provided to make TSQL ANSI-compliant. To have a SELECT statement return only the unique rows, precede the select list with the DISTINCT keyword. If used, the DISTINCT or ALL keywords must immediately follow the SELECT keyword, as shown here:
SELECT DISTINCT 'constraint value', Column1,
@@SERVERNAME, Column2+Column2
FROM MyTable
You can assign values to variables or retrieve a result set with a SELECT statement. You cannot do both in the same SELECT statement. For example, if @a is an integer variable, this is a valid SELECT:
SELECT @a = 5
If @a is an integer variable, this is an invalid SELECT:
SELECT @a = 5, au_id FROM authors
When you perform a SELECT, the result set includes a default column heading for each column returned. Constant values, functions, and other derived columns have blank column headings. The default column heading is the column name. The headings for the previous example would be a blank value for the constant, Columnl for Column 1, a blank value for @@SERVERNAME, and a blank value for Co1umn2+Co1umn2.
You can change column headings by assigning aliases to them in the select list. SQL Server provides three ways to assign column headings to a result set:
Column_Heading = column
Column Column_Heading
Column AS Column_Heading
If the column heading has any special characters, such as spaces, you must enclose the column heading in quotes.
Tip: MS SQL Server allows you to use either single quotes (') or double quotes (") in SQL statements. The ANSI standard uses double quotes for object names and column headings with special characters,and single quotes for everything else. We recommend that you follow this practice.
In conjunction with the select list, you use the FROM clause to specify the tables that contain the data you are looking for. ...
Anonymous
Posted Tue Oct 22 00:00:00 EDT 2002
This book has way to many errors. If you want to pass the exam, don't use this book. I am very displeased because I answered questions correctly but the solutions on the cd mark them incorrect. Extremely poor editing of the book by the publisher. For example, the book states that tranaction logs can be created as part of a file group. They can not. I can get by with the many grammical errors, but I can't approve of incorrect information when I need it to pass the exam.
Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Overview