Pass4sure oracle 1z0 051 Questions are updated and all 1z0 051 dumps answers are verified by experts. Once you have completely prepared with our 1z0 051 dumps pdf exam prep kits you will be ready for the real 1z0 051 dumps pdf exam without a problem. We have Latest Oracle 1z0 051 practice test dumps study guide. PASSED 1z0 051 practice test First attempt! Here What I Did.
♥♥ 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
Q41. - (Topic 2)
Examine the structure of the TRANSACTIONS table:
Name Null Type
TRANS_ID NOT NULL NUMBER(3)
CUST_NAME VARCHAR2(30) TRANS_DATE DATE TRANS_AMT NUMBER(10,2)
You want to display the transaction date and specify whether it is a weekday or weekend. Evaluate the following two queries:
Which statement is true regarding the above queries?
A. Both give wrong results.
B. Both give the correct result.
C. Only the first query gives the correct result.
D. Only the second query gives the correct result.
Answer: C
Explanation:
Range Conditions Using the BETWEEN Operator Use the BETWEEN operator to display rows based on a range of values: SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500; Range Conditions Using the BETWEEN Operator You can display rows based on a range of values using the BETWEEN operator. The range that you specify contains a lower limit and an upper limit. The SELECT statement in the slide returns rows from the EMPLOYEES table for any employee whose salary is between $2,500 and $3,500. Values that are specified with the BETWEEN operator are inclusive. However, you must specify the lower limit first. You can also use the BETWEEN operator on character values: SELECT last_name FROM employees WHERE last_name BETWEEN 'King' AND 'Smith'
Q42. - (Topic 1)
View the Exhibit and examine the structure of the PROMOTIONS table.
You need to generate a report of all promos from the PROMOTIONS table based on the following conditions:
1.
The promo name should not begin with 'T' or 'N'.
2.
The promo should cost more than $20000.
3.
The promo should have ended after 1st January 2001.
Which WHERE clause would give the required result?
A.
WHERE promo_name NOT LIKE 'T%' OR promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
B.
WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR promo_cost > 20000 OR promo_end_date > '1-JAN-01'
C.
WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
D.
WHERE (promo_name NOT LIKE '%T%' OR promo_name NOT LIKE '%N%') AND(promo_cost > 20000 AND promo_end_date > '1-JAN-01')
Answer: C
Q43. - (Topic 2)
What is true about the WITH GRANT OPTION clause?
A. It allows a grantee DBA privileges.
B. It is required syntax for object privileges.
C. It allows privileges on specified columns of tables.
D. It is used to grant an object privilege on a foreign key column.
E. It allows the grantee to grant object privileges to other users and roles.
Answer: E Explanation:
The GRANT command with the WITH GRANT OPTION clause allows the grantee to grant
object privileges to other users and roles.
Incorrect Answers
A:The WITH GRANT OPTION does not allow a grantee DBA privileges.
B:It is not required syntax for object privileges. It is optional clause of GRANT command.
C:GRANT command does not allows privileges on columns of tables.
D:It is not used to grant an object privilege on a foreign key column.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365
Chapter 8: User Access in Oracle
Q44. - (Topic 2)
View the Exhibit and examine the structure of CUSTOMERS and SALES tables.
Evaluate the following SQL statement:
UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id
FROM sales)
SET time_id = '22-MAR-2007'
WHERE cust_id = (SELECT cust_id
FROM customers
WHERE cust_last_name = 'Roberts' AND
credit_limit = 600);
Which statement is true regarding the execution of the above UPDATE statement?
A. It would not execute because two tables cannot be used in a single UPDATE statement.
B. It would not execute because the SELECT statement cannot be used in place of the table name.
C. It would execute and restrict modifications to only the columns specified in the SELECT statement.
D. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
Answer: C
Explanation:
One UPDATE statement can change rows in only one table, but it can change any number of rows in that table.
Q45. - (Topic 2)
In which four clauses can a sub query be used? (Choose four.)
A. in the INTO clause of an INSERT statement
B. in the FROM clause of a SELECT statement
C. in the GROUP BY clause of a SELECT statement
D. in the WHERE clause of a SELECT statement
E. in the SET clause of an UPDATE statement
F. in the VALUES clause of an INSERT statement
Answer: A,B,D,E
Explanation:
A: a sub query is valid on the INTO clause of an ISERT Statement
B: a sub query can be used in the FROM clause of a SELECT statement
D: a sub query can be used in the WHERE clause of a SELECT statement,
E: a sub query can be used in the SET clauses of an UPDATE statement,
Incorrect Answer:
Csub query cannot be used
F: is incorrect.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 6-5
Q46. - (Topic 2)
Examine the SQL statement that creates ORDERS table:
CREATE TABLE orders (SER_NO NUMBER UNIQUE, ORDER_ID NUMBER, ORDER_DATE DATE NOT NULL, STATUS VARCHAR2(10) CHECK (status IN ('CREDIT', 'CASH')), PROD_ID NUMBER REFERENCES PRODUCTS(PRODUCT_ID), ORD_TOTAL NUMBER, PRIMARY KEY (order_id, order_date));
For which columns would an index be automatically created when you execute the above SQL statement? (Choose two.)
A. SER_NO
B. ORDER_ID
C. STATUS
D. PROD_ID
E. ORD_TOTAL
F. composite index on ORDER_ID and ORDER_DATE
Answer: A,F
Explanation: Index exist for UNIQUE and PRIMARY KEY constraints
Incorrect Answer: BORDER_ID is neither UNIQUE nor PRIMARY KEY CSTATUS is neither UNIQUE nor PRIMARY KEY DPROD_ID is neither UNIQUE nor PRIMARY KEY EORD_TOTAL is neither UNIQUE nor PRIMARY KEY
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-15
Q47. - (Topic 2)
View the Exhibit and examine the data in the PROMOTIONS table.
PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the POST category that were launched before January 1, 2000.
Which SQL statement would you use?
A. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category = 'post' AND promo_begin_date < '01-01-00'
B. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000'
C. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00'
D. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00'
Answer: D
Q48. - (Topic 2)
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: B,C
Q49. - (Topic 2)
View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY tables.
SALE_VW is a view created using the following command:
SQL>CREATE VIEW sale_vw AS
SELECT prod_id, SUM(quantity_sold) QTY_SOLD
FROM sales GROUP BY prod_id;
You issue the following command to add a row to the SALE_SUMMARY table:
SQL>INSERT INTO sale_summary
SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products
USING (prod_id) WHERE prod_id = 16;
What is the outcome?
A. It executes successfully.
B. It gives an error because a complex view cannot be used to add data into the SALE_SUMMARY table.
C. It gives an error because the column names in the subquery and the SALE_SUMMARY table do not match.
D. It gives an error because the number of columns to be inserted does not match with the number of columns in the SALE_SUMMARY table.
Answer: D
Q50. - (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
