[Free] 2018(Jan) Latesttests Dumpsleader Oracle 1z0-047 Dumps with VCE and PDF Download 91-100

Latesttests 2018 Jan Oracle Official New Released 1z0-047
100% Free Download! 100% Pass Guaranteed!
http://www.Latesttests.com/1z0-047.html

Oracle Database SQL Expert

Question No: 91

Which statements are true? (Choose all that apply.)

  1. The data dictionary is created and maintained by the database administrator.

  2. The data dictionary views can consist of joins of dictionary base tables and user-defined tables.

  3. The usernames of all the users including the database administrators are stored in the data dictionary.

  4. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.

  5. Both USER_ODBJECTS and CAT views provide the same information about all the objects that are owned by the user.

  6. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary

Answer: C,D,F

Question No: 92

Which three statements are true? (Choose three.)

  1. Only one LONG column can be used per table.

  2. ATIMESTAMP data type column stores only time values with fractional seconds.

  3. The BLOB data type column is used to store binary data in an operating system file.

  4. The minimum column width that can be specified for a varchar2 data type column is one.

  5. The value for a CHAR data type column is blank-padded to the maximum defined column width.

Answer: A,D,E

Question No: 93

Which statement best describes the GROUPING function?

  1. It is used to set the order for the groups to be used for calculating the grand totals and subtotals.

  2. It is used to form various groups to calculate total and subtotals created using ROLLUP and CUBE operators.

  3. It is used to identify if the NULL value in an expression is a stored NULL value or created by ROLLUP or CUBE.

  4. It is used to specify the concatenated group expressions to be used for calculating the grand totals and subtotals.

Answer: C

Question No: 94

View the Exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.

Latesttests 2018 PDF and VCE

To retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written:

SELECT employee_id, first_name, department_name FROM employees NATURAL JOIN departments;

The desired output is not obtained after executing the above SQL statement. What could be the reason for this?

  1. The NATURAL JOIN clause is missing the USING clause.

  2. The table prefix is missing for the column names in the SELECT clause.

  3. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.

  4. The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.

Answer: D

Question No: 95

View the Exhibit and examine the structure of EMPLOYEES and JOB_HISTORY tables.

The EMPLOYEES table maintains the most recent information regarding salary, department, and job for all the employees. The JOB_HISTORY table maintains the record for all the job changes for the employees. You want to delete all the records from the JOB_HISTORY table that are repeated in the EMPLOYEES table.

Latesttests 2018 PDF and VCE

Which two SQL statements can you execute to accomplish the task? (Choose two.)

  1. DELETE

    FROM job_history j WHERE employee_id = (SELECT employee_id FROM employees e

    WHERE j.employee_id = e.employee_id) AND job_id = (SELECT job_id

    FROM employees e WHERE j.job_id = e.job_id);

  2. DELETE

    FROM job_history j

    WHERE (employee_id, job_id) = ALL (SELECT employee_id, job_id FROM employees e

    WHERE j.employee_id = e.employee_id and j.job_id = e.job_id)

  3. DELETE

    FROM job_history j WHERE employee_id = (SELECT employee_id FROM employees e

    WHERE j.employee_id = e.employee_id and j.job_id = e.job_id)

  4. DELETE

FROM job_history j

WHERE (employee_id, job_id) = (SELECT employee_id, job_id FROM employees e

WHERE j.employee_id = e.employee_id and j.job_id = e.job_id)

Answer: C,D

Question No: 96

View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:

SELECT p.product_name, i.item_cnt

FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items

GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id;

Latesttests 2018 PDF and VCE

What would happen when the above statement is executed?

  1. The statement would execute successfully to produce the required output.

  2. The statement would not execute because inline views and outer joins cannot be used together.

  3. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.

  4. The statement would not execute because the GROUP BY clause cannot be used in the inline view.

Answer: A

Question No: 97

View the Exhibit and examine the description of the EMPLOYEES table.

You want to display the EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT_ID for all the employees who work in the same department and have the same manager as that of the

employee having EMPLOYEE_ID 104. To accomplish the task, you execute the following SQL statement:

SELECT employee_id, first_name, department_id FROM employees

WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees WHERE employee_id = 104)

AND employee_id lt;gt; 104;

Latesttests 2018 PDF and VCE

When you execute the statement it does not produce the desired output. What is the reason for this?

  1. The WHERE clause condition in the main query is using the = comparison operator, instead of EXISTS.

  2. The WHERE clause condition in the main query is using the = comparison operator, instead of the IN operator.

  3. The WHERE clause condition in the main query is using the = comparison operator, instead of the = ANY operator.

  4. The columns in the WHERE clause condition of the main query and the columns selected in the subquery should be in the same order.

Answer: D

Question No: 98

View the Exhibit and examine the structure of the EMP table.

You executed the following command to add a primary key to the EMP table: ALTER TABLE emp

ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id) USING INDEX emp_id_idx;

Latesttests 2018 PDF and VCE

Which statement is true regarding the effect of the command?

  1. The PRIMARY KEY is created along with a new index.

  2. The PRIMARY KEY is created and it would use an existing unique index.

  3. The PRIMARY KEY would be created in a disabled state because it is using an existing index.

  4. The statement produces an error because the USING clause is permitted only in the CREATE TABLE command.

Answer: B

Question No: 99

View the Exhibitl and examine the descriptions of the EMPLOYEES and DEPARTMENTS tables.

Latesttests 2018 PDF and VCE

The following SQL statement was executed:

SELECT e.department_id, e.job_id, d.location_id, sum(e.salary) total, GROUPING(e.department_id)GRP_DEPT,

GROUPING(e.job_id) GRPJOB,

GROUPING(d. location_id) GRP_LOC FROM employees e JOIN departments d ON e.department_id = d.department_id

GROUP BY ROLLUP (e.department_id, e.job_id, d.location_id); View the Exhibit2 and examine the output of the command.

Latesttests 2018 PDF and VCE

Which two statements are true regarding the output? (Choose two.)

  1. The value 1 in GRP_LOC means that the LOCATION_ID column is taken into account to generate the subtotal.

  2. The value 1 in GRP_JOB and GRP_LOC means that JOB_ID and LOCATION_ID columns are not taken into account to generate the subtotal.

  3. The value 1 in GRP_JOB and GRP_LOC means that the NULL value in JOB_ID and

    LOCATIONJD columns are taken into account to generate the subtotal.

  4. The value 0 in GRP_DEPT, GRPJOB, and GRP_LOC means that DEPARTMENT_ID, JOB_ID, and LOCATION_ID columns are taken into account to generate the subtotal

Answer: B,D

Question No: 100

Which statements are correct regarding indexes? (Choose all that apply.)

  1. When a table is dropped, the corresponding indexes are automatically dropped.

  2. For each DML operation performed, the corresponding indexes are automatically updated.

  3. Indexes should be created on columns that are frequently referenced as part of an expression.

  4. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.

Answer: A,B,D

100% Free Download!
Download Free Demo:1z0-047 Demo PDF
100% Pass Guaranteed!
Download 2018 Latesttests 1z0-047 Full Exam PDF and VCE

Latesttests ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

2018 Latesttests IT Certification PDF and VCE