SQL
SQL GROUP BY and HAVING Clause with Examples
What is the SQL Group by Clause? The GROUP BY clause is a SQL command that is used to group rows...
Data Definition Language helps you to define the database structure or schema. DDL commands help you to create the structure of the database and the other database objects. Its commands are auto-committed so, the changes are saved in the database permanently. The full form of DDL is Data Definition Language.
In this difference between DML and DDL tutorial, you will learn:
DML commands it to allow you to manage the data stored in the database, although DML commands are not auto-committed. Moreover, they are not permanent. So, It is possible to roll back the operation. The full form of DML is Data Manipulation Language.
Below is the key difference between DDL and DML in DBMS:
Here, are reasons for using DDL method:
Here, benefits/ pros of DML:
DDL vs DML
Here is the main difference between DDL and DML commands:
DDL | DML |
Data Definition Language (DDL) helps you to define the database structure or schema. | Data Manipulation Language (DML command) allows you to manage the data stored in the database. |
DDL command is used to create the database schema. | DML command is used to populate and manipulate database |
DDL is not classified further. | DML is classified as Procedural and Non and Procedural DMLs. |
CREATE, ALTER, DROP, TRUNCATE AND COMMENT and RENAME, etc. | INSERT, UPDATE, DELETE, MERGE, CALL, etc. |
It defines the column of the table. | It adds or updates the row of the table |
DDL statements affect the whole table. | DML effects one or more rows. |
SQL Statement can't be rollback | SQL Statement can be a rollback |
DDL is declarative. | DML is imperative. |
Five types of DDL commands are:
CREATE statements is used to define the database structure schema:
Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
For example:
Create database university; Create table students; Create view for_students;
Drops commands remove tables and databases from RDBMS.
Syntax:
DROP TABLE ;
For example:
Drop object_type object_name; Drop database university; Drop table student;
Alters command allows you to alter the structure of the database.
Syntax:
To add a new column in the table
ALTER TABLE table_name ADD column_name COLUMN-definition;
To modify an existing column in the table:
ALTER TABLE MODIFY(COLUMN DEFINITION....);
For example:
Alter table guru99 add subject varchar;
This command used to delete all the rows from the table and free the space containing the table.
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE table students;
Here are some important DML commands:
This is a statement that is a SQL query. This command is used to insert data into the row of a table.
Syntax:
INSERT INTO TABLE_NAME (col1, col2, col3,.... col N) VALUES (value1, value2, value3, .... valueN); Or INSERT INTO TABLE_NAME VALUES (value1, value2, value3, .... valueN);
For example:
INSERT INTO students (RollNo, FIrstName, LastName) VALUES ('60', 'Tom', 'Erichsen');
This command is used to update or modify the value of a column in the table.
Syntax:
UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]
For example:
UPDATE students SET FirstName = 'Jhon', LastName=' Wick' WHERE StudID = 3;
This command is used to remove one or more rows from a table.
Syntax:
DELETE FROM table_name [WHERE condition];
For example:
DELETE FROM students WHERE FirstName = 'Jhon';
Syntax:
CREATE TABLE tableName ( column_1 datatype [ NULL | NOT NULL ], column_2 datatype [ NULL | NOT NULL ], ... );
Here,
Example:
CREATE TABLE Students ( Student_ID Int, Student_Name Varchar(10) )
Syntax:
Alter TABLE <Table name> ADD Column1 datatype, Column2 datatype;
Example:
ALTER TABLE University.Students_Name ADD Course_Duration VARCHAR(20);
Syntax:
DROP TABLE <tableName>;
The parameter tableName is the name of the table that is to be deleted.
Example:
DROP TABLE COURSE_NAMES;
In PL/SQL, we can insert the data into any table using the SQL command INSERT INTO. This command will take the table name, table column, and column values as the input and insert the value in the base table.
The INSERT command can also take the values directly from another table using 'SELECT' statement rather than giving the values for each column. Through 'SELECT' statement, we can insert as many rows as the base table contains.
Syntax:
BEGIN INSERT INTO <table_name>(<column1 >,<column2>,...<column_n>) VALUES(<valuel><value2>,...:<value_n>); END;
The above syntax shows the INSERT INTO command. The table name and values are mandatory fields, whereas column names are not mandatory if the insert statements have values for all the columns of the table.
The keyword 'VALUES' is mandatory if the values are given separately, as shown above.
Syntax:
BEGIN INSERT INTO <table_name>(<columnl>,<column2>,...,<column_n>) SELECT <columnl>,<column2>,.. <column_n> FROM <table_name2>; END;
The above syntax shows the INSERT INTO command that takes the values directly from the <table_name2> using the SELECT command.
The keyword 'VALUES' should not be present in this case, as the values are not given separately.
Below is the Syntax to delete table
Syntax:
DROP TABLE <TableName>;
The parameter TableName is the name of the table that is to be deleted.
Example:
DROP TABLE COURSE_NAMES;
To view data in SQL Server, we use the SELECT statement.
Syntax:
SELECT expression FROM tableName [WHERE condition];
Example:
SELECT * FROM Course;
What is the SQL Group by Clause? The GROUP BY clause is a SQL command that is used to group rows...
What is PL/SQL Datatypes? A data type is associated with the specific storage format and range...
What is Identifiers? Identifiers are nothing but a name that is given to a PL/SQL object. The...
What is Database Design? Database Design is a collection of processes that facilitate the...
What is PL/SQL block? In PL/SQL, the code is not executed in single line format, but it is always...
What is Object Type in PL/SQL? Object-Oriented Programming is especially suited for building...