Oracle 1Z0-047 Online Practice Questions and Exam Preparation

Oracle 1Z0-047 Online Questions & Answers

• Question 191:

  Which statement correctly differentiates a system privilege from an object privilege?

  A. System privileges can be granted only by the DBA whereas object privileges can be granted by DBAs or the owner of the object.
  B. System privileges give the rights to only create user schemas whereas object privileges give rights to manipulate objects in a schema.
  C. Users require system privileges to gain access to the database whereas they require object privileges to create objects in the database.
  D. A system privilege is the right to perform specific activities in a database whereas an object privilege is a right to perform activities on a specific object in the database.
  D. A system privilege is the right to perform specific activities in a database whereas an object privilege is a right to perform activities on a specific object in the database.

• Question 192:

  View the Exhibit and examine DEPARTMENTS and the LOCATIONS tables.

  Evaluate the following SOL statement:

  SELECT location_id, city FROM locations I WHERE NOT EXISTS (SELECT location_id FROM departments WHERE location_id <> I. location_id);

  This statement was written to display LOCATION_ID and CITY where there are no departments located. Which statement is true regarding the execution and output of the command?

  

  A. The statement would execute and would return the desired results.
  B. The statement would not execute because the = comparison operator is missing in the WHERE clause of the outer query.
  C. The statement would execute but it will return zero rows because the WHERE clause in the inner query should have the = operator instead of .
  D. The statement would not execute because the WHERE clause in the outer query is missing the column name for comparison with the inner query result.
  C. The statement would execute but it will return zero rows because the WHERE clause in the inner query should have the = operator instead of .

• Question 193:

  View the Exhibit and examine the structure of ORDER_ITEMS and ORDERS tables. You need to remove from the ORDER_ITEMS table those rows that have an order status of 0 or 1 in the ORDERS table.

  

  Which DELETE statements are valid? (Choose all that apply.)

  A. DELETEFROM order_itemsWHERE order_id IN (SELECT order_idFROM ordersWHERE order_status in (0,1));
  B. DELETE *FROM order_itemsWHERE order_id IN (SELECT order_idFROM ordersWHERE order_status IN (0,1));
  C. DELETE FROM order_items iWHERE order_id = (SELECT order_id FROM orders o WHERE i. order_id = o. order_id AND order_status IN (0,1));
  D. DELETEFROM (SELECT* FROM order_items i.orders o WHERE i.order_id = o.order_id AND order_status IN (0,1));
  A. DELETEFROM order_itemsWHERE order_id IN (SELECT order_idFROM ordersWHERE order_status in (0,1));
  C. DELETE FROM order_items iWHERE order_id = (SELECT order_id FROM orders o WHERE i. order_id = o. order_id AND order_status IN (0,1));
  D. DELETEFROM (SELECT* FROM order_items i.orders o WHERE i.order_id = o.order_id AND order_status IN (0,1));

• Question 194:

  In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause?

  A. to find the groups forming the subtotal in a row
  B. to create group-wise grand totals for the groups specified within a GROUP BY clause
  C. to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals
  D. to create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is cross-tabular report for calculating the subtotals
  C. to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals

• Question 195:

  A non-correlated subquery can be defined as________ .

  A. a set of sequential queries, all of which must always return a single value
  B. a set of sequential queries, all of which must return values from the same table
  C. a SELECT statement that can be embedded in a clause of another SELECT statement only
  D. a set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query
  D. a set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query

• Question 196:

  View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the

  PRIMARY KEY in the ORDERS table.

  Evaluate the following CREATE TABLE command:

  CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id) AS SELECT order_id.order_date,customer_id

  FROM orders;

  Which statement is true regarding the above command?

  

  A. The NEW_IDRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.
  B. The NEW_IDRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.
  C. The NEW_IDRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
  D. The NEW_IDRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.
  B. The NEW_IDRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.

• Question 197:

  Which three statements are true regarding group functions? (Choose three.)

  A. They can be used on columns or expressions.
  B. They can be passed as an argument to another group function.
  C. They can be used only with a SQL statement that has the GROUP BY clause.
  D. They can be used on only one column in the SELECT clause of a SQL statement.
  E. They can be used along with the single-row function in the SELECT clause of a SQL statement.
  A. They can be used on columns or expressions.
  B. They can be passed as an argument to another group function.
  E. They can be used along with the single-row function in the SELECT clause of a SQL statement.

• Question 198:

  Which statement is true regarding external tables?

  A. The default REJECT LIMIT for external tables is UNLIMITED.
  B. The data and metadata for an external table are stored outside the database.
  C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.
  D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.
  D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.

• Question 199:

  Which statement is true regarding external tables?

  A. The default REJECT LIMIT for external tables is UNLIMITED.
  B. The data and metadata for an external table are stored outside the database.
  C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.
  D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.
  D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.

• Question 200:

  Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)

  A. The HAVING clause conditions can have aggregate functions.
  B. The HAVING clause conditions can use aliases for the columns.
  C. WHERE and HAVING clauses cannot be used together in a SQL statement.
  D. The WHERE clause is used to exclude rows before the grouping of data.
  E. The HAVING clause is used to exclude one or more aggregated results after grouping data.
  A. The HAVING clause conditions can have aggregate functions.
  D. The WHERE clause is used to exclude rows before the grouping of data.
  E. The HAVING clause is used to exclude one or more aggregated results after grouping data.