1z0-082 Sample Questions Answers

SELECT * FROM customers WHERE city LIKE ‘D_%’;

SELECT * FROM customers WHERE city = ‘%D_’;

SELECT * FROM customers WHERE city LIKE ‘D_’;

SELECT * FROM customers WHERE city = ‘D_%’;

SELECT customer_id AS “CUSTOMER-ID”, transaction_date AS DATE, amount + 100 “DUES” FROM transactions;

SELECT customer_id AS “CUSTOMER-ID”, transaction_date AS “DATE”, amount + 100 DUES FROM transactions;

SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100 “DUES AMOUNT” FROM transactions;

SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES FROM transactions;

SELECT customer_id AS ‘CUSTOMER-ID’, transaction_date AS DATE, amount + 100 ‘DUES AMOUNT’ FROM transactions;

The value is changed only in the PFILE and takes effect at the next instance startup

The value is changed for the current instance and in the PFILE

It fails because the SCOPE clause is missing

Change is applied to the current instance, but does not persist after instance restart

writing inbound request to the common request queue from all shared server connections

checking for outbound shared server responses on the common outbound response queue

receiving inbound requests from processes using shared server connections

sending each connection input request to the appropriate shared server input queue

broadcasting shared server session responses back to requesters on all connections

sending shared server session responses back to requesters on the appropriate connection

Add the SEGMENT SPACE MANAGEMENT AUTO clause

Set UNDO_TABLESPACE to UNDOTBS01

Add the NOLOGGING clause

Make certain that the database operates in automatic undo management mode

Add the ONLINE clause

Create a template that can be used to clone the database.

Change the server mode from dedicated to shared, and vice versa.

Change the character set.

Create an additional listener.

Create nonstandard block size tablespaces.

All roles granted to a user are set on by default when the user logs in,

Object privileges may not be granted to roles.

The SET ROLE statement can disable one or more roles for a session.

Roles must be password protected.

Roles may be granted to roles.

The SET ROLE statement can enable one or more roles for a session.

analysis of all privileges used by all users including administrative users in the database

analysis of all privileges used by all users but excluding administrative users in the database

analysis of privileges that a user has on their own schema objects that they did not use

analysis of privileges that a user has on their own schema objects that they did use

analysis of privileges granted directly to a role that are then used by a user who has been granted that role

analysis of privileges granted indirectly to a role that are then used by a user who has been granted that role

ORDER BY 1, LNAME DESC

ORDER BY 1, 2

WHERE city LIKE *%AN%*

WHERE city = =%AN%'

WHERE city IN (•%AN%')

ORDER BY last_narae DESC, city ASC

MOD : returns the quotient of a division operation

FLOOR : returns the smallest integer greater than or equal to a specified number

TRUNC : can be used with NUMBER and DATE values

CONCAT : can be used to combine any number of values

CEIL : can be used for positive and negative numbers

DML may be performed on tables with one or more extents in this data file during the execution of this command.

The tablespace containing SALES1.DBF must be altered READ ONLY before executing the command.

The tablespace containing SALES1.DBF must be altered OFFLINE before executing the command.

If Oracle Managed Files (OMF) is used, then the file is renamed but moved to DB_CREATE_FILE_DEST.

The file is renamed and stored in the same location

SELECT promo_cost, promo_category FROM promotions ORDER BY by 1;

SELECT DISTINCT promo_cost || ’ in ‘ || DISTINCT promo_category FROM promotions ORDER BY 1;

SELECT DISTINCT promo_category || ‘ has ‘ || promo_cost AS COSTS FROM promotions ORDER BY 1;

SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2;

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

Operating System authentication may be used for system-privileged administrative users.

REKOTE_LOGIN_PASSMOREFiLE must be set to exclusive to permit password changes for system-privileged administrative users.

Password authentication must be used for system-privileged administrative users.

Password File authentication must be used for system-privileged administrative users.

Password File authentication is supported for any type of database user.

INTERSECT

UNION ALL

UNION

SUBTRACT

MINUS

SELECT join_date FROM employees WHERE join_date > '10-02-2018';

SELECT join_date I I ' ' II salary FROM employees;

SELECT SUBSTR

SELECT join_date + '20' FROM employees; SELECT salary * '120.50' FROM employees;

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

TIMESTAMP WITH LOCAL TIMEZONE

TIMESTAMP

TIMESTAMP WITH TIMEZONE

It has its own commands that are separate from any SQL statements.

It must be downloaded from the Oracle Technology Network (OTN).

It can run scripts entered at the SQL prompt.

It can run Recovery Manager (RMAN) commands.

It has both command-line and graphical user interfaces (GUI).

It can run scripts passed to it by a shell script.

SELECT NULLIF(NULL, 100) FROM DUAL;

SELECT COALESCE (100, 'AM FROM DUAL;

SELECT 2TOLLIF(100, 'A') FROM DUAL;

SELECT NULLIF(100,100) FROM DUAL;

SELECT COALESCE(100, NULL, 200) FROM DUAL;

COLUMN STORE COMPRESS BASIC

COLUMN STORE COMPRESS ADVANCED

RON STORE COMPRESS BASIC

ROW STORE COMPRESS ADVANCED

The online redo logs are opened

The online data files are opened

The alert log records the execution details

The Oracle background processes are started

The initialization parameter file is read

The control file is read

A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.

GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.

GLOBAL TEMPORARY TABLE space allocation occurs at session start.

Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.

A GLOBAL TEMPORARY TABLE’S definition is available to multiple sessions.

A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.

when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT

when a CREATE INDEX statement is executed successfully in the same session

when a COMMIT statement is issued by the same user from another session in the same database instance

when the session logs out successfully

when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT

when a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session

The ADR base defaults to $ORACLE_HOME/rdbms/admin ifneither DIAGNOSTIC_DEST nor ORACLE_BASE is set

The ADR base defaults to $ORACLE_HOME/dbs if the DIAGNOSTIC_DEST parameter and the ORACLE_BASE environment variable are not set

It supports diagnostics for Automatic Storage Management (ASM)

It supports diagnostics for Oracle Clusterware

It is held inside an Oracle database schema

in the undo tablespace

in the SYSAUX tablespace

in the SGA

in the PGA

in the temporary tablespace

DBA_DATA_FILES displays the original name for the data files.

V$DATAFILE displays the new names for the data files.

DBA_DATA _FILES displays both the new name and the old name for the data files.

DBA_DATA_FILES displays the new name for the data files.

DBA_DATA_FILES must be resynchronized manually with the control file an order to have it display the new file names.

Statement 1 returns only one row of output.

Statement 2 returns only one row of output.

Both statements will return NULL if either UNIT_PRICE or QUANTITY contains NULL.

Both the statements give the same output.

Statement 2 may return multiple rows of output.

The high-water mark (HWM) of orders is adjusted.

Only queries are allowed on ORDERS while the shrink is executing.

Queries and DML statements are allowed on ORDERS while the shrink is executing.

The shrink operation causes rows to be moved to empty space starting from the beginning of the orders segment.

Dependent indexes become UNUSABLE.

The shrink operation causes rows to be moved to empty space starting toward the end of the orders segment.

It registers database services with all local and remote listeners known to the database instance

It frees resources held by abnormally terminated processes

It records checkpoint information in the control file

It frees unused temporary segments

It kills sessions that exceed idle time

Revoke the CREATE SESSION privilege from other users

Revoke the CREATE SESSION privilege from user HR

Log in to the database instance

Grant the CREATE SESSION privilege with ADMIN OPTION to other users

Execute DDL statements in the HR schema

Execute DML statements in the HR schema

There can be only one undo tablespace created in a database.

An instance will crash if the active undo tablespace is lost.

An undo tablespace may be owned by only one instance.

undo segments are owned by SYSTEM.

undo segments are owned by SYSBACKUP.

Local naming can be used if Connect-Time Failover Is required.

A client can connect to an Oracle database instance even If no client side network admin has been configured.

Directory Naming requires setting the TNS_ADMIN environment variable on the client side.

Local Naming requires setting the TNS_ADMIN environment variable on the client side.

Directory Naming can be used if Connect-Time Failover is required.

Easy Connect supports TCP/IP and SSL.

ORDER BY “Last Name”

ORDER BY 2, 1

ORDER BY 2, cust_id

ORDER BY CUST_NO

ORDER BY “CUST_NO”

It succeeds and the generated undo is stored in SYSTEM.

It fails and returns the error message “ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'”.

It succeeds and the least recently written undo block of UNDOTBS1 is overwritten by the generated undo.

It succeeds and the generated undo is stored in SYSAUX.

It succeeds and the least recently read undo block of UNDOTBS1 is overwritten by the generated undo.

Duplicates are eliminated automatically by the UNION ALL operator

The number of columns selected in each SELECT statement must be identical

The names of columns selected in each SELECT statement must be identical

The output is sorted by the UNION ALL operator

NULLS are not ignored during duplicate checking

SELECT TO_CHAR(SYSDATE,‘DD-MON-YYYY’) – ’01-JAN-2019’ FROM DUAL;

SELECT ROUND(SYSDATE – ’01-JAN-2019’) FROM DUAL;

SELECT ROUND(SYSDATE – TO_DATE(‘01/JANUARY/2019’)) FROM DUAL;

SELECT TO_DATE(SYSDATE, ‘DD/MONTH/YYYY’) – ‘01/JANUARY/2019’ FROM DUAL;

SELECT SYSDATE – TO_DATE(’01-JANUARY-2019’) FROM DUAL;

They must be placed on the right side of the comparison operator or condition.

They must be placed on the left side of the comparison operator or condition.

They can be used in the where clause.

A SQL statement may have multiple single row subquery blocks.

They must return a row to prevent errors in the SQL statement.

They can be used in the having clause.

Views can be updated without the need to re-grant privileges on the view

Tables in the defining query of a view must always exist in order to create the view

The WITH CHECK clause prevents certain rows from being displayed when querying the view

Data Manipulation Language (DML) can always be used on views

Inserting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error

Deleting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error

The WITH CHECK clause prevents certain rows from being updated or inserted

to_numberipromo_begin_date) - s will return a number.

PROMO_ BEGIN_DATE - sysdate will return a number.

PROMO_ BEGIN_DATE - sysdate will return an error.

PROMO_ BEGIN_DATE - 5 will return a date.

TO_date(PROMO_ BEGIN_DATE * 5| will return a date.

Aggregating functions and columns used in HAVING clauses must be specified in the SELECT list of a query

WHERE and HAVING clauses can be used in the same statement only if applied to different table columns

The HAVING clause can be used with aggregating functions in subqueries

The WHERE clause can be used to exclude rows before dividing them into groups

The WHERE clause can be used to exclude rows after dividing them into groups