Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 1:

  Examine the statement:

  GRANT select, insert, update

  ON student_grades

  TO manager

  WITH GRANT OPTION;

  Which two are true? (Choose two.)

  A. MANAGER must be a role.
  B. It allows the MANAGER to pass the specified privileges on to other users.
  C. It allows the MANAGER to create tables that refer to the STUDENT_GRADES table.
  D. It allows the MANAGER to apply all DML statements on the STUDENT_GRADES table.
  E. It allows the MANAGER the ability to select from, insert into, and update the STUDENT_GRADES table.
  F. It allows the MANAGER the ability to select from, delete from, and update the STUDENT_GRADES table.
  B. It allows the MANAGER to pass the specified privileges on to other users.
  E. It allows the MANAGER the ability to select from, insert into, and update the STUDENT_GRADES table.

  ---

  explanation:

  GRANT ROLE to ROLE/USER Incorrect answer:

  A. Role can be grant to user

  C. Create table privilege is not granted

  D. Execute privilege is not granted

  F. Delete privilege is not granted Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-15

• Question 2:

  What is the primary difference between the relational database (RDB) and object-oriented database (OODB) models?

  A. OODB supports multiple objects in the same database, whereas RDB supports only tables.
  B. RDB supports E.F. Codd's rules, whereas OODB does not support them.
  C. OODB incorporates methods with data structure definition, whereas RDB does not allow this.
  D. RDB allows the definition of relationships between different tables, whereas OODB does not allow this.
  C. OODB incorporates methods with data structure definition, whereas RDB does not allow this.

• Question 3:

  You want to display the date for the first Monday of the next month and issue the following command:

  SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'MON'), 'dd "is the first Monday for 'fmmonth rrrr') FROM DUAL;

  What is the outcome?

  A. It executes successfully and returns the correct result.
  B. It executes successfully but does not return the correct result.
  C. It generates an error because TO_CHAR should be replaced with TO_DATE.
  D. It generates an error because rrrr should be replaced by rr in the format string.
  E. It generates an error because fm and double quotation marks should not be used in the format string.
  A. It executes successfully and returns the correct result.

  ---

  explanation:

  ?NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string.

  ?LAST_DAY(date): Finds the date of the last day of the month that contains date The second innermost function is evaluated next. TO_CHAR('28-OCT-2009', 'fmMonth') converts the given date based on the Month format mask and returns

  the character string October. The fm modifier trims trailing blank spaces from the name of the month.

• Question 4:

  You are the DBA for an academic database. You need to create a role that allows a group of users to modify existing rows in the STUDENT_GRADES table.

  Which set of statements accomplishes this?

  A. CREATE ROLE registrar; GRANT MODIFY ON student_grades TO registrar; GRANT registrar to user1, user2, user3
  B. CREATE NEW ROLE registrar; GRANT ALL ON student_grades TO registrar; GRANT registrar to user1, user2, user3
  C. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT ROLE registrar to user1, user2, user3
  D. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3;
  E. CREATE registrar; GRANT CHANGE ON student_grades TO registrar; GRANT registrar;
  D. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3;

  ---

  explanation:

  this is the correct solution for the answer. GRANT role_name to users;

  Incorrect answer:

  A. there is no such MODIFY keyword

  B. invalid CREATE command, there is no such NEW keyword C. invalid GRANT command, there is no such ROLE keyword E. invalid GRANT command, there is no such CHANGE keyword

  Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-10

• Question 5:

  Evaluate the following CREATE SEQUENCE statement:

  CREATE SEQUENCE seq1

  START WITH 100

  INCREMENT BY 10 MAXVALUE 200

  CYCLE

  NOCACHE;

  The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL statement:

  SELECT seq1.nextval FROM dual;

  What is displayed by the SELECT statement?

  A. 1
  B. 10
  C. 100
  D. an error
  A. 1

  ---

  explanation:

  But why the answer is not "C" ?

  Because you didn't specify the MINVALUE for the sequence. If you check the sequence definition that you created it will have the default value of 1, which it reverts to when cycling.

  If you wanted to keep the minimum value you would need to specify it in the sequence creation.

  sequence Is the name of the sequence generator

  INCREMENT BY n Specifies the interval between sequence numbers, where n is an integer (If this clause is omitted, the sequence increments by 1.) START WITH n Specifies the first sequence number to be generated (If this clause is

  omitted, the sequence starts with 1.)

  MAXVALUE n Specifies the maximum value the sequence can generate NOMAXVALUE Specifies a maximum value of 10^27 for an ascending sequence and ? for a descending sequence (This is the default option.) MINVALUE n Specifies

  the minimum sequence value

  NOMINVALUE Specifies a minimum value of 1 for an ascending sequence and ?10^26) for a descending sequence (This is the default option.) 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.)

• Question 6:

  View the Exhibit and examine the structure of the CUSTOMERS table.

  You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10, 000.

  Evaluate the following two queries:

  

  Which statement is true regarding the execution of the above queries?

  

  A. Only the first query gives the correct result.
  B. Only the second query gives the correct result.
  C. Both execute successfully and give the same result.
  D. Both execute successfully but do not give the required result.
  A. Only the first query gives the correct result.

• Question 7:

  View the Exhibit and examine the structure of the SALES and PRODUCTS tables.

  

  In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table.

  You want to list each product ID and the number of times it has been sold.

  Evaluate the following query:

  SQL>SELECT p.prod_id, COUNT(s.prod_id)

  FROM products p _____________ sales s

  ON p.prod_id = s.prod_id

  GROUP BY p.prod_id;

  Which two JOIN options can be used in the blank in the above query to get the required output? (Choose two.)

  A. JOIN
  B. FULL OUTER JOIN
  C. LEFT OUTER JOIN
  D. RIGHT OUTER JOIN
  B. FULL OUTER JOIN
  C. LEFT OUTER JOIN

• Question 8:

  SLS is a private synonym for the SH.SALES table.

  The user SH issues the following command:

  DROP SYNONYM sls;

  Which statement is true regarding the above SQL statement?

  A. Only the synonym would be dropped.
  B. The synonym would be dropped and the corresponding table would become invalid.
  C. The synonym would be dropped and the packages referring to the synonym would be dropped.
  D. The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid.
  A. Only the synonym would be dropped.

  ---

  explanation:

  A synonym is an alias for a table (or a view). Users can execute SQL statements against the synonym, and the database will map them into statements against the object to which the synonym points.

  Private synonyms are schema objects. Either they must be in your own schema, or they must be qualified with the schema name. Public synonyms exist independently of a schema. A public synonym can be referred to by any user to whom permission has been granted to see it without the need to qualify it with a schema name.

  Private synonyms must be a unique name within their schema. Public synonyms can have the same name as schema objects. When executing statements that address objects without a schema qualifier, Oracle will first look for the object in the local schema, and only if it cannot be found will it look for a public synonym.

• Question 9:

  Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose two.)

  A. The HAVING clause can be used with aggregate functions in subqueries.
  B. The WHERE clause can be used to exclude rows after dividing them into groups.
  C. The WHERE clause can be used to exclude rows before dividing them into groups.
  D. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
  E. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
  A. The HAVING clause can be used with aggregate functions in subqueries.
  C. The WHERE clause can be used to exclude rows before dividing them into groups.

• Question 10:

  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
  A. in the INTO clause of an INSERT statement
  B. in the FROM clause of a SELECT statement
  D. in the WHERE clause of a SELECT statement
  E. in the SET clause of an UPDATE statement

  ---

  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:

  C. sub query cannot be used

  F: is incorrect.

  Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 6-5