Database Languages: DDL and DML

A DBMS provides one or more specialized programming languages known as Database Languages in order to give the various services to various types of users. Although there are other versions of database language, Structured Query Language (SQL) has recently been the de facto standard. These forms are divided into groups based on the various data abstraction levels supported by a DBMS.

Data-Definition Language (DDL)

Data are described using a data definition language (DDL). A collection of definitions provided in this specific language define the conceptual schema.
The DBMS will have a compiler whose job it is to analyze DDL instructions to identify descriptions of the schema constructs and store those descriptions as a collection of tables in a unique file called the Data Dictionary. Data dictionary is a file that contains metadata, which is information about the data (information about the data in the database). Before actual data are read or edited in the database system, the data dictionary is examined. Definitions of data elements and data attributes, including use, physical representation, ownership (i.e., who in the organization is in charge of preserving the data), authorization, and security, are kept in data dictionaries.
The student table, for instance, is defined with the SQL statement below:

Create Table STUDENT
(stdno number,
sname varchar2(25),
age number);

The STUDENT table is created when the above DDL statement is executed. Furthermore, it refreshes a set of tables known as the data dictionary or data directory.
The internal schema is specified using another language known as the storage definition language (SDL). In some DBMS, there is only one language that supports both DDL and SDL. The database system's storage structure and access methods are defined by a collection of definitions in a specific sort of DDL known as a storage definition language (SDL) or data storage and definition language. The consequence of compiling these specifications is a set of instructions that specify the database's implementation specifics, which are often hidden from users.

Data-Manipulation Language (DML)

The data manipulation language (DML) is the language that allows users to access or manipulate data . In DBMS data manipulation means the following actions:

• Retrieval of information from a database.
• Insertion of new information in the database.
• Deletion of information within the database.
• Modification of information within the database.

DML is classified into two types:

• Procedural DML, which requires users to indicate what data is required and how to obtain it.
• Nonprocedural DML, which requires the user to describe what data is required but not how to obtain it.

A query is a statement that asks for information to be retrieved. A query language is the part of a DML that deals with information retrieval. SQL is a popular non-procedural query language.

Example 1:
To find the name of the student with marks greater than 40.
SELECT ename FROM student
WHERE marks>40;

Example 2:
To find the name and address of all students who study BBA and live in Kathmandu.
SELECT ename, address
FROM student, bba
WHERE student.bbano=bba.bbano
AND bba.address='Kathmandu';

Databases are typically accessed by application programs through:

• One of the Language extensions that allows for integrated SQL.
• Application program interface or API such as ODBC/JDBC, that enable sending SQLqueries to databases.