Today's material supplements the previous presentations, as Transact-SQL is a supplement to the accepted SQL standard. Today's goals are to
Day 13, "Advanced SQL Topics," briefly covered static SQL. The examples on Day 13 depicted the use of embedded SQL in third-generation programming languages such as C. With this method of programming, the embedded SQL code does not change and is, therefore, limited. On the other hand, you can write dynamic SQL to perform the same functions as a procedural programming language and allow conditions to be changed within the SQL code.
As we have mentioned during the discussion of virtually every topic in this book, almost every database vendor has added many extensions to the language. Transact-SQL is the Sybase and Microsoft SQL Server database product. Oracle's product is PL/SQL. Each of these languages contains the complete functionality of everything we have discussed so far. In addition, each product contains many extensions to the ANSI SQL standard.
To illustrate the use of these SQL extensions to create actual programming logic, we are using Sybase and Microsoft SQL Server's Transact-SQL language. It contains most of the constructs found in third-generation languages, as well as some SQL Server-specific features that turn out to be very handy tools for the database programmer. (Other manufacturers' extensions contain many of these features and more.)
Everyone reading this book can use Transact-SQL--casual relational database programmers who occasionally write queries as well as developers who write applications and create objects such as triggers and stored procedures.
NOTE: Users of Sybase and Microsoft SQL Server who want to explore the true capabilities of relational database programming must use the Transact-SQL features.
SQL extensions overcome SQL's limits as a procedural language. For example, Transact-SQL enables you to maintain tight control over your database transactions and to write procedural database programs that practically render the programmer exempt from exhausting programming tasks.
Day 19 covers the following key features of Transact-SQL:
On Day 9, "Creating and Maintaining Tables," we discussed data types. When creating tables in SQL, you must specify a specific data type for each column.
NOTE: Data types vary between implementations of SQL because of the way each database server stores data. For instance, Oracle uses selected data types, whereas Sybase and Microsoft's SQL Server have their own data types.
Sybase and Microsoft's SQL Server support the following data types.
Money values are inserted into a table using the dollar sign; for example:
insert payment_tbl (customer_id, paydate, pay_amt) values (012845, "May 1, 1997", $2099.99)
The data type bit is often used to flag certain rows of data within a table. The value stored within a column whose data type is bit is either a 1 or 0. For example, the value 1 may signify the condition true, whereas 0 denotes a false condition. The following example uses the bit data type to create a table containing individual test scores:
create table test_flag ( ind_id int not null, test_results int not null, result_flag bit not null)
The column result_flag is defined as a bit column, where the bit character represents either a pass or fail, where pass is true and fail is false.
Throughout the rest of the day, pay attention to the data types used when creating tables and writing Transact-SQL code.
NOTE: The code in today's examples uses both uppercase and lowercase. Although SQL keywords are not case sensitive in most implementations of SQL, always check your implementation.
All right, enough talk. To actually run the examples today, you will need to build the following database tables in a database named BASEBALL.
The BASEBALL database consists of three tables used to track typical baseball information: the BATTERS table, the PITCHERS table, and the TEAMS table. This database will be used in examples throughout the rest of today.
NAME char(30) TEAM int AVERAGE float HOMERUNS int RBIS int
The table above can be created using the following Transact-SQL statement:
1> create database BASEBALL on default 2> go 1> use BASEBALL 2> go 1> create table BATTERS ( 2> NAME char(30), 3> TEAM int, 4> AVERAGE float, 5> HOMERUNS int, 6> RBIS int) 7> go
Line 1 creates the database. You specify the database BASEBALL and then create the table BATTERS underneath BASEBALL.
Enter the data in Table 19.1 into the BATTERS table.
NOTE: The command go that separates each Transact-SQL statement in the preceding