Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 1:

  Examine the structure of the customers table:

  

  CUSTNO is the primary key in the table. You want to find out if any customers' details have been entered more than once using different CUSTNO, by listing all the duplicate names.

  Which two methods can you use to get the required result?

  A. Self-join
  B. Subquery
  C. Full outer-join with self-join
  D. Left outer-join with self-join
  E. Right outer-join with self-join
  A. Self-join
  B. Subquery

• Question 2:

  Which three tasks can be performed using SQL functions built into Oracle Database?

  A. Displaying a date in a nondefault format
  B. Finding the number of characters in an expression
  C. Substituting a character string in a text expression with a specified string
  D. Combining more than two columns or expressions into a single column in the output
  A. Displaying a date in a nondefault format
  B. Finding the number of characters in an expression
  C. Substituting a character string in a text expression with a specified string

• Question 3:

  Which statement is true regarding the UNION operator?

  A. By default, the output is not sorted.
  B. Null values are not ignored during duplicate checking.
  C. Names of all columns must be identical across all select statements.
  D. The number of columns selected in all select statements need not be the same.
  B. Null values are not ignored during duplicate checking.

  ---

  For the UNION operator the nulls values are not ignored during duplicate checking.

  Incorrect:

  Not A: The UNION operator implicitly sorts the output. Not D: Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.

• Question 4:

  Examine the description of the CUSTOMERS table:

  

  The CUSTOMER_ID column is the primary key for the table.

  Which statement returns the city address and the number of customers in the cities Los Angeles or San Francisco?

  A. SELECT city_address, COUNT(*)FROM. customersWHERE city_address IN ( `Los Angeles', `San Fransisco');
  B. SELECT city_address, COUNT (*)FROM. customersWHERE city address IN ( `Los Angeles', `San Fransisco')GROUP BY city_address;
  C. SELECT city_address, COUNT(customer_id)FROM. customersWHERE city_address IN ( `Los Angeles', `San Fransisco')GROUP BY. city_address, customer_id;
  D. SELECT city_address, COUNT (customer_id)FROM . customersGROUP BY city_address IN ( `Los Angeles', `San Fransisco');
  B. SELECT city_address, COUNT (*)FROM. customersWHERE city address IN ( `Los Angeles', `San Fransisco')GROUP BY city_address;

  ---

  Not C: The customer ID in the GROUP BY clause is wrong

• Question 5:

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

  

  Using the customers table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed. Which SQL statement would produce the required result?

  

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

  ---

  NVL Function

  Converts a null value to an actual value:

  Data types that can be used are date, character, and number.

  Data types must match:

  NVL(commission_pct, 0)

  NVL(hire_date, '01-JAN-97')

  NVL(job_id, 'No Job Yet')

• Question 6:

  What is true regarding sub queries?

  A. The inner query always sorts the results of the outer query
  B. The outer query always sorts the results of the inner query
  C. The outer query must return a value to the outer query
  D. The inner query returns a value to the outer query
  E. The inner query must always return a value or the outer query will give an error
  D. The inner query returns a value to the outer query

  ---

  The inner query returns a value to the outer query. If the inner query does not return a value, the outer query does not return a result

• Question 7:

  Examine the structure and data of the CUST_TRANS table:

  

  Dates are stored in the default date format dd-mon-rr in the CUST_TRANS table. Which three SQL statements would execute successfully?

  A. SELECT transdate + '10' FROM cust_trans;
  B. SELECT * FROM cust_trans WHERE transdate = '01-01-07';
  C. SELECT transamt FROM cust_trans WHERE custno > '11';
  D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
  E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;
  A. SELECT transdate + '10' FROM cust_trans;
  C. SELECT transamt FROM cust_trans WHERE custno > '11';
  D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';

• Question 8:

  Which two are true about aggregate functions? (Choose two.)

  A. You can use aggregate functions in any clause of a SELECT statement.
  B. You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.
  C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
  D. You can pass column names, expressions, constants, or functions as parameter to an aggregate function.
  E. You can use aggregate functions on a table, only by grouping the whole table as one single group.
  F. You cannot group the rows of a table by more than one column while using aggregate functions.
  A. You can use aggregate functions in any clause of a SELECT statement.
  D. You can pass column names, expressions, constants, or functions as parameter to an aggregate function.

• Question 9:

  The user Sue issues this SQL statement:

  GRANT SELECT ON sue.EMP TO alice WITH GRANT OPTION;

  The user Alice issues this SQL statement:

  GRANT SELECT ON sue.EMP TO reena WITH GRANT OPTION; The user Reena issues this SQL statement:

  GRANT SELECT ON sue.EMP TO timber;

  The user Sue issues this SQL statement:

  REVOKE select on sue.EMP FROM alice;

  For which users does the revoke command revoke SELECT privileges on the SUE.EMP table?

  A. Alice only
  B. Alice and Reena
  C. Alice, Reena, and Timber
  D. Sue, Alice, Reena, and Timber
  C. Alice, Reena, and Timber

  ---

  use the REVOKE statement to revoke privileges granted to other users. Privilege granted to others through the WITH GRANT OPTION clause are also revoked. Alice, Reena and Timber will be revoke.

  Incorrect answer:

  A. the correct answer should be Alice, Reena and Timber B. the correct answer should be Alice, Reena and Timber D. the correct answer should be Alice, Reena and Timber Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-17

• Question 10:

  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.