SQL Language statement
SQL
commands are instructions used to communicate with the database to perform
specific task that work with data. SQL commands can be used not only for
searching the database but also to perform various other functions like, for
example, you can create tables, add data to tables, or modify data, drop the
table, set permissions for users. SQL commands are grouped into four major
categories depending on their functionality:
DDL (Data Definition Language)
DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database. Database object may be table, view, trigger, User defined function, Sp, index etc.
CREATE
This is use to creates data objects in the
database
use TestSSIS
--Table
create table
Employee
(
EmpID int,
Name Varchar(100)
)
--View
Create view
VEmployee
AS
select * from Employee
--SP
create proc
Usp_Emp
As
select * from Employee
|
We can see it on the explorer
ALTER
Alters is used to changes the structure of the
database object. Like suppose I want to change the Data type of the column or
we need to add the new column on the table we use Alter.
See the example here I am going to
Add one column in the table.
Alter table Employee
Add EmpAddress varchar(100)
|
Now we see a new column is added
on the table
DROP
It is used to Deletes objects of
the database
Drop table
Employee
|
Now table Employee has been
deleted from the Database.
Not able to find this table.
TRUNCATE
Deletes all records from a table and resets
table identity to initial value. It is only use for table object.
Now again I am creating the table
and inserting the data on it.
Now I am using Truncate to
deleting the records from the table.
Now see the output
Truncate only delete the data from
the table. Structure will be remaining same.
DML (Data Manipulation language)
DML
is abbreviation of Data Manipulation Language.
It is used to retrieve, store, modify, delete, insert and update data in
database. With the help of DML comment we are manipulating the data.
SELECT
Retrieves data from a table
INSERT
Inserts data into a table
Inserting 3 records
See the all records
UPDATE
Updates existing data into a table
See the update values.
DELETE
Deletes all records from a table
See the deleted values.
TCL (Transactional Control Language)
TCL
is abbreviation of Transactional Control Language. It is used to manage
different transactions occurring within a database.
COMMIT
Saves work done in transactions
Begin transaction trans
--DML
command
Commit
--or
Rollback transaction trans
|
ROLLBACK
Restores database to original state since the
last COMMIT command in transactions
See the example
Begin try
Begin transaction trans
update Employee set
name='Ramesh' where EmpID=2
Commit
Print 'Transaction Committed successfully'
End Try
Begin Catch
Rollback transaction
print 'Transaction Rollback'
End catch
|
See the committed data
Now see the example of Rollback
Begin try
Begin transaction trans
update Employee set
name='Ramesh' where EmpID='Raj'
Commit
Print 'Transaction Committed successfully'
End Try
Begin Catch
Rollback transaction
print 'Transaction Rollback'
End catch
|
This transaction gets failed
because I supplied EmpID as Varchar.
SAVE TRANSACTION
Sets a save point within a transaction
For More details read the Transaction in sql server
http://bageshkumarbagi-msbi.blogspot.in/2016/06/transaction-in-sql-server.htmlDCL (Data Control Language)
DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.
GRANT
Gives user’s access privileges to database
Syntax
Grant [privilege
Name]
on object Name To [user Name/Public/role Name] [with Grant Option] |
See the example
REVOKE
Withdraws user’s access privileges to database
given with the GRANT command
Revoke [privilage
Name]
on objectName
from [user Name/public/role Name]
|
See example.
