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.
good explanation. Thanks!
ReplyDeleteGreat Article Artificial Intelligence Projects
DeleteProject Center in Chennai
JavaScript Training in Chennai
JavaScript Training in Chennai
IntelliMindz is a best IT Training in Bangalore with placement, offering 200 and more software courses with 100% Placement Assistance.
DeleteMSBI Training In Bangalore
MSBI Online Training
MSBI Training In Chennai
Thanks...
ReplyDeletegreat work bagesh
ReplyDeletethank you for providing the valuable information regarding msbi ..keep update with your blogs...once check it out
ReplyDeleteMSBI Online Training Hyderabad
It is very good blog and useful for students and developer ,
ReplyDeleteSql server DBA Online Course Bangalore
Through this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing on Msbi online training
ReplyDeleteI like your post very much. It is very useful for my research. I hope you can share more info about this. Keep posting Mulesoft Developer Certification
ReplyDeleteservicenow developer Certification
Workday training
Workday financial training
Workday HCM Online training
Nice post.
ReplyDeleteSelenium training
Spark online training
Spark training
splunk admin online training
splunk admin training
splunk development online training
splunk development training
splunk online training
splunk training
sql azure online training
sql azure training
sql plsql online training
sql plsql training
sql server dba online training
sql server dba training
sql server developer online training
sql server developer training
very interesting post.this is my first time visit here.i found so mmany interesting stuff in your blog especially its discussion..thanks for the post! שרת וירטואלי
ReplyDeletevery special. easy to understand…
ReplyDeletemsbi course
Msbi Online Training Hyderabad
msbi online training
very special. easy to understand…
ReplyDeletemsbi course
Msbi Online Training Hyderabad
msbi online training
very special. easy to understand…
ReplyDeletemsbi course
Msbi Online Training Hyderabad
msbi online training
very special. easy to understand…
ReplyDeletemsbi course
Msbi Online Training Hyderabad
msbi online training
Very nice article,Keep Updating more posts with us.
ReplyDeleteThank you..
MSBI Online Training India
nice post.splunk training
ReplyDeletesplunk online training
P0wer bi onlinetraining
ReplyDeleteP0wer bi training
nice post.devops training
ReplyDeletedevops online training
devops online course
Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website Workday HCM Online Training
ReplyDeletebest Workday HCM Online Training
top Workday HCM Online Training
Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website Workday HCM Online Training
ReplyDeletebest Workday HCM Online Training
top Workday HCM Online Training
nice post.informatica online training hyderabad
ReplyDeleteThanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website Workday HCM Online Training
ReplyDeletebest Workday HCM Online Training
top Workday HCM Online Training
Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website Workday HCM Online Training
ReplyDeletebest Workday HCM Online Training
top Workday HCM Online Training
Great Information. Thanks for sharing follow us:
ReplyDeletesql server DBA training in Hyderabad
Thanks a lot for information, thats really awesome
ReplyDeleteit’s very helpful thanks for your valuable information follow us
ReplyDeleteTableau online training in Hyderabad
Thanks for sharing us. calgaryregionfocus
ReplyDelete
ReplyDeleteWe are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great work.Business Announcer
ReplyDeleteWe are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great work Business Announcer
I enjoyed your blog Thanks for sharing such an informative post. We are providing the best services click on below links to visit our website.
ReplyDeleteSnowflake Training
Snowflake Training in Hyderabad
Snowflake Online Training
Snowflake Online Training Hyderabad
Snowflake Training Online
Snowflake Training in Ameerpet
Snowflake Training Institute in Hyderabad
Such an impressive post Thanks for sharing.. We are providing the best services click on below links to visit our website.
ReplyDeleteSnowflake Training
Snowflake Training in Hyderabad
Snowflake Online Training
Snowflake Online Training Hyderabad
Snowflake Training Online
Snowflake Training in Ameerpet
Snowflake Training Institute in Hyderabad
Thank you for sharing this useful information. Tableau training in chennai
ReplyDeleteRespect and I have a neat offer you: Where To Loan For House Renovation house renovation companies
ReplyDelete