Database Languages and Query Languages

Database Management System (DBMS) Languages (DDL, DML, DCL, TCL), Query Language Standards (SQL, OQL, SPARQL)

Database Management System Languages

• DBMS Languages/Tools - An overview

• Data Definition Language (DDL)

• Data Manipulation Language (DML)

• Data Control Language (DCL)

• Transaction Control Language (TCL)

• Database Query Language Standards

*****************************

Go to Advance Database Concepts page

Transaction Control Language (TCL)

What is TCL? / List of operations that can be performed by Transaction Control Language / TCL examples / Different commands of TCL / What is Transaction Control Language? / Define TCL

Transaction Control Language (TCL)

The Transaction Control Language (TCL) is yet another component of SQL which is used to control/manage transactions in a database. The data stored in a database can be manipulated differently as and when required. The manipulation could be smaller or larger, i.e, may involve one or more SQL statements. Hence, we need a mechanism to differentiate one manipulation from other. A transaction is a unit which is used to mention the boundary of every manipulation.

A transaction is logical unit of work that comprises one or more SQL statements, usually a group of Data Manipulation Language (DML) statements.

The following list shows the major operations that are performed as part of every transaction;

• Insertion of a record in a table/multiple tables

• Modification of the values in a table

• Deletion of records in a table/multiple tables

The each of the following examples is considered as individual transactions;

• When you deposit some money in your account, your deposit will modify the data in your record. This is treated as single transaction.

• If you would like to withdraw some money from your account from the bank, your withdrawal performs one modification in your record. In some cases, the operation that you performed might by logged in another table for maintaining a history of transaction. This involves an insertion. Here, both modification and insertion put together considered as single transaction.   

• If you would like to transfer some money from your account to your friend’s then your transfer involves modification in your record and your friend’s record. Here, modification of values in both the records treated as single transaction.

Hence, transactions are units or sequences of work performed in a logical order.

The TCL commands include the following;

• Commit – to permanently save the changes that are executed as part of a transaction in a database.

The syntax for using commit;

COMMIT;

• Rollback – to undo the changes that are made on a database through a transaction.

The syntax for using rollback;

ROLLBACK;

• Savepoint - A SAVEPOINT is a point in a transaction that you can use to roll the transaction back to a certain point without rolling back the entire transaction.

The syntax for SAVEPOINT is;

SAVEPOINT Savepoint_Name;

If you have declared a savepoint as part of your transaction, then you can use rollback command to rollback the transaction upto that point. For example,

ROLLBACK TO svpoint1;

This command will rollback the transaction by undoing changes upto the savepoint svpoint.

Data Control Language (DCL)

What is DCL? / List of operations that can be performed by Data Control Language / DCL examples / Different DCL commands

Data Control Language (DCL)

Data Control Language is used for controlling access privileges that are assigned for database users. It is one of the components of SQL like the DDL and DML. It does the following;

Granting privileges – through DCL we can permit individual users with individual permissions to access database and data differently. For example, we can restrict a user only to insert data into a table and to view data of the table. Some users may be permitted to create tables, some may be allowed to delete tables and so on.

This can be done with the GRANT command. For example, the following statement will allow a user to only insert the data into a table.

• GRANT INSERT ON Teacher TO Kumar;

Here, Teacher is the table name and ‘Kumar’ is the name of the user. This statement permits the user Kumar to insert data into the table Teacher.

Other examples:

• GRANT INSERT, SELECT ON Teacher TO Kumar;

• GRANT INSERT ON Teacher TO Kumar, User2;

Revoking privileges – we can revoke the privileges given to an user using revoking privileges statement.

For example, the following query will revoke the permission given to the user kumar in the previous statement.

• REVOKE INSERT ON Teacher FROM Kumar;

Other examples:

• REVOKE INSERT ON Teacher FROM Kumar, User2;

Database Query Languages

Database Query Languages / What is database query language? / List of popular query languages / Query languages for Database systems or Information Retrieval systems

Database Query Languages

Database query languages are languages or interfaces that are used to write queries into databases or information systems. These can be broadly categorized into two major categories Database Query Languages or Information Retrieval Query Languages.

Ideally, a query language allows users to formulate their queries in a simple and intuitive way, without having any special proficiency in the technicalities of the database besides knowledge of the (relevant part of the) database schema.

In this post, I have listed some of the widely used database query languages;

• SQL – Most widely used query language for relational databases is Structured Query Language (SQL). SQL was one of the first commercial languages for Edgar F. Codd's relational model, as described in his influential 1970 paper, "A Relational Model of Data for Large Shared Data Banks."[10] Despite not entirely adhering to the relational model as described by Codd, it became the most widely used database language.

 To read more - Click here

• OQL – Object Query Language (OQL) is a type of query language standard created after SQL for handling Object-oriented Databases. Originally developed by Object Data Management Group (ODMG), OQL supports the model named ODMG. It deals with complex objects without privileging the set construct and the select-from-where clause.

To read more - OQL book , OQL for O2 database

• XQUERY – It is a type of query language used for accessing XML data sources. XQuery is designed to query XML data - not just XML files, but anything that can appear as XML, including databases. It is designed by W3C (World Wide Web Consortium), standards organization for the World Wide Web.

To read more - Click here

• Datalog – It is the query language for handling Deductive Databases. Datalog is a declarative logic language in which each formula is a function-free Horn clause, and every variable in the head of a clause must appear in the body of the clause.

To read more - Datalog tutorial, Datalog user manual

• CQL – Contextual Query Language (CQL) is a formal language for representing queries to information retrieval systems such as web indexes or bibliographic catalogues and museum collection information. The design objective is that queries be human readable and writable, and that the language be intuitive while maintaining the expressiveness of more complex languages. CQL tries to combine simplicity and intuitiveness of expression for simple, every day queries, with the richness of more expressive languages to accommodate complex concepts when necessary.

To read more - Click here

• SPARQL – It is an RDF query language, that is, a semantic query language for databases, able to retrieve and manipulate data stored in Resource Description Framework format. SPARQL is developed by W3C.

To read more - SPARQL home page

************************

Related links:

• CREATE TABLE statement

• Data Manipulation Languge (DML)

• SELECT statement explained