Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 221:

  The DBA issues this SQL command:

  CREATE USER scott IDENTIFIED by tiger; What privileges does the user Scott have at this point?

  A. no privileges
  B. only the SELECT privilege
  C. only the CONNECT privilege
  D. all the privileges of a default user
  A. no privileges

  ---

  when a user is created, by default no privilege is granted

  Incorrect answer:

  B. SELECT is not grant

  C. CONNECT is not grant

  D. default profile is grant by default not privilege.

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

• Question 222:

  Which tasks can be performed using SQL functions that are built into Oracle database? (Choose three.)

  A. finding the remainder of a division
  B. adding a number to a date for a resultant date value
  C. comparing two expressions to check whether they are equal
  D. checking whether a specified character exists in a given string
  E. removing trailing, leading, and embedded characters from a character string
  A. finding the remainder of a division
  C. comparing two expressions to check whether they are equal
  D. checking whether a specified character exists in a given string

• Question 223:

  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.

  ---

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

  Which best describes an inline view?

  A. a schema object
  B. a sub query that can contain an ORDER BY clause
  C. another name for a view that contains group functions
  D. a sub query that is part of the FROM clause of another query
  D. a sub query that is part of the FROM clause of another query

  ---

  a sub query that is part of the FROM clause of another query

  Incorrect answer:

  A. is not a schema object

  B. sub query can contain GROUP BY clause as well.

  C. does not necessary contains group functions

  Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-21

• Question 225:

  What is true about sequences?

  A. The start value of the sequence is always 1.
  B. A sequence always increments by 1.
  C. The minimum value of an ascending sequence defaults to 1.
  D. The maximum value of descending sequence defaults to 1.
  C. The minimum value of an ascending sequence defaults to 1.

• Question 226:

  Examine the structure of the EMP_DEPT_VU view:

  

  Which SQL statement produces an error?

  A. SELECT *FROM emp_dept_vu;
  B. SELECT department_id, SUM(salary) FROM emp_dept_vu GROUP BY department_id;
  C. SELECT department_id, job_id, AVG(salary) FROM emp_dept_vu GROUP BY department_id, job_id;
  D. SELECT job_id, SUM(salary) FROM emp_dept_vu WHERE department_id IN (10, 20) GROUP BY job_id HAVING SUM(salary) > 20000;
  E. None of the statements produce an error; all are valid.
  E. None of the statements produce an error; all are valid.

  ---

  None of the statements produce an error. Incorrect answer:

  A. Statement will not cause error

  B. Statement will not cause error

  C. Statement will not cause error

  D. Statement will not cause error

• Question 227:

  Which two statements are true regarding working with dates? (Choose two.)

  A. The default internal storage of dates is in the numeric format.
  B. The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required.
  C. The default internal storage of dates is in the character format.
  D. The RR date format automatically calculates the century from the SYSDATE function and does not allow the user to enter the century.
  A. The default internal storage of dates is in the numeric format.
  B. The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required.

  ---

  Working with Dates

  The Oracle Database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds.

  The default display and input format for any date is DD-MON-RR.

  RR Date Format

  The RR date format is similar to the YY element, but you can use it to specify different centuries. Use the RR date format element instead of YY so that the century of the return value varies according to the specified two digit year and the last

  two digits of the current year. The table in the slide summarizes the behavior of the RR element.

  

  Note the values shown in the last two rows of the above table. As we approach the middle of the century, then the RR behavior is probably not what you want.

  This data is stored internally as follows:

  CENTURY YEAR MONTH DAY HOUR MINUTE SECOND 19 87 06 17 17 10 43

• Question 228:

  View the Exhibits and examine the structure of the products and sales tables. Exhibit 1:

  

  Exhibit 2:

  

  Which two SQL statements would give the same output? (Choose two.)

  A. SELECT prod_id FROM productsINTERSECTSELECT prod_id FROM sales;
  B. SELECT prod_id FROM productsMINUSSELECT prod_id FROM sales;
  C. SELECT DISTINCT p.prod_idFROM products p JOIN sales sON p.prod_id=s.prod_id;
  D. SELECT DISTINCT p.prod_idFROM products p JOIN sales sON p.prod_id s.prod_id;
  A. SELECT prod_id FROM productsINTERSECTSELECT prod_id FROM sales;
  C. SELECT DISTINCT p.prod_idFROM products p JOIN sales sON p.prod_id=s.prod_id;

• Question 229:

  There is a simple view SCOTT.DEPT_VIEW on the table SCOTT.DEPT.

  This insert fails with an error:

  SQL> insert into dept_view values('SUPPORT','OXFORD'); insert into dept_view values('SUPPORT','OXFORD')

  *

  ERROR at line 1:

  ORA-01400: cannot insert NULL into ("SCOTT"."DEPT"."DEPTNO")

  What might be the problem?

  A. The INSERT violates a constraint on the detail table.
  B. The INSERT violates a constraint on the view.
  C. The view was created as WITH READ ONLY.
  D. The view was created as WITH CHECK OPTION.
  A. The INSERT violates a constraint on the detail table.

  ---

  B is incorrect because constraints are enforced on detail tables, not on views. C and D are incorrect because the error message would be different.

• Question 230:

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