1ZO-082 : Question Set 1
Question 26.
Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data? (Choose four.)
A. A table can have only one primary key but multiple foreign keys
B. A table can have only one primary key and one foreign key
C. The foreign key columns and parent table primary key columns must have the same names
D. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted
E. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted
F. Only the primary key can be defined at the column and table level
G. Primary key and foreign key constraints can be defined at both the column and table level
Correct Answer: ADEG
Question 27.
Which two statements are true about the DUAL table? (Choose two.)
A. It can be accessed only by the SYS user
B. It consists of a single row and single column of VARCHAR2 data type
C. It can display multiple rows but only a single column
D. It can be used to display only constants or pseudo columns
E. It can be accessed by any user who has the SELECT privilege in any schema
F. It can display multiple rows and columns
Correct Answer: BF
A - false; DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. Source: Oracle documentation - Oracle Database, Release 19, SQL Language Reference, 9 SQL Queries and Subqueries, Selecting from the DUAL Table
B - true; single column DUMMY with datatype VARCHAR2(1) and value 'X'
C - false; query SELECT sysdate, sysdate FROM dual;
D - false; Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table. Source: same as mentioned in answer A
E - false; GRANT SELECT any table is not necessary to query DUAL table. Newly created user is not granted with any SELECT privilege, however he/she can query any own object and table DUAL.
F - true; example SELECT with multiple row and column output is SELECT sysdate,sysdate+1 FROM dual CONNECT BY LEVEL <= 5;
Question 28.
You need to calculate the number of days from 1st January 2019 until today.
Dates are stored in the default format of DD-MON-RR.
Which two queries give the required output? (Choose two.)
A. SELECT TO_CHAR(SYSDATE, ‘DD-MON-YYYY’) – ’01-JAN-2019′ FROM DUAL;
B. SELECT ROUND(SYSDATE – ’01-JAN-2019′) FROM DUAL;
C. SELECT ROUND(SYSDATE – TO_DATE(’01/JANUARY/2019′)) FROM DUAL;
D. SELECT TO_DATE(SYSDATE, ‘DD/MONTH/YYYY’) – ’01/JANUARY/2019′ FROM DUAL;
E. SELECT SYSDATE – TO_DATE(’01-JANUARY-2019′) FROM DUAL;
Correct Answer: CE
Question 29.
Which two statements are true about trace files produced by the Oracle Database server? (Choose two.)
A. They can be written by server processes
B. Trace files are written to the Fast Recovery Area (FRA)
C. They can be written by background processes
D. All trace files contain error information that require contacting Oracle Support
E. Trace file names are based on the database name concatenated with a sequential number
Correct Answer: AC
Question 30.
Which three statements are true about GLOBAL TEMPORARY TABLES? (Choose three.)
A. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.
B. GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.
C. GLOBAL TEMPORARY TABLE space allocation occurs at session start.
D. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.
E. A GLOBAL TEMPORARY TABLE’S definition is available to multiple sessions.
F. A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.
Correct Answer: ADE
Question 30a.
Which three statements are true about GLOBAL TEMPORARY TABLES? (Choose three.)
A. A GLOBAL TEMPORARY TABLE can have only one index.
B. A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view
C. DML on GLOBAL TEMPORARY TABLES generates no REDO.
D. A GLOBAL TEMPORARY TABLE cannot have a PUBLIC SYNONYM.
E. A GLOBAL TEMPORARY TABLE can have multiple indexes.
F. A trigger can be created on a GLOBAL TEMPORARY TABLE.
Correct Answer: BEF
Question 30b.
Which two are true about global temporary tables?
A. They can be created only by a user with the DBA role,but can be accessed by all users who can create a session.
B. Backup and recovery operations are available for these tables.
C. If the ON COMMIT clause is session-specific,the table is dropped when the session is terminated.
D. Their data is always stored in the default temporary tablespace of the user who created them.
E. Indexes can be created on them.
F. If the ON COMMIT clause Is transaction-specific, all rows in the table are deleted after each COMMIT or ROLLBACK.
Correct Answer: DE
Question 31.
View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.
You want to display PROD_IDS whose promotion cost is less than the highest cost PROD_ID in a promotion time interval.
SELECT PROD_ID from COSTS
WHERE PROMO_ID IN(
SELECT PROMO_ID FROM PROMOTIONS WHERE PROMOTION_COST < ALL (SELECT MAX(PROMO_COST) FROM PROMOTIONS
GROUP BY (PROMO_END_DATE - PROMO_BEGIN_DATE)));
What will be the result?
A. It gives an error because the ALL keyword is not valid
B. It gives an error because the GROUP BY clause is not valid
C. It executes successfully but does not give the required result
D. It executes successfully and gives the required result
Correct Answer: D
Question 32.
What must you do so that KING is authenticated by the OS when connecting to the database instance?
A. Set OS_AUTHENT_PREFIX to OPS$
B. Have the OS administrator add KING to the OSDBA group
C. Grant DBA to KING
D. Unset REMOTE_LOGIN_PASSWORDFILE
E. Alter user KING to be IDENTIFIED EXTERNALLY
Correct Answer: E
A - false; in that case both db user and OS user must have name OPS$KING
B - false; it would grant DB user DBA role, but login would not succeeded because the user is not identified externally
C - false; user KING is not in DBA group
D - false; REMOTE_LOGIN_PASSWORDFILE must be used to enable user login
E - true; when user is IDENTIFIED EXTERNALLY, it can login with OS authentication
Question 33.
Which two statements are true about the rules of precedence for operators? (Choose two.)
A. The concatenation operator | | is always evaluated before addition and subtraction in an expression
B. NULLS influence the precedence of operators in an expression
C. The + binary operator has the highest precedence in an expression in a SQL statement
D. Arithmetic operators with equal precedence are evaluated from left to right within an expression
E. Multiple parentheses can be used to override the default precedence of operators in an expression
Correct Answer: DE
Question 34.
In the ORCL database, UNDOTBS1 is the active undo tablespace with these properties:
1. A size of 100 MB
2. AUTOEXTEND is off
3. UNDO_RETENTION is set to 15 minutes
4. It has RETENTION GUARANTEE
UNDOTBS1 fills with uncommitted undo 10 minutes after the database opens.
What will happen when the next update is attempted by any transaction?
A. It succeeds and the generated undo is stored in SYSTEM.
B. It fails and returns the error message "ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’".
C. It succeeds and the least recently written undo block of UNDOTBS1 is overwritten by the generated undo.
D. It succeeds and the generated undo is stored in SYSAUX.
E. It succeeds and the least recently read undo block of UNDOTBS1 is overwritten by the generated undo.
Correct Answer: B
Question 35.
Examine the description of the PROMOTIONS table:
You want to display the unique promotion costs in each promotion category.
Which two queries can be used? (Choose two.)
A. SELECT promo_cost, promo_category FROM promotions ORDER BY by 1;
B. SELECT DISTINCT promo_cost || ‘ in ‘ || DISTINCT promo_category FROM promotions ORDER BY 1;
C. SELECT DISTINCT promo_category || ‘ has ‘ || promo_cost AS COSTS FROM promotions ORDER BY 1;
D. SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2;
E. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
Correct Answer: CE
Question 36.
Which two statements are true about views used for viewing tablespace and datafile information? (Choose two.)
A. Tablespace free space can be viewed in V$TABLESPACE
B. V$TABLESPACE displays information that is contained in the controlfile about tablespaces
C. V$TABLESPACE displays information about tablespaces contained in the data dictionary
D. Tablespace free space can be viewed in DBA_TABLESPACES
E. A datafile can be renamed when the database is in MOUNT state and the new file name is displayed when querying DBA_DATA_FILES after the database is opened
Correct Answer: BE
Question 37.
In one of your databases, the user HR has the password HRMGR.
You want to connect to a database instance whose listener listens on port 1531 by using this statement:
CONNECT HR/HRMGR@orcl
No name server is used.
Which statement is true about ORCL?
A. It must be the value of the SERVICE_NAMES parameter on the client side
B. It must resolve to a valid connect descriptor in the server’s tnsnames.ora file
C. It must resolve to a valid connect descriptor in the client’s tnsnames.ora file
D. It must be the name of the database to whose instance HR wishes to connect
E. It must be the name of the server running the database to whose instance HR wishes to connect
Correct Answer: C
Question 38.
Which three statements are true about the Oracle Data Dictionary? (Choose three.)
A. Data dictionary views are created by joins of dictionary base tables and DBA-defined tables
B. The data dictionary is created and maintained by the database administrator
C. Views with the same name but different prefixes, such as CDB, DBA, ALL and USER, reference the same base tables from the data dictionary
D. Base tables can be queried directly
E. It is owned by the SYSTEM user
F. Usernames of all users including database administrators are stored in the data dictionary
Correct Answer: CDF
Question 39.
The ORCL database has RESUMABLE__TIMEOUT = 7200 and DEFERRED_SEGMENT_CREATION = FALSE
User U1 has a 1 MB quota in tablespace DATA.
U1 executes this command:
SQL> CREATE TABLE t1 AS
(SELECT object_name, sharing, created FROM dba_objects);
U1 complains that the command is taking too long to execute.
In the alert log, the database administrator (DBA) finds this:
2017-03-06T12:15:17.183438+05:30 statement in resumable session ‘User U1(136), Session 1, Instance 1’ was suspended due to ORA-01536: space quota exceeded for tablespace ‘DATA’
Which are three actions any one of which the DBA could take to resume the session? (Choose three.)
A. Add a data file to DATA
B. Drop other U1 objects in DATA
C. Increase U1’s quota sufficiently in DATA
D. Set DEFERRED_SEGMENT_CREATION to TRUE
E. Grant UNLIMITED TABLESPACE to U1
F. Set AUTOEXTEND ON for data files in DATA
Correct Answer: BCE
Question 40.
The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE.
You want to display the date of the first Monday after the completion of six months since hiring.
The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day on the week.
Which query can be used?
A. SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY(‘MONDAY’) FROM employees;
B. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), ‘MONDAY’) FROM employees;
C. SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;
D. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;
Correct Answer: B
Question 41.
Which two statements are true about single row functions? (Choose two.)
A. MOD : returns the quotient of a division operation
B. FLOOR : returns the smallest integer greater than or equal to a specified number
C. TRUNC : can be used with NUMBER and DATE values
D. CONCAT : can be used to combine any number of values
E. CEIL : can be used for positive and negative numbers
Correct Answer: CE
Question 42.
Which two statements are true about the results of using the INTERSECT operator in compound queries? (Choose two.)
A. Column names in each SELECT in the compound query can be different
B. The number of columns in each SELECT in the compound query can be different
C. Reversing the order of the intersected tables can sometimes affect the output
D. INTERSECT returns rows common to both sides of the compound query
E. INTERSECT ignores NULLs
Correct Answer: AD
Question 43.
Which three are types of segments in an Oracle Database? (Choose three.)
A. undo
B. index
C. stored procedures
D. sequences
E. tables
F. clusters
Correct Answer: BEF
Question 44.
The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE column of data type DATE.
NLS_DATE_FORMAT is set to DD-MON-RR.
Which two are true about data type conversions involving these columns in query expressions? (Choose two.)
A. CONCAT (qty_sold, invoice_date) : requires explicit conversion
B. invoice_date = ’15-march-2019′ : uses implicit conversion
C. invoie_date > ’01-02-2019′ : uses implicit conversion
D. qty_sold BETWEEN ‘101’ AND ‘110’ : uses implicit conversion
E. qty_sold = ‘0554982’ uses implicit conversion
Correct Answer: BDE
Question 45
For customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.
Which query should be used?
A. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT
FROM customers
WHERE cust_income_level IS NOT NULL
AND due_amount IS NOT NULL;
B. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT
FROM customers
WHERE cust_income_level != NULL
AND cust_credit_level !=NULL;
C. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT
FROM customers
WHERE cust_income_level <> NULL
AND due_amount <> NULL;
D. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT
FROM customers
WHERE cust_income_level != NULL
AND due_amount != NULL;
E. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT
FROM customers
WHERE cust_income_level IS NOT NULL
AND cust_credit_limit IS NOT NULL;
Correct Answer: E
Question 46
Which three statements are true about the DESCRIBE command? (Choose three.)
A. It displays the PRIMARY KEY constraint for any column or columns that have that constraint
B. It can be used from SQL Developer
C. It displays the NOT NULL constraint for any columns that have that constraint
D. It can be used to display the structure of an existing view
E. It displays all constraints that are defined for each column
F. It can be used only from SQL*Plus
Correct Answer: BCD
Question 47.
The SCOTT/TIGER user exists in two databases, BOSTON_DB and DALLAS_DB, in two different locations.
Each database has a tnsnames.ora file defining DALLAS_DB as a service name.
Examine this command:
CREATE DATABASE LINK dblink1 CONNECT TO scott IDENTIFIED BY tiger USING 'dallas_db';
How do you execute the command so that only SCOTT in BOSTON_DB can access the SCOTT schema in DALLAS_DB?
A. as SCOTT in DALLAS_DB
B. as SCOTT in BOSTON_DB
C. as SCOTT in BOSTON_DB and SYS in DALLAS_DB
D. as SYS in both the databases
E. as SCOTT in both the databases
Correct Answer: B
Question 48.
Which two statements are true about UNDO and REDO? (Choose two.)
A. The generation of UNDO generates REDO
B. DML modifies Oracle database objects and only generates UNDO
C. The generation of REDO generates UNDO
D. DML modifies Oracle database objects and only generates REDO
E. DML modifies Oracle database objects and generates UNDO and REDO
Correct Answer: AE
Question 49.
Which two statements are true about the WHERE and HAVING clauses in a SELECT statement? (Choose two.)
A. Aggregating functions and columns used in HAVING clauses must be specified in the SELECT list of a query
B. WHERE and HAVING clauses can be used in the same statement only if applied to different table columns
C. The HAVING clause can be used with aggregating functions in subqueries
D. The WHERE clause can be used to exclude rows before dividing them into groups
E. The WHERE clause can be used to exclude rows after dividing them into groups
Correct Answer: CD
Question 50
In one of your databases, you create a user, HR, and then execute this command:
GRANT CREATE SESSION TO HR WITH ADMIN OPTION;
Which three actions can HR perform? (Choose three.)
A. Revoke the CREATE SESSION privilege from other users
B. Revoke the CREATE SESSION privilege from user HR
C. Log in to the database instance
D. Grant the CREATE SESSION privilege with ADMIN OPTION to other users
E. Execute DDL statements in the HR schema
F. Execute DML statements in the HR schema
Correct Answer: ACD
Thought for the day
No comments:
Post a Comment