In the previous chapters, you have learned about the various forms of relational algebra and relational calculus and their uses with the database management system. In this chapter, you will get to know about the various forms of languages that are used to deal with the database.
What are database Sub languages?
A data sublanguage mainly has two parts:
- Data Definition Language (DDL) and
- Data Manipulation Language (DML).
The Data Definition Language is used for specifying the database schema and the Data Manipulation Language is used for both reading and updating the database. These languages are called data sub-languages as they do not include constructs for all computational requirements.
Computation purposes include conditional or iterative statements that are supported by the high-level programming languages. Many DBMSs have a capability to embed the sublanguage in a high-level programming language such as ‘Fortran’, ‘C’, C++, Java, or Visual Basic. Here, the high-level language is sometimes referred to as the host language as it is acting like a host for this language. To compile the embedded file, the commands in the data sub-language are first detached from the host-language program and are substituted by function calls. The pre-processed file is then compiled and placed in an object module which gets linked with a DBMS-specific library that is having the replaced functions, and executed based on requirement. Most data sub-languages also supply non-embedded or interactive commands which can be input directly using terminal.
Data Definition Language
Data Definition Language (DDL) statements are used to classify the database structure or schema. It is a type of language that allows the DBA or user to depict and name those entities, attributes, and relationships that are required for the application along with any associated integrity and security constraints. Here are the lists of tasks that come under DDL:
- CREATE – used to create objects in the database
- ALTER – used to alters the structure of the database
- DROP – used to delete objects from the database
- TRUNCATE – used to remove all records from a table, including all spaces allocated for the records are removed
- COMMENT – used to add comments to the data dictionary
- RENAME – used to rename an object
Data Manipulation Language
A language that offers a set of operations to support the fundamental data manipulation operations on the data held in the database. Data Manipulation Language (DML) statements are used to manage data within schema objects. Here are the lists of tasks that come under DML:
- SELECT – It retrieve data from the a database
- INSERT – It inserts data into a table
- UPDATE – It updates existing data within a table
- DELETE – It deletes all records from a table, the space for the records remain
- MERGE – UPSERT operation (insert or update)
- CALL – It calls a PL/SQL or Java subprogram
- EXPLAIN PLAN – It explains access path to data
- LOCK TABLE – It controls concurrency
Data Control Language
There is another two forms of database sub-languages. The Data Control Language (DCL) 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. Privileges are of two types,
- System – creating session, table etc are all types of system privilege.
- Object – any command or query to work on tables comes under object privilege. DCL is used to define two commands. These are:
- Grant – It gives user access privileges to database.
- Revoke – It takes back permissions from user.
Transaction Control Language (TCL)
Transaction Control statements are used to run the changes made by DML statements. It allows statements to be grouped together into logical transactions.
- COMMIT – It saves the work done
- SAVEPOINT – It identifies a point in a transaction to which you can later roll back
- ROLLBACK – It restores database to original since the last COMMIT
- SET TRANSACTION – It changes the transaction options like isolation level and what rollback segment to use