BPIT
WRITE DDL & DML QUERIES
We use the DDL commands for creating the database or schema, while DML commands
are used to populate and manipulate the database . DDL commands can affect the
whole database or table, whereas DML statements only affect single or
multiple rows based on the condition specified in a query.
DML
A data manipulation language (DML) is a computer programming language
used for adding (inserting), deleting, and modifying (updating) data in a
database. A DML is often a sublanguage of a broader database language
such as SQL, with the DML comprising some of the operators in the
language. Example- SQL.
Here are some important DML commands in SQL:
INSERT
UPDATE
DELETE
INSERT:
This is a statement 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');
UPDATE:
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
ITM ASSIGNMENT
BPIT
SET FirstName = 'Jhon', LastName= 'Wick'
WHERE StudID = 3;
DELETE:
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';
DDL
Data definition or data description language is a syntax for creating and
modifying database objects such as tables, indices, and users. DDL
statements are similar to a computer programming language for defining
data structures, especially database schemas.
Example
Views
Schemas
Tables
Indexes
Five types of DDL commands in SQL are:
1. CREATE
2. DROP
3. ALTER
4. TRUNCATE
CREATE
CREATE statements is used to define the database structure schema:
Syntax:
ITM ASSIGNMENT
BPIT
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
For example:
Create database university;
Create table students;
Create view for students;
DROP
Drops commands remove tables and databases from RDBMS.
Syntax
DROP TABLE;
For example:
Drop object_type object_name;
Drop database university;
Drop table student;
ALTER
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;
TRUNCATE:
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;
Comparison DDL DML
ITM ASSIGNMENT
BPIT
Basis
Basic It helps us define a database's structure or It allows us to manipulate, i.e
schema and deals with how data is stored in update, and delete the data sto
the database. database.
Full-Form The full form of DDL is Data Definition The full form of DML is Data Ma
Language. Language.
Categorization The DDL commands have no further The DML commands are cla
classification. procedural and non-
(declarative) DMLs.
Command uses The commonly used commands under DDL The commonly used commands
language are: language are:
o CREATE o INSERT
o DROP o UPDATE
o ALTER o DELETE
o TRUNCATE o SELECT
o RENAME
Auto-commit DDL commands are auto-committed, so DML commands are not auto-c
changes that happen in the database will be so database changes are not per
permanent.
Rollback DDL commands made changes permanent; DML commands do not mak
therefore, we cannot roll back these permanent; therefore, rollback
statements. for these statements.
WHERE clause DDL commands have no use of a WHERE The DML statements can use a WHERE
clause because here, filtration of records is clause while manipulating data in a
not possible. database.
Effect The DDL command affects the entire The DML commands will affect the single or
database or table. multiple records based on the specified
condition.
ITM ASSIGNMENT
