Oracle 1z0-071 Actual Exam Practice

Correct Answer: D

*27* Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results from the table, which are not present in the SALES2 table. Which set operator generates the required output? A. INTERSECT B. UNION C. PLUS D. MINUS E. SUBTRACT

Correct Answer: C

*33* Examine the structure of the MEMBERS table. Which query can be used to display the last names and city names only for members from the states MO and MI? A. SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI'; B. SELECT last_name, city FROM members WHERE state LIKE 'M%'; C. SELECT last_name, city FROM members WHERE state IN ('MO', 'MI'); D. SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';

Correct Answer: B

*51* You need to display the faculty name followed by the number of students handled by the faculty at the base location. Examine the following two SQL statements: Statement 1 - SQL>SELECT faculty_name, COUNT(student_id) FROM student JOIN faculty USING (faculty_id, location_id) GROUP BY faculty_name; Statement 2 - SQL>SELECT faculty_name, COUNT(student_id) FROM student NATURAL JOIN faculty GROUP BY faculty_name; Which statement is true regarding the outcome? A. Only statement 2 executes successfully and gives the required result. B. Only statement 1 executes successfully and gives the required result. C. Both statements 1 and 2 execute successfully and give different results. D. Both statements 1 and 2 execute successfully and give the same required result.

Correct Answer: BE

A session's NLS_DATE_FORMAT is set to DD Mon YYYY. Which two queries return the value 1 Jan 2019? A. SELECT TO_DATE("˜2019-01-01') FROM DUAL; B. SELECT DATE "˜2019-01-01' FROM DUAL; C. SELECT "˜2019-01-01' FROM DUAL; D. SELECT TO_CHAR("˜2019-01-01') FROM DUAL; E. SELECT TO_DATE("˜2019-01-01', "˜YYYY-MM-DD') FROM DUAL;

Correct Answer: ABF

An Oracle database server session has an uncommitted transaction in progress which updated 5000 rows in a table.In which three situations does the transactions complete thereby committing the updates? (Choose three.) A. when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT B. when a CREATE INDEX statement is executed successfully in the same session C. when a COMMIT statement is issued by the same user from another session in the same database instance D. when the session logs out successfully E. when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT F. when a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session

Correct Answer: BD

Evaluate the following SELECT statement and view the exhibit to examine its output: SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status, FROM user_constraints - WHERE table_name = 'ORDERS'; CONSTRAINT_NAME CON SEARCH_CONDITI -R_ CONSTRAINT_NAME DELETE_RULE STATUS ON - ORDER_DATE_NN -"ORDER_DATE" IS ENABLED -NOT NULL - ORDER_CUSTOMER_ID_C -"CUSTOMER_ID" IS ENABLED -NN -NOT NULL ORDER_MODE_LOV -C -order _mode in ENABLED -('direct','online') ORDER TOTAL MIN -C -order total >= 0 ENABLED - ORDER PK -P -ENABLED - ORDERS CUSTOMER ID R -CUSTOMERS ID -SET NULL -ENABLED - ORDERS SALES REP -R -EMP EMP ID -SET NULL -ENABLED - Which two statements are true about the output? (Choose two.) A. The R_CONSTRAINT_NAME column gives the alternative name for the constraint. B. In the second column, 'c' indicates a check constraint. C. The STATUS column indicates whether the table is currently in use. D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.

Correct Answer: A

Evaluate the following SQL statement: SELECT product_name || 'it's not available for order' FROM product_information WHERE product_status = 'obsolete'; You received the following error while executing the above query:ERROR -ORA-01756: quoted string not properly terminated What would you do to execute the query successfully? A. Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string. B. Enclose the literal character string in the SELECT clause within the double quotation marks. C. Do not enclose the character literal string in the SELECT clause within the single quotation marks. D. Use escape character to negate the single quotation mark inside the literal character string in the SELECT clause.

Correct Answer: B

Evaluate the following two queries: SQL> SELECT cust_last_name, cust_city FROM customers WHERE cust_credit_limit IN (1000, 2000, 3000) ;SQL> SELECT cust_last_name, cust_city FROM customers WHERE cust_credit_limit = 1000 or cust_credit_limit = 2000 or cust_credit_limit = 3000 Which statement is true regarding the above two queries? A. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column. B. There would be no change in performance. C. Performance would degrade in query 2. D. Performance would improve in query 2.

Correct Answer: C

Examine the SQL statement used to create the TRANSACTION table. SQL > CREATE TABLE transaction - (trn_id char(2) primary key, Start_date date DEFAULT SYSDATE, End_date date NOT NULL); The value 'A1' does not exist for trn_id in this table. Which SQL statement successfully inserts a row into the table with the default value for START_DATE? A. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE(DEFAULT+10)) B. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10')) C. INSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014') D. INSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1', , '10-DEC-2014')

Correct Answer: C

Examine the command: SQL> ALTER TABLE books_transactions ADD CONSTRAINT fk_book_id FOREIGN KEY (book_id)REFERENCES books (book_id) ON DELETE CASCADE; What does ON DELETE CASCADE imply? A. When the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped. B. When the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted but the table structure is retained. C. When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS table are also deleted. D. When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_ID column.

Correct Answer: B

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS: SQL>CREATE TABLE DEPARTMENT_DETAILS(DEPARTMENT_ID NUMBER PRIMARY KEY,DEPARTMENT_NAME VARCHAR2(50), HOD VARCHAR2(50)); SQL>CREATE TABLE COURSE_DETAILS - (COURSE_ID NUMBER PRIMARY KEY,COURSE_NAME VARCHAR2(50), DEPARTMENT_ID VARCHAR2(50)); You want to generate a list of all department IDs along with any course IDs that may have been assigned to them. Which SQL statement must you use? A. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c. department_id); B. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c. department_id); C. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d. department_id); D. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON

Correct Answer: C

Examine the structure of the BOOKS_TRANSACTIONS table: Name Null? Type ---------------- ----------------- ----------------- TRANSACTION_ID NOT NULL VARCHAR2 (6) BORROWED_DATE VARCHAR2 (50) DUE_DATE DATEBOOK_ID DATEMEMBER_ID VARCHAR2 (6) You want to display the member IDs, due date, and late fee as $2 for all transactions. Which SQL statement must you execute? A. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS; B. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS; C. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS; D. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS;

Correct Answer: AC

Examine the structure of the INVOICE table. Name Null? Type ------------------ ------------------- ------------- INV_NO NOT NULL NUMBER(3) INV_DATE DATEINV_AMT NUMBER(10,2) Which two SQL statements would execute successfully? A. SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete') FROM invoice; B. SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available') FROM invoice; C. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate) FROM invoice; D. SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available')

Correct Answer: C

Examine the structure of the MEMBERS table. Name Null? Type ---------------- ----------------- ------------------------- MEMBER_ID NOT NULL VARCHAR2 (6) FIRST_NAME VARCHAR2 (50) LAST_NAME NOT NULL VARCHAR2 (50) ADDRESS VARCHAR2 (50) CITY VARCHAR2 (25)STATE NOT NULL VARCHAR2 (3) Which query can be used to display the last names and city names only for members from the states MO and MI? A. SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI'; B. SELECT last_name, city FROM members WHERE state LIKE 'M%'; C. SELECT last_name, city FROM members WHERE state IN ('MO', 'MI'); D. SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';

Correct Answer: B

Examine the structure of the MEMBERS table: Name Null? Type ---------------- ----------------- ----------------------- MEMBER_ID NOT NULL VARCHAR2 (6) FIRST_NAME VARCHAR2 (50) LAST_NAME NOT NULL VARCHAR2 (50) ADDRESS VARCHAR2 (50) CITY VARCHAR2 (25) STATE VARCHAR2 (3) You want to display details of all members who reside in states starting with the letter A followed by exactly one character. Which SQL statement must you execute? A. SELECT * FROM MEMBERS WHERE state LIKE '%A_'; B. SELECT * FROM MEMBERS WHERE state LIKE 'A_'; C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%'; D. SELECT * FROM MEMBERS WHERE state LIKE 'A%';

Correct Answer: B

Examine this query: SELECT 2 FROM dual d1 CROSS JOIN dual d2 CROSS JOIN dual d3; What is returned upon execution? A. 8 rows B. 1 row C. 6 rows D. 3 rows E. 0 rows F. an error

Correct Answer: B

Examine this query:SELECT TRUNC(ROUND(156.00, -2), -1) FROM DUAL; What is the result? A. 16 B. 200 C. 100 D. 160 E. 150

*Question is up for debate* A IS CORRECT B IS IF "" IS - OR MINUS E IS IF "" IS + OR PLUS

In the PROMOTIONS table, the PROMO_BEGIN_DATE column is of data type DATE and the default date format is DD-MON-RR. Which two statements are true about expressions using PROMO_BEGIN_DATE contained a query? A. PROMO_BEGIN_DATE "" 5 will return a date. B. PROMO_BEGIN_DATE "" SYSDATE will return a number. C. TO_NUMBER(PROMO_BEGIN_DATE) "" 5 will return a number. D. TO_DATE(PROMO_BEGIN_DATE * 5) will return a date. E. PROMO_BEGIN_DATE "" SYSDATE will return an error.

Correct Answer: BDF

In which three situations does a new transaction always start? A. when issuing a TRUNCATE statement after a SELECT statement was issued in the same session B. when issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session C. when issuing a CREATE TABLE statement after a SELECT statement was issued in the same session D. when issuing the first Data Manipulation Language (DML) statement after a COMMIT or ROLLBACK statement was issued in the same session E. when issuing a DML statement after a DML statement failed in the same session F. when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session

Correct Answer: CDE

In which three situations does a transaction complete? A. when a PL/SQL anonymous block is executed B. when a DELETE statement is executed C. when a ROLLBACK command is executed D. when a data definition language (DDL) statement is executed E. when a TRUNCATE statement is executed after the pending transaction

Correct Answer: ACF

No-user-defined locks are used in your database.Which three are true about Transaction Control Language (TCL)? A. COMMIT erases all the transaction's savepoints and releases its locks. B. ROLLBACK TO SAVEPOINT undoes the transaction's changes made since the named savepoint and then ends the transaction. C. COMMIT ends the transaction and makes all its changes permanent. D. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not erase its savepoints. E. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not release its locks. F. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes, releases its locks and erases its savepoints.

Correct Answer: AC

On your Oracle 12c database, you invoked SQL *Loader to load data into the EMPLOYEES table in the HR schema by issuing the following command: $> sqlldr hr/hr@pdb table=employees Which two statements are true regarding the command? A. It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database. B. It fails because no SQL *Loader data file location is specified. C. It fails if the HR user does not have the CREATE ANY DIRECTORY privilege. D. It fails because no SQL *Loader control file location is specified.

Correct Answer: D

Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results from the table, which are not present in the SALES2 table. SALES1 table Name Null Type------------------ ------------- ------------ SALES_ID NUMBER STORE_ID NUMBER ITEMS_ID NUMBER QUANTITY NUMBER SALES_DATE DATE SALES2 table -Name Null Type---------------- ------------- ----------------- SALES_ID NUMBER STORE_ID NUMBER ITEMS_ID NUMBER QUANTITY NUMBER SALES_DATE DATE Which set operator generates the required output? A. INTERSECT B. UNION C. PLUS D. MINUS E. SUBTRACT

Correct Answer: A

Table HR.EMPLOYEES contains a row where the EMPLOYEE_ID is 109. User ALICE has no privileges to access HR.EMPLOYEES. User ALICE starts a session. User HR starts a session and successfully executes these statements:GRANT DELETE ON employees TO alice; UPDATE employees SET salary = 24000 WHERE employee_id = 109; In her existing session ALICE then executes:DELETE FROM hr.employees WHERE employee_id = 109; What is the result? A. The DELETE command will wait for HR'S transaction to end then delete the row. B. The DELETE command will immediately delete the row. C. The DELETE command will immediately return an error. D. The DELETE command will wait for HR'S transaction to end then return an error.

Correct Answer: CD

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 TO_CHAR(NVL(cust_credit_limit * .15, "˜Not Available')) FROM customers; C. SELECT NVL(TO_CHAR(cust_credit_limit * .15), "˜Not Available') FROM customers; D. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), "˜Not Available') FROM customers; E. SELECT NVL2 (cust_credit_limit * .15, "˜Not Available') FROM customers;

Correct Answer: D

The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2. The table has two rows whose CUST_LAST_NAME values are Anderson and Ausson. Which query produces output for CUST_LAST_NAME containing Oder for the first row and Aus for the second? A. SELECT REPLACE (TRIM(TRAILING "˜son' FROM cust_last_name), "˜An', "˜O') FROM customers; B. SELECT INITCAP (REPLACE(TRIM("˜son' FROM cust_last_name), "˜An', "˜O')) FROM customers; C. SELECT REPLACE (SUBSTR(cust_last_name, -3), "˜An', "˜O') FROM customers; D. SELECT REPLACE (REPLACE(cust_last_name, "˜son', "˜'), "˜An', "˜O') FROM customers;

Correct Answer: B

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 month since hiring. The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day of the week. Which query can be used? A. SELECT emp_id, NEXT_DAY (MONTHS_BETWEEN (hire_date, SYSDATE), 6) FROM employees; B. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), "˜MONDAY') FROM employees; C. SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY("˜MONDAY') FROM employees; D. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;

Correct Answer: CD

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 > '01-02-2019' : uses implicit conversion C. invoice_date = '15-march-2019' : uses implicit conversion D. qty_sold BETWEEN "˜101' AND "˜110' : uses implicit conversion E. qty_sold = "˜0554982' : requires explicit conversion

Correct Answer: AB

The ORDERS table has a column ORDER_DATE of data type DATE.The default display format for a date is DD-MON-RR.Which two WHERE conditions demonstrate the correct usage of conversion functions? (Choose two.) A. WHERE TO_CHAR(order_date, 'MON DD YYYY') = 'JAN 20 2019' B. WHERE order_date > TO_DATE('JUL 10 2018', 'MON DD YYYY') C. WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6), 'MON DD YYYY') D. WHERE order_date > TO_DATE(ADD_MONTHS(SYSDATE,6), 'MON DD YYYY') E. WHERE order_date IN (TO_DATE('OCT 21 2018', 'Mon DD YYYY'), TO_CHAR('Nov 21 2018', 'Mon DD YYYY'))

Correct Answer: BCE

The ORDERS table has a primary key constraint on the ORDER_ID column.The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table.The constraint is defined with ON DELETE CASCADE.There are rows in the ORDERS table with an ORDER_TOTAL of less than 1000. Which three DELETE statements execute successfully? A. DELETE order_id FROM orders WHERE order_total < 1000; B. DELETE FROM orders WHERE order_total < 1000; C. DELETE orders WHERE order_total < 1000; D. DELETE * FROM orders WHERE order_total < 1000; E. DELETE FROM orders;

Correct Answer: AC

The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER (8, 2). Evaluate this SQL statement: SELECT TO_CHAR(unit_price, "˜$9,999') FROM product_information; Which two statements are true about the output? A. A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,024. B. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,0236. C. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as #######. D. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,023. E. A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023.

Correct Answer: AC

The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER. Which two queries execute successfully? A. SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING COUNT(*) >10; B. SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY prod_id HAVING COUNT(*) >10; C. SELECT COUNT (prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id; D. SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY COUNT(*) >10; E. SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000;

Correct Answer: D

The SYSDATE function displays the current Oracle Server date as: 21-MAY-19 You wish to display the date as: MONDAY, 21 MAY, 2019 Which statement will do this? A. SELECT TO_DATE(SYSDATE, "˜FMDAY, DD MONTH, YYYY') FROM DUAL; B. SELECT TO_CHAR(SYSDATE, "˜FMDD, DAY MONTH, YYYY') FROM DUAL; C. SELECT TO_CHAR(SYSDATE, "˜FMDAY, DDTH MONTH, YYYY') FROM DUAL; D. SELECT TO_CHAR(SYSDATE, "˜FMDAY, DD MONTH, YYYY') FROM DUAL;

Correct Answer: D

The first DROP operation is performed on PRODUCTS table using this command: DROP TABLE products PURGE; Then a FLASHBACK operation is performed using this command: FLASHBACK TABLE products TO BEFORE DROP; Which is true about the result of the FLASHBACK command? A. It recovers only the table structure. B. It recovers the table structure, data, and the indexes. C. It recovers the table structure and data but not the related indexes. D. It is not possible to recover the table structure, data, or the related indexes.

Correct Answer: D

The following are the steps for a correlated subquery, listed in random order: 1. The WHERE clause of the outer query is evaluated. 2. The candidate row is fetched from the table specified in the outer query. 3. The procedure is repeated for the subsequent rows of the table, till all the rows are processed. 4. Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query. Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery. A. 2, 1, 4, 3 B. 4, 1, 2, 3 C. 4, 2, 1, 3 D. 2, 4, 1, 3

Correct Answer: D

The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following GRANT command: GRANT ALL ON orders, order_items - TO PUBLIC; What correction needs to be done to the above statement? A. PUBLIC should be replaced with specific usernames. B. ALL should be replaced with a list of specific privileges. C. WITH GRANT OPTION should be added to the statement. D. Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables.

Correct Answer: B

There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID. You want to display the name, joining date, and manager for all employees. Newly hired employees are yet to be assigned a department or a manager. For them, "˜No Manager' should be displayed in the MANAGER column. Which SQL query gets the required output? A. SELECT e.last_name, e.hire_date, NVL(m.last_name, "˜No Manager') Manager FROM employees e JOIN employees m ON (e.manager_id = m.employee_id); B. SELECT e.last_name, e.hire_date, NVL(m.last_name, "˜No Manager') Manager FROM employees e LEFT OUTER JOIN employees m ON (e.manager_id = m.employee_id); C. SELECT e.last_name, e.hire_date, NVL(m.last_name, "˜No Manager') Manager FROM employees e RIGHT OUTER JOIN employees m ON (e.manager_id = m.employee_id); D. SELECT e.last_name, e.hire_date, NVL(m.last_name, "˜No Manager') Manager FROM employees e NATURAL JOIN employees m ON (e.manager_id = m.employee_id).

Correct Answer: B

Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level. Which query would give the required result? A. SELECT cust_income_level || "˜ ' || cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers. B. SELECT DISTINCT cust_income_level || "˜ ' || cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers. C. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers. D. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers

Correct Answer: C

What is the primary difference between the relational database (RDB) and object-oriented database (OODB) models? A. OODB supports multiple objects in the same database, whereas RDB supports only tables. B. RDB supports only E.F. Codd's rules, whereas OODB does not support them. C. OODB incorporates methods with data structure definition, whereas RDB does not allow this. D. RDB allows the definition of relationships between different tables, whereas OODB does not allow this.

Correct Answer: C

What is true about non-equijoin statement performance? A. The BETWEEN condition always performs less well than using the >= and <= conditions. B. The join syntax used makes no difference to performance. C. Table aliases can improve performance. D. The BETWEEN condition always performs better than using the >= and <= conditions. E. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.

Correct Answer: B

Which statement is true about TRUNCATE and DELETE? A. You can never TRUNCATE a table if foreign key constraints will be violated. B. For large tables TRUNCATE is faster than DELETE. C. For tables with multiple indexes and triggers DELETE is faster than TRUNCATE. D. You can never DELETE rows from a table if foreign key constraints will be violated.

Correct Answer: C

Which statement is true about aggregate functions? A. The AVG function implicitly converts NULLS to zero. B. Aggregate functions can be nested to any number of levels. C. The MAX and MIN functions can be used on columns with character data types. D. Aggregate functions can be used in any clause of a SELECT statement.

Correct Answer: D

Which statement is true about the INTERSECT operator used in compound queries? A. INTERSECT is of lower precedence than UNION or UNION ALL. B. Multiple INTERSECT operators are not possible in the same SQL statement. C. It ignores NULLs. D. It processes NULLs in the selected columns.

Correct Answer: D

Which statement is true about the Oracle SQL, DELETE and TRUNCATE statements? A. DELETE and TRUNCATE statements can have a rollback done to restore data into a table. B. DELETE and TRUNCATE statements remove all indexes for the tables on which they are performed. C. DELETE but not TRUNCATE statement can be used to remove data from selective columns and rows of a table. D. DELETE but not TRUNCATE statement can be used to selectively remove rows from a table.

Correct Answer: AE

Which statement is true regarding the default behavior of the ORDER BY clause? A. In a character sort, the values are case-sensitive. B. NULLs are not included in the sort operation. C. Only columns that are specified in the SELECT list can be used in the ORDER BY clause. D. Numeric values are displayed in descending order if they have decimal positions. E. Column aliases can be used in the ORDER BY clause.

Correct Answer: ABD

Which three actions can you perform by using the ORACLE_DATAPUMP access driver? (Choose three.) A. Read data from an external table and load it into a table in the database B. Create a directory object for an external table C. Execute DML statements on an external table D. Query data from an external table E. Read data from a table in the database and insert it into an external table F. Create a directory object for a flat file

Correct Answer: ABF

Which three actions can you perform on an existing table containing data? (Choose three.) A. Add a new NOT NULL column with a DEFAULT value B. Change the default value of a column C. Change a DATE column containing data to a NUMBER data type D. Add a new column as the table's first column E. Define a default value that is automatically inserted into a column containing nulls F. Increase the width of a numeric column

Correct Answer: CDF

Which three are key components of an Entity Relationship Model? (Choose three.) A. an activity B. a table C. a relationship D. an attribute E. a unique identifier F. an entity

Correct Answer: ABC

Which three are true about multitable INSERT statements? A. They can be performed on external tables using SQL* Loader. B. They can be performed on relational tables. C. They can be performed only by using a subquery. D. They can insert each computed row into more than one table. E. They can be performed on views. F. They can be performed on remote tables.

Correct Answer: BDG

Which three are true about privileges and roles? A. A role is owned by the user who created it. B. A role can contain a combination of several privileges and roles. C. System privileges always set privileges for an entire database. D. A user has all object privileges for every object in their schema by default. E. All roles are owned by the SYS schema. F. PUBLIC can be revoked from a user. G. PUBLIC acts as a default role granted to every user in a database.

Correct Answer: DEF

Which three are true about privileges? (Choose three.) A. Only users with the DBA role can create roles. B. A combination of object and system privileges can be granted to a role. C. All schema objects have associated object privileges. D. Object privileges granted on a table automatically apply to all synonyms for that table. E. Only users with the GRANT ANY PRIVILEGE privilege can grant and revoke system privileges from other users. F. Schema owners can grant any object privilege on any object in their schema to any other user or role.

Correct Answer: BCE

Which three are true about system and object privileges? (Choose three.) A. WITH GRANT OPTION can be used when granting an object privilege to both users and roles B. Adding a primary key constraint to an existing table in another schema requires a system privilege C. Adding a foreign key constraint pointing to a table in another schema requires the REFERENCES object privilege D. Revoking a system privilege that was granted with WITH ADMIN OPTION has a cascading effect E. Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect. F. WITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC

Correct Answer: ABE

Which three are true about the CREATE TABLE command? (Choose three.) A. It can include the CREATE..INDEX statement for creating an index to enforce the primary key constraint B. It implicitly executes a commit C. A user must have the CREATE ANY TABLE privilege to create tables D. It implicitly rolls back any pending transactions E. The owner of the table should have space quota available on the tablespace where the table is defined F. The owner of the table must have the UNLIMITED TABLESPACE system privilege

Correct Answer: CDF

Which three are true about the MERGE statement? A. It can combine rows from multiple tables conditionally to insert into a single table. B. It can merge rows only from tables. C. It can use subqueries to produce source rows. D. It can update, insert, or delete rows conditionally in multiple tables. E. It can update the same row of the target table multiple times. F. It can use views to produce source rows.

Correct Answer: CFG

Which three privileges can be restricted to a subset of columns in a table? (Choose three.) A. ALTER B. DELETE C. UPDATE D. SELECT E. INDEX F. REFERENCES G. INSERT

Correct Answer: BCF

Which three statements about roles are true? (Choose three.) A. Roles are assigned to roles using the ALTER ROLE statement. B. A single role can be assigned to multiple users. C. A single user can be assigned multiple roles. D. Privileges are assigned to a role using the ALTER ROLE statement. E. A role is named group of related privileges that can only be assigned to a user. F. Privileges are assigned to a role using the GRANT statement. G. Roles are assigned to users using the ALTER USER statement.

Correct Answer: ACD

Which three statements are correct regarding indexes? (Choose three.) A. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically attempts to create a unique index. B. Indexes should be created on columns that are frequently referenced as part of any expression. C. When a table is dropped, corresponding indexes are automatically dropped. D. For each DML operation performed on a table, the corresponding indexes are automatically updated if required.

Correct Answer: ABC

Which three statements are true about Data Manipulation Language (DML)? A. UPDATE statements can have different subqueries to specify the values for each updated column. B. INSERT statements can insert NULLS explicitly into a column. C. DELETE statements can remove multiple rows based on multiple conditions. D. DML statements require a primary key be defined on a table. E. INSERT INTO"¦SELECT"¦FROM statements automatically commit.

Correct Answer: ACD

Which three statements are true about GLOBAL TEMPORARY TABLES? A. A GLOBAL TEMPORARY TABLE can have multiple indexes. B. A GLOBAL TEMPORARY TABLE cannot have a PUBLIC SYNONYM. C. A trigger can be created on a GLOBAL TEMPORARY TABLE. D. A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view. E. A GLOBAL TEMPORARY TABLE can have only one index. F. Data Manipulation Language (DML) on GLOBAL TEMPORARY TABLES generates no REDO.

Correct Answer: DEF

Which three statements are true about GLOBAL TEMPORARY TABLES? A. GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table. B. GLOBAL TEMPORARY TABLE space allocation occurs at session start. C. A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back. D. A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions. E. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted. F. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.

Correct Answer: ADE

Which three statements are true about Structured Query Language (SQL)? A. It best supports relational databases. B. It is used to define encapsulation and polymorphism for a relational table. C. It is the only language that can be used for both relational and object-oriented databases. D. It guarantees atomicity, consistency, isolation, and durability (ACID) features. E. It provides independence for logical data structures being manipulated from the underlying physical data storage F. It requires that data be contained in hierarchical data storage.

Correct Answer: CDE

Which three statements are true about defining relations between tables in a relational database? A. Primary key columns allow null values. B. Every primary or unique key value must refer to a matching foreign key value. C. Foreign key columns allow null values. D. Every foreign key value must refer to a matching primary or unique key value. E. Unique key columns allow null values.

Correct Answer: CEF

Which three statements are true about external tables? (Choose three.) A. DML statements can modify them. B. They can be temporary tables. C. They can be used in queries containing joins. D. They can be indexed. E. They can be used in queries containing sorts. F. Their metadata is stored in the database.

Correct Answer: ACD

Which three statements are true about indexes and their administration in an Oracle database? A. An index can be created as part of a CREATE TABLE statement. B. A DROP INDEX statement always prevents updates to the table during the drop operation. C. A UNIQUE and non-unique index can be created on the same table column. D. A descending index is a type of function-based index . E. If a query filters on an indexed column then it will always be used during execution of the query. F. An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its underlying table.

Correct Answer: BDF

Which three statements are true about inner and outer joins? (Choose three.) A. A full outer join must use Oracle syntax B. An inner join returns matched rows C. A left or right outer join returns only unmatched rows D. A full outer join returns matched and unmatched rows E. Outer joins can only be used between two per query F. Outer joins can be used when there are multiple join conditions on two tables

Correct Answer: ABD

Which three statements are true about multiple row subqueries? A. They can contain GROUP BY clauses. B. They can return multiple columns. C. Two or more values are always returned from the subquery. D. They can contain HAVING clauses. E. They cannot contain a subquery.

Correct Answer: ACF

Which three statements are true about performing Data Manipulation Language (DML) operations on a view in an Oracle Database? A. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword. B. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view. C. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view. D. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view. E. Insert statements can always be done on a table through a view. F. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains aggregating functions.

Correct Answer: BEF

Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined? (Choose three.) A. Insert statements can always be done on a table through a view. B. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view. C. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view. D. Delete statements can always be done on a table through a view. E. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view. F. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.

Correct Answer: ABC

Which three statements are true about sequences in a single instance Oracle database? (Choose three.) A. A sequence can issue duplicate values B. A sequence's unallocated cached value are lost if the instance shuts down C. Sequences can always have gaps D. Two or more tables cannot have keys generated from the same sequence E. A sequence can only be dropped by a DBA F. A sequence number that was allocated can be rolled back if a transaction fails

Correct Answer: ABD

Which three statements are true about single-row functions? (Choose three.) A. They can be nested to any level B. The data type returned can be different from the data type of the argument C. They can accept only one argument D. The argument can be a column name, variable, literal or an expression E. They can be used only in the WHERE clause of a SELECT statement F. They return a single result row per table

Correct Answer: BDE

Which three statements are true about the ALTER TABLE....DROP COLUMN.... command? A. A column can be dropped only if it does not contain any data. B. A column can be dropped only if another column exists in the table. C. A dropped column can be rolled back. D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped. E. A parent key column in the table cannot be dropped.

Correct Answer: ADF

Which three statements are true about the DESCRIBE command? (Choose three.) A. It can be used to display the structure of an existing view B. It can be used only from SQL*Plus C. It displays the PRIMARY KEY constraint for any column or columns that have that constraint D. It can be used from SQL Developer E. It displays all constraints that are defined for each column F. It displays the NOT NULL constraint for any columns that have that constraint

Correct Answer: CDG

Which three statements are true about the Oracle join and ANSI join syntax? A. The Oracle join syntax supports natural joins. B. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax. C. The Oracle join syntax supports the creation of a Cartesian product of two tables. D. The SQL:1999 compliant ANSI join syntax supports natural joins. E. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax. F. The Oracle join syntax only supports right outer joins. G. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.

Correct Answer: ABE

Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database? A. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC). B. A TIMESTAMP data type column contains information about year, month, and day. C. The CURRENT_TIMESTAMP function returns data without time zone information. D. A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row. E. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC).

Correct Answer: ABC

Which three statements are true about views in an Oracle Database? A. Views can join tables only if they belong to the same schema. B. A view can be created that refers to a non-existent table in its defining query. C. Views have no object number. D. Views have no segment. E. Rows inserted into a table using a view are retained in the table if the view is dropped. F. A SELECT statement cannot contain a WHERE clause when querying a view containing a WHERE clause in its defining query.

Correct Answer: ACE

Which three statements are true regarding indexes? (Choose three.) A. A SELECT statement can access one or more indices without accessing any tables B. An update to a table can result in no updates to any of the table's indexes C. A table belonging to one user can have an index that belongs to a different user D. A UNIQUE index can be altered to be non-unique E. An update to a table can result in updates to any or all of the table's indexes F. When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped

Correct Answer: ABC

Which three tasks can be performed by DDL statements? A. providing an alternative name for a table B. modifying a table to prevent data that violate certain conditions from being entered in a column C. preventing any data modification to a table D. preventing data retrieval from a table outside of office hours E. creating multiple savepoints to enable partial rollback of a transaction

Correct Answer: AE

Which two are SQL features? (Choose two.) A. processing sets of data B. providing update capabilities for data in external files C. providing graphical capabilities D. providing variable definition capabilities E. providing database transaction control

Correct Answer: AC

Which two are true about a SQL statement using SET operators such as UNION? A. The number, but not names, of columns must be identical for all SELECT statements in the query. B. The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query. C. The data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query. D. The names and number of columns must be identical for all SELECT statements in the query. E. The data type of each column returned by the second query must exactly match the data type of the corresponding column returned by the first query.

Correct Answer: AD

Which two are true about dropping columns from a table? A. A column drop is implicitly committed. B. A column that is referenced by another column in any other table cannot be dropped. C. A column can be removed only if it contains no data. D. Multiple columns can be dropped simultaneously using the ALTER TABLE command.. E. A column must be set as unused before it is dropped from a table. F. A primary key column cannot be dropped.

Correct Answer: AD

Which two are true about external tables that use the ORACLE_DATAPUMP access driver? (Choose two.) A. When creating an external table, data can be selected from another external table or from a table whose rows are stored in database blocks. B. Creating an external table creates a dump file that can be used only by an external table in the same database. C. When creating an external table, data can be selected only from a table whose rows are stored in database blocks. D. Creating an external table creates a directory object. E. Creating an external table creates a dump file that can be used by an external table in the same or a different database.

Correct Answer: CD

Which two are true about granting privileges on objects? A. An object privilege can be granted to other users only by the owner of that object. B. An object privilege can be granted to a role only by the owner of that object. C. A table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY constraints using that table. D. The owner of an object acquires all object privileges on that object by default. E. The WITH GRANT OPTION clause can be used only by DBA users.

Correct Answer: AC

Which two are true about savepoints? (Choose two.) A. After issuing a savepoints, you can roll back to the savepoint name within the current transaction. B. A ROLLBACK TO SAVEPOINT command issued before the start of a transaction results in an error. C. They make uncommitted updates visible to other sessions owned by the same user. D. After issuing a savepoint, you cannot roll back the complete transaction. E. You can commit updates done between two savepoints without committing other updates in the current transaction. F. They make uncommitted updates visible to sessions owned by other users.

Correct Answer: AD

Which two are true about scalar subquery expressions? (Choose two.) A. They can return at most one row. B. You can use them as a default value for a column. C. You cannot correlate them with a table in the parent statement. D. You must enclose them in parentheses. E. They can return two columns.

Correct Answer: EF

Which two are true about the WITH GRANT OPTION clause? A. The grantee must have the GRANT ANY OBJECT PRIVILEGE system privilege to use this option. B. It can be used when granting privileges to roles. C. It cannot be used to pass on privileges to PUBLIC by the grantee. D. It can be used for system and object privileges. E. It can be used to pass on privileges to other users by the grantee. F. The grantee can grant the object privilege to any user in the database, with or without including this option.

Correct Answer: AE

Which two are true about the data dictionary? A. The SYS user owns all base tables and user-accessible views in the data dictionary. B. All users have permissions to access all information in the data dictionary by default. C. The data dictionary is constantly updated to reflect changes to database objects, permissions, and data. D. All user actions are recorded in the data dictionary. E. Base tables in the data dictionary have the prefix DBA_.

Correct Answer: AC

Which two are true about using constraints? (Choose two.) A. A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints. B. A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints. C. PRIMARY KEY and FOREIGN KEY constraints can be specified at the column and at the table level. D. NOT NULL can be specified at the column and at the table level. E. A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parent table must have the same names. F. A table can have only one PRIMARY KEY and one FOREIGN KEY constraint.

Correct Answer: AE

Which two statements are true about Oracle synonyms? A. A synonym can have a synonym. B. All private synonym names must be unique in the database. C. Any user can create a PUBLIC synonym. D. A synonym can be created on an object in a package. E. A synonym has an object number.

Correct Answer: AD

Which two statements are true about a full outer join? A. It includes rows that are returned by an inner join. B. It returns only unmatched rows from both tables being joined. C. It includes rows that are returned by a Cartesian product. D. It returns matched and unmatched rows from both tables being joined. E. The Oracle join operator (+) must be used on both sides of the join condition in the WHERE clause.

Correct Answer: AC

Which two statements are true about a self join? A. It can be a left outer join. B. It must be a full outer join. C. It can be an inner join. D. It must be an equijoin. E. The join key column must have an index.

Correct Answer: AC

Which two statements are true about an Oracle database? (Choose two.) A. A table can have multiple foreign keys. B. A column definition can specify multiple data types. C. A VARCHAR2 column without data has a NULL value . D. A NUMBER column without data has a zero value. E. A table can have multiple primary keys.

Correct Answer: AC

Which two statements are true about conditional INSERT ALL? A. Each WHEN condition is tested for each row returned by the subquery. B. The total number of rows inserted is always equal to the number of rows returned by the subquery. C. A single WHEN condition can be used for multiple INTO clauses. D. It cannot have an ELSE clause. E. Each row returned by the subquery can be inserted into only a single target table.

Correct Answer: AF

Which two statements are true about date/time functions in a session where NLS_DATE_FORMAT is set to DD-MON-YYYY HH24:MI:SS? A. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE. B. CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of fractional seconds. C. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server. D. SYSDATE can be used in expressions only if the default date format is DD-MON-RR. E. SYSDATE can be queried only from the DUAL table. F. CURRENT_DATE returns the current date and time as per the session time zone.

Correct Answer: DE

Which two statements are true about dropping views? (Choose two.) A. Data selected by a view's defining query is deleted from its underlying tables when the view is dropped. B. Read only views cannot be dropped. C. The creator of a view to be dropped must have the DROP ANY VIEW privilege. D. CASCADE CONSTRAINTS must be specified when referential integrity constraints on other objects refer to primary or unique keys in the view to be dropped. E. Views referencing a dropped view become invalid.

Correct Answer: AB

Which two statements are true about selecting related rows from two tables based on an Entity Relationship Diagram (ERD)? (Choose two.) A. Implementing a relationship between two tables might require joining additional tables B. Relating data from a table with data from the same table is implemented with a self join C. Rows from unrelated tables cannot be joined D. Every relationship between the two tables must be implemented in a join condition E. An inner join relates rows within the same table

Correct Answer: AD

Which two statements are true about sequences created in a single instance Oracle database? (Choose two.) A. When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement. B. DELETE <sequencename> would remove a sequence from the database. C. The numbers generated by an explicitly defined sequence can only be used to insert data in one table. D. CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence. E. When a database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.

Correct Answer: AC

Which two statements are true about single-row functions? (Choose two.) A. CEIL: can be used for positive and negative numbers 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. MOD: returns the quotient of a division operation

Correct Answer: AF

Which two statements are true about substitution variables? (Choose two.) A. A substitution variable can be used with any clause in a SELECT statement B. A substitution variable used to prompt for a column name must be enclosed in a single quotation marks C. A substitution variable prefixed with & always prompts only once for a value in a session D. A substitution variable can be used only in a SELECT statement E. A substitution variable used to prompt for a column name must be enclosed in double quotation marks F. A substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session

Correct Answer: BD

Which two statements are true about the DUAL table? A. It can display multiple rows but only a single column. B. It can be accessed by any user who has the SELECT privilege in any schema. C. It can display multiple rows and columns. D. It consists of a single row and single column of VARCHAR2 data type. E. It can be used to display only constants or pseudo columns. F. It can be accessed only by the SYS user.

Correct Answer: DE

Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION? A. Each SELECT statement in the compound query must have its own ORDER BY clause. B. Each SELECT statement in the compound query can have its own ORDER BY clause. C. Column positions must be used in the ORDER BY clause. D. The first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause. E. Only column names from the first SELECT statement in the compound query are recognized.

Correct Answer: AE

Which two statements are true about the SET VERIFY ON command? (Choose two.) A. It can be used in SQL Developer and SQL*Plus B. It displays values for variables used only in the WHERE clause of a query C. It can be used only in SQL*Plus D. It displays values for variables prefixed with && E. It displays values for variables created by the DEFINE command

Correct Answer: AB

Which two statements are true about the data dictionary? (Choose two.) A. Views with the prefix ALL_, DBA_ and USER_ are not all available for every type of metadata. B. Views with the prefix ALL_ display metadata for objects to which the current user has access. C. Views with the prefix DBA_ display only metadata for objects in the SYS schema. D. The data dictionary does not store metadata in tables. E. The data dictionary is accessible when the database is closed.

Correct Answer: DE

Which two statements are true about the results of using the INTERSECT operator in compound queries? A. INTERSECT ignores NULLs. B. Reversing the order of the intersected tables can sometimes affect the output. C. The number of columns in each SELECT in the compound query can be different. D. INTERSECT returns rows common to both sides of the compound query. E. Column names in each SELECT in the compound query can be different.

Correct Answer: BC

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. Multiple parentheses can be used to override the default precedence of operators in an expression C. Arithmetic operators with equal precedence area evaluated from left to right within an expression D. NULLS influence the precedence of operators in an expression E. The + binary operator has the highest precedence in an expression in a SQL statement

Correct Answer: AF

Which two statements are true about transactions in the Oracle Database server? A. If a session has an uncommitted transaction, then a DDL statement issues a COMMIT before starting a new transaction. B. An uncommitted transaction commits automatically if the user exists SQL*Plus. C. Data Manipulation Language (DML) statements always start a new transaction. D. A user can always see uncommitted updates made by the same user in a different session. E. A Data Definition Language (DDL) statement does a COMMIT automatically only for the data dictionary updates caused by the DDL. F. A session can always see uncommitted updates made by itself.

Correct Answer: AE

Which two statements are true regarding a SAVEPOINT? (Choose two.) A. A SAVEPOINT does not issue a COMMIT B. Only one SAVEPOINT may be issued in a transaction C. Rolling back to a SAVEPOINT can undo a TRUNCATE statement D. Rolling back to a SAVEPOINT can undo a CREATE INDEX statement E. Rolling back to a SAVEPOINT can undo a DELETE statement

Correct Answer: BE

Which two statements are true regarding operators used with subqueries (Choose two.) A. The NOT IN operator is equivalent to IS NULL. B. The <ANY operator means less than the maximum. C. =ANY and =ALL operators have the same functionality. D. The IN operator cannot be used in single-row subqueries. E. The NOT operator can be used with IN, ANY and ALL operators.

Correct Answer: BD

Which two statements are true regarding the COUNT function? (Choose two.) A. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause. B. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column. C. COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column. D. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any column. E. It can only be used for NUMBER data types.

Correct Answer: AE (This results were inconclusive in the discussion, this was the default answer and there were no major arguments against it)

Which two statements are true regarding the DELETE and TRUNCATE commands? A. DELETE can be used to remove rows from only one table in one statement. B. DELETE can be used to remove rows from multiple tables in one statement. C. DELETE can be used to remove rows only for tables that are parents for a child table that has a referential integrity constraint referring to the parent. D. DELETE can be used to remove data from specific columns as well as complete rows. E. DELETE and TRUNCATE can be used for tables that are parents for a child table that has a referential integrity constraint having an ON DELETE rule.

Correct Answer: DE

Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.) A. You can use column alias in the GROUP BY clause. B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups. C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause. D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups. E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY

Correct Answer: CE

Which two statements are true regarding the UNION and UNION ALL operators? (Choose two.) A. The output is sorted by the UNION ALL operator B. The names of columns selected in each SELECT statement must be identical C. The number of columns selected in each SELECT statement must be identical D. Duplicates are eliminated automatically by the UNION ALL operator E. NULLS are not ignored during duplicate checking

Correct Answer: AD

Which two statements are true? (Choose two.) A. The USER_SYNONYMS view can provide information about private synonyms. B. The user SYSTEM owns all the base tables and user-accessible views of the data dictionary. C. All the dynamic performance views prefixed with v$ are accessible to all the database users. D. The USER_OBJECTS view can provide information about the tables and views created by the user who queries the view. E. DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.

Correct Answer: CE

Which two will execute successfully? (Choose two.) A. SELECT COALESCE('DATE', SYSDATE) FROM (SELECT NULL AS "DATE" FROM DUAL); B. SELECT COALESCE('DATE', SYSDATE) FROM DUAL; C. SELECT NVL('DATE', SYSDATE) FROM DUAL; D. SELECT COALESCE(0, SYSDATE) FROM DUAL; E. SELECT NVL('DATE', 200) FROM (SELECT NULL AS "DATE" FROM DUAL);

Correct Answer: CF

You create a table by using this command: CREATE TABLE rate_list (rate NUMBER(6,2)); Which two are true about executing statements? A. INSERT INTO rate_list VALUES (-10) produces an error. B. INSERT INTO rate_list VALUES (87654.556) inserts the value as 87654.6. C. INSERT INTO rate_list VALUES (0.551) inserts the value as .55. D. INSERT INTO rate_list VALUES (-99.99) inserts the value as 99.99. E. INSERT INTO rate_list VALUES (0.999) produces an error. F. INSERT INTO rate_list VALUES (-.9) inserts the value as -.9.

Correct Answer: DE

You execute this command: TRUNCATE TABLE depts; Which two are true? A. It drops any triggers defined on the table. B. It always retains the space used by the removed rows. C. A ROLLBACK statement can be used to retrieve the deleted data. D. It retains the integrity constraints defined on the table. E. It retains the indexes defined on the table. F. A FLASHBACK TABLE statement can be used to retrieve the deleted data.

Correct Answer: AB

You execute this command:ALTER TABLE employees SET UNUSED (department_id)Which two are true? (Choose two.) A. No updates can be made to the data in the DEPARTMENT_ID column. B. A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table. C. A query can be display data from the DEPARTMENT_ID column. D. The DEPARTMENT_ID column is set to null for all rows in the table. E. The DEPARTMENT_ID column can be recovered from the recycle bin. F. The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued.

Correct Answer: C

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 load 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. TIMESTAMP WITH LOCAL TIMEZONE B. TIMESTAMP WITH TIMEZONE C. INTERVAL DAY TO SECOND D. TIMESTAMP E. INTERVAL YEAR TO MONTH

Correct Answer: A

You have the privileges to create any type of synonym. Which statement will create a synonym called EMP for the HCM.EMPLOYEE_RECORDS table that is accessible to all users? A. CREATE PUBLIC SYNONIM emp FOR hcm.employee_records; B. CREATE GLOBAL SYNONIM emp FOR hcm.employee_records; C. CREATE SYNONIM emp FOR hcm.employee_records; D. CREATE SYNONIM PUBLIC.emp FOR hcm.employee_records; E. CREATE SYNONIM SYS.emp FOR hcm.employee_records;

Correct Answer: ABF

You issued the following command: SQL> DROP TABLE employees; Which three statements are true? A. All uncommitted transactions are committed. B. All indexes and constraints defined on the table being dropped are also dropped. C. Sequences used in the employees table become invalid. D. The space used by the employees table is reclaimed immediately. E. The employees table can be recovered using the rollback command. F. The employees table is moved to the recycle bin

Correct Answer: A

You need to allow user ANDREW to: 1. Modify the TITLE and ADDRESS columns of your CUSTOMERS table. 2. GRANT that permission to other users. Which statement will do this? A. GRANT UPDATE (title, address) ON customers TO andrew WITH GRANT OPTION; B. GRANT UPDATE (title, address) ON customers TO andrew WITH ADMIN OPTION; C. GRANT UPDATE ON customers.title, customers.address TO andrew WITH ADMIN OPTION; D. GRANT UPDATE (title, address) ON customers TO andrew; E. GRANT UPDATE ON customers.title, customers.address TO andrew WITH GRANT OPTION; F. GRANT UPDATE ON customers.title, customers.address TO andrew;

Correct Answer: BD

You need to calculate the number of days from 1January 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 SYSDATE "" TO_DATE('01-JANUARY-2019') FROM DUAL; C. SELECT ROUND(SYSDATE "" '01-JAN-2019') FROM DUAL; D. SELECT ROUND(SYSDATE "" TO_DATE("˜01/JANUARY/2019')) FROM DUAL; E. SELECT TO_DATE(SYSDATE, "˜DD/MONTH/YYYY') "" "˜01/JANUARY/2019' FROM DUAL;

Correct Answer: C

You want to display the date for the first Monday of the next month and issue the following command: SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'MON'),'dd "is the first Monday for" fmmonth rrrr')FROM DUAL; What is the outcome? A. In generates an error because rrrr should be replaced by rr in the format string. B. It executes successfully but does not return the correct result. C. It executes successfully and returns the correct result. D. In generates an error because TO_CHAR should be replaced with TO_DATE. E. In generates an error because fm and double quotation marks should not be used in the format string.

Correct Answer: AD

✑ MANAGER is an existing role with no privileges or roles. ✑ EMP is an existing role containing the CREATE TABLE privilege. ✑ EMPLOYEES is an existing table in the HR schema. Which two commands execute successfully? A. GRANT CREATE SEQUENCE TO manager, emp; B. GRANT CREATE ANY SESSION, CREATE ANY TABLE TO manager; C. GRANT SELECT, INSERT ON hr.employees TO manager WITH GRANT OPTION; D. GRANT CREATE TABLE, emp TP manager; E. GRANT CREATE TABLE, SELECT ON hr.employees TO manager; Hide Solution Discussion 21

Correct Answer: D

Name Null? Type ------------------ --------------- --------------------------- MEMBER_ID NOT NULL VARCHAR2 (6) FIRST_NAME VARCHAR2 (50) LAST_NAME NOT NULL VARCHAR2 (50) ADDRESS VARCHAR2 (50) You execute the SQL statement: SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME " FROM members; What is the outcome? A. It fails because the alias name specified after the column names is invalid. B. It fails because the space specified in single quotation marks after the first two column names is invalid. C. It executes successfully and displays the column details in a single column with only the alias column heading. D. It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.

Correct Answer: A

*14* View the Exhibit and examine the structure of CUSTOMERS table. Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed. Which SQL statement would produce the required result? A. SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers; B. SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers; C. SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers; D. SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT" FROM customers;

Correct Answer: D

*20* You must display the NAME of stores along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price. The stores displayed must have START_DATE in the range of 36 months starting from 01-Jan-2000 and above. Which SQL statement would get the desired output? A. SELECT name, concat (address| | ','| |city| |', ', country) AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE MONTHS_BETWEEN (start_date, '01-JAN-2000') <=36; B. SELECT name, concat (address| | ','| |city| |', ', country) AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36; C. SELECT name, address||', '||city||', '||country AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36; D. SELECT name, concat (address||','| |city| |', ', country) AS full_address, start_date, property_price, property_price*115/100 FROM stores WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

Correct Answer: C

*36* Examine the structure of the BOOKS_TRANSACTIONS table: You want to display the member IDs, due date, and late fee as $2 for all transactions. Which SQL statement must you execute? A. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS; B. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS; C. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS; D. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS;

Correct Answer: B

*49* Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables: You want to generate a list of all department IDs along with any course IDs that may have been assigned to them. Which SQL statement must you use? A. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c. department_id); B. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c. department_id); C. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d. department_id); D. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d. department_id);

Correct Answer: A

A non-correlated subquery can be defined as __________. (Choose the best answer.) A. A set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query. B. A set of sequential queries, all of which must return values from the same table. C. A set of sequential queries, all of which must always return a single value. D. A SELECT statement that can be embedded in a clause of another SELECT statement only.

Correct Answer: B

A subquery is called a single-row subquery when _______. A. There is only one subquery in the outer query and the inner query returns one or more values B. The inner query returns a single value to the outer query. C. The inner query uses an aggregating function and returns one or more values. D. The inner query returns one or more values and the outer query returns a single value.

Correct Answer: A

Evaluate the following ALTER TABLE statement: ALTER TABLE orders -SET UNUSED (order_date); Which statement is true? A. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table. B. The ORDER_DATE column must be empty for the ALTER TABLE command to execute successfully. C. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table. D. The DESCRIBE command would still display the ORDER_DATE column.

Correct Answer: D

Evaluate the following CREATE SEQUENCE statement: CREATE SEQUENCE seq1 START WITH 100 INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE; The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement: SELECT seq1.nextval FROM dual; What is displayed by the SELECT statement? A. 100 B. an error C. 10 D. 1

Correct Answer: A

Evaluate the following CREATE TABLE commands: CREATE_TABLE orders (ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY, ord_date DATE, cust_id NUMBER (4) ); CREATE TABLE ord_items (ord _no NUMBER (2), item_no NUMBER(3), qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200), expiry_date date CHECK (expiry_date> SYSDATE), CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no), CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) ); The above command fails when executed. What could be the reason? A. SYSDATE cannot be used with the CHECK constraint. B. The BETWEEN clause cannot be used for the CHECK constraint. C. The CHECK constraint cannot be placed on columns having the DATE data type. D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Correct Answer: A

Evaluate the following CREATE TABLE commands: CREATE_TABLE orders (ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY, ord_date DATE, cust_id NUMBER (4)); CREATE TABLE ord_items (ord _no NUMBER (2), item_no NUMBER(3), qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200), expiry_date date CHECK (expiry_date> SYSDATE), CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no), CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) ); Why would the ORD_ITEMS table not get created? A. SYSDATE cannot be used with the CHECK constraint. B. The BETWEEN clause cannot be used twice for the same table. C. The CHECK constraint cannot be placed on columns having the DATE data type. D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Correct Answer: D

Evaluate the following SQL statement: SELECT product_name || 'it's not available for order' FROM product_information - WHERE product_status = 'obsolete'; You received the following error while executing the above query: ERROR -ORA-01756: quoted string not properly terminated What would you do to execute the query successfully? A. Remove the single quotation marks enclosing the character literal string in the SELECT clause B. Use the escape character to negate the single quotation mark within the literal character string in the SELECT clause C. Enclose the character literal string in the SELECT clause within double quotation marks D. Use the Oracle (q) operator and delimiter to allow the use of a single quotation mark within the literal character string in the SELECT clause

Correct Answer: ABD

Evaluate the following SQL statement: SQL> select cust_id, cust_last_name "Last name" FROM customers WHERE country_id = 10 UNION SELECT cust_id CUST_NO, cust_last_name FROM customers WHERE country_id = 30 -Identify three ORDER BY clauses either one of which can complete the query. A. ORDER BY "Last name" B. ORDER BY 2, cust_id C. ORDER BY CUST_NO D. ORDER BY 2, 1 E. ORDER BY "CUST_NO"

Correct Answer: B

Evaluate the following SQL statements that are issued in the given order: CREATE TABLE emp (emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, ename VARCHAR2(15),salary NUMBER (8,2), mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp(emp_no)); ALTER TABLE emp -DISABLE CONSTRAINT emp_emp_no_pk CASCADE; ALTER TABLE emp -ENABLE CONSTRAINT emp_emp_no_pk; What would be the status of the foreign key EMP_MGR_PK? A. It would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it. B. It would remain disabled and has to be enabled manually using the ALTER TABLE command. C. It would be automatically enabled and immediate. D. It would be automatically enabled and deferred.

Correct Answer: C

Evaluate the following query: SQL> SELECT TRUNC (ROUND(156.00, -1),-1) FROM DUAL; What would be the outcome? A. 150 B. 200 C. 160 D. 16 E. 100

Correct Answer: A

Evaluate the following statement. INSERT ALL WHEN order_total < 10000 THEN INTO small_orders WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders WHEN order_total > 200000 AND order_total < 20000 THEN INTO large_orders SELECT order_id, order_total, customer_id FROM orders; Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement? A. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause. B. They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses. C. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses. D. The insert statement would give an error because the ELSE clause is not present for support in case none of WHEN clauses are true.

Correct Answer: BE

Examine the business rule: Each student can work on multiple projects and each project can have multiple students. You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format: STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK Which two statements are true in this scenario? (Choose two.) A. The ERD must have a 1:M relationship between the STUDENTS and PROJECTS entities. B. The ERD must have a M:M relationship between the STUDENTS and PROJECTS entities that must be resolved into 1:M relationships. C. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity. D. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity. E. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and entities. PROJECTS

Correct Answer: C

Examine the following query: SQL> SELECT prod_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS ONLY; What is the output of this query? A. It displays 5 percent of the products with the highest amount sold. B. It displays the first 5 percent of the rows from the SALES table. C. It displays 5 percent of the products with the lowest amount sold. D. It results in an error because the ORDER BY clause should be the last clause.

Correct Answer: A

Which normal form is a table in if it has no multi-valued attributes and no partial dependencies? A. second normal form B. first normal form C. third normal form D. fourth normal form

Correct Answer: D

The BOOKS_TRANSACTIONS table exists in your database. SQL>SELECT * FROM books_transactions ORDER BY 3; What is the outcome on execution? A. The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name. B. Rows are displayed in the order that they are stored in the table only for the three rows with the lowest values in the key column. C. Rows are displayed in the order that they are stored in the table only for the first three rows. D. Rows are displayed sorted in ascending order of the values in the third column in the table.

Correct Answer: B

The BOOKS_TRANSACTIONS table exists in your schema in this database. You execute this SQL statement when connected to your schema in your database instance. SQL> SELECT * FROM books_transactions ORDER BY 3; What is the result? A. The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name. B. All table rows are displayed sorted in ascending order of the values in the third column. C. The first three rows in the table are displayed in the order that they are stored. D. Only the three rows with the lowest values in the key column are displayed in the order that they are stored.

Correct Answer: ACE

Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.) A. Finding the lowest value B. Finding the quotient C. Raising to a power D. Subtraction E. Addition

Correct Answer: D

The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues this GRANT command: GRANT ALL -ON orders, order_items -TO PUBLIC; What must be done to fix the statement? A. PUBLIC should be replaced with specific usernames. B. ALL should be replaced with a list of specific privileges. C. WITH GRANT OPTION should be added to the statement. D. Separate GRANT statements are required for the ORDERS and ORDER_ITEMS tables.

Correct Answer: D

These are the steps for a correlated subquery, listed in random order: 1. The WHERE clause of the outer query is evaluated. 2. A candidate row is fetched from the table specified in the outer query. 3. This is repeated for the subsequent rows of the table, until all the rows are processed. 4. Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query. Which is the correct sequence in which the Oracle server evaluates a correlated subquery? A. 2, 1, 4, 3 B. 4, 1, 2, 3 C. 4, 2, 1, 3 D. 2, 4, 1, 3

Correct Answer: A

Which statement adds a column called SALARY to the EMPLOYEES table having 100 rows, which cannot contain null? A. ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) DEFAULT 0 NOT NULL; B. ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL; C. ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) DEFAULT NOT NULL; D. ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) NOT NULL;

Correct Answer: C

Which statement correctly grants a system privilege? A. GRANT CREATE VIEW ON table1 TO user1; B. GRANT ALTER TABLE TO PUBLIC; C. GRANT CREATE TABLE TO user1, user2; D. GRANT CREATE SESSION TO ALL;

Correct Answer: C

Which statement is true about Data Manipulation Language (DML)? A. DML automatically disables foreign ley constraints when modifying primary key values in the parent table. B. Each DML statement forms a transaction by default. C. A transaction can consist of one or more DML statements. D. DML disables foreign key constraints when deleting primary key values in the parent table, only when the ON DELETE CASCADE option is set for the foreign key constraint.

Correct Answer: A

Which statement is true about Enterprise Manager (EM) express in Oracle Database 12c? A. By default, EM express is available for a database after database creation. B. You can use EM express to manage multiple databases running on the same server. C. You can perform basic administrative tasks for pluggable databases by using the EM express interface. D. You cannot start up or shut down a database Instance by using EM express. E. You can create and configure pluggable databases by using EM express.

Correct Answer: B

Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.) A. During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution. B. During execution, the Oracle server may read data from storage if the required data is not already in memory. C. During row source generation, rows that satisfy the query are retrieved from the database and stored in memory. D. During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.

Correct Answer: B

Which statement is true about an inner join specified in a query's WHERE clause? A. It only applies for equijoin conditions. B. It applies for equijoin and nonequijoin conditions. C. It requires column names to be the same in all tables being joined. D. It must have primary-key and foreign-key constraints defined on the join columns.

Correct Answer: C

Which statement is true about an inner join specified in the WHERE clause of a query? A. It must have primary-key and foreign-key constraints defined on the columns used in the join condition. B. It requires the column names to be the same in all tables used for the join conditions. C. It is applicable for equijoin and nonequijoin conditions. D. It is applicable for only equijoin conditions.

Correct Answer: B

Which statement is true about transactions? A. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction. B. Each Data Definition Language (DDL) statement executed forms a single transaction. C. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction. D. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.

Correct Answer A

Which statement is true regarding external tables? A. The CREATE TABLE AS SELECT statement can be used to upload data into a normal table in the database from an external table. B. The data and metadata for an external table are stored outside the database. C. The default REJECT LIMIT for external tables is UNLIMITED. D. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.

Correct Answer: D

Which statement is true regarding the INTERSECT operator? A. The names of columns in all SELECT statements must be identical. B. It ignores NULL values. C. Reversing the order of the intersected tables alters the result. D. The number of columns and data types must be identical for all SELECT statements in the query.

Correct Answer: D

Which statement is true regarding the SESSION_PRIVS dictionary view? A. It contains the object privileges granted to other users by the current user session. B. It contains the system privileges granted to other users by the current user session. C. It contains the current object privileges available in the user session. D. It contains the current system privileges available in the user session.

Correct Answer: B

Which statement is true regarding the UNION operator? A. By default, the output is not sorted. B. Null values are not ignored during duplicate checking. C. Names of all columns must be identical across all select statements. D. The number of columns selected in all select statements need not be the same.

Correct Answer: CD

Which statement is true regarding the USING clause in table joins? (Choose two.) A. It can be used to join a maximum of three tables. B. It can be used to access data from tables through equijoins as well as nonequijoins. C. It can be used to join tables that have columns with the same name and compatible data types. D. It can be used to restrict the number of columns used in a NATURAL join.

Correct Answer: A

Which statement is true regarding the default behavior of the ORDER BY clause? A. In a character sort, the values are case-sensitive. B. NULL values are not considered at all by the sort operation. C. Only those columns that are specified in the SELECT list can be used in the ORDER BY clause. D. Numeric values are displayed from the maximum to the minimum value if they have decimal positions.

Correct Answer: C

Which task can be performed by using a single Data Manipulation Language (DML) statement? A. adding a column constraint while inserting a row into a table B. adding a column with a default value while inserting a row into a table C. removing all data only from a single column on which a unique constraint is defined D. removing all data only from a single column on which a primary key constraint is defined

Correct Answer: ACE

Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.) A. SELECT TO_CHAR (1890.55, '$99G999D00') FROM DUAL B. SELECT TO_CHAR (1890.55, '$9,999V99') FROM DUAL; C. SELECT TO_CHAR (1890.55, '$0G000D00') FROM DUAL; D. SELECT TO_CHAR (1890.55, '$99,999D99') FROM DUAL; E. SELECT TO_CHAR (1890.55, '$99G999D99') FROM DUAL

Correct Answer: ABE

Which three statements are true about multiple-row subqueries? A. They can contain a subquery within a subquery. B. They can return multiple columns as well as rows. C. They cannot contain a subquery within a subquery. D. They can return only one column but multiple rows. E. They can contain group functions and GROUP BY and HAVING clauses. F. They can contain group functions and the GROUP BY clause, but not the HAVING clause.

Correct Answer: ABE

Which three statements are true regarding group functions? (Choose three.) A. They can be used on columns or expressions. B. They can be passed as an argument to another group function. C. They can be used only with a SQL statement that has the GROUP BY clause. D. They can be used on only one column in the SELECT clause of a SQL statement. E. They can be used along with the single-row function in the SELECT clause of a SQL statement.

Correct Answer: AEF

Which three statements are true regarding single-row functions? (Choose three.) A. The data type returned can be different from the data type of the argument that is referenced. B. They can return multiple values of more than one data type. C. They can accept only one argument. D. They can be nested up to only two levels. E. They can be used in SELECT, WHERE, and ORDER BY clauses. F. They can accept column names, expressions, variable names, or a user-supplied constants as arguments.

Correct Answer: ADF

Which three statements are true regarding subqueries? (Choose three.) A. A Main query can have many subqueries. B. A subquery can have more than one main query. C. The subquery and main query must retrieve date from the same table. D. The subquery and main query can retrieve data from different tables. E. Only one column or expression can be compared between the subquery and main query. F. Multiple columns or expressions can be compared between the subquery and main query.

Correct Answer: ACD

Which three statements are true regarding subqueries? (Choose three.) A. Multiple columns or expressions can be compared between the main query and subquery. B. Subqueries can contain ORDER BY but not the GROUP BY clause. C. Main query and subquery can get data from different tables. D. Subqueries can contain GROUP BY and ORDER BY clauses. E. Main query and subquery must get data from the same tables. F. Only one column or expression can be compared between the main query and subquery.

Correct Answer: ABC

Which three statements are true regarding subqueries? (Choose three.) A. The ORDER BY Clause can be used in a subquery. B. A subquery can be used in the FROM clause of a SELECT statement. C. If a subquery returns NULL, the main query may still return rows. D. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause. E. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

Correct Answer: ADE

Which three statements are true regarding the SQL WHERE and HAVING clauses? A. The HAVING clause conditions can have aggregating functions. B. The HAVING clause conditions can use aliases for the columns. C. The WHERE and HAVING clauses cannot be used together in a SQL statement. D. The WHERE clause is used to exclude rows before grouping data. E. The HAVING clause is used to exclude one or more aggregated results after grouping data.

Correct Answer: BDE

Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.) A. WHERE and HAVING clauses cannot be used together in a SQL statement. B. The HAVING clause conditions can have aggregate functions. C. The HAVING clause conditions can use aliases for the columns. D. The WHERE clause is used to exclude rows before the grouping of data. E. The HAVING clause is used to exclude one or more aggregated results after grouping data.

Correct Answer: ABE

Which three statements are true regarding the data types? (Choose three.) A. The minimum column width that can be specified for a VARCHAR2 data type column is one. B. Only one LONG column can be used per table. C. A TIMESTAMP data type column stores only time values with fractional seconds. D. The BLOB data type column is used to store binary data in an operating system file. E. The value for a CHAR data type column is blank-padded to the maximum defined column width.

Correct Answer: ABD

Which three statements are true regarding the usage of the WITH clause in complex correlated subqueries? (Choose three.) A. It can be used only with the SELECT clause. B. The WITH clause can hold more than one query. C. If the query block name and the table name are the same, then the table name takes precedence. D. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block

Correct Answer: CDF

Which three statements are true? (Choose three.) A. The data dictionary is created and maintained by the database administrator. B. The data dictionary views consist of joins of dictionary base tables and user-defined tables. C. The usernames of all the users including database administrators are stored in the data dictionary. D. The USER_CONS_COLUMNS view should be queried to find the names of columns to which constraints apply. E. Both USER_OBJECTS and CAT views provide the same information about all objects that are owned by the user. F. Views with the same name but different prefixes, such as DBA, ALL and USER, reference the same base tables from the data dictionary.

Correct Answer: ADE

Which three statements indicate the end of a transaction? (Choose three.) A. after a CREATE statement is issued B. after a SAVEPOINT is issued C. after a SELECT statement is issued D. after a ROLLBACK is issued E. after a COMMIT is issued

Correct Answer: ABC

Which three tasks can be performed using SQL functions built into Oracle Database? A. displaying a date in a nondefault format B. finding the number of characters in an expression C. substituting a character string in a text expression with a specified string D. combining more than two columns or expressions into a single column in the output

Correct Answer: CE

Which two are the minimal requirements for a self-join? (Choose two.) A. Only equijoin conditions may be used in the query. B. Outer joins must not be used in the query. C. There must be a condition on which the self-join is performed. D. No other condition except the self-join may be specified. E. The table used for the self-join must have two different alias names in the query.

Correct Answer: CE

Which two partitioned table maintenance operations support asynchronous Global Index Maintenance in Oracle database 12c? A. ALTER TABLE SPLIT PARTITION B. ALTER TABLE MERGE PARTITION C. ALTER TABLE TRUNCATE PARTITION D. ALTER TABLE ADD PARTITION E. ALTER TABLE DROP PARTITION F. ALTER TABLE MOVE PARTITION

Correct Answer: BE

Which two statements are true about Data Manipulation Language (DML) statements? A. An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table. B. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table. C. A DELETE FROM..... statement can remove rows based on only a single condition on a table. D. An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a table. E. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table. F. An UPDATE....SET.... statement can modify multiple rows based on only a single condition on a table.

Correct Answer: AD

Which two statements are true about sequences created in a single instance Oracle database? (Choose two.) A. When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement. B. DELETE <sequence_name> would remove a sequence from the database. C. The numbers generated by an explicitly defined sequence can only be used to insert data in one table. D. CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence. E. When a database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.

Correct Answer: BD

Which two statements are true regarding constraints? A. A foreign key column cannot contain null values. B. A column with the UNIQUE constraint can contain null values. C. A constraint is enforced only for INSERT operation on the table. D. A constraint can be disabled even if the constraint column contains data. E. All constraints can be defined at the column level and at the table level.

Correct Answer: CD

Which two statements are true regarding constraints? (Choose two) A. A constraint is enforced only for an INSERT operation on a table. B. A foreign key cannot contain NULL values. C. A column with the UNIQUE constraint can store NULLS. D. You can have more than one column in a table as part of a primary key.

Correct Answer: BC

Which two statements are true regarding constraints? (Choose two.) A. All constraints can be defined at the column level and at the table level. B. A constraint can be disabled even if the constraint column contains data. C. A column with the UNIQUE constraint can contain NULLS. D. A foreign key column cannot contain NULLS. E. A constraint is enforced only for INSERT operations.

Correct Answer: AE

Which two statements are true regarding multiple-row subqueries? (Choose two.) A. They can contain group functions. B. They always contain a subquery within a subquery. C. They use the < ALL operator to imply less than the maximum. D. They can be used to retrieve multiple rows from a single table only. E. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.

Correct Answer: BE

Which two statements are true regarding roles? (Choose two.) A. A role can be granted to itself. B. A role can be granted to PUBLIC. C. A user can be granted only one role at any point of time. D. The REVOKE command can be used to remove privileges but not roles from other users. E. Roles are named groups of related privileges that can be granted to users or other roles.

Correct Answer: AB

Which two statements are true regarding savepoints? (Choose two.) A. Savepoints may be used to ROLLBACK. B. Savepoints can be used for only DML statements. C. Savepoints are effective only for COMMIT. D. Savepoints are effective for both COMMIT and ROLLBACK. E. Savepoints can be used for both DML and DDL statements.

Correct Answer: BD

Which two statements are true regarding single row functions? (Choose two.) A. MOD : returns the quotient of a division. B. TRUNC : can be used with NUMBER and DATE values. C. CONCAT : can be used to combine any number of values. D. SYSDATE : returns the database server current date and time. E. INSTR : can be used to find only the first occurrence of a character in a string. F. TRIM : can be used to remove all the occurrences of a character from a string.

Correct Answer: AC

Which two statements are true regarding subqueries? (Choose two.) A. A subquery can appear on either side of a comparison operator. B. Only two subqueries can be placed at one level. C. A subquery can retrieve zero or more rows. D. A subquery can be used only in SQL query statements. E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

Correct Answer: CE

Which two statements are true regarding subqueries? (Choose two.) A. Only two subqueries can be placed at one level. B. A subquery in the WHERE clause of a SELECT statement can be nested up to three levels only. C. A subquery can be used to access data from one or more tables or views. D. The columns in a subquery must always be qualified with the name or alias of the table used. E. If the subquery returns 0 rows, then the value returned by the subquery expression is NULL.

Correct Answer: AD

Which two statements are true regarding table joins available in the Oracle Database server? (Choose two.) A. You can use the ON clause to specify multiple conditions while joining tables. B. You can explicitly provide the join condition with a NATURAL JOIN. C. You can use the JOIN clause to join only two tables. D. You can use the USING clause to join tables on more than one column.

Correct Answer: AC

Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.) A. The outer query stops evaluating the result set of the inner query when the first value is found. B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query. C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query. D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

Correct Answer: DE

Which two statements are true regarding the SQL GROUP BY clause? A. You can use a column alias in the GROUP BY clause. B. Using the WHERE clause after the GROUP BY clause excludes rows after creating groups. C. The GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause. D. Using the WHERE clause before the GROUP BY clause excludes rows before creating groups. E. If the SELECT clause has an aggregating function, then columns without an aggregating function in the SELECT clause should be included in the GROUP BY clause.

Correct Answer: CD

Which two statements are true regarding the USING and ON clauses in table joins? A. Both USING and ON clauses can be used for equijoins and nonequijoins. B. A maximum of one pair of columns can be joined between two tables using the ON clause. C. The ON clause can be used to join tables on columns that have different names but compatible data types. D. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.

Correct Answer: DE

Which two statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose two.) A. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table. B. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query. C. The WHERE clause can be used to exclude rows after dividing them into groups. D. The HAVING clause can be used with aggregate functions in subqueries. E. The WHERE clause can be used to exclude rows before dividing them into groups.

Correct Answer: AD

Which two statements are true regarding the execution of the correlated subqueries? (Choose two.) A. The nested query executes after the outer query returns the row. B. The nested query executes first and then the outer query executes. C. The outer query executes only once for the result returned by the inner query. D. Each row returned by the outer query is evaluated for the results returned by the inner query.

Correct Answer: BC

Which two statements are true regarding views? (Choose two.) A. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view. B. The OR REPLACE option is used to change the definition of an existing view without dropping and re-creating it. C. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword. D. Rows added through a view are deleted from the table automatically when the view is dropped. E. A simple view in which column aliases have been used cannot be updated. F. A subquery used in a complex view definition cannot contain group functions or joins.

Correct Answer: AD

Which two statements are true regarding working with dates? (Choose two.) A. The RR date format automatically calculates the century from the SYSDATE function but allows the session user to enter the century. B. The RR date format automatically calculates the century from the SYSDATE function and does not allow a session user to enter the century. C. The default internal storage of dates is in character format. D. The default internal storage of dates is in numeric format.

Correct Answer: AB

Which two statements best describe the benefits of using the WITH clause? (Choose two.) A. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace. B. It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query. C. It enables sessions to store a query block permanently in memory and use it to create complex queries. D. It enables sessions to store the results of a query permanently.

Correct Answer: AC

Which two tasks can be performed by using Oracle SQL statements? (Choose two.) A. changing the password for an existing database user B. connecting to a database instance C. querying data from tables in different databases D. starting up a database instance E. executing operating system (OS) commands in a session

Correct Answer: CF

You are designing the structure of a table in which two columns have the specifications: "" must be able to contain a maximum of 12 alphanumeric characters and must uniquely identify the row COMPONENT_ID -"" contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons EXECUTION_DATETIME -between components. Which two options define the data types that satisfy these requirements most efficiently? (Choose two.) A. The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type. B. The EXECUTION_DATETIME must be of TIMESTAMP data type. C. The EXECUTION_DATETIME must be of DATE data type. D. The COMPONENT_ID must be of ROWID data type. E. The COMPONENT_ID must be of VARCHAR2 data type. F. The COMPONENT_ID column must be of CHAR data type.

Correct Answer: D

You execute the following commands: SQL > DEFINE hiredate = '01-APR-2011' SQL >SELECT employee_id, first_name, salary FROM employees WHERE hire_date > '&hiredate' AND manager_id > &mgr_id; For which substitution variables are you prompted for the input? A. none, because no input required B. both the substitution variables ''hiredate' and 'mgr_id'. C. only hiredate' D. only 'mgr_id'

Correct Answer: AC

You executed the following CREATE TABLE statement that resulted in an error: SQL> CREATE TABLE employees (emp_id NUMBER(10) PRIMARY KEY, ename VARCHAR2(20), email NUMBER(3) UNIQUE, address VARCHAR2(500), phone VARCHAR2(20), resume LONG, hire_date DATE, remarks LONG, dept_id NUMBER(3) CONSTRAINT emp_dept_id_fk REFERENCES departments (dept_id), CONSTRAINT ename_nn NOT NULL(ename)); Identify two reasons for the error. A. The NOT NULL constraint on the ENAME column must be defined as the column level B. FOREIGN KEY defined on the DEPT_ID column must be at the table level only C. Only one LONG column can be used per table D. The FOREIGN KEY keyword is missing in the constraint definition E. The PRIMARY KEY constraint in the EMP_ID column must have a name and must be defined at the table level only

Correct Answer: ABD

You issue the following command to drop the PRODUCTS table: SQL > DROP TABLE products; Which three statements are true about the implication of this command? (Choose three.) A. All data along with the table structure is deleted. B. A pending transaction in the session is committed. C. All indexes on the table remain but they are invalidated. D. All views and synonyms on the table remain but they are invalidated. E. All data in the table is deleted but the table structure remains.

Correct Answer: ABC

You issue this command which succeeds: SQL> DROP TABLE products; Which three statements are true? A. All existing views and synonyms that refer to the table are invalidated but retained. B. Any uncommitted transaction in the session is committed. C. Table data and the table structure are deleted. D. All the table's indexes if any exist, are invalidated but retained. E. Table data is deleted but the table structure is retained.

Correct Answer: BCF

You issued this command: SQL > DROP TABLE employees; Which three statements are true? (Choose three.) A. Sequences used in the EMPLOYEES table become invalid. B. If there is an uncommitted transaction in the session, it is committed. C. All indexes and constraints defined on the table being dropped are also dropped. D. The space used by the EMPLOYEES table is always reclaimed immediately. E. The EMPLOYEES table can be recovered using the ROLLBACK command. F. The EMPLOYEES table may be moved to the recycle bin.

Correct Answer: D

You must create a table for a banking application. One of the columns in the table has these requirements: 1: A column to store the duration of a short team loan 2: The data should be stored in a format supporting DATE arithmetic with DATE datatypes without using conversion functions. 3: The maximum loan period is 30 days. 4: Interest must be calculated based on the number of days for which the loan remains unpaid. Which data type would you use? A. DATE B. NUMBER C. TIMESTAMP D. INTERVAL DAY TO SECOND E. INTERVAL YEAR TO MONTH

Correct Answer: D

You must display details of all users whose username contains the string 'ch_'. (Choose the best answer.) Which query generates the required output? A. SELECT * FROM users Where user_name LIKE '%ch_'; B. SELECT * FROM users Where user_name LIKE '%ch_%'ESCAPE'%'; C. SELECT * FROM users Where user_name LIKE 'ch\_%' ESCAPE '_'; D. SELECT * FROM users Where user_name LIKE '%ch\_%' ESCAPE '\';

Correct Answer: C

You must write a query that prompts users for column names and conditions every time it is executed. The user must be prompted only once for the table name. Which statement achieves those objectives? A. SELECT &col1, '&col2' FROM &table WHERE &&condition = '&cond'; B. SELECT &col1, &col2 FROM "&table" WHERE &condition = &cond; C. SELECT &col1, &col2 FROM &&table WHERE &condition = &cond; D. SELECT &col1, &col2 FROM &&table WHERE &condition = &&cond

Correct Answer: A

You need to display the date 11-oct-2007 in words as "˜Eleventh of October, Two Thousand Seven'. Which SQL statement would give the required result? A. SELECT TO_CHAR (TO_DATE ('11-oct-2007'), "˜fmDdthsp "of" Month, Year') FROM DUAL B. SELECT TO_CHAR ("˜11-oct-2007', "˜fmDdspth "of" Month, Year') FROM DUAL C. SELECT TO_CHAR (TO_DATE ("˜11-oct-2007'), "˜fmDdspth of month, year') FROM DUAL D. SELECT TO_DATE (TO_CHAR ('11-oct-2007'), "˜fmDdspth "of" Month, Year')) FROM DUAL

Correct Answer: B

You notice a performance change in your production Oracle 12c database. You want to know which change caused this performance difference. Which method or feature should you use? A. Compare Period ADDM report. B. AWR Compare Period report. C. Active Session History (ASH) report. D. Taking a new snapshot and comparing it with a preserved snapshot.

Correct Answer: A

You want to display 5 percent of the rows from the SALES table for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same even if this causes the output to exceed 5 percent of the rows. AMOUNT_SOLD - Which query will provide the required result? A. SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS WITH TIES; B. SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS ONLY WITH TIES; C. SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS WITH TIES ONLY; D. SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS ONLY;

Correct Answer: B

You want to display details of all members who reside in states starting with the letter A followed by exactly one character. Which SQL statement must you execute? A. SELECT * FROM MEMBERS WHERE state LIKE '%A_'; B. SELECT * FROM MEMBERS WHERE state LIKE 'A_'; C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%'; D. SELECT * FROM MEMBERS WHERE state LIKE 'A%';

Correct Answer: D

You want to find the total value of all the orders for each year and issue this command: SQL> SELECT TO_CHAR(order_date,'rr'), SUM(order_total) FROM orders GROUP BY TO_CHAR(order_date, 'yyyy'); Which statement is true regarding the result? A. It executes successfully but does not give the correct output. B. It executes successfully and gives the correct output. C. It returns an error because the TO_CHAR function is not valid. D. It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

Correct Answer: C

You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10,000.Evaluate the following two queries: SQL> SELECT cust_last_name, cust_credit_limit FROM customers WHERE (UPPER(cust_last_name) LIKE "˜A%' OR UPPER (cust_last_name) LIKE "˜B%' OR UPPER (cust_last_name) LIKE "˜C%') AND cust_credit_limit < 10000; SQL>SELECT cust_last_name, cust_credit_limit FROM customers WHERE UPPER (cust_last_name) BETWEEN "˜A' AND "˜C' AND cust_credit_limit < 10000; Which statement is true regarding the execution of the above queries? A. Only the second query gives the correct result B. Both execute successfully but do not give the required result C. Only the first query gives the correct result D. Both execute successfully and give the same result