Your success in Oracle 1z0 051 dumps is our sole target and we develop all our oracle 1z0 051 braindumps in a way that facilitates the attainment of this target. Not only is our oracle 1z0 051 study material the best you can find, it is also the most detailed and the most updated. oracle 1z0 051 Practice Exams for Oracle 1z0 051 practice test are written to the highest standards of technical accuracy.
♥♥ 2021 NEW RECOMMEND ♥♥
Free VCE & PDF File for Oracle 1Z0-051 Real Exam (Full Version!)
★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions
Free Instant Download NEW 1Z0-051 Exam Dumps (PDF & VCE):
Available on:
http://www.surepassexam.com/1Z0-051-exam-dumps.html
Q11. - (Topic 1)
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: Atable to view all constraints definition and names Bshow all object name belong to user Cdoes not display column associated Eno such view
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25
Q12. - (Topic 1)
The STUDENT_GRADES table has these columns:
STUDENT_IDNUMBER(12)
SEMESTER_ENDDATE
GPANUMBER(4,3)
The registrar has asked for a report on the average grade point average (GPA), sorted from the highest grade point average to each semester, starting from the earliest date.
Which statement accomplish this?
A.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end DESC, gpa DESC;
B.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end, gpa ASC
C.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;
D.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end DESC;
E.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;
F.
SELECT student_id,semester_end,gpa FROM student_grades ORDER BY semester_end,gpa DESC
Answer: F
Q13. - (Topic 2)
You issue the following query:
SQL> SELECT AVG(MAX(qty))
FROM ord_items
GROUP BY item_no
HAVING AVG(MAX(qty))>50;
Which statement is true regarding the outcome of this query?
A. It executes successfully and gives the correct output.
B. It gives an error because the HAVING clause is not valid.
C. It executes successfully but does not give the correct output.
D. It gives an error because the GROUP BY expression is not valid.
Answer: B
Explanation:
The general form of the SELECT statement is further enhanced by the addition of the
HAVING clause and becomes:
SELECT column|expression|group_function(column|expression [alias]),…}
FROM table
[WHERE condition(s)]
[GROUP BY {col(s)|expr}]
[HAVING group_condition(s)]
[ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];
An important difference between the HAVING clause and the other SELECT statement
clauses is that it may only be specified if a GROUP BY clause is present. This dependency
is sensible since group-level rows must exist before they can be restricted. The HAVING
clause can occur before the GROUP BY clause in the SELECT statement. However, it is
more common to place the HAVING clause after the GROUP BY clause. All grouping is
performed and group functions are executed prior to evaluating the HAVING clause.
Q14. - (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
Q15. - (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
Q16. - (Topic 2)
Which two statements are true regarding views? (Choose two.)
A. A simple view in which column aliases have been used cannot be updated.
B. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
C. Rows added through a view are deleted from the table automatically when the view is dropped.
D. The OR REPLACE option is used to change the definition of an existing view without dropping and recreating it.
E. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.
Answer: B,D
Q17. - (Topic 2)
Examine the data in the PROMO_BEGIN_DATE column of the PROMOTIONS table:
PROMO_BEGIN _DATE
04-jan-00
10-jan-00
15-dec-99
18-oct-98
22-aug-99
You want to display the number of promotions started in 1999 and 2000.
Which query gives the correct output?
A.
SELECT SUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0)) "2000",
SUM(DECODE(SUBSTR
(promo_begin_date,8),'99',1,0)) "1999"
FROM promotions;
B.
SELECT SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0
END) "1999",SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE
0 END) "2000"
FROM promotions;
C.
SELECT COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0 END) "1999", COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE 0 END) "2000" FROM promotions;
D.
SELECT COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8), '1999', 1,
0)) "1999", COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8),'2000', 1,
0)) "2000"
FROM promotions;
Answer: A
Q18. - (Topic 2)
Examine the following SQL commands:
Which statement is true regarding the execution of the above SQL commands?
A. Both commands execute successfully.
B. The first CREATE TABLE command generates an error because the NULL constraint is not valid.
C. The second CREATE TABLE command generates an error because the CHECK constraint is not valid.
D. The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY constraints cannot be used for the same column.
E. The first CREATE TABLE command generates an error because the column PROD_ID cannot be used in the PRIMARY KEY and FOREIGN KEY constraints.
Answer: B
Explanation:
Defining Constraints The slide gives the syntax for defining constraints when creating a table. You can create
constraints at either the column level or table level. Constraints defined at the column level
are included when the column is defined. Table-level constraints are defined at the end of
the table definition and must refer to the column or columns on which the constraint
pertains in a set of parentheses. It is mainly the syntax that differentiates the two;
otherwise, functionally, a columnlevel constraint is the same as a table-level constraint.
NOT NULL constraints must be defined at the column level.
Constraints that apply to more than one column must be defined at the table level.
Q19. - (Topic 1)
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
Answer: B
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),
Q20. - (Topic 1)
Which statement is true regarding sub queries?
A. The LIKE operator cannot be used with single- row subqueries.
B. The NOT IN operator is equivalent to IS NULL with single- row subqueries.
C. =ANY and =ALL operators have the same functionality in multiple- row subqueries.
D. The NOT operator can be used with IN, ANY, and ALL operators in multiple- row subqueries.
Answer: D
Explanation:
Using the ANY Operator in Multiple-Row Subqueries
The ANY operator (and its synonym, the SOME operator) compares a value to each value
returned by a subquery.
<ANY means less than the maximum.
>ANY means more than the minimum.
=ANY is equivalent to IN
Using the ALL Operator in Multiple-Row Subqueries
The ALL operator compares a value to every value returned by a subquery.
>ALL means more than the maximum and
<ALL means less than the minimum.
The NOT operator can be used with IN, ANY, and ALL operators.
