Our pass rate is high to 98.9% and the similarity percentage between our oracle 1z0 051 study guide and real exam is 90% based on our seven-year educating experience. Do you want achievements in the Oracle 1z0 051 latest dumps free download pdf exam in just one try? I am currently studying for the Oracle 1z0 051 practice test exam. Latest Oracle 1z0 051 dumps pdf Test exam practice questions and answers, Try Oracle oracle 1z0 051 Brain Dumps First.

Q11. - (Topic 1) 

User Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She has the privilege to create a public synonym, and would like to create a synonym for this view that can be used by all users of the database. 

Which SQL statement can Mary use to accomplish that task? 

A. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu; 

B. CREATE PUBLIC SYNONYM EDL:VU FOR mary (emp_dept_loc_vu); 

C. CREATE PUBLIC SYNONYM EDL_VU FOR emp_dept_loc_vu; 

D. CREATE SYNONYM EDL_VU ON emp_dept_loc_vu FOR EACH USER; 

E. CREATE SYNONYM EDL_VU FOR EACH USER ON emp_dept_loc_vu; 

F. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu FOR ALL USERS; 

Answer:

Explanation: 

The general syntax to create a synonym is: 

CREATE [PUBLIC] SYNONYM synonym FOR object; 


Q12. - (Topic 2) 

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

Evaluate the following SQL statement: 

SQL>SELECT promo_name,CASE 

WHEN promo_cost >=(SELECT AVG(promo_cost) 

FROM promotions 

WHERE promo_category='TV') 

then 'HIGH' 

else 'LOW' 

END COST_REMARK 

FROM promotions; 

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

A. It shows COST_REMARK for all the promos in the table. 

B. It produces an error because the subquery gives an error. 

C. It shows COST_REMARK for all the promos in the promo category 'TV'. 

D. It produces an error because subqueries cannot be used with the CASE expression. 

Answer:


Q13. - (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:


Q14. - (Topic 1) 

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

Exhibit: 

Which two SQL statements would execute successfully? (Choose two.) 

A. 

UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000' 

B. 

SELECT promo_begin_date FROM promotions WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98' 

C. 

UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000',8)); 

D. 

SELECT TO_CHAR(promo_begin_date,'dd/month') FROM promotions 

WHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98')); 

Answer: A,B 


Q15. - (Topic 2) 

Evaluate the SQL statement: 

SELECT ROUND(45.953, -1), TRUNC(45.936, 2) 

FROM dual; 

Which values are displayed? 

A. 46 and 45 

B. 46 and 45.93 

C. 50 and 45.93 

D. 50 and 45.9 

E. 45 and 45.93 

F. 45.95 and 45.93 

Answer:

Explanation: 

ROUND (45.953,-1) will round value to 1 decimal places to the left. TRUNC (45.936,2) will truncate value to 2 decimal The answer will be 50 and 45.93 

Incorrect Answers : 

A. Does not meet round and truncate functions 

B. Does not meet round functions 

D. Does not meet truncate functions 

E. Does not meet round functions 

F. Does not meet round functions 

Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Single-Row functions, p. 3-13 


Q16. - (Topic 2) 

Examine the structure of the ORDERS table: 

You want to find the total value of all the orders for each year and issue the following command: 

SQL>SELECT TO_CHAR(order_date,'rr'), SUM(order_total) 

FROM orders 

GROUP BY TO_CHAR(order_date,'yyyy'); 

Which statement is true regarding the outcome? 

A. It executes successfully and gives the correct output. 

B. It gives an error because the TO_CHAR function is not valid. 

C. It executes successfully but does not give the correct output. 

D. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause. 

Answer:


Q17. - (Topic 1) 

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

A. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns 

B. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column 

C. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column 

D. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE clause 

E. The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types 

Answer: A,C 

Explanation: 

Using the COUNT Function 

The COUNT function has three formats: 

COUNT(*) 

COUNT(expr) 

COUNT(DISTINCT expr) 

COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT 

statement, including duplicate rows and rows containing null values in any of the columns. 

If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of 

rows that satisfy the condition in the WHERE clause. 

In contrast, 

COUNT(expr) returns the number of non-null values that are in the column identified by 

expr. 

COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the 

column identified by expr. 


Q18. - (Topic 2) 

Examine the structure of the CUSTOMERS table: 

CUSTNO is the PRIMARY KEY in the table. You want to find out if any customers' details have been entered more than once using different CUSTNO, by listing all the duplicate names. 

Which two methods can you use to get the required result? (Choose two.) 

A. self-join 

B. subquery 

C. full outer-join with self-join 

D. left outer-join with self-join 

E. right outer-join with self-join 

Answer: A,B 


Q19. - (Topic 1) 

Evaluate the following SQL commands: 

The command to create a table fails. Identify the reason for the SQL statement failure? 

(Choose all that apply.) 

A. You cannot use SYSDATE in the condition of a CHECK constraint. 

B. You cannot use the BETWEEN clause in the condition of a CHECK constraint. 

C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column. 

D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD NO is also the FOREIGN KEY. 

Answer: A,C 

Explanation: 

CHECK Constraint The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions: References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows A single column can have multiple CHECK constraints that refer to the column in its 

definition. 

There is no limit to the number of CHECK constraints that you can define on a column. 

CHECK constraints can be defined at the column level or table level. 

CREATE TABLE employees 

(... 

salary NUMBER(8,2) CONSTRAINT emp_salary_min 

CHECK (salary > 0), 


Q20. - (Topic 2) 

Examine the data in the CUSTOMERS table: 

You want to list all cities that have more than one customer along with the customer details. Evaluate the following query: 

SQL>SELECT c1.custname, c1.city FROM Customers c1 __________________ Customers c2 ON (c1.city=c2.city AND c1.custname<>c2.custname); 

Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.) 

A. JOIN 

B. NATURAL JOIN 

C. LEFT OUTER JOIN 

D. FULL OUTER JOIN 

E. RIGHT OUTER JOIN 

Answer: A,E