Basic T-SQL Tutorial

T-SQL or Transact Structured Query Language is a programming language for databases. Most of the major database systems such as Oracle, Microsoft SQL Server and MySQL, utilize some form of T-SQL. It’s essentially a language that lets you quickly and easily do almost anything that you would ever want to do with a database. Let’s go over some of the basic T-SQL commands that are available and what they do.

CRUD Operations

CRUD (Create, Read, Update and Delete) Operations are the basic data functions that you will most likely need to perform in any programming language. Let’s break each one of these down as it pertains to T-SQL.

• CREATE does exactly what it says. It creates new objects. For instance, you can create a new database [ CREATE DATABASE ], then create new tables [ CREATE TABLE ] inside the database, then create columns [ CREATE COLUMN ] inside the tables. After the create statements have been executed to build your table structure, you can now create new data. However, you will use the INSERT statement to add data to the tables that you have created by specifying the columns that you will be inserting data into and then the data that you would like to insert. For example, [ INSERT INTO NewTable (column1, column2) VALUES (1,2) ].

• READ statements are performed in T-SQL by using SELECT statements. You simply specify the columns that you would like to read and the table where they are contained. You can also filter, sort, group and perform other operations on the data to return exactly the data that you are looking for by adding a WHERE, GROUP BY and/or ORDER BY clauses to the SELECT statement. For example, [ SELECT column1, column2 FROM NewTable WHERE column1 = 1 GROUP BY column1, column2 ORDER BY column1, column2 ]. This statement selects column one and column two from the new table and then filters, groups and sorts the data all in one statement.

• UPDATE statements are simple like CREATE statements in that they do just what you think. The syntax tells SQL which columns in the specified tables you would like to update with what data. For instance, [ UPDATE NewTable SET column1 = value1 ].

• DELETE is also explanatory. You can DELETE all the data in a table simply by specifying which table you would like to clear, for example, [ DELETE NewTable ]. This doesn’t delete the table, but only the data contained in the table. If you wish to delete the actual table, you will need to use a DROP statement, which is [ DROP TABLE NewTable ]. By having two different commands, you can make sure you’re performing the desired action.

User Operations

You would use a CREATE statement to create a new user much the same way you would with any other object, which would be [ CREATE USER User1 ] and then the user id of the user you wish to create. To grant permissions to that user, you would use the GRANT statement followed by the user name and the permissions you would like to grant that user.

For example, to allow the user to read data from the database, you would use the [ GRANT SELECT TO User1 ] statement. You can also add multiple permissions by separating them with commas, for example, [ GRANT SELECT, UPDATE, DELETE, INSERT, EXECUTE TO User 1 ].

Learning basic T-SQL commands is fairly easy. However, if you really want to dive into Transact-SQL and learn how to make it work for you, why not consider some in-depth training. This tutorial only barely scratches the surface. With training provided by a top-notch training service such as ONLC, you can open up the world of database programming.

With the ability to attend training at one of their over 300 Remote Learning Classrooms or online in your home or office, you can easily obtain that vital training you’ll need to become proficient in one of the most powerful information technologies available today. Why not give us a call and find out how to get trained today?