Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 331:

  Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results from the SALES1 table, Which are not present in the SALES2 table.

  

  Which set operator generates the required output?

  A. INTERSECT
  B. UNION
  C. PLUS
  D. MINUS
  E. SUBTRACT
  D. MINUS

  ---

  Explanation/Reference:

  References: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm

• Question 332:

  Examine data in the BRICKS table:

  

  Examine the BOXES table:

  

  Which two queries only return CUBE? (Choose two.)

  

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

• Question 333:

  Examine the description of the EMPLOYEES table:

  

  NLS_DATE_FORMAT is set to DD-MON-YY. Which query requires explicit data type conversion?

  A. SELECT salary + 120.50 FROM employees;
  B. SELECT SUBSTR(join date, 1, 2)- 10 FROM employees;
  C. SELECT join date 11.'11 salary FROM employees;
  D. SELECT join date FROM employees where join date > *10-02-2018*;
  E. SELECT join date + 20 FROM employees;
  D. SELECT join date FROM employees where join date > *10-02-2018*;

• Question 334:

  You execute this command:

  TRUNCATE TABIE depts;

  Which two are true?

  A. A ROLLBACK statement can be used to retrieve the deleted data.
  B. It drops any triggers defined on the table.
  C. It retains the indexes defined on the table.
  D. It retains the integrity constraints defined on the table,
  E. It always retains the space used by the removed rows.
  F. A FLASHBACK TABLE statement can be used to retrieve the deleted data.
  C. It retains the indexes defined on the table.
  D. It retains the integrity constraints defined on the table,

• Question 335:

  Examine this query:

  SELECT 2 FROM dual d1 CROSS JOIN dual d2 CROSS JOIN dual d3;

  What is returned upon execution?

  A. 0 rows
  B. an error
  C. 8 rows
  D. 6 rows
  E. 1 row
  F. 3 rows
  E. 1 row

• Question 336:

  Examine this constraint information:

  

  Which three statements are true?

  A. The SALARY column must have a value.
  B. The DEPTNO column in the EMP table can contain NULLS.
  C. The COMMISION column can contain negative values.
  D. The DEPTNO column in the EMP table can contain the value 1.
  E. The MANAGER column is a foreign key referencing the EMPNO column.
  F. The DNAME column has a unique constraint.
  G. An index is created automatically in the MANAGER column.
  B. The DEPTNO column in the EMP table can contain NULLS.
  C. The COMMISION column can contain negative values.
  E. The MANAGER column is a foreign key referencing the EMPNO column.

• Question 337:

  Evaluate the following CREATE TABLE commands:

  CREATE_TABLE orders

  (ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY,

  ord_date DATE,

  cust_id NUMBER (4) );

  CREATE TABLE ord_items

  (ord _no NUMBER (2),

  item_no NUMBER(3),

  qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200),

  expiry_date date CHECK (expiry_date> SYSDATE),

  CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),

  CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) );

  The above command fails when executed. What could be the reason?

  A. SYSDATE cannot be used with the CHECK constraint.
  B. The BETWEEN clause cannot be used for the CHECK constraint.
  C. The CHECK constraint cannot be placed on columns having the DATE data type.
  D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.
  A. SYSDATE cannot be used with the CHECK constraint.

• Question 338:

  Which three statements are true about an ORDER BY clause? (Choose three.)

  A. By default an ORDER BY clause sorts rows in descending order
  B. An ORDER BY clause will always precede a HAVING clause if both are used in the same top-level query.
  C. An ORDER BY clause always sorts NULL values last.
  D. By default an ORDER BY clause sorts rows in ascending order.
  E. An ORDER BY clause can perform a binary sort.
  F. An ORDER BY clause can perform a linguistic sort.
  D. By default an ORDER BY clause sorts rows in ascending order.
  E. An ORDER BY clause can perform a binary sort.
  F. An ORDER BY clause can perform a linguistic sort.

• Question 339:

  Which two statements are true about the rules of precedence for operators? (Choose two.)

  A. The + binary operator has the highest precedence in an expression in a SQL statement.
  B. The concatenation operator || is always evaluated before addition and subtraction in an expression.
  C. Multiple parentheses can be used to override the default precedence of operators in an expression.
  D. NULLS influence the precedence of operators in an expression.
  E. Arithmetic operators with equal precedence are evaluated from left to right within an expression.
  C. Multiple parentheses can be used to override the default precedence of operators in an expression.
  E. Arithmetic operators with equal precedence are evaluated from left to right within an expression.

• Question 340:

  Which three statements are true about single row functions?

  A. They can be used only in the where clause of a select statement.
  B. They can accept only one argument.
  C. They return a single result row per table.
  D. The argument can be a column name, variable, literal or an expression.
  E. They can be nested to any level.
  F. The date type returned can be different from the data type of the argument.
  D. The argument can be a column name, variable, literal or an expression.
  E. They can be nested to any level.
  F. The date type returned can be different from the data type of the argument.