We promise you full success together with our Oracle Oracle practice braindumps. Otherwise, you can get 100% refund. You need to email us your failed transcript along with relevant data; then you will get the money involving purchasing charges back since soon since possible. Dont worry about your individual details, many of us keep them strictly confidential. However, should you require extra information, you are able to contact us by means of email or are living chat. Our assistance staff is seasoned and knowledgeable, who will certainly able to answer your entire questions satisfactorily.

2017 Jan oracle 1z0-051 exam questions:

Q191. - (Topic 2) 

View the exhibit and examine the description for the SALES and CHANNELS tables. 

You issued the following SQL statement to insert a row in the SALES table: 

INSERT INTO sales VALUES (23, 2300, SYSDATE, (SELECT channel_id FROM channels WHERE channel_desc='Direct Sales'), 12, 1, 500); 

Which statement is true regarding the execution of the above statement? 

A. The statement will execute and the new row will be inserted in the SALES table. 

B. The statement will fail because subquery cannot be used in the VALUES clause. 

C. The statement will fail because the VALUES clause is not required with subquery. 

D. The statement will fail because subquery in the VALUES clause is not enclosed with in single quotation marks. 

Answer:


Q192. - (Topic 2) 

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

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: 

Which statement is true regarding the execution of the above queries? 

A. Only the first query gives the correct result. 

B. Only the second query gives the correct result. 

C. Both execute successfully and give the same result. 

D. Both execute successfully but do not give the required result. 

Answer:


Q193. - (Topic 2) 

You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMERS table has these columns: 

CUST_IDNUMBER(4)NOT NULL CUST_NAMEVARCHAR2(100)NOT NULL CUST_ADDRESSVARCHAR2(150) 

CUST_PHONEVARCHAR2(20) 

Which SELECT statement accomplishes this task? 

A. SELECT * FROM customers 

B. SELECT name, address FROM customers; 

C. SELECT id, name, address, phone FROM customers; 

D. SELECT cust_name, cust_address FROM customers; 

E. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers; 

Answer:

Explanation: 

This answer provides correct list of columns for the output. 

Incorrect Answers 

A:This answer does not provide correct list of columns for the output. It is not required to 

show all columns of the table. Symbol “*” is used in the SELECT command to substitute a 

list of all columns of the table. 

B:This answer does not provide correct list of columns for the output. There are not NAME 

and ADDRESS columns in the CUSTOMERS table. 

C:This answer does not provide correct list of columns for the output. There are not ID, 

NAME, ADDRESS or PHONE columns in the CUSTOMERS table. 

E:This answer does not provide correct list of columns for the output. It is not required to 

show all columns of the table. 

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 20-24 

Chapter 1: Overview of Oracle Databases 


Q194. - (Topic 1) 

Which three statements/commands would cause a transaction to end? (Choose three.) 

A. COMMIT 

B. SELECT 

C. CREATE 

D. ROLLBACK 

E. SAVEPOINT 

Answer: A,C,D 


Q195. - (Topic 1) 

View the Exhibit to examine the description for the SALES table. Which views can have all DML operations performed on it? (Choose all that apply.) 

A. CREATE VIEW v3 AS SELECT * FROM SALES WHERE cust_id = 2034 WITH CHECK OPTION; 

B. CREATE VIEW v1 AS SELECT * FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION; 

C. CREATE VIEW v2 AS SELECT prod_id, cust_id, time_id FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION; 

D. CREATE VIEW v4 AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES WHERE time_id <= SYSDATE - 2*365 GROUP BY prod_id, cust_id WITH CHECK OPTION; 

Answer: A,B 

Explanation: 

Creating a View You can create a view by embedding a subquery in the CREATE VIEW statement. In the syntax: CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; OR REPLACE Re-creates the view if it already exists FORCE Creates the view regardless of whether or not the base tables exist NOFORCE Creates the view only if the base tables exist (This is the default.) View Is the name of the view alias Specifies names for the expressions selected by the view’s query (The number of aliases must match the number of expressions selected by the view.) subquery Is a complete SELECT statement (You can use aliases for the columns in the SELECT list.) WITH CHECK OPTION Specifies that only those rows that are accessible to the view can be inserted or updated ANSWER D constraint Is the name assigned to the CHECK OPTION constraint WITH READ ONLY Ensures that no DML operations can be performed on this view Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view – ANSWER C 


Up to date 1z0-051 free dumps:

Q196. - (Topic 2) 

Which SQL statement accepts user input for the columns to be displayed, the table name, and WHERE condition? 

A. SELECT &1, "&2" 

FROM &3 

WHERE last_name = '&4' 

B. SELECT &1, '&2' 

FROM &3 

WHERE '&last_name = '&4' ' 

C. SELECT &1, &2 

FROM &3 

WHERE last_name = '&4' 

D. SELECT &1, '&2' 

FROM EMP 

WHERE last_name = '&4' 

Answer:

Explanation: 

In a WHERE clause, date and characters values must be enclosed within single quotation marks. 

Sample of the correct syntax 

SELECT EMPLOYEE_ID, &COLUMN_NAME FROM EMPLOYEES 

Incorrect Answers : 

A. Incorrect use of " symbol 

B. Incorrect use of ' symbol 

D. No input for table name as EMP has been use in the statement. 

Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Producing Readable Output with iSQL*PLUS, p. 7-8 


Q197. - (Topic 1) 

You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit: 

Examine the structure of PRODUCTS table. 

Using the PRODUCTS table, you issue the following query to generate the names, current list price and discounted list price for all those products whose list price fails below $10 after a discount of 25% is applied on it. 

Exhibit: 

The query generates an error. 

What is the reason of generating error? 

A. The column alias should be put in uppercase and enclosed within double quotation marks in the WHERE clause 

B. The parenthesis should be added to enclose the entire expression 

C. The column alias should be replaced with the expression in the WHERE clause 

D. The double quotation marks should be removed from the column alias 

Answer:

Explanation: Note: You cannot use column alias in the WHERE clause. 


Q198. - (Topic 2) 

View the Exhibit and examine the data in the PROJ_TASK_DETAILS table. 

The PROJ_TASK_DETAILS table stores information about tasks involved in a project and the relation between them. 

The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on the completion of any other tasks. 

You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, and the name of the employee in charge of the task it depends on. 

Which query would give the required result? 

A. 

SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.based_on = d.task_id); 

B. 

SELECT p.task_id, p.based_on, d.task_in_charge 

FROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on = 

d.task_id); 

C. 

SELECT p.task_id, p.based_on, d.task_in_charge 

FROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on = 

d.task_id); 

D. 

SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.task_id = d.task_id); 

Answer:


Q199. - (Topic 1) 

View the Exhibit and examine the description for the CUSTOMERS table. 

You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID 2566. 

Which UPDATE statement will accomplish the task? 

A. 

UPDATE customers SET cust_income_level = (SELECT cust_income_level FROM customers WHERE cust_id = 2560), cust_credit_limit = (SELECT cust_credit_limit FROM customers WHERE cust_id = 2566) WHERE cust_id=2360; 

B. 

UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 OR cust_id=2566) WHERE cust_id=2360; 

C. 

UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id IN(2560, 2566) WHERE cust_id=2360; 

D. 

UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 AND cust_id=2566) WHERE cust_id=2360; 

Answer:

Explanation: 

Updating Two Columns with a Subquery 

You can update multiple columns in the SET clause of an UPDATE statement by writing 

multiple subqueries. The syntax is as follows: 

UPDATE table 

SET column = 

(SELECT column 

FROM table 

WHERE condition) 

[ , 

column = 

(SELECT column 

FROM table 

WHERE condition)] 

[WHERE condition ] ; 


Q200. - (Topic 2) 

View the Exhibit and examine the structure of CUSTOMERS table. Evaluate the following query: 

Which statement is true regarding the above query? 

A. It executes successfully. 

B. It produces an error because the condition on the CUST_CITY column is not valid. 

C. It produces an error because the condition on the CUST_FIRST_NAME column is not valid. 

D. It produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid. 

Answer: