Class 3 - Brief about T-SQL

Brief about TSQL

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

Eg : _ SELECT @@lNGUAGE displays the name of your SQL Server language.
_ SELECT @@SERVERNAME displays the name of the SQL Server for the current connection.
_ SELECT @@VERSION displays information about Microsoft SQL Server version, build, edition, and so on.
_ SELECT @@TRANCOUNT displays the number of open transactions for the current connection.
_ SELECT @@ERROR displays an error number giving

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