1Z0-051 | All About Precise 1Z0-051 pdf


Q41. - (Topic 1) 

Which two statements are true regarding views? (Choose two.) 

A. A sub query that defines a view cannot include the GROUP BY clause 

B. A view is created with the sub query having the DISTINCT keyword can be updated 

C. A Data Manipulation Language (DML) operation can be performed on a view that is created with the sub query having all the NOT NULL columns of a table 

D. A view that is created with the sub query having the pseudo column ROWNUM keyword cannot be updated 

Answer: C,D 

Explanation: 

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 

Q42. - (Topic 2) 

SLS is a private synonym for the SH.SALES table. 

The user SH issues the following command: 

DROP SYNONYM sls; 

Which statement is true regarding the above SQL statement? 

A. Only the synonym would be dropped. 

B. The synonym would be dropped and the corresponding table would become invalid. 

C. The synonym would be dropped and the packages referring to the synonym would be dropped. 

D. The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid. 

Answer:

Explanation: 

A synonym is an alias for a table (or a view). Users can execute SQL statements against the synonym, and the database will map them into statements against the object to which the synonym points. 

Private synonyms are schema objects. Either they must be in your own schema, or they must be qualified with the schema name. Public synonyms exist independently of a schema. A public synonym can be referred to by any user to whom permission has been granted to see it without the need to qualify it with a schema name. 

Private synonyms must be a unique name within their schema. Public synonyms can have the same name as schema objects. When executing statements that address objects without a schema qualifier, Oracle will first look for the object in the local schema, and only if it cannot be found will it look for a public synonym. 

Q43. - (Topic 1) 

View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables. 

You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007. 

You issue the following query: 

Which statement is true regarding the above query? 

A. It executes successfully and gives the required result. 

B. It executes successfully but does not give the required result. 

C. It produces an error because the join order of the tables is incorrect. 

D. It produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECT statement. 

Answer:

Q44. - (Topic 2) 

Evaluate this SQL statement: 

SELECT ename, sal, 12*sal+100 FROM emp; 

The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"? 

A. No change is required to achieve the desired results. 

B. SELECT ename, sal, 12*(sal+100) FROM emp; 

C. SELECT ename, sal, (12*sal)+100 FROM emp; 

D. SELECT ename, sal+100,*12 FROM emp; 

Answer:

Explanation: 

to achieve the result you must add 100 to sal before multiply with 12. Select ename, sal, 12*(sal+100) from EMP; 

Incorrect Answer: AMultiplication and division has priority over addition and subtraction in Operator precedence. CGive wrong results DWrong syntax 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-11 

Q45. - (Topic 1) 

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

You need to display all promo categories that do not have 'discount' in their subcategory. 

Which two SQL statements give the required result? (Choose two.) 

A. 

SELECT promo_category FROM promotions MINUS SELECT promo_category FROM promotions WHERE promo_subcategory = 'discount' 

B. 

SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM promotions WHERE promo_subcategory = 'discount' 

C. 

SELECT promo_category FROM promotions MINUS SELECT promo_category FROM promotions WHERE promo_subcategory <> 'discount' 

D. 

SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM promotions WHERE promo_subcategory <> 'discount' 

Answer: A,D 

Q46. - (Topic 2) 

Examine the structure of the TRANSACTIONS table: 

Name Null Type 

TRANS_ID NOT NULL NUMBER(3) 

CUST_NAME VARCHAR2(30) 

TRANS_DATE TIMESTAMP 

TRANS_AMT NUMBER(10,2) 

You want to display the date, time, and transaction amount of transactions that where done before 12 noon. 

The value zero should be displayed for transactions where the transaction amount has not been entered. 

Which query gives the required result? 

A. 

SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), 

TO_CHAR(trans_amt,'$99999999D99') 

FROM transactions 

WHERE TO_NUMBER(TO_DATE(trans_date,'hh24')) < 12 AND 

COALESCE(trans_amt,NULL)<>NULL; 

B. 

SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), 

NVL(TO_CHAR(trans_amt,'$99999999D99'),0) 

FROM transactions 

WHERE TO_CHAR(trans_date,'hh24') < 12; 

C. 

SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), 

COALESCE(TO_NUMBER(trans_amt,'$99999999.99'),0) 

FROM transactions 

WHERE TO_DATE(trans_date,'hh24') < 12; 

D. 

SELECT TO_DATE (trans_date,'dd-mon-yyyy hh24:mi:ss'), 

NVL2(trans_amt,TO_NUMBER(trans_amt,'$99999999.99'), 0) 

FROM transactions 

WHERE TO_DATE(trans_date,'hh24') < 12; 

Answer:

Q47. - (Topic 2) 

Which two statements complete a transaction? (Choose two) 

A. DELETE employees; 

B. DESCRIBE employees; 

C. ROLLBACK TO SAVEPOINT C; 

D. GRANT SELECT ON employees TO SCOTT; 

E. ALTER TABLE employeesSET UNUSED COLUMN sal; 

F. Select MAX(sal)FROM employeesWHERE department_id = 20; 

Answer: D,E 

Explanation: 

D: GRANT is a DML operation which will cause an implicit commit 

E: It is important to understand that an implicit COMMIT occurs on the database when a user exits SQL*Plus or issues a data-definition language (DDL) command such as a CREATE TABLE statement, used to create a database object, or an ALTER TABLE statement, used to alter a database object. 

Incorrect Answers A:The DELETE command is data-manipulation language (DML) command and it does not complete a transaction. B:The DESCRIBE command is internal SQL*Plus command and it has nothing to do with completion a transaction. 

C: ROLLBACK is not used to commit or complete a transaction, it is used to undo a transaction F:SELECT command is used to retrieve data. It does not complete a transaction. 

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 281-282 Chapter 3: Advanced Data Selection in Oracle 

Q48. - (Topic 2) 

Examine the structure of the PROMOS table: 

You want to generate a report showing promo names and their duration (number of days). 

If the PROMO_END_DATE has not been entered, the message 'ONGOING' should be displayed. Which queries give the correct output? (Choose all that apply.) 

A. SELECT promo_name, TO_CHAR(NVL(promo_end_date -promo_start_date,'ONGOING')) FROM promos; 

B. SELECT promo_name,COALESCE(TO_CHAR(promo_end_date -promo_start_date),'ONGOING') FROM promos; 

C. SELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date),'ONGOING') FROM promos; 

D. SELECT promo_name, DECODE(promo_end_date 

-promo_start_date,NULL,'ONGOING',promo_end_date - promo_start_date) FROM 

promos; 

E. SELECT 

promo_name,ecode(coalesce(promo_end_date,promo_start_date),null,'ONGOING', 

promo_end_date - promo_start_date) 

FROM promos; 

Answer: B,C,D 

Q49. - (Topic 1) 

Examine the structure and data of the CUSTJTRANS table: 

CUSTJRANS 

Name Null? Type 

CUSTNO NOT NULL CHAR(2) TRANSDATE DATE TRANSAMT NUMBER(6.2) CUSTNO TRANSDATE TRANSAMT 

11 01-JAN-07 1000 

22 01-FEB-07 2000 

33 01-MAR-07 3000 

Dates are stored in the default date format dd-mon-rr in the CUSTJTRANS table. Which three SQL statements would execute successfully? (Choose three.) 

A. SELECT transdate + '10' FROM custjrans; 

B. SELECT * FROM custjrans WHERE transdate = '01-01-07': 

C. SELECT transamt FROM custjrans WHERE custno > '11': 

D. SELECT * FROM custjrans WHERE transdate='01-JANUARY-07': 

E. SELECT custno - 'A' FROM custjrans WHERE transamt > 2000: 

Answer: A,C,D 

Q50. - (Topic 2) 

What is true about sequences? 

A. Once created, a sequence belongs to a specific schema. 

B. Once created, a sequence is linked to a specific table. 

C. Once created, a sequence is automatically available to all users. 

D. Only the DBA can control which sequence is used by a certain table. 

E. Once created, a sequence is automatically used in all INSERT and UPDATE statements. 

Answer: