1Z0-051 Guide

Where to find 1z0 051 latest dumps free download pdf

Your success in Oracle 1z0 051 dumps is our sole target and we develop all our 1z0 051 practice test braindumps in a way that facilitates the attainment of this target. Not only is our 1z0 051 pdf study material the best you can find, it is also the most detailed and the most updated. 1z0 051 dumps pdf Practice Exams for Oracle 1z0 051 latest dumps free download pdf are written to the highest standards of technical accuracy.

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


Q62. - (Topic 2) 

Which two statements about creating constraints are true? (Choose two) 

A. Constraint names must start with SYS_C. 

B. All constraints must be defines at the column level. 

C. Constraints can be created after the table is created. 

D. Constraints can be created at the same time the table is created. 

E. Information about constraints is found in the VIEW_CONSTRAINTS dictionary view. 

Answer: C,D 

Explanation: 

Constraints can be created after the table is created. Use ALTER TABLE command for 

that. 

Constraints can be created at the same time the table is created (CREATE TABLE 

command). 

Incorrect Answers 

A:There is no requirements in Oracle that constraint names must start with SYS_C. Oracle 

can use prefix “SYS” to build indexes for UNIQUE and NOT NULL constraints, but it is not 

required for user to follow this naming rule. 

B:Not all constraints must be defines at the column level. Only NOT NULL constraint must 

be. 

E:There is no VIEW_CONSTRAINTS dictionary view in Oracle. 

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

Chapter 5: Creating Oracle Database Objects 


Q63. - (Topic 1) 

You need to display the date 11-Oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result? 

A. SELECT TO_CHAR('11-oct-2007', 'fmDdspth "of" Month, Year') FROM DUAL; B. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdspth of month, year') FROM DUAL; 

C. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp "of" Month, Year') FROM DUAL; 

D. SELECT TO_DATE(TO_CHAR('11-oct-2007','fmDdspth ''of'' Month, Year')) FROM DUAL; 

Answer:

Explanation: 

Using the TO_CHAR Function with Dates TO_CHAR converts a datetime data type to a value of VARCHAR2 data type in the format specified by the format_model. A format model is a character literal that describes the format of datetime stored in a character string. For example, the datetime format model for the string '11-Nov-1999' is 'DD-Mon-YYYY'. You can use the TO_CHAR function to convert a date from its default format to the one that you specify. Guidelines 

The format model must be enclosed with single quotation marks and is case-sensitive. 

The format model can include any valid date format element. But be sure to separate the date value from the format model with a comma. 

The names of days and months in the output are automatically padded with blanks. 

To remove padded blanks or to suppress leading zeros, use the fill mode fm element. 

Elements of the Date Format Model 

DY Three-letter abbreviation of the day of the week 

DAY Full name of the day of the week 

DD Numeric day of the month 

MM Two-digit value for the month 

MON Three-letter abbreviation of the month 

MONTH Full name of the month 

YYYY Full year in numbers 

YEAR Year spelled out (in English) 


Q64. - (Topic 1) 

You work as a database administrator at ABC.com. You study the exhibit carefully 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 execute and restrict modifications to only the column specified in the SELECT statement 

B. It would not execute because two tables cannot be used in a single UPDATE 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 the SELECT statement cannot be used in place of the table name 

Answer:


Q65. - (Topic 2) 

Examine the structure of the EMPLOYEES table: 

EMPLOYEE_ID NUMBER Primary Key 

FIRST_NAME VARCHAR2(25) 

LAST_NAME VARCHAR2(25) 

Which three statements insert a row into the table? (Choose three.) 

A. INSERT INTO employees VALUES ( NULL, 'John', 'Smith'); 

B. INSERT INTO employees( first_name, last_name) VALUES( 'John', 'Smith'); 

C. INSERT INTO employees VALUES ( 1000, 'John', NULL); 

D. INSERT INTO employees (first_name, last_name, employee_id) VALUES ( 1000, 'John', 'Smith'); 

E. INSERT INTO employees (employee_id) VALUES (1000); 

F. INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, 'John', ' '); 

Answer: C,E,F 

Explanation: EMPLOYEE_ID is a primary key. Incorrect Answer: AEMPLOYEE_ID cannot be null BEMPLOYEE_ID cannot be null Dmismatch of field_name with datatype 

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


Q66. - (Topic 1) 

When does a transaction complete? (Choose all that apply.) 

A. When a PL/SQL anonymous block is executed B. When a DELETE statement is executed 

C. When a data definition language statement is executed 

D. When a TRUNCATE statement is executed after the pending transaction 

E. When a ROLLBACK command is executed 

Answer: C,D,E 


Q67. - (Topic 1) 

Which object privileges can be granted on a view? 

A. none 

B. DELETE, INSERT,SELECT 

C. ALTER, DELETE, INSERT, SELECT 

D. DELETE, INSERT, SELECT, UPDATE 

Answer:

Explanation: Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE. 

Incorrect Answer: AObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE BObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE CObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE 

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


Q68. - (Topic 2) 

Evaluate the following CREATE SEQUENCE statement: 

CREATE SEQUENCE seq1 

START WITH 100 

INCREMENT BY 10 

MAXVALUE 200 

CYCLE 

NOCACHE; 

The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL statement: 

SELECT seq1.nextval FROM dual; 

What is displayed by the SELECT statement? 

A. 1 

B. 10 

C. 100 

D. an error 

Answer:

Explanation: 

But why the answer is not "C" ? Because you didn't specify the MINVALUE for the sequence. If you check the sequence definition that you created it will have the default value of 1, which it reverts to when cycling. If you wanted to keep the minimum value you would need to specify it in the sequence creation. sequence Is the name of the sequence generator INCREMENT BY n Specifies the interval between sequence numbers, where n is an integer (If this clause is omitted, the sequence increments by 1.) START WITH n Specifies the first sequence number to be generated (If this clause is omitted, the sequence starts with 1.) MAXVALUE n Specifies the maximum value the sequence can generate NOMAXVALUE Specifies a maximum value of 10^27 for an ascending sequence and –1 for a descending sequence (This is the default option.) MINVALUE n Specifies the minimum sequence value NOMINVALUE Specifies a minimum value of 1 for an ascending sequence and –(10^26) for a descending sequence (This is the default option.) 

CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after reaching its maximum or minimum value (NOCYCLE is the default option.) CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and keeps in memory (By default, the Oracle server caches 20 values.) 


Q69. - (Topic 2) 

The CUSTOMERS table has these columns: 

A promotional sale is being advertised to the customers in France. Which WHERE clause identifies customers that are located in France? 

A. WHERE lower(country_address) = "france" 

B. WHERE lower(country_address) = 'france' 

C. WHERE lower(country_address) IS 'france' 

D. WHERE lower(country_address) = '%france%' 

E. WHERE lower(country_address) LIKE %france% 

Answer:

Explanation: 

WHERE lower(country_address)=’france’ 

Incorrect Answer: Ainvalid use of symbol “” Cinvalid use of IS keyword Dinvalid use of % in condition Einvalid use of condition Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-12 


Q70. - (Topic 2) 

View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables. 

You want to update the EMPLOYEES table as follows:4 ? 4; 

-Update only those employees who work in Boston or Seattle (locations 2900 and 2700). 

-Set department_id for these employees to the department_id corresponding to London 

 (location_id 2100). 

-Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department. 

-Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department. 

You issue the following command: SQL>UPDATE employees SET department_id = (SELECT department_id FROM departments WHERE location_id = 2100), (salary, commission) = (SELECT 1.1*AVG(salary), 1.5*AVG(commission) FROM employees, departments WHERE departments.location_id IN(2900,2700,2100)) WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700) 

What is the outcome? 

A. It executes successfully and gives the correct result. 

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

C. It generates an error because a subquery cannot have a join condition in an UPDATE statement. 

D. It generates an error because multiple columns (SALARY, COMMISION) cannot be specified together in an UPDATE statement. 

Answer:


To know more about the 1Z0-051, click here.

Tagged as : Oracle 1Z0-051 Dumps, Download 1Z0-051 pdf, 1Z0-051 VCE, 1Z0-051 pass4sure, examcollection 1Z0-051