Next step is a big progress, it is about learning TSQL basics, basically if you learn this concept and the most common commands that we are going to see here, you can do a lot of work in SQL database and you can improve this knowledge so much faster.
TSQL (short for Transact SQL) is an extension to SQL that adds extra programming functionality and control mechanisms. Depending on which version of SQL Server you’re running, you’ll have different query tools available.
To start learn TSQL you need to learn and understand the basics, some of the point that I saw today:
- Creating a database in TSQL
The TSQL CREATE DATABASE statement is used to create a new SQL database, always database name should be unique.
TIP: Make sure you have admin privilege before creating any database, it can be an issue for you and we don’t want it happens 😉
The following TSQL code creates a database:
CREATE DATABASE DatabaseDWJourneyBlog;
After that you need to create tables, there isn’t rule, it depends on your business workflow. Creating a basic table involves the table and defining its columns and each column’s data type (We’ll learn about data types in the future).
The TSQL sintax of CREATE TABLE statement is as follows:
CREATE TABLE table_name(
PRIMARY KEY (one or more columns)
This is the structure, but an example is:
CREATE TABLE Customers(
Id INT NOT NULL,
Name VARCHAR(20) NOT NULL,
Age INT NOT NULL,
Salary DECIMAL(18, 2),
PRIMARY KEY (Id)
An important concept is the CRUD. Create (insert), Read (select), Update and Delete (CRUD) are the four main actions you perform on your SQL Server database. A database is useless if you can’t edit and manage the data, and that’s where CRUD is used.
First, the INSERT statement creates (the “C” in CRUD) a record. The following code is an example of an INSERT statement for T-SQL:
INSERT INTO customer (name, email) VALUES(‘Walisson’, ‘email@example.com’)
Next, you have the SELECT statement (the “R” in the CRUD acronym), which is used to read and retrieve data from your tables. The SELECT statement is probably the most commonly used keyword when you work with your data. The SELECT statement is used in subqueries and main queries that return data sets to your front-end code. When you work with your data, you will also run SELECT statements just to analyze and review your data.
The following TSQL code selects a list of orders for a particular customer:
SELECT * FROM order WHERE customerId = @customerId
Next, UPDATE is used to edit your database content. The most important part of your UPDATE statement is the “where” clause. The where clause determines the records that update when you run the statement. Take the following code:
UPDATE customer SET first_name = @firstname WHERE customerId = @customerId
Finally, you might want to delete some information from your data. Most database administrators urge you to have an active and inactive column. This means you only edit this particular column and leave the rest of your data intact.
The following code is an example of a DELETE TSQL command:
DELETE FROM customer WHERE customerId = @customerId
With this knowledge you already can do a lot of work in TSQL!