Brief about TSQL (Transact SQL)

Transact SQL is Structure Query Language which is using by SQL Server. When working with Oracle; PL-SQL can be used. A brief about TSQL query language.........

DDL – DATA DEFINITION LANGUAGE
DML – DATA MANIPULATION LANGUAGE
DCL – DATA CONTROL LANGUAGE
TCL – TRANSACT CONTROL LANGUAGE

DDL – CREATE, ALTER, DROP, TRUNCATE

Eg : CREATE DATABASE SampleDatabase
ON
( NAME = MyDatabase,
FILENAME = ‘C:\program files\MyData\mydatabasefile.mdf’,
SIZE = 10MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1MB)
LOG ON
( NAME = MyDatabase_LOG,
FILENAME = ‘C:\program files\MyData\mydatabaselog.ldf’,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )

Eg :
CREATE TABLE MyTable (
Field1 int PRIMARY KEY,
Field2 char(10) NOT NULL),
Field3 datetime
)

Eg : ALTER DATABASE SampleDatabase
ADD FILE
(
NAME = MyDatabase1,
FILENAME =’c:\program files\MyData\mydatabasefile2.ndf’,
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 1MB
)

Eg : ALTER TABLE MyTable ADD Field4 VARCHAR(10) NULL
ALTER TABLE MyTable DROP COLUMN Field4

Eg : DROP DATABASE SampleDatabase

Eg : DROP TABLE MyTable

DML – SELECT, INSERT, UPDATE, DELETE

SELECT :
Select * from [ABCD]

INSERT :
Insert into ABCD [ColumnA, ColumnB] values 5, 6

Insert into ABCD values 5,6

DELETE
Delete from ABCD where ColumnA = 5

UPDATE
Update ABCD set ColumnA = 8 where ColumnA = 5

DCL – GRANT, REVOKE

CREATE LOGIN [venky] WITH PASSWORD=N'111'
CREATE USER [venky] FOR LOGIN [venky]
EXEC sp_addrolemember N'db_owner', N'venky'
DROP LOGIN [venky]

Grant select to venkat
Revoke select to venkat

TCL – COMMIT, ROLLBACK

Commit
Rollback