Home

	Expand Search

Introducing Sybase Workplace SQL Server

Chapter 2: Terms and Concepts

Transact-SQL

Transact-SQL

Transact-SQL(R) is Sybase's enhanced version of SQL and is the language that you, or the client application you are running, uses to communicate requests to the RDBMS.

Queries

The "Q" in "SQL" stands for query. You query or retrieve data from a database with Transact-SQL's workhorse select command. The basic query operations in a relational system are selection, projection, and join. The select command implements all of them.

The following sections present some simplified scenarios of retrieving data from a database using the select command and show the actual SQL statements you would use to accomplish the queries. The purpose of these statements is merely to familiarize you with what Transact-SQL looks like, not teach you SQL. For detailed instruction in how to use Transact-SQL, consult the Transact-SQL User's Guide, which is part of your user documentation.

Selection

A selection (also called restriction) is a subset of the rows in a table, based on some conditions. Figure 2-5 shows an example of selecting the rows for books of the type "business" from the titles table.

Figure 2-5: Selection

The actual Transact-SQL select command that retrieves the highlighted rows is:

select * 
    from titles 
    where type = "business"

Projection

A projection is a subset of the columns in a table. Figure 2-6 shows a sample projection in which you want look only at the title and price of all books in the titles table.

Figure 2-6: Projection

The actual Transact-SQL select command that retrieves the highlighted columns is:

select titles, price 
    from titles 

Join

A join links the rows in two or more tables by comparing the values in key columns and concatenating rows that have matching values. For example, you might want to select data from the relevant tables showing information about Berkeley publishers and the type and titles of books they published. Figure 2-7 shows the join.

Figure 2-7: A join

The actual SQL select statement that returns the highlighted data from the two tables is:

select * 
    from publishers, titles
    where publishers.pub_id = titles.pub_id
    and city = "Berkeley"

Other Commands

You can do more with SQL than just query, however. Transact-SQL includes other commands to create tables and views, such as create table and create view. It also includes commands to modify tables (the insert, update, and delete commands), and commands to perform many other database tasks discussed in this guide.

Transact-SQL offers extensions to the standard library of SQL commands. We have already looked at one powerful extension, in "Stored Procedure". Another is control-of-flow language, which is Transact-SQL's programming-like constructs that control the flow of execution of Transact-SQL statements. When combined with Transact-SQL's sophisticated error handling techniques, including the ability to capture a return status and get reports from global variables, they make SQL Server a fully programmable database server.

The Transact-SQL commands are described in the SQL Server Reference Manual. For in-depth discussions of how to use many of the Transact-SQL commands, see the Transact-SQL User's Guide.

Presenting Transact-SQL to the RDBMS

There are several ways to present Transact-SQL commands to the RDBMS. One way is to use Sybase's interactive query parser, isql. You can invoke isql at the operating system command line and then enter Transact-SQL commands at the isql prompt.

End users typically do not use SQL itself but access the databases using specialized client applications. The client applications send Transact-SQL commands to the RDBMS by passing them as parameters to Open Client library routines (see "Building or Buying the Client") or to ODBC drivers that in turn called the Open Client library routines.