Oracle PLSQL Online Test Series 1, Oracle PLSQL Online Exam

The SQL operators allow you to limit rows based on,

1. pattern matching of strings,
2. lists of values,
3. ranges of values, and
4. null values.

Yes, we can use NOT operator to reverse the meaning of LIKE, IN, BETWEEN, and IS NULL:

NOT LIKE
NOT IN
NOT BETWEEN
IS NOT NULL
IS NOT NAN
IS NOT INFINITE

Union returns all distinct rows that are in both queries.
Union all returns all rows that are in both queries
intersect returns rows that exist in both queries only
if one is pedantic , technically this occurs in all three operators to some degree

Sequence is a feature supported by some database systems to produce unique values on demand.

Syntax:
CREATE Sequence sequence-name
start with initial-value
increment by increment-value
maxvalue maximum-value
cycle|nocycle

where,

initial-value specifies the starting value of the Sequence.
increment-value is the value by which sequence will be incremented.
maxvalue specifies the maximum value until which sequence will increment itself.
Cycle specifies that if the maximum value exceeds the set limit, sequence will restart its cycle from the begining.
No cycle specifies that if sequence exceeds maxvalue an error will be thrown.

NOCACHE specifies no integers are to be stored. Specify NOCACHE to indicate that the values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, then the database caches 20 sequence numbers by default.

The WITH CHECK OPTION clause is an optional part of the CREATE VIEW statement. The WITH CHECK OPTION clause prevents you from updating or inserting rows that are not visible through the view. In other words, whenever you update or insert a row of the base table through a view, MySQL ensures that the insert or update operation conforms to the definition of the view.

Syntax:
CREATE OR REPLACE VIEW view_name
AS
select_statement
WITH CHECK OPTION;

Note that you put the semicolon (;) at the end of the WITH CHECK OPTION clause, not at the end of the SELECT statement defined the view.

WITH READ ONLY implies that there can be no operations can be performed on this view. Now an updatable view is one you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you can create an INSTEAD OF trigger on any view to make it updatable. You write WITH READ ONLY to indicate that the table or view cannot be updated.

The above statement is true. Oracle database automatically creates an index for the primary key of a table and for columns included in a unique constraint. When a unique or primary key constraint is enabled, Oracle Database creates an index automatically, but Oracle recommends that you create these indexes explicitly. If you want to use an index with a foreign key constraint, then you must create the index explicitly.

The above statement is false. Ltrim removes a set of characters from the left of a string.

SELECT VARIANCE(salary) FROM employee; – This is a correct way to calculate variance of a column. VARIANCE returns the variance of expression. You can use it as an aggregate or analytic function. This function takes as an argument any numeric datatype or any non-numeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.

FLOOR(x) gets the largest integer less than or equal to x.

UNISTR(x) converts the characters in x to the national language character set (NCHAR). UNISTR takes as its argument a text literal or an expression that resolves to character data and returns it in the national character set. The national character set of the database can be either AL16UTF16 or UTF8. UNISTR provides support for Unicode string literals by letting you specify the Unicode encoding value of characters in the string. This is useful, for example, for inserting data into NCHAR columns.

The above statement is true. SQLCODE returns an error code based upon the current error. The SQLCODE option holds the value returned by the Oracle RDBMS after the most recently attempted SQL operation.

Following are the metacharacters with their standard meanings:

\ : Quote the next metacharacter.
^ : Match the beginning of the line.
. : Match any character (except newline).
$ : Match the end of the line (or before newline at the end).
| : Alternation.
() : Grouping.
[] : Character class.

Metacharacter [==] specifies equivalence classes. Use [= and =] to surround a letter when you want to match all accented and unaccented versions of that letter. The resulting equivalence class reference must always be within a bracket expression.

REGR_R2(y, x) returns the coefficient of determination, or R-squared, of the regression. REGR_R2 is a linear regression analytic function which takes two numeric inputs and returns a numeric output which denotes how well a regression line is fitted. Another way of stating this is that it returns the coefficient of determination, or R-squared, of the regression line.

The above statement is true. The declaration and exception blocks are optional in PL/SQL block. The basic program unit in PL/SQL is the block. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords partition the block into a declarative part, an executable part, and an exception-handling part. Only the executable part is required. You can nest a block within another block wherever you can place an executable statement.

For an ORA-04098 error, the cause might be a trigger was attempted to be retrieved for execution and was found to be invalid. This also means that compilation/authorization failed for the trigger. The options are to resolve the compilation/authorization errors, disable the trigger, or drop the trigger. You can also try running the following command to check for errors on the trigger:

SHOW ERRORS TRIGGER trigger_name;

An empty string is treated as a null value in Oracle. A NULL value represents the absence of a value for a record in a field (other software calls it a missing value). An empty value is a “field-formatted” value with no significant data in it. Null has no bounds, it can be used for string, integer, date, etc. fields in a database. Empty string is just regarding a string. If you have no value for a field, use null, not an empty string.

A disabled trigger does not execute its trigger body, even if a triggering statement is issued and the trigger restriction (if any) evaluates to true. To enable or disable triggers using the ALTER TABLE statement, you must own the table, have the ALTER object privilege for the table, or have the ALTER ANY TABLE system privilege. To enable or disable an individual trigger using the ALTER TRIGGER statement, you must own the trigger or have the ALTER ANY TRIGGER system privilege.

Yes, a unique constraint can be a combination of fields that uniquely defines a record. A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.

Left Outer Join,
Right Outer Join and
Full Outer Join

indicates that, if a consumer _id value in the consumer table does not exist in the orders table, all data in the consumer table will display as in the result set. (Macthing+unmatched records form orders data will dispaly)

In PL/SQL, we can pass parameters to procedures and functions in three ways:

1) IN type parameter: These types of parameters are used to send values to stored procedures.
2) OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
3) IN OUT parameter: These types of parameters are used to send values and get values from stored procedures.

NOTE: If a parameter is not explicitly defined a parameter type then by default it is an IN type parameter.

UNION operator eliminates duplicate selected rows and returns only distinct rows that appear in either result.
UNION ALL operator returns all rows and does not eliminate duplicate selected rows.
INTERSECT operator returns only those rows returned by both queries.
DISTINCT statement is used to return only distinct (different) values.
MINUS operator returns only unique rows returned by the first query but not by the second.

The HAVING clause enables you to specify conditions that filter which group results appear in the final results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

Syntax:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used.
The following is the syntax of the SELECT statement, including the HAVING clause:

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

The Oracle ORDER BY clause is used to sort the records in your result set. The ORDER BY clause can only be used in SELECT statements.

Syntax:

SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];

where,

expressions: The columns or calculations that you wish to retrieve.
tables: The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
WHERE condition is optional. The conditions that must be met for the records to be selected.
ASC condition is optional. It sorts the result set in ascending order by expression (default, if no modifier is provider).
DESC condition is optional. It sorts the result set in descending order by expression.

Asynchronous commit is an option that allows transaction to complete more quickly, at the cost that the most recent transactions may be lost if the database should crash. In many applications this is an acceptable trade-off.