Lesson 2:Introduction to Transact-SQL | MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)

3 4

Transact-SQL allows you to administer instances of SQL Server; create and manage all objects in an instance of SQL Server; and insert, retrieve, modify, and delete all data in SQL Server tables. Transact-SQL is an extension of the language defined in the SQL standards published by the International Organization for Standardization (ISO) and the American National Standards Institute (ANSI). This lesson introduces you to Transact-SQL and to the various types of Transact-SQL statements that are used in SQL Server, including data definition language (DDL) statements, data control language (DCL) statements, and data manipulation language (DML) statements.

After this lesson, you will be able to:

Provide an overview of Transact-SQL and how it is used in SQL Server.
Describe the three types of Transact-SQL statements: DDL, DCL, and DML.
Write and execute basic DDL, DCL, and DML statements, including CREATE, GRANT, SELECT, INSERT, UPDATE, and DELETE statements.

Estimated lesson time: 35 minutes

Overview of Transact-SQL

Transact-SQL is central to the use of SQL Server. Each application that communicates with SQL Server does so by sending Transact-SQL statements to the server, regardless of the application's user interface.

SQL Server Books Online includes a complete reference for Transact-SQL statements. This reference describes each Transact-SQL element and provides examples that illustrate how to apply that element in a Transact-SQL statement. You can copy the syntax example from the reference to the Editor pane of SQL Query Analyzer to help create a Transact-SQL statement. While in the Editor pane, you can select a Transact-SQL statement, function, stored procedure, or other Transact-SQL element and then press Shift+F1 to view information about the selected text.

Transact-SQL Statements

A Transact-SQL statement is a set of code that performs some action on database objects or on data in a database. SQL Server supports three types of Transact-SQL statements: DDL, DCL, and DML.

Data Definition Language

Data definition language, which is usually part of a database management system, is used to define and manage all attributes and properties of a database, including row layouts, column definitions, key columns, file locations, and storage strategy. A DDL statement supports the definition or declaration of database objects such as databases, tables, and views. The Transact-SQL DDL used to manage objects is based on SQL-92 DDL statements (with extensions). For each object class, there are usually CREATE, ALTER, and DROP statements (for example, CREATE TABLE, ALTER TABLE, and DROP TABLE).

Most DDL statements take the following form:

CREATE object_name
ALTER object_name
DROP object_name

The following three examples illustrate how to use the Transact-SQL CREATE keyword to create, alter, and drop tables. CREATE is not limited only to table objects, however.

CREATE TABLE

The CREATE TABLE statement creates a table in an existing database. The following statement will create a table named Importers in the Northwind database. The table will include three columns: CompanyID, CompanyName, and Contact.

 USE Northwind CREATE TABLE Importers ( CompanyID int NOT NULL, CompanyName varchar(40) NOT NULL, Contact varchar(40) NOT NULL )

ALTER TABLE

The ALTER TABLE statement enables you to modify a table definition by altering, adding, or dropping columns and constraints or by disabling or enabling constraints and triggers. The following statement will alter the Importers table in the Northwind database by adding a column named ContactTitle to the table.

 USE Northwind ALTER TABLE Importers ADD ContactTitle varchar(20) NULL

DROP TABLE

The DROP TABLE statement removes a table definition and all data, indexes, triggers, constraints, and permission specifications for that table. Any view or stored procedure that references the dropped table must be explicitly dropped by using the DROP VIEW or DROP PROCEDURE statement. The following statement drops the Importers table from the Northwind database.

 USE Northwind DROP TABLE Importers

Data Control Language

Data control language is used to control permissions on database objects. Permissions are controlled by using the SQL-92 GRANT and REVOKE statements and the Transact-SQL DENY statement.

GRANT

The GRANT statement creates an entry in the security system that enables a user in the current database to work with data in that database or to execute specific Transact-SQL statements. The following statement grants the Public role SELECT permission on the Customers table in the Northwind database:

 USE Northwind GRANT SELECT ON Customers TO PUBLIC

REVOKE

The REVOKE statement removes a previously granted or denied permission from a user in the current database. The following statement revokes the SELECT permission from the Public role for the Customers table in the Northwind database:

 USE Northwind REVOKE SELECT ON Customers TO PUBLIC

DENY

The DENY statement creates an entry in the security system that denies a permission from a security account in the current database and prevents the security account from inheriting the permission through its group or role memberships.

 USE Northwind DENY SELECT ON Customers TO PUBLIC

Data Manipulation Language

Data manipulation language is used to select, insert, update, and delete data in the objects defined with DDL.

SELECT

The SELECT statement retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables. The following statement retrieves the CustomerID, CompanyName, and ContactName data for companies who have a CustomerID value equal to alfki or anatr. The result set is ordered according to the ContactName value:

 USE Northwind SELECT CustomerID, CompanyName, ContactName FROM Customers WHERE (CustomerID = 'alfki' OR CustomerID = 'anatr') ORDER BY ContactName

INSERT

An INSERT statement adds a new row to a table or a view. The following statement adds a row to the Territories table in the Northwind database. The TerritoryID value for the new row is 98101; the TerritoryDescription value is Seattle; and the RegionID value is 2.

 USE Northwind INSERT INTO Territories VALUES (98101, 'Seattle', 2)

NOTE

The INTO keyword is an optional keyword that can be used between INSERT and the target table. Use the INTO keyword for code clarity.

UPDATE

The UPDATE statement changes data in a table. The following statement updates the row in the Territories table (in the Northwind database) whose TerritoryID value is 98101. The TerritoryDescription value will be changed to Downtown Seattle.

 USE Northwind UPDATE Territories SET TerritoryDescription = 'Downtown Seattle' WHERE TerritoryID = 98101

DELETE

The DELETE statement removes rows from a table. The following statement removes the row from the Territories table (from the Northwind database) whose TerritoryID value is 98101.

 USE Northwind DELETE FROM Territories WHERE TerritoryID = 98101

NOTE

The FROM keyword is an optional keyword that can be used between the DELETE keyword and the target table, view, or rowset function. Use the FROM keyword for code clarity.

Exercise 2: Creating and Executing DDL, DCL, and DML Statements

In this exercise, you will create and execute DDL, DCL, and DML statements. For all procedures, you will be working with the Northwind database. To perform this exercise, you should be logged into your Windows 2000 Server computer as Administrator.

NOTE

The Northwind database is a sample database provided with SQL Server. If you make changes to the database and later discover that you want to restore it to its original state, you can reinstall it by running a script from the Install folder of the SQL Server 2000 installation CD-ROM.

To open SQL Query Analyzer

On the Start menu, point to Programs and then to Microsoft SQL Server, then click Query Analyzer.

As SQL Query Analyzer starts to open, the Connect To SQL Server dialog box appears.

In the SQL Server drop-down list, select Local. If Local is not listed, click the ellipsis button to browse for the local computer (the computer on which you are working).
Verify that the Windows Authentication radio button is selected.
Click OK.

The SQL Query Analyzer interface appears and displays two windows: the Object Browser window and the Query Window.

If the Object Browser window did not appear, click the Object Browser button on the toolbar.

The Object Browser window appears. The Object Browser window and the Editor pane (in the Query window) should now be the only windows open in SQL Query Analyzer.

Size SQL Query Analyzer as well as the Object Browser window and the Query window so you can comfortably view the features within the interface.

To create a table in the Northwind database

In the Editor pane, enter the following CREATE TABLE statement:

 USE Northwind CREATE TABLE Investors ( InvestorID INT NOT NULL, FirstName varchar(30) NOT NULL, LastName varchar(30) NOT NULL )

Notice that the keywords USE, CREATE TABLE, int, and varchar all appear in blue type on the screen. If one of these words is not blue, verify that you have entered it correctly. The keywords int and varchar are data types and are discussed in later lessons.

Also notice that in the Database drop-down list on the toolbar, the Master database is displayed. This database will change to Northwind after you execute the USE Northwind statement.

Click the Execute Query button on the toolbar.

The Results pane appears with the Messages tab active, saying that the command has been completed successfully.

In the Object Browser window, expand Northwind and then expand User Tables.

The Investors table is listed as one of the tables. The dbo that appears before the table name indicates who owns the table object.

Right-click dbo.Investors, then click Open.

The Open Table window appears and displays the names of the three attributes that you created. No information appears in the grids below the attribute names because no data has been added to the table.

Close the Open Table window.

To modify a table

In the Editor pane, position your cursor several lines beneath the statement that you just executed.

You will be entering a new Transact-SQL statement several lines after the preceding one so that you can copy and paste code as necessary, referring back to other statements that you executed. You can also save the code as a script file in case you want to reference it later. This process enables you to reuse code and troubleshoot problems if any should arise. For the remainder of this exercise, you should enter each Transact-SQL statement two or three lines beneath the preceding statement.

Enter the following ALTER TABLE statement:

 ALTER TABLE Investors ADD InvestmentCode INT NULL

The USE Northwind command does not need to be used here because Northwind is now the active database. The active database is displayed in the Database drop-down list box on the toolbar.

Highlight the ALTER TABLE statement, then click the Execute Query button.

NOTE

You can also press F5 to execute a query.

The Messages tab displays a message saying that the command has been completed successfully.

In the Object Browser window, right-click dbo.Investors, then click Open.

The Open Table window appears, displaying the attributes of the Investors table. The table now includes the InvestmentCode attribute.

Close the Open Table window.

To display information about a table

In the Editor pane, enter the following code several lines beneath the ALTER TABLE statement:

 EXEC sp_help Investors

The sp_help entry is a system-stored procedure that displays information about database objects. Stored procedures are discussed in Chapter 8, "Implementing Stored Procedures."

Highlight the EXEC statement, then execute it.

The Grids tab of the Results pane displays information about the Investors table. Notice that this information is similar to what you found in the Open Table window that you accessed through the Object Browser window.

Review the information on the Grids tab.

Note the data type for each attribute and whether that attribute is nullable. Also note the owner of the table and the type of object.

Close the Object Window but leave the Query window open for the next procedure.

To grant permissions on a database object

In the Editor pane, enter the following GRANT statement beneath the last statement that you executed:

 GRANT SELECT ON Investors TO PUBLIC

Highlight the GRANT statement, then execute it.

The Messages tab of the Results pane displays a message indicating that the command has been completed successfully.

Enter the following EXEC statement:

 EXEC sp_helpprotect Investors

Execute the EXEC statement.

The Grids tab of the Results pane displays information about user permissions for the Investors table. The Public role has been granted SELECT permission for the table.

To revoke permissions on a database object

Enter the following REVOKE statement:

 REVOKE SELECT ON Investors TO PUBLIC

HINT

You can copy the previous GRANT statement that you executed and paste it at the bottom of the Editor pane. Then, simply modify the statement so that it revokes permissions rather than grants them.

The Messages tab of the Results pane displays a message indicating that the command has been completed successfully.

Enter the following EXEC statement:

 EXEC sp_helprotect Investors

Execute the EXEC statement.

The Messages tab of the Results pane displays a message indicating that there are no matching rows on which to report. Because you revoked SELECT permissions for the Public role, there are no granted or denied permissions on which to report.

HINT

You do not have to re-enter the EXEC sp_helprotect statement, nor do you have to copy and paste it. Simply highlight the original statement and then execute it.

To retrieve data

In the Editor pane, enter the following INSERT statements beneath the last statement displayed:

 INSERT Investors VALUES (01, 'Amie', 'Baldwin', 103) INSERT Investors VALUES (02, 'Jo', 'Brown', 102) INSERT Investors VALUES (03, 'Scott', 'Culp', 103) INSERT Investors VALUES (04, 'Jon', 'Grande', 103) INSERT Investors VALUES (05, 'Lani', 'Ota', 102)

Highlight the INSERT statements, then execute them.

The Messages tab on the Results pane displays a set of five messages, each indicating that one row has been affected.

Enter the following SELECT statement and then execute that statement:

 SELECT * FROM Investors

The Grids pane displays the five rows that you entered into the Investors table.

Enter the following SELECT statement, then execute that statement:

 SELECT FirstName, LastName FROM Investors WHERE (InvestorID = 03 OR InvestorID = 05) ORDER BY FirstName

The Grids pane displays the Lani Ota and Scott Culp rows. The only information displayed is the first and last names, and the names are displayed in alphabetical order according to first name.

To modify data

Enter the following UPDATE statement and then execute that statement:

 UPDATE Investors SET InvestmentCode = 101 WHERE InvestorID = 04

The Messages tab displays a message indicating that one row has been affected.

Execute the following SELECT statement:

 SELECT * FROM Investors

The Grids pane displays the five rows in the Investors table. Notice that Jon Grande's InvestmentCode value is now 101.

Enter the following DELETE statement, then execute that statement:

 DELETE FROM Investors WHERE InvestorID = 04

The Messages tab displays a message indicating that one row has been affected.

Execute the following SELECT statement:

 SELECT * FROM Investors

The Grids pane now displays only four rows for the Investors table. Notice that Jon Grande is no longer listed.

Enter the following DROP statement, then execute that statement:

 DROP TABLE Investors

The Messages tab displays a message indicating that the command was completed successfully.

Open the Object Browser window, expand Northwind, and expand User Tables.

The Investors table is no longer listed.

Close SQL Query Analyzer.

NOTE

When you close SQL Query Analyzer, you will be prompted to save the Transact-SQL statements in the Editor pane to a file. As an extra exercise, you can save the file and then open it again. You might try using the existing code to re-create the Investors table—adding data, manipulating data, and then dropping the table. If you do not want to save the statements, click No in the SQL Query Analyzer message box.

Lesson Summary

Transact-SQL helps you to administer instances of SQL Server; create and manage all objects in an instance of SQL Server; and insert, retrieve, modify, and delete all data in SQL Server tables. Transact-SQL is central to the use of SQL Server. The SQL Server 2000 Transact-SQL version complies with the Entry level of the SQL-92 standard and supports many additional features from the Intermediate and Full levels of the standard. SQL Server Books Online includes a complete reference on Transact-SQL statements. A Transact-SQL statement is a set of code that performs some action on database objects or on data in a database. SQL Server supports three types of Transact-SQL statements: DDL, DCL, and DML. DDL is used to define and manage database objects such as databases, tables, and views. For each object class, there are usually CREATE, ALTER, and DROP statements. DCL is used to control permissions on database objects. Permissions are controlled by using the SQL-92 GRANT and REVOKE statements and the Transact-SQL DENY statement. DML is used to select, insert, update, and delete data in the objects that are defined with DDL.