Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 211:

  View the Exhibits and examine the structures of the products and sales tables.

  

  Which two SQL statements would give the same output?

  

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

  ---

  INTERSECT and JOIN works on comparable columns.

• Question 212:

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

  

  Evaluate the following SQL statement:

  

  Which statement is true regarding the outcome of the above query?

  A. It shows COST_REMARK for all the promos in the table.
  B. It produces an error because the SUBQUERY gives an error.
  C. It shows COST_REMARK for all the promos in the promo category 'TV'
  D. It produces an error because SUBQUERIES cannot be used with the case expression.
  A. It shows COST_REMARK for all the promos in the table.

• Question 213:

  Which three statements/commands would cause a transaction to end? (Choose three.)

  A. COMMIT
  B. SELECT
  C. CREATE
  D. ROLLBACK
  E. SAVEPOINT
  A. COMMIT
  C. CREATE
  D. ROLLBACK

• Question 214:

  Examine the structure of the employees table:

  

  There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.

  You want to display the name, joining date, and manager for all the employees. Newly hired employees are yet to be assigned a department or a manager. For them, 'No Manager' should be displayed in the manager column.

  Which SQL query gets the required output?

  

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

  ---

  We should use LEFT OUTER JOIN as we want to display employees which have no (have NULL values for) managers.

• Question 215:

  A data manipulation language statement _____.

  A. completes a transaction on a table
  B. modifies the structure and data in a table
  C. modifies the data but not the structure of a table
  D. modifies the structure but not the data of a table
  C. modifies the data but not the structure of a table

  ---

  modifies the data but not the structure of a table

  Incorrect answer:

  A. DML does not complete a transaction

  B. DDL modifies the structure and data in the table D. DML does not modified table structure.

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

• Question 216:

  View the Exhibits and examine the structures of the products, sales, and customers tables.

  

  

  You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for a customers in 'Tokyo'. Which two queries give the required result?

  

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

• Question 217:

  The PART_CODE column in the SPARES table contains the following list of values:

  

  Which statement is true regarding the outcome of the above query?

  A. It produces an error.
  B. It displays all values.
  C. It displays only the values A%_WQ123 and AB_WQ123 .
  D. It displays only the values A%_WQ123 and A%BWQ123 .
  E. It displays only the values A%BWQ123 and AB_WQ123.
  D. It displays only the values A%_WQ123 and A%BWQ123 .

  ---

  Combining Wildcard Characters The % and _ symbols can be used in any combination with literal characters. The example in the slide displays the names of all employees whose last names have the letter "o" as the second character. ESCAPE Identifier When you need to have an exact match for the actual % and _ characters, use the ESCAPE identifier. This option specifies what the escape character is. If you want to search for strings that contain SA_, you can use the following SQL statement: SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\';

• Question 218:

  Which statement is true regarding transactions? (Choose all that apply.)

  A. A transaction can consist only of a set of DML and DDL statements.
  B. A part or an entire transaction can be undone by using ROLLBACK command.
  C. A transaction consists of a set of DML or DCL statements.
  D. A part or an entire transaction can be made permanent with a COMMIT.
  E. A transaction can consist of only a set of queries or DML or DDL statements.
  B. A part or an entire transaction can be undone by using ROLLBACK command.
  C. A transaction consists of a set of DML or DCL statements.

• Question 219:

  Which two statements are true about WHERE and HAVING clauses? (Choose two)

  A. A WHERE clause can be used to restrict both rows and groups.
  B. A WHERE clause can be used to restrict rows only.
  C. A HAVING clause can be used to restrict both rows and groups.
  D. A HAVING clause can be used to restrict groups only.
  E. A WHERE clause CANNOT be used in a query of the query uses a HAVING clause.
  F. A HAVING clause CANNOT be used in sub queries.
  B. A WHERE clause can be used to restrict rows only.
  D. A HAVING clause can be used to restrict groups only.

  ---

  B: WHERE clause cannot be use to restrict groups WHERE clause cannot be use when there is group functions.

  D: A HAVING clause can only e used to restrict GROUPS.

  Note: HAVING clause to specify which groups are to be displayed and thus further restrict the groups on the basis of aggregate information. The Oracle server performs the following steps when you use the Having clause

  1.

  rows are grouped

  2.

  the group function is applied to the group

  3.

  the group that match the criteria in the Having clause are displayed. Incorrect Answers :

  A. Where clause cannot be use to restrict groups C. A HAVING clause can only e used to restrict GROUPS. E. WHERE clause cannot be use when there is group function, instead HAVING is to be use.

  F. There is no constraint to use HAVING clause in a sub queries.

  Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Aggregating Data using Group Functions, p. 5-20

• Question 220:

  View the Exhibit and examine the structure of the ORDERS and CUSTOMERS tables.

  

  Evaluate the following SQL command:

  SQL> SELECT o.order_id, c.cust_name, o.order_total, c.credit_limit

  FROM orders o JOIN customers c

  USING (customer_id)

  WHERE o.order_total > c.credit_limit

  FOR UPDATE

  ORDER BY o.order_id;

  Which two statements are true regarding the outcome of the above query? (Choose two.)

  A. It locks all the rows that satisfy the condition in the statement.
  B. It locks only the columns that satisfy the condition in both the tables.
  C. The locks are released only when a COMMIT or ROLLBACK is issued.
  D. The locks are released after a DML statement is executed on the locked rows.
  A. It locks all the rows that satisfy the condition in the statement.
  C. The locks are released only when a COMMIT or ROLLBACK is issued.

  ---

  FOR UPDATE Clause in a SELECT Statement ?Locks the rows in the EMPLOYEES table where job_id is SA_REP. ?Lock is released only when you issue a ROLLBACK or a COMMIT. ?If the SELECT statement attempts to lock a row that is locked by another user, the database waits until the row is available, and then returns the results of the SELECTstatement SELECT employee_id, salary, commission_pct, job_id FROM employees WHERE job_id = 'SA_REP' FOR UPDATE ORDER BY employee_id;