Oracle 1Z0-047 Online Practice Questions and Exam Preparation

Oracle 1Z0-047 Online Questions & Answers

• Question 161:

  Which two statements are true regarding roles? (Choose two.)

  A. A role can be granted to itself.
  B. A role can be granted to PUBLIC.
  C. A user can be granted only one role at any point of time.
  D. The REVOKE command can be used to remove privileges but not roles from other users.
  E. Roles are named groups of related privileges that can be granted to users or other roles.
  B. A role can be granted to PUBLIC.
  E. Roles are named groups of related privileges that can be granted to users or other roles.

• Question 162:

  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 LOCATIONJD 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 163:

  Which two statements are true regarding operators used with subqueries? (Choose two.

  A. The NOT IN operator is equivalent to IS NULL
  B. The
  C. =ANY and =ALL operators have the same functionality.
  D. The IN operator cannot be used in single-row subqueries.
  E. The NOT operator can be used with IN, ANY and ALL operators.
  B. The
  E. The NOT operator can be used with IN, ANY and ALL operators.

• Question 164:

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

  

  Evaluate the following SOL statement:

  SELECT street_address FROM locations WHERE REGEXP_INSTR(street_address,'[^[: alpha:]]') = 1;

  Which statement is true regarding the output of this SOL statement?

  A. It would display all the street addresses that do not have a substring 'alpha'.
  B. It would display all the street addresses where the first character is a special character.
  C. It would display all the street addresses where the first character is a letter of the alphabet.
  D. It would display all the street addresses where the first character is not a letter of the alphabet.
  D. It would display all the street addresses where the first character is not a letter of the alphabet.

• Question 165:

  Which statement is true regarding the ROLLUP operator specified in the GROUP BY clause of a SQL statement?

  A. It produces only the subtotals for the groups specified in the GROUP BY clause.
  B. It produces only the grand totals for the groups specified in the GROUP BY clause.
  C. It produces higher-level subtotals, moving from right to left through the list of grouping columns specified in the GROUP BY clause.
  D. It produces higher-level subtotals, moving in all the directions through the list of grouping columns specified in the GROUP BY clause.
  C. It produces higher-level subtotals, moving from right to left through the list of grouping columns specified in the GROUP BY clause.

• Question 166:

  View the Exhibit and examine the description of the EMPLOYEES table.

  You want to calculate the total remuneration for each employee. Total remuneration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission.

  Which SOL statement would you execute to get the desired output?

  

  A. SELECTfirst_name, salary, salary*12+salary*commission_pct "Total" FROM EMPLOYEES;
  B. SELECTfirst_name, salary, salary*12+NVL((salary*commission_pct), 0) "Total" FROM EMPLOYEES;
  C. SELECTfirst_name, salary, salary*12 + NVL(salary, O)*commission_pct "Total" FROM EMPLOYEES;
  D. SELECTfirst_name, salary, salary*12+ (salary*NVL2(commission_pct, salary,salary +commission_pct))"Total" FROM EMPLOYEES;
  B. SELECTfirst_name, salary, salary*12+NVL((salary*commission_pct), 0) "Total" FROM EMPLOYEES;

• Question 167:

  View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

  

  You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:

  SELECT p.product_name, i.item_cnt FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id; What would happen when the above statement is executed?

  A. The statement would execute successfully to produce the required output.
  B. The statement would not execute because inline views and outer joins cannot be used together.
  C. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.
  D. The statement would not execute because the GROUP BY clause cannot be used in the inline view.
  A. The statement would execute successfully to produce the required output.

• Question 168:

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

  

  You executed the following command to add a primary key to the EMP table:

  ALTER TABLE emp

  ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)

  USING INDEX emp_id_idx;

  Which statement is true regarding the effect of the command?

  A. The PRIMARY KEY is created along with a new index.
  B. The PRIMARY KEY is created and it would use an existing unique index.
  C. The PRIMARY KEY would be created in a disabled state because it is using an existing index.
  D. The statement produces an error because the USING clause is permitted only in the CREATE TABLE command.
  B. The PRIMARY KEY is created and it would use an existing unique index.

• Question 169:

  View the Exhibit and examine the details for the CATEGORIES_TAB table. Evaluate the following incomplete SQL statement:

  

  SELECT category_name ,category_description FROM categories_tab You want to display only the rows that have 'harddisks' as part of the string in the CATEGORY_DESCRIPTION column. Which two WHERE clause options can give you the desired result? (Choose two.)

  A. WHERE REGEXPJJKE (category_description, 'hard+.s');
  B. WHERE REGEXPJJKE (category_description, `^H|hard+.s');
  C. WHERE REGEXPJJKE (category_description, '^H|hard+.s$');
  D. WHERE REGEXPJJKE (category_description, '[^Hlhard+.s]');
  A. WHERE REGEXPJJKE (category_description, 'hard+.s');
  B. WHERE REGEXPJJKE (category_description, `^H|hard+.s');

• Question 170:

  View the Exhibit and examine the description of EMPLOYEES and DEPARTMENTS tables. You want to display the EMPLOYEE_ID, LAST_NAME, and SALARY for the employees who get the maximum salary in their respective

  departments. The following SQL statement was written:

  WITH

  SELECT employee_id, last_name, salary

  FROM employees

  WHERE (department_id, salary) = ANY (SELECT*

  FROM dept_max)

  dept_max as (SELECT d.department_id, max(salary)

  FROM departments d JOIN employees j

  ON (d. department_id = j. department_id)

  GROUP BY d. department_id);

  Which statement is true regarding the execution and the output of this statement?

  

  A. The statement would execute and give the desired results.
  B. The statement would not execute because the = ANY comparison operator is used instead of=.
  C. The statement would not execute because the main query block uses the query name before it is even created.
  D. The statement would not execute because the comma is missing between the main query block and the query name.
  C. The statement would not execute because the main query block uses the query name before it is even created.