http://https://www.examslabs.com/Oracle/Oracle-Database/best-1z0-061-exam-dumps.html (340 Q&As Dumps, 30%OFF Special Discount: bmzblwH7 )

NEW QUESTION NO: 15
Which two statements are true regarding views? (Choose two.)
A. A sub query that defines a view cannot include the GROUP BY clause
B. A view is created with the sub query having the DISTINCT keyword can be updated
C. A Data Manipulation Language (DML) operation can be performed on a view that is created with the sub query having all the NOT NULL columns of a table
D. A view that is created with the sub query having the pseudo column ROWNUM keyword cannot be updated
Answer: C,D
Explanation/Reference:
Explanation:
Rules for Performing DML Operations on a View
You cannot add data through a view if the view includes:
Group functions
A GROUP BY clause
The DISTINCT keyword
The pseudocolumn ROWNUM keyword
Columns defined by expressions
NOT NULL columns in the base tables that are not selected by the view

NEW QUESTION NO: 16
Which two statements are true about sequences created in a single instance database? (Choose two.)
A. CURRVAL is used to refer to the last sequence number that has been generated
B. DELETE <sequencename> would remove a sequence from the database
C. The numbers generated by a sequence can be used only for one table
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: A,D
Explanation/Reference:
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;

NEW QUESTION NO: 17
See the structure of the PROGRAMS table:

Which two SQL statements would execute successfully? (Choose two.)
A. SELECT NVL(ADD_MONTHS(END_DATE, 1), SYSDATE)
FROM programs;
B. SELECT TO_DATE(NVL(SYSDATE-END_DATE, SYSDATE))
FROM programs;
C. SELECT NVL(MONTHS_BETWEEN(start_date, end_date), 'Ongoing')
FROM programs;
D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date, end_date)), 'Ongoing') FROM programs;
Answer: A,D
Explanation/Reference:
Explanation:
NVL Function
Converts a null value to an actual value:
Data types that can be used are date, character, and number.
Data types must match:
- NVL(commission_pct, 0)
- NVL(hire_date, '01-JAN-97')
- NVL(job_id, 'No Job Yet')
MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2
.The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.
MONTHS_BETWEEN returns a numeric value. - answer C NVL has different datatypes - numeric and strings, which is not possible!
The data types of the original and if null parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert if null to the type of the original parameter. The NVL function returns a value with the same data type as the original parameter.

NEW QUESTION NO: 18
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/Reference:
Explanation:
The general syntax to create a synonym is:
CREATE [PUBLIC] SYNONYM synonym FOR object;

NEW QUESTION NO: 19
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 in the table and deleted rows can be rolled back
B. It removes all the rows as well as the structure of the table
C. It would not remove the rows if the table has a primary key
D. It removes all the rows in the table and deleted rows cannot be rolled back
Answer: A

NEW QUESTION NO: 20
Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)
A. The BLOB data type column is used to store binary data in an operating system file
B. The minimum column width that can be specified for a VARCHAR2 data type column is one
C. A TIMESTAMP data type column stores only time values with fractional seconds
D. The value for a CHAR data type column is blank-padded to the maximum defined column width
E. Only One LONG column can be used per table
Answer: B,D,E
Explanation/Reference:
Explanation:
■ LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should be converted to CLOB. There can only be one LONG column in a table.
DVARCHAR2 Variable-length character data, from 1 byte to 4KB. The data is stored in the database character set. The VARCHAR2 data type must be qualified with a number indicating the maximum length of the column.
If a value is inserted into the column that is less than this, it is not a problem: the value will only take up as much space as it needs. If the value is longer than this maximum, the INSERT will fail with an error.
VARCHAR2(size)
Variable-length character data (A maximum size must be specified: minimum size is 1; maximum size is 4,
000.)
BLOB Like CLOB, but binary data that will not undergo character set conversion by Oracle Net.
BFILE A locator pointing to a file stored on the operating system of the database server. The size of the files is limited to 4GB.
TIMESTAMP This is length zero if the column is empty, or up to 11 bytes, depending on the precision specified.
Similar to DATE, but with precision of up to 9 decimal places for the seconds, 6 places by default.

NEW QUESTION NO: 21
Which substitution variable would you use if you want to reuse the variable without prompting the user each time?
A. &
B. ACCEPT
C. PROMPT
D. &&
Answer: D
Explanation/Reference:
Explanation:
To reuse the variable without prompting the user each time you can use && substitution variable.
Incorrect Answers
A:. This substitution variable will prompt the user each time.
B:. ACCEPT is command, not substitution variable. It used to define more accurate or specific prompt or when you want more output to display as the values are defined.
C:. PROMPT is part of the ACCEPT command, it is not a variable.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 165-173 Chapter 4: Sub queries

NEW QUESTION NO: 22
You need to extract details of those products in the SALES table where the PROD_ID column contains the string '_D123'. Which WHERE clause could be used in the SELECT statement to get the required output?
A. WHERE prod_id LIKE '%_D123%' ESCAPE '_'
B. WHERE prod_id LIKE '%\_D123%' ESCAPE '\'
C. WHERE prod_id LIKE '%_D123%' ESCAPE '%_'
D. WHERE prod_id LIKE '%\_D123%' ESCAPE '\_'
Answer: B
Explanation/Reference:
Explanation:
A naturally occurring underscore character may be escaped (or treated as a regular nonspecial symbol) using the ESCAPE identifier in conjunction with an ESCAPE character. The second example in Figure 3-
12 shows the SQL statement that retrieves the JOBS table records with JOB_ID values equal to SA_MAN and SA_REP and which conforms to the original requirement:
select job_id from jobs
where job_id like 'SA\_%' escape '\';

NEW QUESTION NO: 23
Examine the data in the ORD_ITEMS table:

Evaluate the following query:

Which statement is true regarding the outcome of the above query?
A. It gives an error because the having clause should be specified after the group by clause.
B. It gives an error because all the aggregate functions used in the having clause must be specified in the select list.
C. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.
D. It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table.
Answer: C

NEW QUESTION NO: 24
You issue the following command to drop the products table:
SQL> DROP TABLE products;
Which three statements are true about the implication of this command?
A. All data along with the table structure is deleted.
B. A pending transaction in the session is committed.
C. All indexes on the table remain but they are invalidated.
D. All views and synonyms remain but they are invalidated.
E. All data in the table is deleted but the table structure remains.
Answer: A,B,D
Explanation/Reference:
A: The DROP TABLE statement moves a table or object table to the recycle bin.
B: If a user issues a DDL (CREATE, ALTER, or DROP) or DCL (GRANT or REVOKE) command, the transaction in progress (if any) will Incorrect:
Not C: Dropping a table invalidates dependent objects, such as indexes and constraints.
Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9003.htm

NEW QUESTION NO: 25
View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.


Evaluate the following SQL statements:

Which statement is true regarding the output of the above query?
A. It gives the details of promos for which there have been no sales
B. It gives details of product IDs that have been sold irrespective of whether they had a promo or not
C. It gives details of all promos irrespective of whether they have resulted in a sale or not
D. It gives the details of promos for which there have been sales
Answer: C