Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 41:

  Examine the description of the EMPLOYEES table:

  EMP_ID NUMBER(4) NOT NULL

  LAST_NAME VARCHAR2(30) NOT NULL

  FIRST_NAME VARCHAR2(30)

  DEPT_ID NUMBER(2)

  Which statement produces the number of different departments that have employees with last name Smith?

  A. SELECT COUNT(*) FROM employees WHERE last_name='Smith';
  B. SELECT COUNT (dept_id) FROM employees WHERE last_name='Smith';
  C. SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith';
  D. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';
  E. SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith';
  D. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';

• Question 42:

  You are currently located in Singapore and have connected to a remote database in Chicago.

  You issue the following command:

  Exhibit:

  

  PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table.

  What is the outcome?

  A. Number of days since the promo started based on the current Singapore data and time.
  B. An error because the ROUND function specified is invalid
  C. An error because the WHERE condition specified is invalid
  D. Number of days since the promo started based on the current Chicago data and time
  D. Number of days since the promo started based on the current Chicago data and time

• Question 43:

  A sequence is created as follows:

  create sequence seq1 maxvalue 50;

  If the current value is already 50, when you attempt to select SEQ1.NEXTVAL what will happen?

  A. The sequence will cycle and issue 0.
  B. The sequence will cycle and issue 1.
  C. The sequence will reissue 50.
  D. There will be an error.
  D. There will be an error.

  ---

  The default is NOCYCLE, and the sequence cannot advance further.

• Question 44:

  Which statement is true regarding synonyms?

  A. Synonyms can be created only for a table
  B. Synonyms are used to reference only those tables that are owned by another user
  C. The DROP SYNONYM statement removes the synonym and the table on which the synonym has been created becomes invalid D. A public synonym and a private synonym can exist with the same name for the same table
  D

• Question 45:

  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
  B. Abigail Pa

• Question 46:

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

  You need to generate a report of all promos from the PROMOTIONS table based on the following conditions:

  1.

  The promo name should not begin with 'T' or 'N'.

  2.

  The promo should cost more than $20000.

  3.

  The promo should have ended after 1st January 2001. Which WHERE clause would give the required result?

  A. WHERE promo_name NOT LIKE 'T%' OR promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
  B. WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR promo_cost > 20000 OR promo_end_date > '1-JAN-01'
  C. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
  D. WHERE (promo_name NOT LIKE '%T%' OR promo_name NOT LIKE '%N%') AND(promo_cost > 20000 AND promo_end_date > '1-JAN-01')
  C. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'

• Question 47:

  What is true of using group functions on columns that contain NULL values?

  A. Group functions on columns ignore NULL values.
  B. Group functions on columns returning dates include NULL values.
  C. Group functions on columns returning numbers include NULL values.
  D. Group functions on columns cannot be accurately used on columns that contain NULL values.
  E. Group functions on columns include NULL values in calculations if you use the keyword INC_NULLS.
  A. Group functions on columns ignore NULL values.

  ---

  group functions on column ignore NULL values

  Incorrect answer:

  B. group functions on column ignore NULL values

  C. group functions on column ignore NULL values

  D. NVL function can be use for column with NULL values E. no such INC_NULLS keyword

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

• Question 48:

  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.

  ---

  ?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 49:

  Examine the structure of the employees table.

  

  You want to display the maximum and minimum salaries of employees hired 1 year ago. Which two statements would get the correct output?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  A. Option A
  C. Option C

  ---

  Incorrect:

  Not B, not D: You cannot use a GROUP BY clause in a WHERE statement.

• Question 50:

  Evaluate the following SQL statement:

  

  Which statement is true regarding the above query if one of the values generated by the subquery is null?

  A. It produces an error.
  B. It executes but returns no rows.
  C. It generates output for null as well as the other values produced by the subquery.
  D. It ignores the null value and generates output for the other values produced by the subquery.
  D. It ignores the null value and generates output for the other values produced by the subquery.