Ddl & dml commands

DDL & DML commands

1. DDL & DML commands Presented by-: A njali K awadiya

2. CONTENTS Introduction Of data definition language(DDL) Commands of DDL Create command Drop command Alter command Truncate command Introduction of data manipulation language(DML) Commands of DML Select command Insert command Update command Delete command Difference between DDL & DML commands Summary

3. Introduction of DATA DEFINITION LANGUAgE Data Definition Language (DDL) statements are used to define the database structure or schema . It is used by the database administrator and others to define internal and conceptual schema. Data definition language are auto-committed .That means it saves all the changes permanently in the database.

4. Commands of ddl

5. Create command Create command is a DDL command used to create a table or a database. Some objects like tables, indexes, function, views . Syntax:- Example:- The above command will create Employee table: Employee CREATE TABLE < Table_Name >( Column_Name Datatypes [size]); CREATE TABLE Employee( Emp_Id Number(10), Emp_Name Varchar (25) , Email_id Varchar (100)); Emp_Id Emp_Name Email_id

6. Drop command Drop command is used to delete whole database or table .It permanently removes objects from the memory or database. Syntax:- Example:- DROP TABLE < Table_Name >; DROP TABLE Employee;

7. Alter command Alter command is used to alter the structure of the database. There are various uses of alter command such as:- To add a column to existing table. To rename any existing column. To change datatype of any column or to modify its size. Alter is also used to drop a column. Syntax:- Example:- The above command alter the Employee table : ALTER TABLE < Table_name > ADD ( Column_Name datatype [size]) ALTER TABLE EMPLOYEE ADD ( Emp_Salary Number(10)); Emp_Id Emp_Name Email_id Emp_salary

8. Truncate command Truncate command is used to remove all the records or rows from the table and free the space containing the table. But this command will not destroy the table’s structure. When we apply truncate command on a table its primary key is initialized. Syntax:- Example:- The above command remove all records from Employee table: TRUNCATE TABLE < Table_name >; TRUNCATE TABLE Employee; Emp_Id Emp_Name Email_id Emp_salary

9. Introduction of Data manipulation language Data Manipulation Language commands it allow you to manage the data stored in the database. DML command is used by the database user and application programs to retrive , add ,remove or update the information in the database. DML commands are not auto-committed. So, it is possible to roll back the operation.

10. Commands of dml

11. Insert command Insert command is used to insert data into a table and add a new row to a table. Syntax:- OR Example:- The above command will insert a record into Employee table: INSERT INTO < Table_Name >(col1,col2,… colN )VALUES(value1,value2,… valueN ); INSERT INTO < Table_Name >VALUES(value1,value2,… valueN ); INSERT INTO Employee( Emp_Id,Emp_Name,Email_id,Emp_Salary )VALUES(101, 'Disha', '[email protected] ',10000); INSERT INTO Employee VALUES(102, 'Aditi','[email protected] ',20000); Emp_Id Emp_Name Email_id Emp_Salary 101 Disha [email protected] 10000 102 Aditi [email protected] 20000

12. Select command Select command is used to retrieve data from a database. Syntax: Example1: Example2: The above command will select retrive data from employee: Example1 Example:2 SELECT < column_name > FROM < table_name >; SELECT Emp_id,Emp_Name FROM Employee WHERE Emp_salary =10000; Emp_Id Emp_Name 101 Disha Emp_Id Emp_Name Email_id Emp_Salary 101 Disha [email protected] 10000 102 Aditi [email protected] 20000 SELECT * FROM Employee;

13. Update command Update command is used to update or modify the value of a column in the table .The set keyword is used to update the value. Syntax:- Example:- The above command will update a value of a column of employee table: Update < Table_Name > SET [Column_name1=value1,… Column_nameN = valueN ] WHERE[condition]; UPDATE Employee SET Emp_Salary =25000 WHERE Emp_Id =101; Emp_Id Emp_Name Email_id Emp_salary 101 Disha [email protected] 25000 102 Aditi [email protected] 20000

14. Delete command Delete command is used to delete or remove one or more rows from a table. Syntax:- Example:- The above command will delete all the records from Employee table: DELETE FROM < Table_Name > WHERE [condition]; DELETE FROM Employee WHERE Emp_name =‘ Disha ’; Emp_Id Emp_Name Email_id Emp_Salary 102 Aditi [email protected] 20000

15. Difference between DDL & DML DDL DML DDL stands for Data Definition Language. DML stands for Data Manipulation Language. 2. It is used to create the database schema. 2. It is used to populate and manipulate database. 3. It is used by database administrator to define schema. 3. It is used by database user to manipulate data in a database. 4. DDL command affect the entire database or the table. 4.DML command affect one or more records in a table. 5. Create, Drop, Alter, Truncate are DDL commands. 5.Select, Insert, Update, Delete are DML commands.

16. SUMMARY DLL commands are used to define the database structure and schema and it is used by database administrator. DLL commands are :create command, drop command, a lter command and truncate command. DML command are used to manage the data stored in the database and it is used by database user. DML commands are : select command, insert command, update command and delete command.

17. THANK YOU