Programming languages which are used to create and operate database, known as database language like Structured Query language aka SQL etc.
Most of database languages are non-procedural, means the language focus on “what to do instead of how to do?”
We can further divide database languages in these 4 categories as per the type of users:
- Data definition language aka DDL
- Data manipulation language aka DML
- Data control language aka DCL
- Data query language aka DQL
DDL
It is used to specify or create database schema like new database or tables creation along with their properties. It is commonly used by Database administrator.
Sample codes are as per given below:
CREATE: To create new database or tables
ALTER: To change the structure of table
DROP: To delete objects from table
TRUNCATE: To remove all records from a table
COMMENT: To add comments to the data dictionary
It also updates data dictionary.
Data dictionary is kind of metadata about the database. In simple words data dictionary is like a map of the database, which contain all information about the database.
Example:
create table employee (id number (10), name char (20), address varchar (30));
After passing this code a table will be formed named “employee”.
| Table employee | ||
| Id | Name | address |
DML
It is used by end users to access or perform operations on a database or table. It helps users to manipulate the database or a table.
DML is further classify in two sections:
- Procedural DML: In this section user needs to define “what data is needed and how to get that?” like PL/SQL.
- Non-Procedural DML: In this section user needs to define just “what is needed?”. Its not required to define any procedure to get that data. Like SQL.
Sample codes are as per given below:
INSERT: To insert data in table
SELECT: To select data from a table or complete table
UPDATE: To update existing data in table
MODIFY: To modify or change any data in table
DELETE: To delete all records from a table without remove spaces allotted.
REMOVE: To remove selected data from a table
LOCK TABLE: To control concurrency
Example:
insert into table employee (id, name, address) values (‘01’,’Ajay’,’C5, Anand Vihar, Pune’);
After applying this command, the table employee will be look like this:
| Table employee | ||
| Id | Name | Address |
| 01 | Ajay | C5, Anand Vihar, Pune |
DCL
This part is like components of SQL that are used to control access to data and database along with database users. These are function which help to manage any type of approach to database to maintain security and integrity.
Sample codes are as per given below:
COMMIT: To save the work done till current.
ROLLBACK: To revert the database till the last COMMIT point.
SAVE POINT: To make a save point so that we can revert to only that point in case of any changes needs to be done. By default, COMMIT point is a SAVE POINT, but SAVE POINT is not a COMMIT point.
GRANT/REVOKE: To give or take back permission from users of database.
SET TRANSACTION: To give or take back permission from users of database, and to set limit of permissions.
DQL
This is a format language which is used to get or retrieve any data from the database. This format creates different queries. Also, it helps database to understand the reaming part of the query except commands. All queries in database belongs to DQL.