Operators – ORACLE PL/SQL Chapter Wise Interview Questions

Operators – ORACLE PL/SQL Chapter Wise Interview Questions

Question 1:
How to use %LIKE operator?
Answer:
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:

ename LIKE ']%SON'

Question 2:
What are the Logical Operators? What’s the operator precedence?
Answer:
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.
Answer:
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:

DELETE FROM emp WHERE ename IN (NULL, 'KING', 'FORD'); 
Furthermore, expressions of the form 
value NOT IN set

Question 4:
Explain BETWEEN… AND
Answer:

SELECT column_name FROM table_name WHERE column_name
BETWEEN value1 AND value2

The values can be numbers, text, or dates.

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

<>, like'%.....' is NOT functions, field +constant, field ||"

Question 6:
What is the significance of the & and && operators in PL SQL?
Answer:
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?
Answer:
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:

IF variable IS NULL THEN...

Question 8:
Explain BETWEEN Operator with example.
Answer:
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:

45 BETWEEN 38 AND 44

Question 9:
Explain Concatenation Operator with example.
Answer:
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:
‘suitcase’
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?
Answer:
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.