Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 251:

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

  

  You want to generate a report showing the total compensation paid to each employee to date. You issue the following query:

  

  What is the outcome?

  A. It generates an error because the alias is not valid.
  B. It executes successfully and gives the correct output.
  C. It executes successfully but does not give the correct output.
  D. It generates an error because the usage of the ROUND function in the expression is not valid.
  E. It generates an error because the concatenation operator can be used to combine only two items.
  C. It executes successfully but does not give the correct output.

  ---

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

• Question 252:

  The EMPLOYEES table contains these columns:

  EMPLOYEE_ID NUMBER(4)

  ENAME VARCHAR2 (25)

  JOB_ID VARCHAR2(10)

  Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column, for those employees whose ENAME ends with a the letter "n"?

  A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n';
  B. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, , -1, 1) FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n';
  C. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1, 1) FROM EMPLOYEES WHERE INSTR(ENAME, 1, 1) = 'n';
  D. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1, 1) FROM EMPLOYEES WHERE INSTR(ENAME, -1, 1) = 'n';
  A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n';

  ---

  INSTR is a character function return the numeric position of a named string.

  INSTR(NAMED, 'a')

  Incorrect answer:

  B. Did not return a numeric position for `a'.

  C. Did not return a numeric position for `a'.

  D. Did not return a numeric position for `a'.

  Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-8

• Question 253:

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

  

  Each promotion has a duration of at least seven days.

  Your manager has asked you to generate a report, which provides the weekly cost for each promotion done to l date.

  Which query would achieve the required result?

  A. SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7 FROM promotions;
  B. SELECT promo_name, (promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions;
  C. SELECT promo_name, promo_cost/(promo_end_date-promo_begin_date/7) FROM promotions;
  D. SELECT promo_name, promo_cost/((promo_end_date-promo_begin_date)/7) FROM promotions;
  D. SELECT promo_name, promo_cost/((promo_end_date-promo_begin_date)/7) FROM promotions;

• Question 254:

  Which statement correctly describes SQL and /SQL*Plus?

  A. Both SQL and /SQL*plus allow manipulation of values in the database.
  B. /SQL*Plus recognizes SQL statements and sends them to the server; SQL is the Oracle proprietary interface for executing SQL statements.
  C. /SQL*Plus is a language for communicating with the Oracle server to access data; SQL recognizes SQL statements and sends them to the server.
  D. SQL manipulates data and table definitions in the database; /SQL*Plus does not allow manipulation of values in the database.
  A. Both SQL and /SQL*plus allow manipulation of values in the database.

• Question 255:

  You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:

  

  You want to create a SALE_PROD view by executing the following SQL statements:

  

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

  A. The view will be created and you can perform DLM operations on the view
  B. The view will not be created because the join statements are not allowed for creating a view
  C. The view will not be created because the GROUP BY clause is not allowed for creating a view
  D. The view will be created but no DML operations will be allowed on the view
  D. The view will be created but no DML operations will be allowed on the view

  ---

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

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

  

  NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the customers table. Evaluate the following insert statement:

  

  The insert statement fails when executed. What could be the reason?

  A. The values clause cannot be used in an INSERT with a subquery.
  B. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match.
  C. The where clause cannot be used in a subquery embedded in an INSERT statement.
  D. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table.
  A. The values clause cannot be used in an INSERT with a subquery.

  ---

  Copying Rows from Another Table

  Write your INSERT statement with a subquery:

  Do not use the VALUES clause.

  Match the number of columns in the INSERT clause to those in the subquery. Inserts all the rows returned by the subquery in the table, sales_reps.

• Question 257:

  A view and a synonym are created as follows:

  create view dept_v as select * from dept; create synonym dept_s for dept_v; Subsequently the table DEPT is dropped.

  What will happen if you query the synonym DEPT_S ? (Choose the best answer.)

  A. There will not be an error because the synonym addresses the view, which still exists, but there will beno rows returned.
  B. There will not be an error if you first recompile the view with the command ALTER VIEW DEPT_VCOMPILE FORCE;
  C. There will be an error because the synonym will be invalid.
  D. There will be an error because the view will be invalid.
  E. There will be an error because the view will have been dropped implicitly when the table was dropped.
  D. There will be an error because the view will be invalid.

  ---

  The synonym will be fine, but the view will be invalid. Oracle will attempt to recompile the view, but this will fail.

• Question 258:

  Exhibit contains the structure of PRODUCTS table:

  

  Evaluate the following query:

  

  What would be the outcome of executing the above SQL statement?

  A. It produces an error
  B. It shows the names of products whose list price is the second highest in the table.
  C. It shown the names of all products whose list price is less than the maximum list price
  D. It shows the names of all products in the table
  B. It shows the names of products whose list price is the second highest in the table.

• Question 259:

  For which action can you use the TO_DATE function?

  A. Convert any date literal to a date
  B. Convert any numeric literal to a date
  C. Convert any character literal to a date
  D. Convert any date to a character literal
  E. Format '10-JAN-99' to `January 10 1999'
  C. Convert any character literal to a date

• Question 260:

  You need to display the first names of all customers from the customers table that contain the character 'e' and have the character 'a' in the second last position.

  Which query would give the required output?

  

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

  ---

  The SUBSTR(string, start position, number of characters) function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position:

  substr('http://www.domain.com', 12, 6) = domain

  The position at which the first character of the returned string begins. When position is 0 (zero), then it is treated as 1. When position is positive, then the function counts from the beginning of string to find the first character.

  When position is negative, then the function counts backward from the end of string.

  substring_length

  The length of the returned string. SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode complete characters.

  SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points. When you do not specify a value for this argument, then the function

  The INSTR(source string, search item, [start position], [nth occurrence of search item]) function returns a number that represents the position in the source string, beginning from the given start position, where the nth occurrence of the search

  item begins:

  instr('http://www.domain.com', '.', 1, 2) = 18