PassLeader now are offering 100% pass ensure 1Z0-051 dumps! All 1Z0-051 exam questions have been updated with correct answers, welcome to download the newest PassLeader 1Z0-051 VCE dumps and PDF dumps: http://www.passleader.com/1z0-051.html (303 Q&As)
BTW: Download PassLeader 1Z0-051 dumps from Google Drive for free: https://drive.google.com/open?id=0B-ob6L_QjGLpWXpBUEhyRnVXVlE
QUESTION 181
Which two statements are true about sequences created in a single instance database? (Choose two.)
A. The numbers generated by a sequence can be used only for one table.
B. DELETE <sequencename> would remove a sequence from the database.
C. CURRVAL is used to refer to the last sequence number that has been generated.
D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.
E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted.
Answer: CD
Explanation:
Gaps in the Sequence
Although sequence generators issue sequential numbers without gaps, this action occurs independent of a commit or rollback. Therefore, if you roll back a statement containing a sequence, the number is lost.
Another event that can cause gaps in the sequence is a system crash. If the sequence caches values in memory, those values are lost if the system crashes. Because sequences are not tied directly to tables, the same sequence can be used for multiple tables.
However, if you do so, each table can contain gaps in the sequential numbers.
Modifying a Sequence
If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are allocated and you will receive an error indicating that the sequence exceeds the MAXVALUE. To continue to use the sequence, you can modify it by using the ALTER SEQUENCE statement To remove a sequence, use the DROP statement:
DROP SEQUENCE dept_deptid_seq;
QUESTION 182
Examine the following PL/SQL block:
SET SERVEROUTPUT ON
SET LONG 10000
ECLARE report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE();
DBMS_OUTPUT.PUT_LINE(report);
END;
Which statement describes the effect of the execution of the above PL/SQL block?
A. The plan baselines are verified with the SQL profiles.
B. All fixed plan baselines are converted into nonfixed plan baselines.
C. All the nonaccepted SQL profiles are accepted into the plan baseline.
D. The nonaccepted plans in the SQL Management Base are verified with the existing plan baselines.
Answer: D
QUESTION 183
Which two statements are true regarding savepoints? (Choose two.)
A. Savepoints are effective only for COMMIT.
B. Savepoints may be used to ROLLBACK.
C. Savepoints can be used for only DML statements.
D. Savepoints are effective for both COMMIT and ROLLBACK.
E. Savepoints can be used for both DML and DDL statements.
Answer: BC
QUESTION 184
Evaluate these two SQL statements:
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC;
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;
What is true about them?
A. The two statements produce identical results.
B. The second statement returns a syntax error.
C. There is no need to specify DESC because the results are sorted in descending order by default.
D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.
Answer: A
Explanation:
the two statement produce identical results as ORDER BY 2 will take the second column as sorting column.
Incorrect answer:
Bthere is no syntax error
Cresult are sorted in ascending order by default
DORDER BY 2 will take the second column as sorting column.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22
QUESTION 185
Which view should a user query to display the columns associated with the constraints on a table owned by the user?
A. USER_CONSTRAINTS
B. USER_OBJECTS
C. ALL_CONSTRAINTS
D. USER_CONS_COLUMNS
E. USER_COLUMNS
Answer: D
Explanation:
view the columns associated with the constraint names in the USER_CONS_COLUMNS view.
Incorrect answer:
– A table to view all constraints definition and names
– B show all object name belong to user
– C does not display column associated
– E no such view
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25
QUESTION 186
Which statement is true regarding the INTERSECT operator?
A. It ignores NULL values
B. The number of columns and data types must be identical for all SELECT statements in the query
C. The names of columns in all SELECT statements must be identical
D. Reversing the order of the intersected tables the result
Answer: B
Explanation:
INTERSECT Returns only the rows that occur in both queries’ result sets, sorting them and removing duplicates. The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.
QUESTION 187
Examine these statements:
CREATE ROLE registrar;
GRANT UPDATE ON student_grades TO registrar;
GRANT registrar to user1, user2, user3;
What does this set of SQL statements do?
A. The set of statements contains an error and does not work.
B. It creates a role called REGISTRAR, adds the MODIFY privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.
C. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.
D. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and creates three users with the role.
E. It creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES object.
F. It creates a role called STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registrar.
Answer: C
Explanation:
The statement will create a role call REGISTRAR, grant UPDATE on student_grades to registrar, grant the role to user1,user2 and user3.
Incorrect answer:
Athe statement does not contain error
Bthere is no MODIFY privilege
Dstatement does not create 3 users with the role
Eprivilege is grant to role then grant to user
Fprivilege is grant to role then grant to user
QUESTION 188
Which are /SQL*Plus commands? (Choose all that apply.)
A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE
E. DELETE
F. RENAME
Answer: D
Explanation:
Describe is a valid iSQL*Plus/ SQL*Plus command.
INSERT, UPDATE & DELETE are SQL DML Statements.
A SELECT is an ANSI Standard SQL Statement not an iSQL*Plus Statement.
RENAME is a DDL Statement.
QUESTION 189
Which one is a system privilege?
A. SELECT
B. DELETE
C. EXECUTE
D. ALTER TABLE
E. CREATE TABLE
Answer: E
QUESTION 190
You work as a database administrator at ABC.com. You study the exhibit carefully. 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: BC
QUESTION 191
What is true about sequences?
A. The start value of the sequence is always 1.
B. A sequence always increments by 1.
C. The minimum value of an ascending sequence defaults to 1.
D. The maximum value of descending sequence defaults to 1.
Answer: C
QUESTION 192
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables. Which MERGE statement is valid?
A. MERGE INTO new_employees c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
B. name = e.first_name ||’,’|| e.last_name
WHEN NOT MATCHED THEN
INSERT
value
S(e.employee_id, e.first_name ||’,
‘||e.last_name);
C. MERGE new_employees c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN EXISTS THEN
UPDATE SET
D. name = e.first_name ||’,’|| e.last_name
WHEN NOT MATCHED THEN INSERT
valueS(e.employee_id, e.first_name ||’,
‘||e.last_name);
E. MERGE INTO new_employees cUSING employees e
ON (c.employee_id = e.employee_id)
WHEN EXISTS THEN
UPDATE SET
F. name = e.first_name ||’,’|| e.last_name
WHEN NOT MATCHED THEN
INSERT
value
S(e.employee_id, e.first_name ||’,
‘||e.last_name);
G. MERGE new_employees c
FROM employees e ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
H. name = e.first_name ||’,’|| e.last_name
WHEN NOT MATCHED THEN
INSERT INTO
new_employees valueS(e.employee_id, e.first_name ||’, ‘||e.last_name);
Answer: A
Explanation:
this is the correct MERGE statement syntax
Incorrect answer:
Bit should MERGE INTO table_name
Cit should be WHEN MATCHED THEN
Dit should MERGE INTO table_name
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29
QUESTION 193
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: D
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
QUESTION 194
Examine the structure of the STUDENTS table. You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999. Which SQL statement accomplishes this task?
A. SELECT student_ id, marks, ROWNUM “Rank”
FROM students
WHERE ROWNUM <= 10
AND finish_date BETWEEN ’01-JAN-99′
AND ’31-DEC-99 AND course_id = ‘INT_SQL’
ORDER BY marks DESC;
B. SELECT student_id, marks, ROWID “Rank”
FROM students
WHERE ROWID <= 10
AND finish_date BETWEEN ’01-JAN-99′
AND ’31-DEC-99′ AND course_id = ‘INT_SQL’
ORDER BY marks;
C. SELECT student_id, marks, ROWNUM “Rank”
FROM (SELECT student_id, marks
FROM students
WHERE ROWNUM <= 10
AND finish_date BETWEEN ’01-JAN-99′
AND ’31-DEC-99′
AND course_id = ‘INT_SQL’
ORDER BY marks DESC);
D. SELECT student_id, marks, ROWNUM “Rank”
FROM (SELECT student_id, marks
FROM students
WHERE (finish_date BETWEEN ’01-JAN-99 AND ’31-DEC-99′
AND course_id = `INT_SQL’
ORDER BY marks DESC)
WHERE ROWNUM <= 10 ;
E. SELECTstudent id, marks, ROWNUM “Rank”
FROM(SELECT student_id, marks
FROM students
ORDER BY marks)
WHEREROWNUM <= 10
ANDfinish date BETWEEN ’01-JAN-99′ AND ’31-DEC-99′
ANDcourse_id = `INT_SQL’;
Answer: D
QUESTION 195
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: C
Explanation:
The general syntax to create a synonym is:
CREATE [PUBLIC] SYNONYM synonym FOR object;
QUESTION 196
Examine the structure of the EMPLOYEES table:
You want to create a SQL script file that contains an INSERT statement. When the script is run, the INSERT statement should insert a row with the specified values into the EMPLOYEES table. The INSERT statement should pass values to the table columns as specified below:
Which INSERT statement meets the above requirements?
A. INSERT INTO employees
VALUES (emp_id_seq.NEXTVAL, ‘&ename’, ‘&jobid’, 2000, NULL, &did);
B. INSERT INTO employees
VALUES (emp_id_seq.NEXTVAL, ‘&ename’, ‘&jobid’,
2000, NULL, &did IN (20,50));
C. INSERT INTO (SELECT *
FROM employees
WHERE department_id IN (20,50))
VALUES (emp_id_seq.NEXTVAL, ‘&ename’, ‘&jobid’, 2000, NULL, &did);
D. INSERT INTO (SELECT *
FROM employees
WHERE department_id IN (20,50)
WITH CHECK OPTION)
VALUES (emp_id_seq.NEXTVAL, ‘&ename’, ‘&jobid’, 2000, NULL, &did);
E. INSERT INTO (SELECT *
FROM employees
WHERE (department_id = 20 AND
department_id = 50)
WITH CHECK OPTION )
VALUES (emp_id_seq.NEXTVAL, ‘&ename’, ‘&jobid’, 2000, NULL, &did);
Answer: D
QUESTION 197
A SELECT statement can be used to perform these three functions:
– Choose rows from a table
– Choose columns from a table
– Bring together data that is stored in different tables by creating a link between them
Which set of keywords describes these capabilities?
A. difference, projection, join
B. selection, projection, join
C. selection, intersection, join
D. intersection, projection, join
E. difference, projection, product
Answer: B
Explanation:
Choose rows from a table is SELECTION, Choose column from a table is PROJECTION.
Bring together data in different table by creating a link between them is JOIN.
Incorrect answer:
Aanswer should have SELECTION, PROJECTION and JOIN.
Canswer should have SELECTION, PROJECTION and JOIN.
Danswer should have SELECTION, PROJECTION and JOIN.
Eanswer should have SELECTION, PROJECTION and JOIN.
QUESTION 198
Which three statements are true regarding sub queries? (Choose three.)
A. Multiple columns or expressions can be compared between the main query and sub query
B. Sub queries can contain GROUP BY and ORDER BY clauses
C. Only one column or expression can be compared between the main query and subqeury
D. Main query and sub query can get data from different tables
E. Main query and sub query must get data from the same tables
F. Sub queries can contain ORDER BY but not the GROUP BY clause
Answer: ABD
QUESTION 199
Which three statements are true about multiple-row sub queries? (Choose three.)
A. They can contain a subquery within a sub query.
B. They can return multiple columns as well as rows.
C. They cannot contain a sub query within a sub query.
D. They can return only one column but multiple rows.
E. They can contain group functions and GROUP BY and HAVING clauses.
F. They can contain group functions and the GROUP BY clause, but not the HAVING clause.
Answer: ABE
QUESTION 200
Examine the structure of the EMPLOYEES table. Which UPDATE statement is valid?
A. UPDATE employees
SET first_name = `John’
SET last_name = `Smith’
WHERE employee_id = 180;
B. UPDATE employees
SET first_name = `John’,
SET last_name = `Smoth’
WHERE employee_id = 180;
C. UPDATE employee
SET first_name = `John’
AND last_name = `Smith’
WHERE employee_id = 180;
D. UPDATE employee
SET first_name = `John’,
last_name = `Smith’
WHERE employee_id = 180;
Answer: D
PassLeader now are offering 100% pass ensure 1Z0-051 dumps! All 1Z0-051 exam questions have been updated with correct answers, welcome to download the newest PassLeader 1Z0-051 VCE dumps and PDF dumps: http://www.passleader.com/1z0-051.html (303 Q&As)
BTW: Download PassLeader 1Z0-051 dumps from Google Drive for free: https://drive.google.com/open?id=0B-ob6L_QjGLpWXpBUEhyRnVXVlE