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

Data Manipulation Language (DML)

What is DML? / List of operations that can be performed by Data Manipulation Language / DML examples / Types of DML / Procedural and Declarative DML

Data Manipulation Language (DML)

Data Manipulation is about handling the data stored in a database differently. The following operations would be considered as data manipulation in a database;

• Retrieving the data stored in the database – the way to view the data stored in the database

• Insertion of new data into the database – the way to insert and store the data in the database

• Deletion of stored data – the way to delete old or unwanted data from the database

• Modification of the stored data – the way to modify when required or when false data stored

The above said operations can be executed in Oracle using the following SQL statements respectively, for example;

Assume a table Teacher with attributes TID, Name, and Phone number. 

Retrieval – SELECT * FROM Teacher;

Insertion – INSERT INTO Teacher VALUES (100, ‘Ramesh’, 9900887771);

Deletion – DELETE FROM Teacher WHERE TID = 100;

Modification – UPDATE Teacher SET phone = 9900887766;

There are basically two types of DML languages;

1. Procedural DMLs – in procedural DMLs the user has to specify

• how to get the required data along with

• what data are required.

2. Declarative DMLs (Non-procedural) – it requires the user to specify

• What data are required only. (Note:- The DML component of Structured Query Language (SQL) is declarative, ie., non-procedural)

 

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

Related links:

• CREATE TABLE statement

• Database query languages

• SELECT statement explained

Database Languages (DDL, DML, DCL, and TCL)

Database Languages / What is database language? / List of database languages / DDL, DML, DCL, and TCL

Database Languages

Database languages are the easiest way to interact with the database. As we know already, a DBMS is a set of programs that are used to store, manipulate, and delete data from database easily. These are mostly done with the database languages. They are named according to their purposes. They are,

• Data Definition Language (DDL)

• Data Manipulation Language (DML)

• Data Control Language (DCL)

• Transaction Control Language (TCL)

These languages are acting as interface between us and database to define, create, store, retrieve, delete, and control data and data related privileges based on the type of user who access. These are combined together to form a single database language like SQL. Most RDBMSs like Oracle, MySQL, Microsoft SQL Server etc. are using SQL or SQL like special purpose languages to interact with the database.

Data Definition Language (DDL)

What is DDL? / Data Definition Language / Role of Data Definition Language / What can a DDL statement do? / Important aspects of DDL statements

Data Definition Language (DDL)

As specified in the name, DDL is used to define database schemas. With DDL statements we can do the following;

• To create databases.

For example we can create a database named University in MySQL as follows;

CREATE DATABASE University;

• To create table structures.

For example, we can create a table named Student in Oracle as follows;

CREATE TABLE student (Regno NUMBER(10), Name VARCHAR(30));

• To change the structure of the tables.

We can alter the structure of student table by adding/dropping columns, by changing the size of the accepted values etc. For example, the following statement adds a new column with Student table in Oracle;

ALTER TABLE student ADD COLUMN Phone Number(10);

• To remove tables.

We can delete the whole table structure Student using the following statement in Oracle;

DROP TABLE student;

• To rename tables.

To rename an existing table, we can use the following query in Oracle;

RENAME student TO student_table;

• To define referential integrities.

We can define referential integrities like primary key constraints, check constraints, specific type etc using DDL statements. For example, the following query creates a table Teacher with primary key constraint.

CREATE TABLE Teacher(Id NUMBER(3) PRIMARY KEY, Name VARCHAR(30));

• To analyze information.

We can analyze a table, index or clusters for handling performance related issues.

• To add comments to the Data Dictionary.

We can add comments to tables, table columns when they are stored in Data Dictionary. For example, the statement will add a comment ‘Name of the teacher’ with the column definition Name of Teacher table.

COMMENT ON COLUMN Teacher.Name IS ‘Name of the teacher’;

When we execute DDL statements, it does the following things;

1. It does the required things as specified through query. [For example, as mentioned above]


2. Update the special table called Data Dictionary. The Data Dictionary gets updated every time you execute one of the DDL statements.

Decomposition into BCNF - Examples

Normalization Examples / Decomposition into BCNF tables / How to decompose a table into BCNF compliant table? / BCNF decomposition examples

Here, I have listed some examples and links for decomposing a relation into a perfect BCNF relation.

1. Simple and neatly explained example of BCNF decomposition - Click here.

2. Method for normalizing a relation to BCNF based on functional dependencies. Steps are explained. Click here.