01. The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER. Which two queries execute successfully?
a) SELECT NVL(cust_credit_limit * .15, ‘Not Available’) FROM customers;
b) SELECT NVL2(cust_credit_limit * .15, ‘Not Available’) FROM customers;
c) SELECT NVL(TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;
d) SELECT TO_CHAR(NVL(cust_credit_limit * .15, ‘Not Available’)) FROM customers;
e) SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;
02. You have been tasked to create a table for a banking application. One of the columns must meet three requirements:
1) Be stored in a format supporting date arithmetic without using conversion functions
2) Store a loan period of up to 10 years
3) Be used for calculating interest for the number of days the loan remains unpaid
Which data type should you use?
a) INTERVAL YEAR TO MONTH
b) INTERVAL DAY TO SECOND
c) TIMESTAMP WITH LOCAL TIMEZONE
e) TIMESTAMP WITH TIMEZONE
03. A database is configured to use automatic undo management with temporary undo enabled. An UPDATE is executed on a temporary table.
Where is the UNDO stored?
a) in the undo tablespace
b) in the SYSAUX tablespace
c) in the SGA
d) in the PGA
e) in the temporary tablespace
04. Which three instance situations are possible with the Oracle Database server without multi-tenant?
a) two or more instances on separate servers all associated with one database
b) one instance on one server associated with one database
c) one instance on one server associated with two or more databases on the same server
d) one instance on one server not associated with any database
e) one instance on one server associated with two or more databases on separate servers
05. Your database instance was shut down normally and then started in NOMOUNT state. You then execute this command: ALTER DATABASE MOUNT;
Which two actions are performed?
a) The online redo logs are opened
b) The online data files are opened
c) The alert log records the execution details
d) The Oracle background processes are started
e) The initialization parameter file is read
f) The control file is read
06. Which two statements are true about space-saving features in an Oracle Database?
a) Private Temporary Tables (PTTS) store metadata in memory only
b) An index created with the UNUSABLE attribute has no segment
c) If they exist for a session, Private Temporary Tables (PTTs) are always dropped at the next COMMIT OR ROLLBACK statement
d) An index that is altered to be UNUSABLE will retain its segment
e) A table that is truncated will always have its segment removed
07. 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?
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;
08. Which two statements are true about the rules of precedence for operators?
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
09. 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?
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
10. You want to apply the principle of Least Privilege in all your live databases. One of your requirements is to revoke unnecessary privileges from all users who have them using Privilege Analysis.
Which three types of analyses can be done using the DBMS_PRIVILEGE_CAPTURE package?
a) analysis of all privileges used by all users including administrative users in the database
b) analysis of all privileges used by all users but excluding administrative users in the database
c) analysis of privileges that a user has on their own schema objects that they did not use
d) analysis of privileges that a user has on their own schema objects that they did use
e) analysis of privileges granted directly to a role that are then used by a user who has been granted that role
f) analysis of privileges granted indirectly to a role that are then used by a user who has been granted that role