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;
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;
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
