A DBMS must provide appropriate languages and interfaces for each category of users to express database queries and updates.
Database Languages :
- communicates with the database
- used to create and maintain database on computer.
Mainly two types of Database Languages :
- Data Definition Language (DDL) : to specify database schema
- Data Manipulation Language (DML) : to express database queries and updates.
In practical, DDL and DML are not separate languages, instead they are the parts of a single database language such as widely used SQI (Structured Query Language)
Data Definition Language (DDL)
DDL (stands for Data Definition Language) is used for specifying the database schema.
- Used by the DBA and database designers to specify the conceptual schema of a database
- DDL is used for creating tables, schema, indexes, constraints etc. in database.
Eg : create table account (
account-number char(10), balance (integer)
- DDL is used to store the information of metadata like the number of tables and schemas, their names, indexes, columns in each table, constraints, etc.
- DDL compiler generates a set of table templates stored in a data dictionary.
- Data dictionary contains Metadata (i.e. data about data)
- Database Schema
- Table name
- Column names & types
- Integrity Constraints
- Primary Key
- Who can access what
- Database Schema
DDL Commands in SQL :
- CREATE : used to create the database instance .
- ALTER : used to alter the structure of the database ·
- DROP : used to delete the database instance .
- TRUNCATE : Used to remove all records from a table
- RENAME : used to rename database instances .
create table department
Note : These commands either defines or update the database schema that's why they come under Data Definition language.
Data Manipulation Language (DML)
DML (stands for Data Manipulation Language) is used for accessing and manipulating data in a database.
It allows users to insert, update, delete and retrieve data from the database.
DML is also known as Query Language
- A query is a statement requesting the retrieval of information
Two classes of DML languages :
- Procedural DMLS/High-level DMLS :
- User specifies what data is required and how to get those data
- Procedural DML is embedded into a high-level programming language like java, etc.
- Eg : PL/SQL
- Declarative (Non-Procedural)DMLs/Low-level DMLS :
- User specifies what data is required only; without specifying how to get those data
- Declarative DMLS are usually easier to learn and use than procedural DMLs. However, since a user does not have to specify how to get the data, the database system has to figure out an efficient means of accessing data.
- Eg : SQL
DML Commands in SQL :
- SELECT : used to retrieve data from a database.
- INSERT : used to insert data into a table.
- UPDATE : used to update existing data within a table.
- DELETE : used to delete all records from a table.
select student_name, Student_age Classes
Other Database Language
- Data Control Language (DCL)
- Transaction Control Language (TCL)
Data Control language (DCL)
Data Control Language (DCL) is used to control privilege in Databases i.e. DCL is used for granting and revoking user access on a database (Authorization)
To perform any operation in the database, such as for creating tables, sequences, or views, we need privileges.
In particular, it is a component of Structured Query Language (SQL)
DCL Commands :
- GRANT - To give user access privileges to a database
- REVOKE - To take back permissions from the user
- The operations for which privileges may be granted to or revoked from a user or role apply to both the Data definition language (DDL) and the Data manipulation language (DML),
Transaction Control Language (TCL)
TCL (Transaction Control Language) commands are used to manage transactions in the database.
TCL is used to run the changes made by the DML statement.
The changes in the database that we made using DML commands are either performed or rollbacked using TCL.
TCL Commands :
COMMIT : to save the transaction on the database
- ROLLBACK : to restore the database to original since the last Commit.
- SAVEPOINT : Savepoint command is used to temporarily save a transaction so that you can rollback to that point whenever necessary.