NEW QUESTION 1
Which statement is true regarding external tables?

• A. The CREATE TABLE AS SELECT statement can be used to upload data into regular 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.

Answer: A

Explanation: References:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm

NEW QUESTION 2
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.

Answer: CE

NEW QUESTION 3
Examine the structure of the EMPLOYEES table. NameNull?Type
---------------------- ------------ EMPLOYEE_IDNOT NULLNUMBER(6) FIRST_NAMEVARCHAR2(20) LAST_NAMENOT NULLVARCHAR2(25) EMAILNOT NULLVARCHAR2(25) PHONE NUMBERVARCHAR2(20) HIRE_DATENOT NULLDATE JOB_IDNOT NULLVARCHAR2(10) SALARYNUMBER(8,2) COMMISSION_PCTNUMBER(2,2) MANAGER_IDNUMBER(6) DEPARTMENT_IDNUMBER(4)
There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_ID is 123.
Which query provides the correct output?

• A. SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.manager_id = m.employee_id)AND e.employee_id = 123;
• B. SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.employee_id = m.manager_id)WHERE e.employee_id = 123;
• C. SELECT e.last_name, e.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.employee_id = m.employee_id)WHERE e.employee_id = 123;
• D. SELECT m.last_name, e.manager_idFROM employees e LEFT OUTER JOIN employees mon (e.manager_id = m.manager_id)WHERE e.employee_id = 123;

Answer: B

NEW QUESTION 4
Which two statement 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.

Answer: AD

NEW QUESTION 5
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.

Answer: ABC

NEW QUESTION 6
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.

Answer: A

Explanation: References:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm

NEW QUESTION 7
View the Exhibit and examine the structure of the PORDUCT_INFORMATION table. (Choose the best answer.)

PRODUCT_ID column is the primary key. You create an index using this command: SQL > CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
No other indexes exist on the PRODUCT_INFORMATION table. Which query would use the UPPER_NAME_IDX index?

• A. SELECT product_id, UPPER(product_name)FROM product_informationWHERE UPPER(product_name) = 'LASERPRO' OR list_price > 1000;
• B. SELECT UPPER(product_name)FROM product_information;
• C. SELECT UPPER(product_name)FROM product_informationWHERE product_id = 2254;
• D. SELECT product_idFROM product_informationWHERE UPPER(product_name) IN ('LASERPRO', 'CABLE');

Answer: D

NEW QUESTION 8
View the Exhibit and examine the structure of the PROMOTIONS table.

Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?

• A. It produces an error because subqueries cannot be used with the CASE expression.
• B. It shows COST_REMARK for all the promos in the promo category ‘TV’.
• C. It shows COST_REMARK for all the promos in the table.
• D. It produces an error because the subquery gives an error.

Answer: C

NEW QUESTION 9
View the Exhibit and examine the description of the ORDERS table. (Choose two.)

Which two WHERE clause conditions demonstrate the correct usage of conversion functions?

• A. WHERE Order_date_IN ( TO_DATE('OCT 21 2003', 'MON DD YYYY'), TO_CHAR('NOV 21 2003', 'MON DD YYYY') )
• B. WHERE Order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')
• C. WHERE TO_CHAR(Order_date, 'MON DD YYYY') = 'JAN 20 2003'
• D. WHERE Order_date > ( TO_DATE('JUL 10 2006', 'MON DD YYYY')

Answer: CD

NEW QUESTION 10
View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)

You executed this SQL statement: SELECT first_name, department_id, salary FROM employees
ORDER BY department_id, first_name, salary desc; Which two statements are true regarding the result?

• A. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.
• B. The values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.
• C. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
• D. The values in the all columns would be returned in descending order.
• E. The values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.

Answer: AB

NEW QUESTION 11
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

Answer: ABD

NEW QUESTION 12
In which normal form is a table, 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

Answer: A

Explanation: References:
https://blog.udemy.com/database-normal-forms/

NEW QUESTION 13
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.

Answer: C

NEW QUESTION 14
View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.

The PROD_ID column is the foreign key in the SALES table referencing the PRODUCTS table.
The CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Examine this command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id FROM sales;
Which statement is true?

• A. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
• B. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
• C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
• D. The NEW_SALES table would get created and all the NOT NULL constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.

Answer: D

NEW QUESTION 15
Examine the structure of the PROMOTIONS table: (Choose the best answer.)

Management requires a report of unique promotion costs in each promotion category. Which query would satisfy this requirement?

• A. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1
• B. SELECT promo_category, DISTINCT promo_cost FROM promotions
• C. SELECT DISTINCT promo_cost, promo_category FROM promotions
• D. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;

Answer: A

NEW QUESTION 16
You are designing the structure of a table in which two columns have the specifications:
COMPONENT_ID – must be able to contain a maximum of 12 alphanumeric characters and uniquely identify the row
EXECUTION_DATETIME – contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons between components.
Which two options define the data types that satisfy these requirements most efficiently?

• 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.

Answer: CF

NEW QUESTION 17
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.

Answer: D

NEW QUESTION 18
Which two statements are true regarding the COUNT function?

• 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 NULL values in the INV_AMT column.
• C. COUNT (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.
• D. COUNT (*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns.
• E. The COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data types.

Answer: BD