Latest Oracle 1Z0-051 Exam Questions Ensure 100 Percent Exam Pass

New VCE and PDF– If you want to pass Oracle 1Z0-051 exam successfully,do not miss to test Cisco latest Oracle 1Z0-051 brain dumps.All Oracle 1Z0-051 the new questions and answers were timely added, visit Flydumps.com to free download VCE player and PDF files.

QUESTION 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
Correct Answer: ADF Explanation
Explanation/Reference:
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: C is not true D is not true E is not true
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-18
QUESTION 2
You need to design a student registration database that contains several tables storing academic information. The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student’s grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key.
You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?
A. CREATE TABLE student_grades (student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id));
B. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
C. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));
D. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
Correct Answer: D Explanation
Explanation/Reference:
Explanation: CONSTRAINT name FOREIGN KEY (column_name) REFERENCES table_name (column_name);
Incorrect answer: A invalid syntax B invalid syntax C invalid syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-14
QUESTION 3
Here is the structure and data of the CUST_TRANS table: Exhibit:

Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table. Which three SQL statements would execute successfully? (Choose three.)
A. SELECT transdate + ’10’ FROM cust_trans;
B. SELECT * FROM cust_trans WHERE transdate = ’01-01-07′;
C. SELECT transamt FROM cust_trans WHERE custno > ’11’;
D. SELECT * FROM cust_trans WHERE transdate=’01-JANUARY-07′;
E. SELECT custno + ‘A’ FROM cust_trans WHERE transamt > 2000; Correct Answer: ACD
Explanation Explanation/Reference:
QUESTION 4
See the Exhibit and examine the structure and data in the INVOICE table: Exhibit:

Which two SQL statements would executes successfully? (Choose two.)
A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
B. SELECT MAX(AVG(SYSDATE – inv_date)) FROM invoice;
C. SELECT (AVG(inv_date) FROM invoice;
D. SELECT AVG(inv_date – SYSDATE),AVG(inv_amt) FROM invoice;
Correct Answer: AD Explanation
Explanation/Reference:
QUESTION 5
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. Main query and sub query can get data from different tables
C. Sub queries can contain GROUP BY and ORDER BY clauses
D. Main query and sub query must get data from the same tables
E. Sub queries can contain ORDER BY but not the GROUP BY clause
F. Only one column or expression can be compared between the main query and subqeury
Correct Answer: ABC Explanation
Explanation/Reference:
QUESTION 6
See the Exhibit and examine the structure of the CUSTOMERS table:

Using the CUSTOMERS table, you need to generate a report that shown the average credit limit for
customers in WASHINGTON and NEW YORK.
Which SQL statement would produce the required result?

A. SELECT cust_city, AVG(cust_credit_limit) FROM customers WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’) GROUP BY cust_credit_limit, cust_city;
B. SELECT cust_city, AVG(cust_credit_limit) FROM customers WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’) GROUP BY cust_city,cust_credit_limit;
C. SELECT cust_city, AVG(cust_credit_limit) FROM customers WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’) GROUP BY cust_city;
D. SELECT cust_city, AVG(NVL(cust_credit_limit,0)) FROM customers WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’);
Correct Answer: C Explanation
Explanation/Reference:
Explanation: Creating Groups of Data: GROUP BY Clause Syntax You can use the GROUP BY clause to divide the rows in a table into groups. You can then use the group functions to return summary information for each group. In the syntax: group_by_expression Specifies the columns whose values determine the basis for grouping rows Guidelines · If you include a group function in a SELECT clause, you cannot select individual results as well, unless the individual column appears in the GROUP BY clause. You receive an error message if you fail to include the column list in the GROUP BY clause. · Using a WHERE clause, you can exclude rows before dividing them into groups. · You must include the columns in the GROUP BY clause. · You cannot use a column alias in the GROUP BY clause.
QUESTION 7
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.
Correct Answer: A Explanation
Explanation/Reference:
Explanation: the two statement produce identical results as ORDER BY 2 will take the second column as sorting column.
Incorrect answer: B there is no syntax error C result are sorted in ascending order by default D ORDER BY 2 will take the second column as sorting column. Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22
QUESTION 8
Where can sub queries be used? (Choose all that apply)
A. field names in the SELECT statement
B. the FROM clause in the SELECT statement
C. the HAVING clause in the SELECT statement
D. the GROUP BY clause in the SELECT statement
E. the WHERE clause in only the SELECT statement
F. the WHERE clause in SELECT as well as all DML statements
Correct Answer: ABCF Explanation Explanation/Reference:
Explanation: SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query. A subquery can have any of the usual clauses for selection and projection. The following are required clauses: A SELECT list A FROM clause
The following are optional clauses: WHERE GROUP BY HAVING
The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent.
QUESTION 9
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)
A. SELECT TO_CHAR(1890.55,’$99G999D00′) FROM DUAL;
B. SELECT TO_CHAR(1890.55,’$9,999V99′) FROM DUAL;
C. SELECT TO_CHAR(1890.55,’$0G000D00′) FROM DUAL;
D. SELECT TO_CHAR(1890.55,’$99G999D99′) FROM DUAL;
E. SELECT TO_CHAR(1890.55,’$9,999D99′) FROM DUAL;
Correct Answer: ACD Explanation
Explanation/Reference:
QUESTION 10
Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?
A. It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement
B. It executes successfully and displays rows in the descending order of PROMO_CATEGORY
C. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement
D. It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators

Correct Answer: A Explanation
Explanation/Reference:
Explanation: Using the ORDER BY Clause in Set Operations The ORDER BY clause can appear only once at the end of the compound query. Component queries cannot have individual ORDER BY clauses. The ORDER BY clause recognizes only the columns of the first SELECT query. By default, the first column of the first SELECT query is used to sort the output in an ascending order.
QUESTION 11
Which statement correctly describes SQL and /SQL*Plus?
A. Both SQL and /SQL*plus allow manipulation of values in the database.
B. /SQL*Plus recognizes SQL statements and sends them to the server; SQL is the Oracle proprietary interface for executing SQL statements.
C. /SQL*Plus is a language for communicating with the Oracle server to access data; SQL recognizes SQL statements and sends them to the server.
D. SQL manipulates data and table definitions in the database; /SQL*Plus does not allow manipulation of values in the database.
Correct Answer: A Explanation
Explanation/Reference:
QUESTION 12
Which four are types of functions available in SQL? (Choose 4)
A. string
B. character
C. integer
D. calendar
E. numeric
F. translation
G. date
H. conversion
Correct Answer: BEGH Explanation
Explanation/Reference:
Explanation: SQL have character, numeric, date, conversion function.
Incorrect answer:
A SQL have character, numeric, date, conversion function.
C SQL have character, numeric, date, conversion function.
D SQL have character, numeric, date, conversion function.
F SQL have character, numeric, date, conversion function.

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

QUESTION 13
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);
Correct Answer: A Explanation

Explanation/Reference:
Explanation: this is the correct MERGE statement syntax Incorrect answer: B it should MERGE INTO table_name C it should be WHEN MATCHED THEN D it should MERGE INTO table_name Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29
QUESTION 14
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

Correct Answer: D Explanation
Explanation/Reference:
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 15
The COMMISSION column shows the monthly commission earned by the employee. Exhibit

Which two tasks would require sub queries or joins in order to be performed in a single step? (Choose two.)
A. listing the employees who earn the same amount of commission as employee 3
B. finding the total commission earned by the employees in department 10
C. finding the number of employees who earn a commission that is higher than the average commission of the company
D. listing the departments whose average commission is more that 600
E. listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID
F. listing the employees whose annual commission is more than 6000

Correct Answer: AC Explanation
Explanation/Reference: QUESTION 16
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. SELECT student id, marks, ROWNUM “Rank” FROM (SELECT student_id, marks FROM students ORDER BY marks) WHERE ROWNUM <= 10 AND finish date BETWEEN ’01-JAN-99′ AND ’31-DEC-99′ AND course_id = `INT_SQL’;
Correct Answer: D Explanation

Explanation/Reference: QUESTION 17
Evaluate the following SQL statements: Exhibit:

Exhibit:

The above command fails when executed. What could be the reason?
A. The BETWEEN clause cannot be used for the CHECK constraint
B. SYSDATE cannot be used with the CHECK constraint
C. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY
D. The CHECK constraint cannot be placed on columns having the DATE data type

Correct Answer: B Explanation
Explanation/Reference:
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),
QUESTION 18
Evaluate the following SQL statements: DELETE FROM sales;
There are no other uncommitted transactions on the SALES table. Which statement is true about the DELETE statement?
A. It removes all the rows as well as the structure of the table
B. It removes all the rows in the table and deleted rows cannot be rolled back
C. It removes all the rows in the table and deleted rows can be rolled back
D. It would not remove the rows if the table has a primary key Correct Answer: C

Explanation Explanation/Reference:
QUESTION 19
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);
Correct Answer: D Explanation
Explanation/Reference:
QUESTION 20
Which two statements are true regarding constraints? (Choose two.)
A. A constraint can be disabled even if the constraint column contains data
B. A constraint is enforced only for the INSERT operation on a table
C. A foreign key cannot contain NULL values
D. All constraints can be defined at the column level as well as the table level
E. A columns with the UNIQUE constraint can contain NULL values

Correct Answer: AE Explanation
Explanation/Reference:

CCNA Exam Certification Guide is a best-of-breed Oracle 1Z0-051 exam study guide that has been completely updated to focus specifically on the objectives.Senior instructor and best-selling author Wendell Odom shares preparation hints and Oracle 1Z0-051 tips to help you identify areas of weakness and improve both your conceptual and hands-on knowledge.Oracle 1Z0-051 Material is presented in a concise manner, focusing on increasing your understanding and retention of exam topics.