Basics of dml queries

This slide is about Data Manipulation Language( DML )

1. Basics of DML Queries Anoop K. C. [email protected] www.facebook.com/anoopbaabte twitter.com/ anoop_baabte in.linkedin.com/in/ anoopbaabte / [phone]

2. Data Manipulation Language (DML) statements are used for managing data in database. These SQL commands are used for storing, modifying, and deleting data. DML commands are not auto-committed. It means changes made by DML command are not permanent to database, it can be rolled back. There are three main DML statements in SQL server The “ INSERT ” command The “ UPDATE ” command The “ DELETE ” command

3. The “INSERT” command Insert command is used to insert data into a table. Following is its general syntax, Example Consider a table Student with following fields. The following command will insert a record into the student table The resulting table will look like

4. The “INSERT” command – contnd . Both the statements below will insert NULL value into age column of the Student table.

5. The “UPDATE” command Update command is used to update a row of a table. Following is its general syntax, Example Consider the below table The following statement will update the age of “Alex” to 18 which is null at present

6. The “UPDATE” command – contnd . The resulting table will look like We can also update multiple columns in a single command, for example, the command Will update two columns of a the record which is having “ s_id ” as 103

7. The “DELETE” command Delete command is used to delete data from a table. Delete command can also be used with condition to delete a particular row. Following is its general syntax, Example The following command will delete all the records from student table Consider the following student table

8. The “DELETE” command – contnd . The following command will delete the record where s_id is 103 from Student</b> table. The resulting table will look like

9. Sql Commands Categories We have different sql commands for different purposes. We can group Sql Commands into five major categories depending on their functionality. Data Definition Language (DDL) These SQL commands are used to create, modify, and drop database objects like table, view, procedure, indexes etc. In this category we have CREATE, ALTER, DROP and TRUNCATE commands. Data Manipulation Language (DML) These SQL commands are used to store, modify, and delete data from database tables. In this category we have INSERT, UPDATE , and DELETE commands. Data Query Language (DQL) These SQL commands are used to fetch/retrieve data from database tables. The SELECT command comes under this category.

10. Sql Commands Categories - contnd Transaction Control Language (TCL) These SQL commands are used to handle changes which affect the data in database. Basically we use these commands with in the transaction or to make a stable point during changes in database at which we can rollback the database state if required. In this category we have SAVEPOINT, ROLLBACK and COMMIT commands. Data Control Language (DCL) These SQL commands are used to implement security on database objects like table,view,stored procedure etc. In this category we have GRANT and REVOKE commands.

11. Follow us @  twitter.com/ baabtra Like us @ facebook.com/ baabtra Subscribe to us @ youtube.com/ baabtra Become a follower @ slideshare.net/ BaabtraMentoringPartner Connect to us @ in.linkedin.com/in/ baabtra Thanks in advance .    www.baabtra.com  |  www.massbaab.com  | www.baabte.com

12. Contact Us Emarald Mall (Big Bazar Building) Mavoor Road, Kozhikode, Kerala, India. Ph: + 91 – 495 40 25 550 NC Complex, Near Bus Stand Mukkam , Kozhikode, Kerala, India. Ph: + 91 – 495 40 25 550 Cafit Square, Hilite Business Park, Near Pantheerankavu , Kozhikode Start up Village Eranakulam , Kerala, India. Email: [email protected]