Logical Operators - Pattern Matching

The use of the LIKE predicate:

The comparison operators discussed so far have compared one value, exactly to one other value. Such a precision may not always be desired or necessary. For this purpose Oracle provides a predicate LIKE.

The LIKE depreciate allows for a comparison of one string value with another string value, which is not identical. This is achieved by using wildcard characters. Two wildcard characters that are available are:

For character data types:

1. The Percent sign (%) matches any string.

2. The Underscore (_) matches any single character.

Example:

1.
Retrieve all information about suppliers whose names begin with the letters 'ja' from supplier_master.

SELECT * FROM supplier_master WHERE supplier_name LIKE 'ja %':

2.
Retrieve all information about suppliers where the second character of names are either 'r' or 'h'.

SELECT * FROM supplier_master WHERE supplier_name LIKE '_R%' OR supplier_name LIKE '_h%';

The IN and NOT IN predicates:

The arithmetic operator (=) compares a single value to another single value. In case a value needs to be compared to a list of values the the IN predicate is used. One can check a single value against multiple values by using the IN predicate.

Example:

Retrieve the supplier_name,address1,address2,city and pincode from the table supplier_master where the supplier_name is either Murali or Mahesh or yatish or leela.

SELECT supplier_name,address1,address2,city,pincode FROM supplier_master WHERE supplier_name IN('murali','mahes','yatish','leela');

The NOT IN predicate is the opposite of the IN predicate. This will select all the rows where values do not match all of the values in the list.

Logical Operators - Pattern Matching

Range Searching

In order to select data that is within a range of values, the BETWEEN operator is used. The BETWEEN operator allows the selection of rows that contain values within a specified lower and upper limit. The range coded after the word BETWEEN is inclusive.

The lower value must be coded first. The two values in between the range must be linked with the keyword AND. A BETWEEN operator can be used with both character and numeric data types. However, one cannot mix the data types that is the lower value of a range of values from a character column and the other from a numeric column.

Example:

1. Retrieve product_no,description,profit_percent,sell_price from the table product_master where the values contained within the field profit_percent is Between 10 and 20 both inclusive.

SELECT product_no,description,profit_percent,sell_price FROM Product_Master WHERE profit_percent BETWEEN 10 AND 20;

The above select will retrieve all the records from the product_master table where the profit_percent is in between 10 and 20 (both values inclusive).

2. Retrieve product_no,description,profit_percent, and sell_price from the produt_master table where the values contained in the field profit_percent are not between 10 and 15 both inclusive

SELECT product_no,description,profit_percent,sell_price FROM product_master WHERE profit_percent NOT BETWEEN 10 AND 15;

The above select will retrieve all the records from the product_master table expect where the profit_percent is in between 10 and 15 (both values inclusive).

Range Searching

Logical Operators - NOT Operator

The Oracle engine will process all rows in a table and display the result only when row of the conditions specified using the NOT operator are satisfied.

Example: Retrieve specified client information for the clients who are NOT in "Hyderabad" OR "Bangalore".

SELECT client_no,name,address1,address2,city,pincode FROM client_master WHERE NOT ( city='Hyderabad' or city='Bangalore');

The oracle engine will not display the rows from the client_master table where the value of the field city is either Hyderabad or Bangalore.

Logical Operators - NOT Operator

Logical Operators - OR Operator

The Oracle engine will process all rows in a table and display the result only when any of the conditions specified using the OR operator are satisfied.

Ex: Retrieve client information like client_no,address1,address2,city and pincode for all the clients where the field pincode has the value 400065 or 500089;

SELECT client_no,name,address1,address2,city,pincode From client_master WHERE (pincode=400065 OR picode=500089);

The contents of the fields client_no,name,address1,address2,city,pincode satisfying the condition of pincode being 400065 OR 500089 in the client_master table will be displayed on the screen.

Logical Operators - OR Operator

Logical Operators - AND Operator

Logical Operators that can be used in SQL sentence are:

AND Operator: Oracle engine will process all rows in a table and display the result only when all of the conditions specified using and AND operator are satisfied.

Example: Retrieve the contents of the columns product_no,description,profit_percent,sell_price from the table product_master where the values contained in the field profit_percent is between 10 and 20 both inclusive.

SELECT product_no,description,profit_percent,sell_price From product_master Where profit_percent >=10 AND profit_percent <= 20;

Logical Operators - AND Operator

ASCII EDITOR Running the SQL file using SQL Prompt

Running the .sql file using Get at the SQL Prompt:

1. To run this .sql file, after saving the file and exiting from notepad, at the sql >Prompt, type the command get

Ex: SQL > get

2. To execute the .sql file at the SQL > prompt type, /

Ex: SQL > /

3. The contents of the .sql file can be edited and the file run as required.

Running the .sql file using start at the SQL Prompt:

The .sql file can be compiled and executed using start or run at the SQL > Prompt.

Ex: SQL > start ;

Saving into an ASCII file from the SQL Prompt

The steps involved in saving an SQL statement into an ASCII file from the SQL prompt are:

1. At the SQL > Prompt, You can execute any valid SQL statement.

Ex: Select * from client_master;

2. If you want to save the SQL statement in a file, at the SQL > Prompt type-in save . This command automatically creates a file by the in the current working directory.

SQL > save ;

ASCII EDITOR Running the SQL file using SQL Prompt

ASCII EDITOR

Working With an ASCII EDITOR: SQL is a single sentence language. The sentence once typed at the SQL > Prompt cannot be retrieved and corrected if a spelling or syntax error was discovered after it was dispatched to the Oracle engine for processing.

The technique used to overcome this constant retyping of sentences is to create an ASCII file. Place the SQL sentence as the contents of the ASCII file. When the sentence has to be executed, appropriate commands in SQL * Plus opens the ASCII file and passes its contents to the Oracle Server for execution.

Any ASCII editor can be used. Once convenient editor to use will be Windows Notepad Editor, which is a part of the MS Windows Operating System. Hence, Notepad can be invoked while the SQL prompt is on the screen as the editor of choice.

1. To invoke Notepad, Window's full page ASCII editor, from SQL * plus type Ed followed by the file name at the SQL prompt. If the file does not exist then the system comes up with a message asking the user to create a new file.

2. At this point an ASCII file of the filename specified is opened in memory. Start to type in SQL sentences in the ASCII file. Each SQL sentence is to be terminated with a / (backslash) in the first column of the next line.

3. Save the file using the Notepad Menu choices File,Save.

4. This file can be invoked and used repeatedly at the SQL > Prompt.

5. To exit from the tool click on File,Exit.

ASCII EDITOR