1Z0-051 | All About Tested 1Z0-051 dumps

Q111. - (Topic 1)

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

Using the PROMOTIONS table, you need to display the names of all promos done after

January 1, 2001 starting with the latest promo.

Which query would give the required result? (Choose all that apply.)

A. SELECT promo_name,promo_begin_date

FROM promotions

WHERE promo_begin_date > '01-JAN-01'

ORDER BY 1 DESC;

B. SELECT promo_name,promo_begin_date "START DATE"

FROM promotions

WHERE promo_begin_date > '01-JAN-01'

ORDER BY "START DATE" DESC;

C. SELECT promo_name,promo_begin_date

FROM promotions

WHERE promo_begin_date > '01-JAN-01'

ORDER BY 2 DESC;

D. SELECT promo_name,promo_begin_date

FROM promotions

WHERE promo_begin_date > '01-JAN-01'

ORDER BY promo_name DESC;

Answer: B,C

Q112. - (Topic 2)

The DBA issues this SQL command:

CREATE USER Scott

IDENTIFIED by tiger;

What privileges does the user Scott have at this point?

A. No privileges.

B. Only the SELECT privilege.

C. Only the CONNECT privilege.

D. All the privileges of a default user.

Answer: A

Explanation:

There are no privileges for the user Scott at this point. They are not added themselves to

the user immediately after creation. The DBA needs to grant all privileges explicitly.

Incorrect Answers

B:There are no privileges for the user Scott at this point. SELECT privilege needs to be

added to the user Scott.

C:There are no privileges for the user Scott at this point. CONNECT privilege needs to be

added to the user Scott.

D:There is no default user in Oracle.

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

Chapter 8: User Access in Oracle

Q113. - (Topic 1)

View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables.

Evaluate the following SQL statements:

Which statement is true regarding the above compound query?

A. It shows products that have a cost recorded irrespective of sales

B. It shows products that were sold and have a cost recorded

C. It shows products that were sold but have no cost recorded

D. It reduces an error

Answer: C

Q114. - (Topic 2)

Examine the structure and data in the PRICE_LIST table:

Name Null Type

PROD_ID NOT NULL NUMBER(3) PROD_PRICE VARCHAR2(10) PROD_ID PROD_PRICE

100 $234.55 101 $6,509.75 102 $1,234

You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.

Which SQL statement would give the required result?

A. SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST;

B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST;

C. SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;

D. SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;

Answer: B

Explanation: Use TO_NUMBER on the prod_price column to convert from char to number

to be able to multiply it with 0.25. Then use the TO_CHAR function (with

formatting'$99,999.00') to convert the number back to char.

Incorrect:

Not C: Use the formatting'$99,999.00' with the TO_CHAR function, not with the

TO_NUMBER function.

Note:

Using the TO_CHAR Function The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available. The syntax is as follows: TO_CHAR(number1, [format], [nls_parameter]), The number1 parameter is mandatory and must be a value that either is or can be implicitly converted into a number. The optional format parameter may be used to specify numeric formatting information like width, currency symbol, the position of a decimal point, and group (or thousands) separators and must be enclosed in single

Syntax of Explicit Data Type Conversion Functions TO_NUMBER(char1, [format mask], [nls_parameters]) = num1 TO_CHAR(num1, [format mask], [nls_parameters]) = char1 TO_DATE(char1, [format mask], [nls_parameters]) = date1 TO_CHAR(date1, [format mask], [nls_parameters]) = char1

Q115. - (Topic 1)

View the Exhibits and examine the structures of the PRODUCTS SALES and CUSTOMERS tables.

You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for all customers in Tokyo'. Which two queries give the required result? (Choose two.)

SELECT c.cust_last_name,p.prod_name, s.quantity_sold FROM sales s JOIN products p

USING(prod_id)

JOIN customers c

USING(cust_id)

WHERE c.cust_city='Tokyo'

SELECT c.cust_last_name, p.prod_name, s.quantity_sold

FROM products p JOIN sales s JOIN customers c

ON(p.prod_id=s.prod_id)

ON(s.cust_id=c.cust_id)

WHERE c.cust_city='Tokyo'

SELECT c.cust_last_name, p.prod_name, s.quantity_sold

FROM products p JOIN sales s

ON(p.prod_id=s.prod_id)

JOIN customers c

ON(s.cust_id=c.cust_id)

AND c.cust_city='Tokyo'

SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold FROM

products p JOIN sales s

USING(prod_id)

JOIN customers c

USING(cust_id)

WHERE c.cust_city='Tokyo'

Answer: A,C

Q116. - (Topic 2)

View the Exhibit and examine the data in the EMPLOYEES table:

You want to display all the employee names and their corresponding manager names.

Evaluate the following query:

SQL> SELECT e.employee_name "EMP NAME", m.employee_name "MGR NAME"

FROM employees e ______________ employees m

ON e.manager_id = m.employee_id;

Which JOIN option can be used in the blank in the above query to get the required output?

Exhibit:

A. only inner JOIN

B. only FULL OUTER JOIN

C. only LEFT OUTER JOIN

D. only RIGHT OUTER JOIN

Answer: C

Q117. - (Topic 2)

What is true of using group functions on columns that contain NULL values?

A. Group functions on columns ignore NULL values.

B. Group functions on columns returning dates include NULL values.

C. Group functions on columns returning numbers include NULL values.

D. Group functions on columns cannot be accurately used on columns that contain NULL values.

E. Group functions on columns include NULL values in calculations if you use the keyword INC_NULLS.

Answer: A

Explanation: group functions on column ignore NULL values

Incorrect Answer: Bgroup functions on column ignore NULL values Cgroup functions on column ignore NULL values DNVL function can be use for column with NULL values Eno such INC_NULLS keyword

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-12

Q118. - (Topic 1)

Evaluate the SQL statement:

TRUNCATE TABLE DEPT;

Which three are true about the SQL statement? (Choose three.)

A. It releases the storage space used by the table.

B. It does not release the storage space used by the table.

C. You can roll back the deletion of rows after the statement executes.

D. You can NOT roll back the deletion of rows after the statement executes.

E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.

F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table

Answer: A,D,F

Explanation:

A: The TRUNCATE TABLE Statement releases storage space used by the table,

D: Can not rollback the deletion of rows after the statement executes,

F: You must be the owner of the table or have DELETE ANY TABLE system privilege to truncate the DEPT table.

Incorrect Answer: Cis not true Dis not true Eis not true

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-18

Q119. - (Topic 1)

Which two statements about sub queries are true? (Choose two.)

A. A sub query should retrieve only one row.

B. A sub query can retrieve zero or more rows.

C. A sub query can be used only in SQL query statements.

D. Sub queries CANNOT be nested by more than two levels.

E. A sub query CANNOT be used in an SQL query statement that uses group functions.

F. When a sub query is used with an inequality comparison operator in the outer SQL statement, the column list in the SELECT clause of the sub query should contain only one column.

Answer: B,F

Explanation: Explanation: sub query can retrieve zero or more rows, sub query is used with an inequality comparison operator in the outer SQL statement, and the column list in the SELECT clause of the sub query should contain only one column.

Incorrect Answer: Asub query can retrieve zero or more rows Csub query is not SQL query statement Dsub query can be nested Egroup function can be use with sub query

Q120. - (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: A

1Z0-051 | All About Tested 1Z0-051 dumps

View the entire exam for free: 1Z0-051 Exam Questions List

Related 1Z0-051 posts: