Letter T – Network Encyclopedia

T-SQL stands for Transact-SQL, which is Microsoft's and Sybase's proprietary extension to the SQL (Structured Query Language) used to interact with relational databases. T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements. T-SQL Index Creating a DatabaseCreating a TableInsert and UpdateRead DataCreating a Stored ProcedureBasic T-SQL Video TutorialAdditional Web Resources Creating a Database 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 put the database files. When you execute CREATE DATABASE without the optional parameters, SQL Server uses default values for many of these parameters. In a Query Editor window, type code: CREATE DATABASE DataBaseNameGO Press F5 to execute and a database named DataBaseName is created. When you create a database, SQL Server makes a copy of the model database, and renames the copy to the database name. This operation should only take several seconds, unless you specify a large initial size of the database as an optional parameter. Creating a Table To create a table, you must provide a name for the table, and the names and data types of each column in the table. It is also a good practice to indicate whether null values are allowed in each column. To create a table, you must have the CREATE TABLE permission and the ALTER SCHEMA permission on the schema that will contain the table. The db_ddladmin fixed database role has these permissions. In a Query Editor window, type and execute the following code to create a table named Products. CREATE TABLE Products (ProductID int PRIMARY KEY NOT NULL, ProductName varchar(25) NOT NULL, Price money NULL, ProductDescription varchar(max) NULL) GO Back to index Insert and Update The basic syntax is: INSERT, table name, column list, VALUES, and then a list of the values to be inserted. The two hyphens in front of a line indicate that the line is a comment and the text will be ignored by the compiler. In this case, the comment describes a permissible variation of the syntax. INSERT Products (ProductID, ProductName, Price, ProductDescription) VALUES (1, 'Clamp', 12.48, 'Workbench clamp') GO Type and execute the following UPDATE statement to change the ProductName of the second product from Screwdriver, to Flat Head Screwdriver. UPDATE Products SET ProductName = 'Flat Head Screwdriver' WHERE ProductID = 50 GO Back to index Read Data The SELECT statement is one of the most important Transact-SQL statements, and there are many variations in the syntax. Read the data in a table -- The basic syntax for reading data from a single table SELECT ProductID, ProductName, Price, ProductDescription FROM Products GO You can use an asterisk (*) to select all the columns in the table. The asterisk is for ad hoc queries. In permanent code, provide the column list so that the statement returns the predicted columns, even if a new column is added to the table later. -- Returns all columns in the table -- Does not use the optional schema, dbo SELECT * FROM Products GO You can use a WHERE clause to limit the rows that are returned to the user. -- Returns only two of the records in the table SELECT ProductID, ProductName, Price, ProductDescription FROM dbo.Products WHERE ProductID < 60 GO You can work with the values in the columns as they are returned. The following example performs a mathematical operation on the Price column. Columns that have been changed in this way will not have a name unless you provide one by using the AS keyword. -- Returns ProductName and the Price including a 15% tax -- Provides the name CustomerPays for the calculated column SELECT ProductName, Price * 1.15 AS CustomerPays FROM dbo.Products GO Back to index Create a Stored Procedure The following statement creates a stored procedure name pr_Names, accepts an input parameter named @VarPrice of data type money. The stored procedure prints the statement Products less than concatenated with the input parameter that is changed from the money data type into a varchar(10) character data type. Then, the procedure executes a SELECT statement on the view, passing the input parameter as part of the WHERE clause. This returns all products that cost less than the input parameter 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; END GO To test the stored procedure, type and execute the following statement. The procedure should return the names of the two products entered into the Products table in Lesson 1 with a price that is less than 10.00. EXECUTE pr_Names 10.00; GO What's next? To learn more about T-SQL we suggest the following book available on Amazon: T-SQL Fundamentals by Itzik Ben-Gan. Back to index Basic T-SQL Video Tutorial https://www.youtube.com/watch?v=V4hTLja7jU8 Getting Started with T-SQL using SQL Server Management Studio Web Resources Tutorials for SQL ServerT-SQL Tutorial Back to index