Database Languages in DBMS: Explanation with Syntax
Database Languages in DBMS || Explanation with Syntax: To read data, update and store information in DBMS, some languages are used. Database languages in DBMS are given as below.
- DDL – Data Definition Language
- DML – Data Manipulation Language
- DCL – Data Control Language
- TCL – Transaction Control Language
Database Languages in DBMS || Explanation with Syntax
- Data Definition Language (DDL)
DDL stands for data definition language and used to define database patterns or structures. DDL is a syntax which is same as syntax of computer programming language for defining patterns of database.
Few examples of it are:
- CREATE – used to create objects in database
- ALTER – alter the pattern of database
- DROP – helps in detecting objects
- TRUNCATE – erase all records from table
- COMMENT – adding of comments to data dictionary
- RENAME – useful in renaming an object
CREATE statement or command is used to create a new database. In structured query language the create command creates an object in a relational database management system. The commonly used create command is as follows
- CREATE TABLE [name of table] ( [ definitions of column ]) [parameters of table]
DROP statement destroys or deletes database or table. In structured query language, it also deletes an object from relational database management system. Typically used DROP statement is
- DROP type of object name of object
ALTER statement enhance the object of database. In structured query language it modifies the properties of database object. The ALTER statement is
- ALTER type of object name of object
RENAME statement is used to rename a database. It’s statement is as follows
- RENAME TABLE old name of table to new name of table
- Data manipulation language (DML)
It has statements which are used to manage the data within the pattern of objects. Some of the samples of the statements are as follows:
- SELECT – useful in holding data from a database
- INSERT – helps in inserting data in to a table
- UPDATE – used in updating the data
- DELETE – do the function of deleting the records
- MERGE – this do the UPSERT operation i.e. insert or update operation
- CALL – this calls a structured query language or a java subprogram
- EXPLAIN PLAN – has the parameter of explaining data
- LOCK TABLE – this ha the function of controlling concurrency
These syntax elements are similar to the syntax elements used in computer programming language. Performing the operation of reading of queries is also a component of data manipulation language. Other forms of data manipulation languages (DML) are used by IMS, CODASYL databases.
DML also include the structured query language (SQL) data modifying statements, they modify the saved data but not the pattern of objects. The initial word of the DML statements has functional capability.
The query statement SELECT is grouped with data statements of structured query language (SQL). In practice there is no such difference and it is viewed to be a portion of DML.
Data manipulation languages contribute to have distinct relishes between database sellers. They are divided as:
- Procedural programming
- Declarative programming
Initially data manipulation languages were only used in computer programs, but with the coming of structured query languages it is also used in the database executors.
- Data Control Language (DCL)
Data Control Language (DCL) is syntax similar to the programming language, which was used to retrieve the stored or saved data. Examples of the commands in the data control language (DCL) are:
- GRANT – this permits particular users to perform particular tasks
- REVOKE – it blocks the previously granted untrue permissions
The operations which has the authorization of REVOKE are CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and SELECT.
The execution of DCL is transactional; it also has the parameter of rolling back. But the execution of data control language in oracle database does not have the feature of rolling back.
- Transaction Control Language (TCL)
Transaction Control Language (TCL) has commands which are used to manage the transactions or the conduct of a database. They manage the changes made by data manipulation language statements and also group up the statements in o logical management.
Some examples of it are:
- COMMIT – use to save work
- SAVE POINT – helps in identifying a point in the transaction, can be rolled back to the identified point
- ROLL BACK – has the feature of restoring the database to the genuine point, since from the last COMMIT
- SET TRANSACTION – have parameter of changing settings like isolation level and roll back point
COMMIT command permanently save the transaction in to database.
- It’s syntax is: Commit;
ROLL BACK command uses the save point command to jump to save point in transaction.
- It’ s syntax is: rollback to name-save point;
SAVE POINT command is used to save a transaction temporarily.
- It’s syntax is: Save point name-save point;
These are different types of DBMS languages, which are used to read, upgrade, save information.
So it was all about Database Languages in DBMS || Explanation with Syntax. If you liked it then please share it with your friends.
Thank you notes was quite understandable
Thanks a lot Tarun 🙂
Thankyou so much