Tutorial: compile a Transact-SQL statement

Lesson 1st: Create a database object this lesson describes how to create a database, create a table in the database, and then access and change the data in the table. Since this course is an introduction to the use of Transact-SQL, it does not use or describe many options provided for these statements. You can use the following methods to write a Transact-SQL statement and submit it to the data

Lesson 1st: Create a database object this lesson describes how to create a database, create a table in the database, and then access and change the data in the table. Since this course is an introduction to the use of Transact-SQL, it does not use or describe many options provided for these statements. You can use the following methods to write a Transact-SQL statement and submit it to the data

Lesson 1st: create database objects

This section describes how to create a database, create a table in the database, and then access and modify the data in the table. Since this course is an introduction to the use of Transact-SQL, it does not use or describe many options provided for these statements.

You can use the following methods to write a Transact-SQL statement and submit it to the database engine:

• Use SQL Server Management Studio. This tutorial assumes that you are using Management Studio, but you can also use Management Studio Express (which can be downloaded free of charge from Microsoft Download Center ).

• Use the sqlcmd utility.

• Connect from the application you created.

Code is executed on the database engine in the same way and with the same permissions, regardless of how you submit code statements.

To run a Transact-SQL statement in Management Studio, open Management Studio and connect to the instance of the SQL Server database engine. If you are not familiar with Management Studio, see the first two tasks in the tutorial.

Create a database (Tutorial)

Like many Transact-SQL statements, the CREATE DATABASE statement has a required parameter: DATABASE Name. Create database also has many optional parameters, such as the disk location where the DATABASE files are to be stored. When you execute create database without optional parameters, SQL Server uses the default values of many of these parameters. This tutorial uses very few optional syntax parameters.

Create a database

1. In the query editor window, type the following code, but do not execute it:

   CREATE DATABASE TestDataGO

2. Use the pointer to select the word "create database" and then press F1. The create database topic in SQL Server books online should be opened. You can use this method to find the complete Syntax of create database and other statements used in this tutorial.

3. In the query Editor, press F5 to execute the statement and create a database named TestData.

When you create a database, SQL Server createsModelCopy of the database and rename it as the database name. Unless you specify a database with a large initial size as an optional parameter, this operation takes only a few seconds.

Note:
When multiple statements are submitted in a single batch, you can use the GO keyword to separate the statements. When the batch processing contains only one statement, GO is optional.

• Create a table (Tutorial)

  To create a table, you must provide the table name and the name and data type of each column in the table. It is also a good practice to specify whether null values are allowed in each column.

  Most tables have a primary key, which consists of one or more columns. The primary key is always unique. The Database Engine enforces the following restrictions: no primary key values in the table can be repeated.

  For a list of data types and links to descriptions of each data type, see data types (Transact-SQL ).

  Note:
  The database engine can be installed in case sensitive or case insensitive. If the database engine is case-sensitive, the object name must always have the same case. For example, a table named OrderData is different from a table named ORDERDATA. If the database engine is installed in case-insensitive mode, the two table names are regarded as the same table and can only be used once.

  Create a database to include a new table

  • Enter the following code in the query editor window.

    USE master;GO--Delete the TestData database if it exists.IF EXISTS(SELECT * from sys.databases WHERE name='TestData')BEGIN    DROP DATABASE TestData;END--Create a new database called TestData.CREATE DATABASE TestData;--Press the F5 key to execute the code and create the database.

  Switch query editor connection to TestData Database

  • In the query editor window, type the following code and execute it to change the connection to the TestData database.

    USE TestDataGO

  Create a table

  • In the query editor window, type the following code and execute it to create a simple table named Products. The names of each column in this table are ProductID, ProductName, Price, and ProductDescription. The ProductID column is the primary key of the table. Int, varchar (25), money, and text are data types. When a row is inserted or changed, only the Price and ProductionDescription columns can contain no data. This statement contains an optional element called architecture (dbo .). The architecture is a database object with tables. If you are an administrator, dbo is the default architecture. Dbo represents the database owner.

    CREATE TABLE dbo.Products   (ProductID int PRIMARY KEY NOT NULL,    ProductName varchar(25) NOT NULL,    Price money NULL,    ProductDescription text NULL)GO

  You have createdProductsYou can use the INSERT statement to INSERT data to the table. After data is inserted, the row content is changed by using the UPDATE statement. You will use the WHERE clause of the UPDATE statement to limit the UPDATE of a single row. The following data is input for these four statements.

  ProductID	ProductName	Price	ProductDescription
  1	Clamp	12.48	Workbench clamp
  50	Screwdriver	3.17	Flat head
  75	Tire Bar		Tool for changing tires.
  3000	3mm Bracket	. 52

  The basic syntax is as follows: INSERT, table name, column list, VALUES, and then the list of VALUES to be inserted. If there are two hyphens before a line, it indicates the behavior comment, and the compiler ignores the text. In this case, annotations describe the allowed syntax variations.

  Insert data to a table

  1. Run the following statement to insert a row to the Products table created in the previous task. The basic syntax is as follows:

     -- Standard syntaxINSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)    VALUES (1, 'Clamp', 12.48, 'Workbench clamp')GO

  2. The following statement shows how to change the order of parameters provided by switching the ProductID and ProductName positions in the field list (in parentheses) and value lists.

     -- Changing the order of the columnsINSERT dbo.Products (ProductName, ProductID, Price, ProductDescription)    VALUES ('Screwdriver', 50, 3.17, 'Flat head')GO

  3. The following statement demonstrates that the column name is optional as long as the values are listed in the correct order. This syntax is common, but we recommend that you do not use it because it may be more difficult for others to understand your code. NULL is specified for the Price column because the Price of this product is unknown.

     -- Skipping the column list, but keeping the values in orderINSERT dbo.Products    VALUES (75, 'Tire Bar', NULL, 'Tool for changing tires.')GO

  4. The schema name is optional as long as the table is accessed and changed in the default schema. Because the ProductDescription column allows Null values without providing values, you can completely delete the names and values of the ProductDescription column from the statement.

     -- Dropping the optional dbo and dropping the ProductDescription columnINSERT Products (ProductID, ProductName, Price)    VALUES (3000, '3mm Bracket', .52)GO

  Update the products table

  • Type and execute the following UPDATE statement to change ProductName of the second product from Screwdriver to Flat Head Screwdriver.

    UPDATE dbo.Products    SET ProductName = 'Flat Head Screwdriver'    WHERE ProductID = 50GO

  Reading data from a table (Tutorial)

  You can use the SELECT statement to read data in the table. The SELECT statement is one of the most important Transact-SQL statements, and its syntax has many variants. In this tutorial, you will use five simple versions.

  Read table data

  1. Type and execute the following statement to read data in the Products table.

     -- The basic syntax for reading data from a single tableSELECT ProductID, ProductName, Price, ProductDescription    FROM dbo.ProductsGO

  2. You can use asterisks to select all columns in the table. This is usually used in ad hoc queries. You should provide a list of columns in the permanent code so that the statement will return the predicted column, even if a new column is added to the table later.

     -- Returns all columns in the table-- Does not use the optional schema, dboSELECT * FROM ProductsGO

  3. You can omit columns that do not want to be returned. Columns are returned in the order of listing them.

     -- Returns only two of the columns from the tableSELECT ProductName, Price    FROM dbo.ProductsGO

  4. Use the WHERE clause to limit the rows returned to the user.

     -- Returns only two of the records in the tableSELECT ProductID, ProductName, Price, ProductDescription    FROM dbo.Products    WHERE ProductID < 60GO

  5. You can use the values in the returned column. The following example performs a mathematical operation on the Price column. Unless you use the AS keyword to provide a name, the column to be changed in this way will not have a name.

     Lesson 2nd: Configure database object permissions

     It takes three steps to grant the user the permission to access the database. First, create a login name. With the login name, you can connect to the SQL Server database engine. Then, configure the login name as the user in the specified database. Finally, grant the user the permission to access the database objects. This section describes these three steps and describes how to create a view and stored procedure as an object.

     This course includes the following topics:

     • Create Login Name

     • Grant Database Access Permissions

     • Create views and stored procedures

     • Grant Database Object Access Permissions

     • Abstract: Configure database object permissions

  Create Login Name

  To access the database engine, you must have a login name. The logon name can indicate the user identity according to the Windows Account or members of the Windows Group. The logon name can also be the only logon name in SQL Server. Use Windows Authentication whenever possible.

  Create views and stored procedures

  Since Mary can accessTestDataDatabase, you may want to create some database objects (such as views and stored procedures), and then grant their access permissions to Mary. A view is a SELECT statement stored, and a stored procedure is one or more Transact-SQL statements executed in batches.

  Queries are performed according to the image table, but parameters are not accepted. Stored procedures are more complex than views. Stored Procedures can have both input and output parameters, and can include statements that control code streams, such as IF and WHILE statements. It is a good programming practice to apply stored procedures to all repeated operations in the database.

  In this example, you will use create view to CREATE a VIEW, which only selectsProductsTwo columns in the table. Then, you will use create procedure to CREATE a stored PROCEDURE that accepts price parameters and returns only those products whose prices are less than the specified value.

  Create View

  • Run the following statement to create a very simple view. This view executes the Select statement and returns the product name and price to the user.

    CREATE VIEW vw_Names   AS   SELECT ProductName, Price FROM Products;GO

  Test View

  • View processing is similar to table processing. Use the SELECT statement to access the view.

    SELECT * FROM vw_Names;GO

  Create a stored procedure

  • The following statement creates a stored procedure named pr_Names and accepts input parameters named @ VarPrice and whose data type is money. The statement Products less than that prints data in tandem with the input parameter (which has been changed from the money data type to the varchar (10) character data type. The stored procedure then runs the SELECT statement on the view and transmits the input parameters as part of the WHERE clause. This will return all products whose prices are less than the input value.

    CREATE PROCEDURE pr_Names @VarPrice money   AS   BEGIN      -- The print statement returns text to the user      PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10));      -- A second statement starts here      SELECT ProductName, Price FROM vw_Names            WHERE Price < @varPrice;   ENDGO

  Test the Stored Procedure

  • To test the stored procedure, type and execute the following statement. The stored procedure should return the names of the two Products whose prices are less than 1st that are entered in the Products table in Lesson 10.00.

    EXECUTE pr_Names 10.00;GO

  
  

• Grant Database Access Permissions

  Currently, Mary has the permission to access this SQL Server instance, but does not have the permission to access the database. Before you authorize her as a database user, she even has no permission to access its default database.TestData.

  To grant the Mary access permission, switchTestDataDatabase, and then use the create user statement to map her login name to a USER named Mary.

  Create a user in the database

  • Enter and execute the following statement (replace computer_name with your computer name) to grant Mary the permission to access the TestData database.

    USE [TestData];GOCREATE USER [Mary] FOR LOGIN [computer_name\Mary];GO

    Currently, for SQL Server and TestData databases, Mary has access permissions.

  Delete database objects

  To delete all objects created in this tutorial, you only need to delete the database. However, in this topic, you will complete the following steps to perform operations that are the opposite of each operation in the tutorial.

  Delete permissions and objects

  1. Before deleting an object, make sure that the correct database is used:

     USE TestData;GO

  2. Use the REVOKE statement to delete Mary's permission to execute the stored procedure:

     REVOKE EXECUTE ON pr_Names FROM Mary;GO

  3. Use the DROP statement to delete Mary's access to the TestData database:

     DROP USER Mary;GO

  4. Use the DROP statement to delete Mary's access permission to this SQL Server 2005 instance.

     DROP LOGIN [
          
           \Mary];GO
          

  5. Use the DROP statement to delete the Stored Procedure pr_Names:

     DROP PROC pr_Names;GO

  6. Use the DROP statement to delete the view vw_Names:

     DROP View vw_Names;GO

  7. Use the DELETE statement to DELETE all rows in the Products table:

     DELETE FROM Products;GO

  8. Use the DROP statement to delete the Products table:

     DROP Table Products;GO

  9. When the TestData database is being used, the database cannot be deleted. Therefore, you must first switch the context to another database, and then use the DROP statement to delete the TestData database:

     USE MASTER;GODROP DATABASE TestData;GO

  The "Write A Transact-SQL statement" tutorial ends here. Remember, this tutorial is just a brief overview and does not introduce all the options of the statements used. Designing and creating an effective database structure and Configuring Secure Access to data requires a database that is more complex than the database shown in this tutorial.