Oracle section 2nd filters and sorts data, oracle section 2nd

Oracle section 2nd filters and sorts data, oracle section 2nd

LessonAim

While retrieving data from the database, you may need to restrict the rows of data that are displayed or specify the order in which the rows are displayed.

This lesson explains the SQL statements that youuse to perform these actions.

• Filter rows in the query.
• Sort rows in the query.

SELECT * | {[DISTINCT]Column | expression[Alias],...}

FROMTable

[WHERECondition (s)];

• The WHERE clause follows the FROM clause.

SELECTEmployee_id,Last_name,Job_id,Department_id

FROM employees

WHEREDepartment_id= 90;

• Characters and dates must be included in single quotes. • The characters are case sensitive and the date format is sensitive.

SELECTLast_name,Job_id,Department_id

FROM employees

WHERELast_name= 'Whalen ';

SELECTLast_name,Hire_date,Department_id

FROM employees

WHEREHire_date= '7-6Month-1994'

The date is written in a specific format!

SELECTLast_name, Salary

FROM employees

WHERE salary <= 3000;

1.

SELECTLast_name, Salary

FROM employees

WHERE salary BETWEEN 2500 AND 3500;

2.

SELECTEmployee_id,Last_name, Salary,Manager_id

FROM employees

WHEREManager_idIN (100,101,201 );

• Use the LIKE operation to select a similar value • selection conditions can contain characters or numbers:-% Represents zero or multiple characters (any character ). _ Represents a character

3.

SELECTFirst_name

FROM employees

WHEREFirst_nameLIKE's % ';

4.

SELECTLast_name

FROM employees

WHERELast_nameLIKE '_ o % ';

• You can use the ESCAPE identifier to select the '%' and '_' symbols.

• Escape special characters: Use escape characters. For example, convert [%] to [\ %], [_] to [\ _], and then add [ESCAPE.

SELECTJob_id

FROMJobS

WHEREJob_idLIKE'IT \_%'Escape '\';

5.

Use IS (NOT) NULL to determine a NULL value.

SELECTLast_name,Manager_id

FROM employees

WHEREManager_idIs null;

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.