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

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: 41

Evaluate the CREATE TABLE statement:

CREATE TABLE products

(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY, product_name VARCHAR2(15));

Which statement is true regarding the PROD_ID_PK constraint?

  1. It would be created only if a unique index is manually created first.

  2. It would be created and would use an automatically created unique index.

  3. It would be created and would use an automatically created nonunique index.

  4. It would be created and remains in a disabled state because no index is specified in the command.

Answer: B

Question No: 42

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

You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those orders that were placed after the last order placed by the customer whose CUSTOMER_ID is 101

Which query would give you the desired output?

Latesttests 2018 PDF and VCE

  1. SELECT order_id, order_date FROM orders WHERE order_date gt; ALL (SELECT MAX(order_date) FROM orders) AND

    Customer_id = 101;

  2. SELECT order_id, order_date FROM orders WHERE order_date gt; ANY (SELECT order_date FROM orders

    WHERE customer_id = 101);

  3. SELECT order_id, order_date FROM orders WHERE order_date gt; ALL (SELECT order_date FROM orders

    WHERE customer_id = 101);

  4. SELECT order_id, order_date FROM orders WHERE order_date IN (SELECT order_date

FROM orders

WHERE customer id = 101);

Answer: C

Question No: 43

You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL If a new customer has a credit limit greater than 10,000, then the details have to be inserted into CUST_SPECIAL All new customer details have to be inserted into the CUST table. Which technique should be used to load the data most efficiently?

  1. external table

  2. the MERGE command

  3. the multitable INSERT command

  4. INSERT using WITH CHECK OPTION

Answer: C

Question No: 44

View the Exhibit and examine the data in the EMPLOYEES tables.

Latesttests 2018 PDF and VCE

Evaluate the following SQL statement: SELECT employee_id, department_id

FROM employees

WHERE department_id= 50 ORDER BY department_id UNION

SELECT employee_id, department_id FROM employees

WHERE department_id= 90 UNION

SELECT employee_id, department_id FROM employees

WHERE department_id= 10;

What would be the outcome of the above SQL statement?

  1. The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.

  2. The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.

  3. The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.

  4. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.

Answer: D

Question No: 45

Evaluate the following command:

CREATE TABLE employees

(employee_id NUMBER(2) PRIMARY KEY, last_name VARCHAR2(25) NOT NULL,

department_id NUMBER(2), job_id VARCHAR2(8), salary NUMBER(10,2));

You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization:

CREATE OR REPLACE VIEW sales_staff_vu AS SELECT employee_id, last_name job_id

FROM employees

WHERE job_id LIKE #39;SA_%#39; WITH CHECK OPTION;

Which statements are true regarding the above view? (Choose all that apply.)

  1. It allows you to insert details of all new staff into the EMPLOYEES table.

  2. It allows you to delete the details of the existing sales staff from the EMPLOYEES table.

  3. It allows you to update the job ids of the existing sales staff to any other job id in the EMPLOYEES table.

  4. It allows you to insert the IDs, last names and job ids of the sales staff from the view if it is used in multitable INSERT statements.

Answer: B,D

Question No: 46

View the Exhibit and examine the data in EMPLOYEES and

Latesttests 2018 PDF and VCE

DEPARTMENTS tables. In the EMPLOYEES table EMPLOYEE_ID is the PRIMARY KEY and DEPARTMENT_ID is the FOREIGN KEY. In the DEPARTMENTS table DEPARTMENT_ID is the PRIMARY KEY.

Evaluate the following UPDATE statement: UPDATE employees a

SET department_jd = (SELECT department_id FROM departments

WHERE location_id = ‘2100’), (salary, commission_pct) =

(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct) FROM employees b

WHERE a. department_jd = b. department_id) WHERE first_name|| #39;||last_name = #39;Amit Banda#39;;

What would be the outcome of the above statement?

  1. It would execute successfully and update the relevant data.

  2. It would not execute successfully because there is no LOCATION_ID 2100 in the DEPARTMENTS table.

  3. It would not execute successfully because the condition specified with the concatenation operator is not valid.

  4. It would not execute successfully because multiple columns (SALARY,COMMISSION_PCT)cannot be used in an UPDATE statement.

Answer: A

Question No: 47

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

Latesttests 2018 PDF and VCE

Your manager asked you to get the SALES_REP_ID and the total numbers of orders placed by each of the sales representatives. Which statement would provide the desired result?

  1. SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders

    GROUP BY sales_rep_id;

  2. SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders

    GROUP BY sales_rep_id, total_orders;

  3. SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders;

  4. SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders

WHERE sales_rep_id IS NOT NULL;

Answer: A

Question No: 48

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

Latesttests 2018 PDF and VCE

You want to display the expiration date of the warranty for a product. Which SQL statement would you execute?

  1. SELECT product_id, SYSDATE warranty_period FROM product_information;

  2. SELECT product_jd, TO_YMINTERVAL(warranty_period) FROM product_information;

  3. SELECT product_id, TO_YMINTERVAL(SYSDATE) warranty_period FROM product_information;

  4. SELECT product_jd, TO_YMINTERVAL(SYSDATE warranty_period) FROM product_information;

Answer: A

Question No: 49

View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables.

In the ORDERS table, ORDER_ID is the PRIMARY KEY and ORDER_DATE has the DEFAULT value as SYSDATE.

Latesttests 2018 PDF and VCE

Evaluate the following statement: UPDATE orders

SET order_date=DEFAULT

WHERE order_id IN (SELECT order_id FROM order_items WHERE qty IS NULL);

What would be the outcome of the above statement?

  1. The UPDATE statement would not work because the main query and the subquery use different tables.

  2. The UPDATE statement would not work because the DEFAULT value can be used only in INSERT statements.

  3. The UPDATE statement would change all ORDER_DATE values to SYSDATE provided the current ORDER_DATE is NOT NULL and QTY is NULL

  4. The UPDATE statement would change all the ORDER_DATE values to SYSDATE irrespective of what the current ORDER_DATE value is for all orders where QTY is NULL

Answer: D

Question No: 50

View the Exhibit and examine the structure of the PRODUCT INFORMATION table.

Latesttests 2018 PDF and VCE

Which two queries would work? (Choose two.)

  1. SELECT product_name FROM product_information

    WHERE list_price = (SELECT AVG(list_price) FROM product_information);

  2. SELECT product_status FROM product_information GROUP BY product_status

    WHERE list_price lt; (SELECT AVG(list_price) FROM product_information);

  3. SELECT product_status FROM product_information GROUP BY product_status

    HAVING list_price gt; (SELECT AVG(list_price) FROM product_information);

  4. SELECT product_name FROM product_jnformation

WHERE list_price lt; ANY(SELECT AVG(list_price) FROM product_jnformation

GROUP BY product_status);

Answer: A,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