Database Languages in DBMS

Database Language: 

A database system provides a data definition language to specify the database schema and a data manipulation language to express database queries and updates. In practice, the data definition and data manipulation languages are not two separate languages, instead they simply form parts of a single database language, such as the widely used SQL language. Database Languages are used to create and maintain database on computer. The statements (SQL) used in database languages are categorized as data definition language(DDL), Data Control language (DCL), and Data Manipulation Language(DML).

DDL :   DDLs are used to define the metadata of the database. Data Definition Language(DDL) is a standard for commands that define the different structures in a database. DDL statements create, modify and remove database objects such as tables, indexes, and users. Common DDL statements are CREATE, ALTER and DROP. It is also used to specify the structure of each table, set of associated values with each attribute, integrity constraints, security and authorization information for each table and physical storage structure of each table on disk. Some DLL statements and its usage:

DDL Statement Usage
CREATECreate schema objects
ALTERAlter schema objects
DROPDelete schema objects
RENAMERename schema objects
CommentAdd a comment to the data dictonary
TRUNCATERemove all records from a table

DML: A Data Manipulation Language is a language that enables users to access or manipulate data as organized by the appropriate data model. It is a language that provides a set of operations to support the basic manipulation operations on the data held in the databases. It allows users to insert, update, delete and retrieve data from the database. The portion of a DML that involves information retrieval is called a query language. A query is a statement requesting the retrieval of information. Some DML statements and its usage :

DML Statement Usage
DELETERemove rows from tables or views
INSERTAdd new rows of data into table or views
SELECTRetrive data from one or more tables
UPDATEChange column values in existing rows of a table or view
LOCK TABLEConcurrency Control
CALLCall a PL/SQL or Java subprogram
MERGEUPSERT operation (insert or update)
EXPLAIN PLANinterpretation of the data access path

DCL : A Data Control Language is used to control privilege in Database. To perform any operation in the database, such as for creating tables, sequences or views we need privileges. A privilege can either be granted to a User with the help of GRANT statement. The privileges assigned can be SELECT, ALTER, DELETE, EXECUTE, INSERT, INDEX etc. In addition to granting of privileges, you can also revoke (taken back) it by using REVOKE command. Some DCL statements and its usages :

DCL Statement Usage
GRANTGives user access privileges to database
REVOKETake back permissions from user

Next Topic :

No comments:

Post a Comment