Master Oracle 1Z0-071 Exam with Reliable Practice Questions
Upgrade to Premium
Whith three statements are true about built in data types?
Correct : B, D, F
The true statements about built-in data types in Oracle are:
B: A BFILE is a data type in Oracle that allows for a read-only link to binary files stored outside the database in the operating system. This is correct as per Oracle's documentation.
D: The default length for a CHAR column, when not specified, is one character. This is according to the Oracle SQL standard.
F: A BLOB is used for storing binary data within the Oracle database, allowing for storage of large amounts of unstructured binary data.
The incorrect options are:
A: A VARCHAR2 column does not blank-pad values; it is CHAR that may blank-pad to the fixed length.
C: A CHAR column requires a length specification, although if omitted, the default is one character.
E: A VARCHAR2 column requires a length specification; without it, the statement will fail.
Oracle Documentation on Data Types: Data Types
Oracle Documentation on LOBs: LOBs
Oracle Documentation on Character Data Types: Character Data Types
Start a Discussions
Examine this data in the EMPLOYERS table
Which statement will execute successfully?
Correct : A
In SQL, the GROUP BY clause is used in conjunction with aggregate functions to group the result-set by one or more columns.
A . This statement will execute successfully. MAX() is an aggregate function that can be used to return the highest value of the selected column, and SUM() is an aggregate function used to sum the values. Both are valid in the SELECT statement with a GROUP BY clause grouping the results by dept_id.
B . This statement will not execute successfully when used with GROUP BY because LENGTH(last_name) is not an aggregate function and it doesn't appear in the GROUP BY clause. All selected columns that are not under an aggregate function must be included in the GROUP BY clause.
C . This statement will not execute successfully because STDDEV() is an aggregate function that calculates the standard deviation of a set of numbers, and it can only be used on numeric data types. last_name is not a numeric column.
D . This statement will not execute successfully for the same reason as B: INSTR(last_name, 'A') is not an aggregate function and must appear in the GROUP BY clause if it's in the SELECT clause.
Start a Discussions
Which statements is true about using functions in WHERE and HAVING?
Correct : C
Single-row functions can be used in the WHERE and HAVING clauses without requiring a subquery. However, aggregate functions, which operate on many rows to give one result per group, cannot be used in a WHERE clause unless a subquery is used because the WHERE clause is processed before the individual rows are aggregated into groups.
A . False. Single-row functions can be directly used in the WHERE clause.
B . False. Single-row functions can be directly used in the HAVING clause.
C . True. Aggregate functions cannot be used in the WHERE clause without a subquery because the WHERE clause filters individual rows before they are aggregated.
Start a Discussions
Which two statements about INVISIBLE indexes are true?
Correct : D, E
INVISIBLE indexes are a feature in Oracle Database 12c and later versions that allow an index to be maintained but not used by the optimizer unless explicitly hinted.
A . False. An INVISIBLE index still consumes storage space as it is maintained in the background.
B . False. There is no such restriction. You can create multiple INVISIBLE indexes on the same column list.
C . False. The optimizer can consider INVISIBLE indexes if they are hinted at in the query.
D . True. You can alter the visibility of an index using the ALTER INDEX command to make an INVISIBLE index VISIBLE.
E . True. Even though they are invisible to the optimizer by default, all DML operations such as INSERT, UPDATE, and DELETE continue to maintain the index as they would with a VISIBLE index.
Start a Discussions
Which statement executes successfully?
Correct : E
Oracle's interval data types store a period of time. When you want to format an interval as a string, you can use the TO_CHAR function.
A . This statement will not execute successfully because TO_DATE expects a string representing a date, not a number derived from an interval.
B . This statement will not execute successfully because TO_NUMBER does not support interval data types as an input.
C . This statement will not execute successfully because TO_DATE does not support interval data types as an input.
D . This statement will not execute successfully because TO_NUMBER expects a number or a string representing a number as an argument, not a date.
E . This statement will execute successfully. TO_CHAR function can be used to convert interval values to a formatted string, such as 'HH24:MM'.
Start a Discussions