Transact-SQL (T-SQL)

Content

• Introduction
• Retrieving data using T-SQL
• Using update statement
• Using delete statement
• Using insert statement

I. Introduction

SQL is a database query and programming language used for accessing data and for querying, updating, and managing relational database systems.

The SQL language contains statements that fit into two main programming language categories: DDL and DML. We'll look at these language categories in the following sections.

Data Definition Language (DDL)

DDL is used to define and manage database objects such as databases, tables, and views.

DDL statements usually include CREATE, ALTER, and DROP commands for each object. For example, the statements CREATE TABLE, ALTER TABLE, and DROP TABLE are used to initially create a table, alter its properties (by adding or deleting columns, for instance), and drop a table.

Data Manipulation Language (DML) 

DML is used to manipulate the data contained in database objects, using statements such as INSERT, SELECT, UPDATE, and DELETE. These statements allow you to select rows of data by performing queries, insert new rows of data, modify existing rows of data, and delete unwanted rows of data, respectively. This section gives basic examples of each.

T-SQL

T-SQL is an enhancement of the standard SQL programming language. It is the primary language used for communications between applications and SQL Server. T-SQL provides the DDL and DML capabilities of standard SQL, plus extended functions, system stored procedures, and programming constructs (such as IF and WHILE) to allow more flexibility in programming.

You can execute interactive T-SQL statements by using one of three SQL Server utilities—ISQL, OSQL, or Query Analyzer—or you can create and execute T-SQL scripts.

II. Retrieving Data Using T-SQL

Syntax:

SELECT FieldName FROM TableName

Ex:

SELECT name FROM Student

Select Multiple Fields

Syntax:

SELECT field1, field2,field3 FROM tablename

Ex:

SELECT id,name,gender FROM Student

Select all fields

Syntax:

SELECT * FROM tablename

Ex:

SELECT * FROM Student

Select by Specific Number of Row 

Syntax:

SELECT TOP Number field FROM tablename

Ex:

SELECT TOP 10 * FROM Student

Select Non Duplicated Data 

Syntax:

SELECT DISTINCT field FROM tablename

Ex:

SELECT DISTINCT name FROM Student

Sorting Data 

Syntax:

SELECT FieldName1 [,FieldName2,…]

FROM TableName 

ORDER BY FieldName ASC [,FieldName2 [Asc/Desc],.. .]

Ex:

SELECT * FROM Student ORDER BY name ASC

SELECT * FROM Student ORDER BY name DESC

Filtering Data by Using [Where Clause]

Syntax:

SELECT FieldName [,FieldName2,…]

FROM TableName 

WHERE Condition

Ex:

SELECT * FROM Student WHERE id=10

Using Wildcard Operator 

Syntax:

SELECT field FROM tablename 

WHERE field like ‘%condition’

Ex:

SELECT * FROM Student WHERE name like ‘%dara’

Using Aggregate Functions

There are some aggregated functions as below. 

SUM (sum value)

COUNT (count row)

AVG (average)

MAX (maximum)

MIN (minimum)

Ex:

SELECT SUM(Price * Quantity) As Amount FROM saledetail

Grouping Data 

You can group by data by using Group By statement of T-SQL below when you select one or more column with using aggregate function.

Ex:

SELECT code , SUM(price * quantity) As Amount 

FROM SaleDetail

WHERE code Like ‘a%’

GROUP BY code

Using SubQuery

You can use sub query to get data from tables by using T-SQL below. 

Ex:

SELECT name FROM  product

WHERE id NOT IN (SELECT ProductID FROM SaleDetail) 

Using Join Operator 

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN returns all rows from multiple tables where the join condition is met.

SQL INNER JOIN

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

Syntax:

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

SQL LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

Syntax:

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;

SQL RIGHT JOIN

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

Syntax

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;

SQL FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).

The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

Syntax

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;

SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

Syntax

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;

III. INSERT DATA

INSERT statement is used to insert (add) rows to a database table. INSERT can be used in several ways: 

.Inserting a single complete row

.Inserting a single partial row

.Inserting the results of q query

Syntax

INSERT INTO table_name VALUES (value1,value2,value3,...);

OR

INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

SQL INSERT INTO SELECT Statement

With SQL, you can copy information from one table into another.

The INSERT INTO SELECT statement copies data from one table and inserts it into an existing table.

Syntax

INSERT INTO table2 SELECT * FROM table1;

OR

INSERT INTO table2 (column_name(s)) SELECT column_name(s) FROM table1;

IV. Update Data

To update (modify) data in a table the UPDATE statement is used. UPDATE can be used in two ways: 

. To update specific rows in a table

. To update all rows in a table. 

Ex:

UPDATE product SET cost= 30 

WHERE id=100

V. Delete Data 

To delete (remove) data from a table, the delete statement is used. DELETE can be used in two ways: 

. To delete specific rows from a table 

. To delete all rows from table 

Ex:

DELET FROM Category WHERE id= 10