Facts about oracle 1z0 051

Pass4sure offers free demo for 1z0 051 dumps pdf exam. "Oracle Database: SQL Fundamentals I", also known as 1z0 051 dumps pdf exam, is a Oracle Certification. This set of posts, Passing the Oracle 1z0 051 dumps pdf exam, will help you answer those questions. The 1z0 051 dumps pdf Questions & Answers covers all the knowledge points of the real exam. 100% real Oracle 1z0 051 dumps pdf exams and revised by experts!


♥♥ 2021 NEW RECOMMEND ♥♥

Free VCE & PDF File for Oracle 1Z0-051 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 1Z0-051 Exam Dumps (PDF & VCE):
Available on: http://www.surepassexam.com/1Z0-051-exam-dumps.html

Q101. - (Topic 1) 

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

Exhibit: 

and examine the structure of CUSTOMRS AND SALES tables: 

Evaluate the following SQL statement: 

Exhibit: 

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

A. It would not execute because the SELECT statement cannot be used in place of the table name 

B. It would execute and restrict modifications to only the column specified in the SELECT statement 

C. It would not execute because a sub query cannot be used in the WHERE clause of an UPDATE statement 

D. It would not execute because two tables cannot be used in a single UPDATE statement 

Answer:


Q102. - (Topic 2) 

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

Which two tasks would require subqueries? (Choose two.) 

A. Display the minimum list price for each product status. 

B. Display all suppliers whose list price is less than 1000. 

C. Display the number of products whose list price is more than the average list price. 

D. Display the total number of products supplied by supplier 102 and have product status as 'obsolete'. 

E. Display all products whose minimum list price is more than the average list price of products and have the status 'orderable'. 

Answer: C,E 


Q103. - (Topic 1) 

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

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category. 

Which query would give you the required output? 

A. 

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions )AND promo_category = 'INTERNET' 

B. 

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category='INTERNET'); 

C. 

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET'); 

D. 

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ANY (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET'); 

Answer:


Q104. - (Topic 2) 

EMPLOYEES and DEPARTMENTS data: EMPLOYEES 

DEPARTMENTS 

On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID managers and refers to the EMPLOYEE_ID. 

On the DEPARTMENTS table DEPARTMENT_ID is the primary key. 

Evaluate this UPDATE statement. 

UPDATE employees SET mgr_id = (SELECT mgr_id FROMemployees WHERE dept_id= (SELECT department_id FROM departments WHERE department_name = 'Administration')), Salary = (SELECT salary 

FROM employees 

WHERE emp_name = 'Smith') 

WHERE job_id = 'IT_ADMIN' 

What happens when the statement is executed? 

A. The statement executes successfully, leaves the manager ID as the existing value, and changes the salary to 4000 for the employees with ID 103 and 105. 

B. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105. 

C. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105. 

D. The statement fails because there is more than one row matching the employee name Smith. 

E. The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES table. 

F. The statement fails because there is no 'Administration' department in the DEPARTMENTS table. 

Answer:

Explanation: 

'=' is use in the statement and sub query will return more than one row. 

Employees table has 2 row matching the employee name Smith. 

The update statement will fail. 

Incorrect Answers : 

A. The Update statement will fail no update was done. 

B. The update statement will fail no update was done. 

C. The update statement will fail no update was done. 

E. The update statement will fail but not due to job_it='IT_ADMIN' 

F. The update statement will fail but not due to department_id='Administration' 

Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Sub queries, p. 6-12 


Q105. - (Topic 2) 

Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME 

Lex De Haan Renske Ladwig Jose Manuel Urman 

Jason Mallin 

You want to extract only those customer names that have three names and display the * symbol in place of the 

first name as follows: 

CUST NAME 

*** De Haan 

**** Manuel Urman 

Which two queries give the required output? (Choose two.) 

A. 

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') 

"CUST NAME" FROM customers 

WHERE INSTR(cust_name, ' ',1,2)<>0; 

B. 

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') 

"CUST NAME" FROM customers 

WHERE INSTR(cust_name, ' ',-1,2)<>0; 

C. 

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,''),'*') "CUST NAME" 

FROM customers 

WHERE INSTR(cust_name, ' ',-1,-2)<>0; 

D. 

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,' '),'*') "CUST NAME" 

FROM customers 

WHERE INSTR(cust_name, ' ',1,2)<>0 ; 

Answer: A,B 


Q106. - (Topic 2) 

Examine the structure of the SHIPMENTS table: 

You want to generate a report that displays the PO_ID and the penalty amount to be paid if 

the 

SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day. 

Evaluate the following two queries: 

Which statement is true regarding the above commands? 

A. Both execute successfully and give correct results. 

B. Only the first query executes successfully but gives a wrong result. 

C. Only the first query executes successfully and gives the correct result. 

D. Only the second query executes successfully but gives a wrong result. 

E. Only the second query executes successfully and gives the correct result. 

Answer:

Explanation: 

The MONTHS_BETWEEN(date 1, date 2) function returns the number of months between two dates: months_between('01-FEB-2008','01-JAN-2008') = 1 The DECODE Function Although its name sounds mysterious, this function is straightforward. The DECODE function implements if then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. DECODE Function Facilitates conditional inquiries by doing the work of a CASE expression or an IF-THENELSE statement: DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default]) DECODE Function The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic that is used in various languages. The DECODE function decodes expression after comparing it to each search value. If the expression is the same as search, result is returned. 

If the default value is omitted, a null value is returned where a search value does not match any of the result values. 


Q107. - (Topic 1) 

You need to generate a list of all customer last names with their credit limits from the CUSTOMERS table. Those customers who do not have a credit limit should appear last in the list. Winch two queries would achieve the required result? (Choose two.) 

A. SELECT cust_last_name. cust_credit_limit FROM customers ORDER BY cust_credit_limit DESC: 

B. SELECT cust_last_name. cust_credit_limit FROM customers ORDER BY cust_credit_limit: 

C. SELECT cust_last_name. cust_credit_limit FROM customers ORDER BY cust_credit_limit NULLS LAST: 

D. SELECT cust_last_name. cust_credit_limit FROM customers ORDER BY cust_last_name. cust_credit_limit NULLS LAST: 

Answer: B,C 

Explanation: 

If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order. Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence. ANSWER C Sorting The default sort order is ascending: 

Numeric values are displayed with the lowest values first (for example, 1 to 999). 

Date values are displayed with the earliest value first (for example, 01-JAN-92 before 01-JAN-95). 

Character values are displayed in the alphabetical order (for example, “A” first and “Z” last). 

Null values are displayed last for ascending sequences and first for descending sequences. 

-ANSWER B 

. You can also sort by a column that is not in the SELECT list. 


Q108. - (Topic 1) 

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

Exhibit: 

You issue the following SQL statement: 

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

A. It produces an error because the AMT_SPENT column contains a null value. 

B. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT. 

C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null. 

D. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function. 

Answer:

Explanation: 

The NULLIF Function The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned. 


Q109. - (Topic 1) 

You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command: 

Exhibit: 

PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table. 

What is the outcome? 

A. Number of days since the promo started based on the current Chicago data and time 

B. Number of days since the promo started based on the current Singapore data and time. 

C. An error because the WHERE condition specified is invalid 

D. An error because the ROUND function specified is invalid 

Answer:


Q110. - (Topic 1) 

Examine the structure of the EMPLOYEES table: 

Which INSERT statement is valid? 

A. 

INSERT INTO employees (employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ‘01/01/01’); 

B. 

INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ’01 January 01’); 

C. 

INSERT INTO employees(employee_id, first_name, last_name, Hire_date) VALUES ( 1000, ‘John’, ‘Smith’, To_date(‘01/01/01’)); 

D. 

INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, 01-Jan-01); 

Answer:

Explanation: It is the only statement that has a valid date; all other will result in an error. Answer A is incorrect, syntax error, invalid date format