Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 271:

  Which two statements are true regarding single row functions? (Choose two.)

  A. They can be nested only to two levels
  B. They always return a single result row for every row of a queried table
  C. Arguments can only be column values or constant
  D. They can return a data type value different from the one that is referenced
  E. They accept only a single argument
  B. They always return a single result row for every row of a queried table
  D. They can return a data type value different from the one that is referenced

  ---

  A function is a program written to optionally accept input parameters, perform an operation, or return a single value. A function returns only one value per execution. Three important components form the basis of defining a function. The first is the input parameter list. It specifies zero or more arguments that may be passed to a function as input for processing. These arguments or parameters may be of differing data types, and some are mandatory while others may be optional. The second component is the data type of its resultant value. Upon execution, only one value is returned by the function. The third encapsulates the details of the processing performed by the function and contains the program code that optionally manipulates the input parameters, performs calculations and operations, and generates a return value.

• Question 272:

  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
  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

  ---

  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

• Question 273:

  Consider these three statements:

  create synonym s1 for employees; create public synonym s1 for departments; select * from s1;

  Which of the following statements is correct?

  A. The second statement will fail because an object S1 already exists.
  B. The third statement will show the contents of EMPLOYEES.
  C. The third statement will show the contents of DEPARTMENTS.
  D. The third statement will show the contents of the table S1, if such a table exists in the current schema.
  B. The third statement will show the contents of EMPLOYEES.

  ---

  The order of priority is to search the schema namespace before the public namespace, so it will be the private synonym (to EMPLOYEES) that will be found.

• Question 274:

  Evaluate the following query:

  SQL> SELECT TRUNC(ROUND(156.00, -1), -1)

  FROM DUAL;

  What would be the outcome?

  A. 16
  B. 100
  C. 160
  D. 200
  E. 150
  C. 160

  ---

  Function Purpose ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.) TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero

• Question 275:

  View the Exhibit and examine the data in the products table.

  

  You need to display product names from the products table that belong to the 'software/other' category with minimum prices as either S2000 or S4000 and no unit of measure. You issue the following query:

  

  Which statement is true regarding the above query?

  A. It executes successfully but returns no result.
  B. It executes successfully and returns the required result.
  C. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
  D. It generates an error because the condition specified for the prod category column is not valid.
  A. It executes successfully but returns no result.

• Question 276:

  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 '\_'
  B. WHERE prod_id LIKE '%\_D123%' ESCAPE '\'

  ---

  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 '\';

• Question 277:

  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.
  B. The first CREATE TABLE command generates an error because the NULL constraint is not valid.

  ---

  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.

• Question 278:

  Which one is a system privilege?

  A. SELECT
  B. DELETE
  C. EXECUTE
  D. ALTER TABLE
  E. CREATE TABLE
  E. CREATE TABLE

• Question 279:

  What is true about updates through a view?

  A. You cannot update a view with group functions.
  B. When you update a view group functions are automatically computed.
  C. When you update a view only the constraints on the underlying table will be in effect.
  D. When you update a view the constraints on the views always override the constraints on the underlying tables.
  A. You cannot update a view with group functions.

• Question 280:

  Evaluate the SQL statement

  DROP TABLE DEPT:

  Which four statements are true of the SQL statement? (Choose four.)

  A. You cannot roll back this statement.
  B. All pending transactions are committed.
  C. All views based on the DEPT table are deleted.
  D. All indexes based on the DEPT table are dropped.
  E. All data in the table is deleted, and the table structure is also deleted.
  F. All data in the table is deleted, but the structure of the table is retained.
  G. All synonyms based on the DEPT table are deleted.
  A. You cannot roll back this statement.
  B. All pending transactions are committed.
  D. All indexes based on the DEPT table are dropped.
  E. All data in the table is deleted, and the table structure is also deleted.

  ---

  You cannot roll back DROP TABLE statement. All pending transactions related on this table are committed. If the table is dropped, Oracle automatically drops any index, trigger and constraint associated with the table as well. All data in the

  table is deleted, and the table structure is also deleted.

  Incorrect Answers

  C:. All views based on the DEPT table become invalid, but they are not deleted.

  F:. All data in the table is deleted, and the table structure is also deleted. Command TRUNCATE deletes all data in the table, but does not delete the structure of the table.

  G:. All synonyms based on the DEPT table are not deleted after dropping the table.

  OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 225 Chapter 5: Creating Oracle Database Objects