Full Access Oracle 1z0-071 Tutorials

Equijoins are joins that use the equality operator (=) to match rows. Non-equijoins are joins that use operators other than the equality operator.

A. False. This condition is an equijoin as it uses the equality operator (=).

B. True. This join condition uses the BETWEEN operator, which is not based on equality.

C. False. A natural join is a type of equijoin where the join is made on all columns with the same names in both tables.

D. False. USING clause is used to specify an equijoin on one or more columns.

E. True. This join condition uses the greater than or equal to (>=) operator, making it a non-equijoin.

A: NEXTVAL is used to increment the sequence and return the next value; it does not give the last number generated.

B: CURRVAL returns the current value of the sequence, which is the last value generated in the user's current session. However, CURRVAL cannot be queried unless NEXTVAL has been called at least once in that session.

C: CURRVAL is used correctly, but the syntax 'sequence.CURRVAL' is not correct in Oracle SQL.

D: NEXTVAL is used to generate the next sequence number, not to retrieve the last one generated.

In Oracle Database 12c, when creating an external table using the CREATE TABLE ... ORGANIZATION EXTERNAL statement, there are certain clauses that are mandatory for the command to execute successfully.

Statement C, the LOCATION clause, is required. The LOCATION clause specifies one or more external data source locations, typically a file or a directory that the external table will read from. Without this, Oracle would not know where to find the external data for the table.

Statement E, the access driver TYPE clause, is also required. The access driver tells Oracle how to interpret the format of the data files. The most common access driver is ORACLE_LOADER, which allows the reading of data files in a format compatible with the SQL*Loader utility. Another option could be ORACLE_DATAPUMP, which reads data in a Data Pump format.

Statements A, B, and D are not strictly required for the command to execute successfully, although they are often used in practice:

A, the DEFAULT DIRECTORY clause, is not mandatory if you have specified the full path in the LOCATION clause, but it is a best practice to use it to avoid hard-coding directory paths in the LOCATION clause.

B, the REJECT LIMIT clause, is optional and specifies the maximum number of errors to allow during the loading of data. If not specified, the default is 0, meaning the load will fail upon the first error encountered.

D, the ACCESS PARAMETERS clause, is where one would specify parameters for the access driver, such as field delimiters and record formatting details. While it is common to include this clause to define the format of the external data, it is not absolutely required for the command to execute; defaults would be used if this clause is omitted.

For reference, you can find more details in the Oracle Database SQL Language Reference for version 12c, under the CREATE TABLE statement for external tables.

In Oracle SQL, the following syntactical rules apply:

A. This query will result in an error because there is no comma separating the column names FIRST_NAME and LAST_NAME. The correct syntax should include a comma to separate the column names in the SELECT list.

B. This query is correctly formatted with a comma separating the column names, so it will not result in an error.

C. This query will result in an error because an alias defined in the SELECT list (ANNUAL_SALARY) cannot be used in the WHERE clause of the same query level. It must be repeated in the WHERE clause as 12 * SALARY.

D. This query will execute successfully because 12 * SALARY is directly used in the WHERE clause, and ANNUAL_SALARY is used in the ORDER BY clause, which is allowed.

E. This query is correct and will not result in an error. It uses 12 * SALARY in both the WHERE and ORDER BY clauses.

F. Similar to option C, this query will execute successfully because ANNUAL_SALARY is correctly used in the ORDER BY clause, and the WHERE clause does not attempt to reference the alias.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Database Object Names and Qualifiers"