DDL vs DML in Relational Databases

DDL vs DML in Relational Databases - Vijay Thapa

DDL - Data Definition Language in Relational Database Management System (RDBMS)

DDL or Data Definition Language defines the Database Schema and also deals with how data must be stored on it.

The DDL SQL Commands are used to Create, Modify and Drop the structure of Database objects in the database.

Some of the DDL SQL Commands are as follows

Watch this video to learn CREATING, ALTERING and DROP Database

CREATE DATABASE

The following query is used to Create Database

Basic Syntax is

Create database <yourdatabasename>

Example
To create a database named 'Testdb', Run the following query

Create database Testdb

ALTER DATABASE

The following query is used to Alter or Modify Database

Basic Syntax is

ALTER DATABASE <old database name> MODIFY name=<new database name>

Example
To rename the database from 'Testdb' to 'Testdb_new', Run the following query

ALTER DATABASE Testdb MODIFY name=Testdb_new

DROP DATABASE

The following query is used to Drop or Delete Database

Basic Syntax is

Drop database <your database name>

Example
To delete the database named 'Testdb', run the following query

Drop database Testdb

Watch this video to learn CREATING, ALTERING and DROP Table

CREATE TABLE

The following query is used to Create Table

Basic Syntax is

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),     
   PRIMARY KEY (ID)
);

If you run the query above, then it will create a table as follows

Customers Table Created

ALTER TABLE  

The following query is used to Alter or Modify Table.

Basic Syntax is
Here is the query to rename the table in MS SQL Server Database

sp_rename '<old database name>', '<new database name>'

Example
Let's change the table name from "CUSTOMERS" to USERS

sp_rename 'CUSTOMERS', 'USERS'

DROP TABLE 

The following query is used to Drop or Delete Table

Basic Syntax is

DROP TABLE <table name>;

Example

DROP TABLE USERS;

Watch this video to learn ADDING, REMOVING and MODIFYING Table in Database

ADD COLUMN

The following query is used to Add Column in Existing Table

Basic Syntax

ALTER TABLE table_name ADD column_name datatype;

Example

ALTER TABLE tbl_user ADD email varchar(150);

ALTER TABLE tbl_user ADD contact varchar(20); 

First query will add 'email' column and Second query will add 'contact' column in our 'tbl_user' Table.

DROP COLUMN

The following query is used to Drop or Remove Column in Existing Table

Basic Syntax

ALTER TABLE table_name DROP COLUMN column_name;

Example

ALTER TABLE tbl_user DROP COLUMN email;

This query will remove the 'email' column from our 'tbl_user' Table.

MODIFY COLUMNS

The following query is used to Modify Column in Existing Table

Basic Syntax

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

Example

ALTER TABLE tbl_user MODIFY COLUMN contact int;

This query changes the data type of 'contact' column from varchar to int in our 'tbl_user' Column.

DML - Data Manipulation Language in Relational Database Management System (DML)

As the name itself describes, most of the SQL Commands in DML performs manipulation of data in database like INSERT, UPDATE and DELETE.

Some of the DML SQL Commands are

INSERT

This query is used to Insert data in table inside Relational Database Management System

Basic Syntax

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

Example

INSERT INTO tbl_user
(user_id, full_name, gender, email, guardian)
VALUES
(1, 'Vijay Thapa', 1, '[email protected]', 'Anyone');

Watch this tutorial to learn "How to Insert data in Table"?

UPDATE

This query is used to Update data in Table

Basic Syntax

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

Example

UPDATE tbl_user
SET full_name='Bijay Thapa Magar'
WHERE user_id=1;

This query will update the full name to 'Bijay Thapa Magar' (only for the user having id 1).

Note: If you do not give the id with WHERE then the query will set the full_name of all the users as 'Bijay Thapa Magar'

Watch this tutorial to learn "How to Update data in Table?"

DELETE

This query is used to Delete data in Table

Basic Syntax

DELETE FROM table_name
WHERE [condition];

Example

DELETE FROM tbl_user
WHERE user_id=2;

Note: If you do not give the user_id with WHERE keyword then all the data from the Table 'tbl_user' will be deleted.

Watch this tutorial to learn "How to Delete data from Table in Database?"

Do you want to learn Database?

Here's a "Database for Beginners" FREE Course for you.

Get Access Now