1Z0-051 | how many questions of 1Z0-051 exam dumps?
Q161. - (Topic 1)
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
There is only one customer with the CUST_LAST_NAME column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?
A.
INSERT INTO orders VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
B.
INSERT INTO orders (order_id,order_date,order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit_limit=600),order_total)
VALUES(1,'10-mar-2007', 'direct', &&customer_id, 1000);
C.
INSERT INTO(SELECT o.order_id, o.order_date,o.order_mode,c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' ANDc.credit_limit=600 ) VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
D.
INSERT INTO orders (order_id,order_date,order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit_limit=600),order_total)
VALUES(1,'10-mar-2007', 'direct', &customer_id, 1000);
Answer: A
Q162. - (Topic 2)
Which statements are true regarding the FOR UPDATE clause in a SELECT statement? (Choose all that apply.)
A. It locks only the columns specified in the SELECT list.
B. It locks the rows that satisfy the condition in the SELECT statement.
C. It can be used only in SELECT statements that are based on a single table.
D. It can be used in SELECT statements that are based on a single or multiple tables.
E. After it is enforced by a SELECT statement, no other query can access the same rows until a COMMIT or ROLLBACK is issued.
Answer: B,D
Explanation:
FOR UPDATE Clause in a SELECT Statement Locks the rows in the EMPLOYEES table where job_id is SA_REP. Lock is released only when you issue a ROLLBACK or a COMMIT. If the SELECT statement attempts to lock a row that is locked by another user, the database waits until the row is available, and then returns the results of the SELECT statement. FOR UPDATE Clause in a SELECT Statement When you issue a SELECT statement against the database to query some records, no locks are placed on the selected rows. In general, this is required because the number of records locked at any given time is (by default) kept to the absolute minimum: only those records that have been changed but not yet committed are locked. Even then, others will be able to read those records as they appeared before the change (the “before image” of the data). There are times, however, when you may want to lock a set of records even before you change them in your program. Oracle offers the FOR UPDATE clause of the SELECT statement to perform this locking. When you issue a SELECT...FOR UPDATE statement, the relational database management system (RDBMS) automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement, thereby holding the records “for your changes only.” No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT. You can append the optional keyword NOWAIT to the FOR UPDATE clause to tell the Oracle server not to wait if the table has been locked by another user. In this case, control will be returned immediately to your program or to your SQL Developer environment so that you can perform other work, or simply wait for a period of time before trying again. Without the NOWAIT clause, your process will block until the table is available, when the locks are released by the other user through the issue of a COMMIT or a ROLLBACK command.
Q163. - (Topic 2)
Which statement describes the ROWID data type?
A. Binary data up to 4 gigabytes.
B. Character data up to 4 gigabytes.
C. Raw binary data of variable length up to 2 gigabytes.
D. Binary data stored in an external file, up to 4 gigabytes.
E. A hexadecimal string representing the unique address of a row in its table.
Answer: E
Explanation:
The ROWID datatype stores information related to the disk location of table rows. They
also uniquely identify the rows in your table. The ROWID datatype is stored as a
hexadecimal string.
Incorrect Answers
A:It is not a binary data. The ROWID datatype is a hexadecimal string.
B:It is not a character data. The ROWID datatype is a hexadecimal string.
C:It is not a raw binary data. The ROWID datatype is a hexadecimal string.
D:It is not binary data stored in an external file. The ROWID datatype is a hexadecimal
string.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 216
Chapter 5: Creating Oracle Database Objects
Q164. - (Topic 2)
Which two statements are true regarding tables? (Choose two.)
A. A table name can be of any length.
B. A table can have any number of columns.
C. A column that has a DEFAULT value cannot store null values.
D. A table and a view can have the same name in the same schema.
E. A table and a synonym can have the same name in the same schema.
F. The same table name can be used in different schemas in the same database.
Answer: E,F
Explanation:
Synonyms Synonyms are database objects that enable you to call a table by another name. You can create synonyms to give an alternative name to a table.
Q165. - (Topic 2)
In the CUSTOMERS table, the CUST_CITY column contains the value 'Paris' for the
CUST_FIRST_NAME 'ABIGAIL'.
Evaluate the following query:
What would be the outcome?
A. Abigail PA B. Abigail Pa
C. Abigail IS
D. an error message
Answer: B
Q166. - (Topic 2)
Which two statements are true regarding indexes? (Choose two.)
A. They can be created on tables and clusters.
B. They can be created on tables and simple views.
C. You can create only one index by using the same columns.
D. You can create more than one index by using the same columns if you specify distinctly different combinations of the columns.
Answer: A,D
Q167. - (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
Q168. - (Topic 2)
View the Exhibit and examine the structure of CUSTOMERS table. Evaluate the following query:
Which statement is true regarding the above query?
A. It executes successfully.
B. It produces an error because the condition on the CUST_CITY column is not valid.
C. It produces an error because the condition on the CUST_FIRST_NAME column is not valid.
D. It produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid.
Answer: A
Q169. - (Topic 2)
Which three statements are true regarding views? (Choose three.)
A. Views can be created only from tables.
B. Views can be created from tables or other views.
C. Only simple views can use indexes existing on the underlying tables.
D. Both simple and complex views can use indexes existing on the underlying tables.
E. Complex views can be created only on multiple tables that exist in the same schema.
F. Complex views can be created on multiple tables that exist in the same or different schemas.
Answer: B,D,F
Explanation:
Creating a Sequence (continued)
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.)
Q170. - (Topic 2)
Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?
A. DROP emp_dept_vu;
B. DELETE emp_dept_vu;
C. REMOVE emp_dept_vu;
D. DROP VIEW emp_dept_vu;
E. DELETE VIEW emp_dept_vu;
F. REMOVE VIEW emp_dept_vu;
Answer: D
Explanation:
DROP VIEW viewname;
Incorrect Answer: ANot a valid drop view statement BNot a valid drop view statement CNot a valid drop view statement ENot a valid drop view statement FNot a valid drop view statement
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-20