Tutorial: Writing Transact-SQL statements

Lesson 1th: Creating Database objects

This lesson describes how to create a database, create a table in a database, and then access and change the data in the table. Because this lesson is an introduction to using Transact-SQL, it does not use or describe many of the options that are provided for these statements.

You can write Transact-SQL statements and submit them to the database engine by using the following methods:

• By using SQL Server Management Studio. This tutorial assumes that you are using Management Studio, but you can also use Management Studio Express, which you can download for free from Microsoft Download Center (Microsoft Download Centre).

• By using the sqlcmd utility.

• By connecting from an application that you create.

The code executes on the database engine in the same way and with the same permissions, regardless of how you commit the code statement.

To run Transact-SQL statements in Management Studio, open Management Studio and connect to an instance of the SQL Server database engine. If you are unfamiliar 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: the name of the database. CREATE database also has many optional parameters, such as the disk location where you want to place the database files. When you execute CREATE DATABASE with no optional parameters, SQL Server uses the default values for many of these parameters. The optional syntax parameters used in this tutorial are very small.

Create a database

1. In the Query Editor window, type the following code, but do not execute it:

   Copy Code

   CREATE DATABASE Testdatago

2. Use the pointer to select the word CREATE DATABASE, and then press F1. You should open the CREATE DATABASE topic in SQL Server Books Online. You can use this method to find the complete syntax for the CREATE DATABASE and the 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 the database, SQL Server makes a copy of the model database and renames the copy to the database name. This operation should take only a few seconds unless you specify a database with a large initial size as an optional parameter.

Attention
When you submit multiple statements in a single batch, you can use the keyword GO to separate the statements. GO is optional when the batch contains only one statement.

• Create a table (tutorial)

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

  Most tables have a primary key, and the primary key consists of one or more columns of the table. The primary key is always unique. The database engine enforces the following restrictions: No primary key values in the table can be duplicated.

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

  Attention

  The database engine can be installed to be case-sensitive or case insensitive. If the database engine is case-sensitive for installation, the object name must always have the same case. For example, a table named OrderData is a different table than a table named OrderData. If the database engine is installed on a case-insensitive basis, the two table names are treated as the same table, and the name can only be used once.

  Create a database to include a new table

  • Enter the following code into the Query Editor window.

    Copy Code

    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.

  To switch the Query Editor connection to the TestData database

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

    Copy Code

    Use Testdatago

  Create a table

  • In the Query Editor window, type the following code and execute it to create a simple table called products. The names of the columns in the table are ProductID,ProductName, Price, and productdescription. The ProductID column is the primary key for the table. int,varchar (+), money, and text are all data types. When rows are inserted or changed, only the price and productiondescription columns can contain no data. This statement contains an optional element called a schema (dbo.). A schema is a database object that owns a table. If you are an administrator, dbo is the default schema. The dbo represents the database owner.

    Copy Code

    CREATE TABLE dbo. Products (ProductID int PRIMARY KEY is not NULL, ProductName varchar (+) NOT NULL, price money NULL, PRODUCTDESCRI Ption text NULL) GO

  You have created the Products table, and you can insert data into the table by using the INSERT statement. After the data is inserted, the content of the row is changed by using the UPDATE statement. You will use the WHERE clause of the UPDATE statement to restrict updates to a single row. These four statements will enter the following data.

  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, List of columns, values, and then the list of values to insert. If there are two hyphens in front of a row, the behavior comment is indicated, and the compiler ignores its text. In this case, the comment describes the allowed syntax variants.

  Inserting data into a table
  2. Execute the following statement to insert a row into the Products table that you created in the previous task. The basic syntax is as follows:

     Copy Code

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

  4. The following statement shows how to change the order in which parameters are supplied by toggling the position of ProductID and ProductName in the field list (in parentheses) and in the list of values.

     Copy Code

     --changing the order of the Columnsinsert dbo. Products (ProductName, ProductID, Price, ProductDescription) VALUES (' screwdriver ', ' 3.17 ', ' Flat head ') GO

  6. The following statement shows that the name of the column is optional, as long as the values are listed in the correct order. This syntax is common, but it is not recommended because it may be more difficult for others to understand your code. NULL was specified for the Price column because it is not yet known.

     Copy Code

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

  8. 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 and does not provide a value, the name and value of the productdescription column can be completely removed from the statement.

     Copy Code

     --Dropping the optional dbo and dropping the ProductDescription Columninsert products (ProductID, ProductName, Price) VALUES (+, ' 3mm bracket ',.) GO

  Update Products table

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

    Copy Code

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

  Reading data from a table (tutorial)

  Use the SELECT statement to read the 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.

  Reading data from a table
  2. Type and execute the following statement to read the data in the Products table.

     Copy Code

     --The basic syntax for reading data from a single tableselect ProductID, ProductName, Price, productdescription from D Bo. Productsgo

  4. You can use the asterisk to select all the columns in the table. This is typically used in ad hoc queries. You should provide a list of columns in the permanent code so that the statement will return a prediction column, even if you later add the new column to the table.

     Copy Code

     --Returns all columns in the table--Does does use the optional schema, Dboselect * from Productsgo

  6. You can omit columns that you do not want to return. The columns are returned in the order in which they are listed.

     Copy Code

     --Returns only and the columns from the Tableselect ProductName, and price from dbo. Productsgo

  8. Use the WHERE clause to restrict the rows that are returned to the user.

     Copy Code

     --Returns only A records in the Tableselect ProductID, ProductName, Price, and productdescription from dbo. Products WHERE ProductID < 60GO

  10. You can use them when you return the values in a column. The following example performs a mathematical operation on the price column. Columns that change in this way will have no name unless you provide a name by using the AS keyword.

      2nd lesson: Configuring Permissions for Database objects

      Granting a user access to a database involves three steps. First, create the login name. With the login name, users can connect to the SQL Server database engine. The logon name is then configured as a user in the specified database. Finally, the user is granted permission to access the database object. This lesson describes these three steps and describes how to create views and stored procedures as objects.

      This course covers the following topics:

      • Create a login name

      • Granting access to a database

      • Creating views and stored Procedures

      • Granting permissions to Access database objects

      • Summary: Configure permissions for database objects

  Create a login name

  To access the database engine, the user needs to have a login name. A login can represent a user as a Windows account or a member of a Windows group, or it can be a SQL Server login that exists only in SQL Server. You should use Windows authentication whenever possible.

  Creating views and stored Procedures

  Now that Mary has access to the TestData database, you might want to create some database objects, such as views and stored procedures, and then grant Mary the access rights. A view is a stored SELECT statement, and a stored procedure is one or more Transact-SQL statements that are executed in batch mode.

  Queries are treated as if they were image tables, 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 the flow of code, such as if and while statements. It is a good programming practice to use stored procedures for all repetitive operations in the database.

  In this example, you will create a view with the CREATE view, which selects only two columns in the Products table. You will then create a stored procedure using the Create PROCEDURE, which accepts the price parameter and returns only those products whose price is less than the value of the specified parameter.

  Create a View

  • Execute the following statement to create a very simple view that executes a Select statement and returns the name and price of the product to the user.

    Copy Code

    CREATE VIEW vw_names as SELECT ProductName; GO

  Test View

  • Views are handled in a similar way to tables. Use the SELECT statement to access the view.

    Copy Code

    SELECT * from Vw_names; GO

  Create a stored procedure

  • The following statement creates a stored procedure named Pr_names that accepts an input parameter named @VarPrice, with a data type of money. The stored procedure prints a statement that is concatenated with the input parameters (changed from the Money data type to the varchar () character data type) of the products less than. The stored procedure then executes the SELECT statement on the view and passes the input parameters as part of the WHERE clause. This will return all products with a price less than the input parameter value.

    Copy Code

    CREATE PROCEDURE pr_names @VarPrice money as BEGIN – The print statement returns text to the user print ' PR      Oducts less than ' + CAST (@VarPrice as varchar (10));   --A second statement starts here SELECT ProductName, price from Vw_names WHERE Price < @varPrice; Endgo

  Test stored Procedures

  • To test your stored procedure, type and execute the following statement. The stored procedure should return the names of the two products that were entered in lesson 1th in the product table and whose price is less than 10.00 .

    Copy Code

    EXECUTE pr_names 10.00; GO

  
  

• Granting access to a database

  Mary now has permission to access this instance of SQL Server, but does not have access to the database. She doesn't even have access to her default database TestDatauntil you authorize her as a database user.

  To grant Mary access, switch to the TestData database, and then use the CREATE user statement to map her login to a user named Mary.

  Create a user in the database

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

    Copy Code

    use [TestData]; Gocreate USER [Mary] for LOGIN [computer_name\mary]; GO

    Mary now has access to both SQL Server and TestData databases.

  To delete a database object

  To delete all of the objects created in this tutorial, you can simply delete the database. However, in this topic, you will complete the following steps to perform the opposite of each action in the tutorial.

  Delete Permissions and objects
  2. Before you delete an object, make sure that you use the correct database:

     Copy Code

     Use TestData; GO

  4. Use the REVOKE statement to remove Mary 's execute permission on the stored procedure:

     Copy Code

     REVOKE EXECUTE on pr_names from Mary; GO

  6. Use the drop statement to remove Mary 's access rights to the TestData database:

     Copy Code

     DROP USER Mary; GO

  8. Use the drop statement to remove Mary 's access rights to this instance of SQL Server 2005.

     Copy Code

     DROP LOGIN [<computer_name>\mary]; GO

  10. To delete a stored procedure pr_namesusing the drop statement:

      Copy Code

      DROP PROC Pr_names; GO

  12. To delete a view vw_namesusing the drop statement:

      Copy Code

      DROP View Vw_names; GO

  14. Delete all rows in the Products table using the DELETE statement:

      Copy Code

      DELETE from Products; GO

  16. Delete the Products table using the drop statement:

      Copy Code

      DROP Table Products; GO

  18. The database cannot be deleted when the TestData database is being used, so first switch the context to a different database, and then use the drop statement to delete the TestData database:

      Copy Code

      Use MASTER; Godrop DATABASE TestData; GO

  This concludes the "Writing Transact-SQL statements" tutorial. Keep in mind that this tutorial is just a brief overview, and it does not cover all the options for the statements you use. Designing and creating an effective database structure and configuring secure access to data requires a more complex database than the database shown in this tutorial.

  
  

Tutorial: Writing Transact-SQL statements