Database languages

• 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
    • Authorization
      • Who can access what

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 .

• Example :

  create table department

  (dept_name char(20),

  building char(15),

  budget numeric(12,2));

  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.

• Example :

  select student_name, Student_age Classes

  from student;

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.