Restricting and sorting data in oracle

Explain how to limit the rows that are retrieved by a query.

The number of rows returned by a select query can be restricted by the LIMIT clause.

Example:
SELECT * from employee

LIMIT 20

Explain how to sort the rows that are retrieved by a query.

Rows that are returned by a query can be sorted using the ORDER BY clause. By default the rows are sorted in an ascending order.

Example:
Select * from employee ORDER BY id DESC

What are the comparison operators in oracle. List them with description

Comparison operators in oracle are used to compare data. Such operators are used in WHERE clause.

List of such operators:
1. = -> EQUAL TO
2. <> != -> Both means NOT EQUAL TO
3. < LESS THAN
4. > GREATER THAN
5. <= >= -> LESS THAN OR EQUAL TO, GREATER THAN OR EQUAL TO
6. ANY- compares one value with any value

Example:
Select * from employee
Where id >=1

PL/SQL has three logical operators: AND, OR, and NOT. Explain then by illustrating an example for each

AND- It is used for conjunction. This means that for a row to be selected all the specified conditions must be true.

Example:
Select * from employee Where id= 1 AND 2

NOT- Is logical negation. This means that for a row to be selected the specified condition must be false.

Example:
Select * from employee Where NOT id=1

OR- it is used for inclusion. This means for the row to be selected at least one of the conditions must be true.

Example:
Select * from employee Where id =1 OR 2
Working with multiple tables joins
Multiple tables joins - What are joins? Explain its characteristic features, Cross joins, Inner joins, Outer joins, Equi- and non-equi-joins, Self joins...
Working with multiple tables
Working with multiple tables - Define subquery. Explain with an example, What is correlated query? Explain its uses, What are Set Operators? Explain union and union all with an example for each...
Post your comment

You may like