Data Query Language (DQL)

Data Query Language (DQL)

This sub-language deals with retrieval of data from database and it includes SELECT command.

Syntax to Fetch Records from Table:

SELECT * []
FROM

[

]
[WHERE ]
[START WITH ]
[CONNECT BY PRIOR ]
[GROUP BY
[HAVING ] ]
[ORDER BY ];

Relational Operators:

Relational operators are pre-defined programs that are used in defining condition during data retrieval from tables. Relational Operators take input of values at left and right side and after evaluation they return Boolean value TRUE/FALSE. SQL provides two group of relational operators:

· Basic Relational Operators (=, <>, >, <, >=, <=) · Advanced Relational Operators (IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE,NOT LIKE, IS, IS NOT, ANY, ALL) Syntax for Advanced Relational Operators:











Note:

Two special characters are used to define with LIKE operator. These characters are:

% (Percent) - It represent all characters or rest of characters,
_ (Underscore) - It represent single character.

Some of the pattern examples are:

‘A%’ - Starting with alphabet A,
‘%A’ - Ending with alphabet A,
‘%A%’ - Any where search for alphabet A,
‘_A%’ - Second character must be alphabet A,
‘%A_’ - Second last character must be alphabet A,
‘_ _ _’ - Searches for only three characters

Concatenation Operator:

This operator is used to combine two values as single value. Two vertical bar () are used for concatenation operator. Syntax:


Example: SELECT ename job FROM emp;

Logical Operators:

Logical operators are used to combine two or more conditions. SQL provides three types of logical operators:

a) NOT

It reverse the meaning of condition. This operator is used before the condition.

b) AND

It joins two conditions and returns true when both conditions are true else it returns false.

c) OR

It joins two conditions and returns true if any of the conditions are true. If both conditions are false then it returns false.

Sub-query:

A query within another query is known as sub-query. A sub-query problem is needed in following cases:

a) When output columns are from one table but columns for conditions are from another table or from more than one tables.

OR

b) When output columns are from one record while columns for conditions are from another record or from more than on records.

A sub-query problem may have following type of queries:

a) Root Query:

The top most queries are root query. It can be SELECT, DELETE, UPDATE or INSERT statements.

b) Parent Query:

A query having a sub-query under it is known as Parent Query. Every Root Query is a parent query. Only SELECT statement can be a parent query.

c) Child Query:

A query under any other query is child query. Only SELECT statement can be a Child query.

A sub-query can be defined with following clauses of a parent query:

--> FROM --> WHERE --> START WITH --> HAVING --> SET


Types of Sub-query:

There are two types of sub-query:

a)Independent or Simple Sub-query:

This type of sub-query is processed once for its parent query and parent query starts the process with the output of sub-query.

b)Correlated Sub-query:

This type of sub-query is processed for each record of its parent query. Correlated Sub-query uses columns from its parent query within its condition or output.