Data Manipulation Language (DML)

What is DML? / List of operations that can be performed by Data Manipulation Language / DML examples / Types of DML / Procedural and Declarative DML

Data Manipulation Language (DML)

Data Manipulation is about handling the data stored in a database differently. The following operations would be considered as data manipulation in a database;

• Retrieving the data stored in the database – the way to view the data stored in the database

• Insertion of new data into the database – the way to insert and store the data in the database

• Deletion of stored data – the way to delete old or unwanted data from the database

• Modification of the stored data – the way to modify when required or when false data stored

The above said operations can be executed in Oracle using the following SQL statements respectively, for example;

Assume a table Teacher with attributes TID, Name, and Phone number. 

Retrieval – SELECT * FROM Teacher;

Insertion – INSERT INTO Teacher VALUES (100, ‘Ramesh’, 9900887771);

Deletion – DELETE FROM Teacher WHERE TID = 100;

Modification – UPDATE Teacher SET phone = 9900887766;

There are basically two types of DML languages;

1. Procedural DMLs – in procedural DMLs the user has to specify

• how to get the required data along with

• what data are required.

2. Declarative DMLs (Non-procedural) – it requires the user to specify

• What data are required only. (Note:- The DML component of Structured Query Language (SQL) is declarative, ie., non-procedural)

 

************************

Related links:

• CREATE TABLE statement

• Database query languages

• SELECT statement explained

Database Languages (DDL, DML, DCL, and TCL)

Database Languages / What is database language? / List of database languages / DDL, DML, DCL, and TCL

Database Languages

Database languages are the easiest way to interact with the database. As we know already, a DBMS is a set of programs that are used to store, manipulate, and delete data from database easily. These are mostly done with the database languages. They are named according to their purposes. They are,

• Data Definition Language (DDL)

• Data Manipulation Language (DML)

• Data Control Language (DCL)

• Transaction Control Language (TCL)

These languages are acting as interface between us and database to define, create, store, retrieve, delete, and control data and data related privileges based on the type of user who access. These are combined together to form a single database language like SQL. Most RDBMSs like Oracle, MySQL, Microsoft SQL Server etc. are using SQL or SQL like special purpose languages to interact with the database.

Data Definition Language (DDL)

What is DDL? / Data Definition Language / Role of Data Definition Language / What can a DDL statement do? / Important aspects of DDL statements

Data Definition Language (DDL)

As specified in the name, DDL is used to define database schemas. With DDL statements we can do the following;

• To create databases.

For example we can create a database named University in MySQL as follows;

CREATE DATABASE University;

• To create table structures.

For example, we can create a table named Student in Oracle as follows;

CREATE TABLE student (Regno NUMBER(10), Name VARCHAR(30));

• To change the structure of the tables.

We can alter the structure of student table by adding/dropping columns, by changing the size of the accepted values etc. For example, the following statement adds a new column with Student table in Oracle;

ALTER TABLE student ADD COLUMN Phone Number(10);

• To remove tables.

We can delete the whole table structure Student using the following statement in Oracle;

DROP TABLE student;

• To rename tables.

To rename an existing table, we can use the following query in Oracle;

RENAME student TO student_table;

• To define referential integrities.

We can define referential integrities like primary key constraints, check constraints, specific type etc using DDL statements. For example, the following query creates a table Teacher with primary key constraint.

CREATE TABLE Teacher(Id NUMBER(3) PRIMARY KEY, Name VARCHAR(30));

• To analyze information.

We can analyze a table, index or clusters for handling performance related issues.

• To add comments to the Data Dictionary.

We can add comments to tables, table columns when they are stored in Data Dictionary. For example, the statement will add a comment ‘Name of the teacher’ with the column definition Name of Teacher table.

COMMENT ON COLUMN Teacher.Name IS ‘Name of the teacher’;

When we execute DDL statements, it does the following things;

1. It does the required things as specified through query. [For example, as mentioned above]


2. Update the special table called Data Dictionary. The Data Dictionary gets updated every time you execute one of the DDL statements.