Operators – ORACLE PL/SQL Chapter Wise Interview Questions

Operators – ORACLE PL/SQL Chapter Wise Interview Questions

Question 1:
How to use %LIKE operator?
LIKE operator is used to compare a character, string, or CLOB value to a pattern. Case is significant. LIKE returns the Boolean value TRUE if the patterns match or FALSE if they do not match. The patterns matched by LIKE can include two special-purpose characters called wildcards. An underscore (_) matches exactly one character; a percent sign (%) matches zero or more characters. For example, if the value of ename is ‘JOHNSON’, the following expression is true:

Question 2:
What are the Logical Operators? What’s the operator precedence?
There are three logical operators that may be used in a WHERE clause which are AND, OR, NOT. The logical operators allow you to limit rows based on logical conditions. The logical operators are used in the following way:
a AND b: Returns true when both a and b are true
a OR b: Returns true when either a or b is true
NOT a: Returns true if a is false and returns false if a is true.
If we combine AND & OR in the same expression, the AND operator takes precedence over the OR operator. The comparison operators take precedence over AND. We can override this using parenthesis.

Question 3:
Explain IN Operator with example.
The IN operator tests set membership. It means “equal to any member of.” The set can contain nulls, but they are ignored. For example, the following statement does not delete rows in which the ename column is null:

Question 4:

The values can be numbers, text, or dates.

Question 5:
For which relational operators in where clause, index is not used?

Question 6:
What is the significance of the & and && operators in PL SQL?
The & operator means that the PL SQL block requires user input for a variable.
The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable

Question 7:
How to use IS NULL Operator?
The IS NULL operator returns the Boolean value TRUE if its operand is null or FALSE if it is not null. Comparisons involving nulls always yield NULL. So, test for nullity (the state of being null), as follows:

Question 8:
Explain BETWEEN Operator with example.
The BETWEEN operator tests whether a value lies in a specified range. It means “greater than or equal to low value and less than or equal to high value.” For example, the following expression is false:

Question 9:
Explain Concatenation Operator with example.
Double vertical bars (||) serve as the concatenation operator, which appends one string (CHAR, VARCHAR2, CLOB, or the equivalent Unicode-enabled type) to another. For example, the expression
‘suit’ || ‘case’
returns the following value:
If both operands have datatype CHAR, the concatenation operator returns a CH AR value. If either operand is a CLOB value, the operator returns a temporary CLOB. Otherwise, it returns a VARCHAR2 value.

Question 10:
What are the Comparison Operators, list them and describe?
Comparison Operators: are used in the WHERE clause of a data manipulation statement to form which compare one expression to another and always yield TRUE, FALSE, or NULL. Comparison operators listed below to form predicates. It can be combined using the logical operators AND, OR, and NOT.

Operator Description
ALL Compares a value to each value in a list or returned by a subquery and yields TRUE if all of the individual comparisons yield TRUE.
Compares a value to each value in a list or returned by a subquery and yields TRUE if any of the individual comparisons yields TRUE.
BETWEEN Tests whether a value lies in a specified range.
EXISTS Returns TRUE if a subquery returns at least one row.
IN Tests for set membership.
IS NULL Tests for nulls.
LIKE Tests whether a character string matches a specified pattern, which can include wildcards.

About the author


Leave a Comment