Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 291:

  A session's NLS_DATE_FORMAT is set to DD Mon YYYY.

  Which two queries return the value 1 Jan 2019? (Choose two.)

  A. SELECT TO_DATE('2019-01-01', 'YYYY-MM-DD') FROM DUAL;
  B. SELECT DATE '2019-01-01' FROM DUAL ;
  C. SELECT TO_CHAR('2019-01-01') FROM DUAL; 2019-01-01
  D. SELECT '2019-01-01' FROM DUAL ; 2019-01-01
  E. SELECT TO_ DATE('2019-01-01') FROM DUAL;
  A. SELECT TO_DATE('2019-01-01', 'YYYY-MM-DD') FROM DUAL;
  B. SELECT DATE '2019-01-01' FROM DUAL ;

• Question 292:

  Which two are true about the data dictionary? (Choose two.)

  A. The data dictionary is constantly updated to reflect changes to database objects, permissions, and data.
  B. All user actions are recorded in the data dictionary.
  C. All users have permissions to access all information in the data dictionary by default.
  D. The SYS user owns all base tables and user-accessible views in the data dictionary.
  E. Base tables in the data dictionary have the prefix DBA_.
  A. The data dictionary is constantly updated to reflect changes to database objects, permissions, and data.
  D. The SYS user owns all base tables and user-accessible views in the data dictionary.

• Question 293:

  Which statement correctly grants a system privilege?

  A. GRANT CREATE VIEW ON table1 TO user1;
  B. GRANT ALTER TABLE TO PUBLIC;
  C. GRANT CREATE TABLE TO user1, user2;
  D. GRANT CREATE SESSION TO ALL;
  C. GRANT CREATE TABLE TO user1, user2;

• Question 294:

  Which two statements are true regarding constraints? (Choose two)

  A. A constraint is enforced only for an INSERT operation on a table.
  B. A foreign key cannot contain NULL values.
  C. A column with the UNIQUE constraint can store NULLS.
  D. You can have more than one column in a table as part of a primary key.
  C. A column with the UNIQUE constraint can store NULLS.
  D. You can have more than one column in a table as part of a primary key.

• Question 295:

  Which two are true about unused columns? (Choose two.)

  A. Setting an indexed column to unused results in an error.
  B. You can query the data dictionary to see the names of unused columns.
  C. You can specify multiple column names in an ALTER TABLE...SET UNUSED statement.
  D. If you set all the columns of a table to unused, the table is automatically dropped.
  E. CASCADE CONSTRAINTS must be specified when setting a column to unused if that column is referenced in a constraint on another column.
  C. You can specify multiple column names in an ALTER TABLE...SET UNUSED statement.
  E. CASCADE CONSTRAINTS must be specified when setting a column to unused if that column is referenced in a constraint on another column.

• Question 296:

  Evaluate the following SQL statement SQL>SELECT promo_id, prom _category FROM promotions WHERE promo_category='Internet' ORDER BY promo_id UNION SELECT promo_id, promo_category FROM Pomotions WHERE promo_category = `TV' UNION SELECT promoid, promocategory FROM promotions WHERE promo category='Radio' Which statement is true regarding the outcome of the above query?

  A. It executes successfully and displays rows in the descend ignore of PROMO CATEGORY.
  B. It produces an error because positional, notation cannot be used in the ORDER BY clause with SBT operators.
  C. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.
  D. It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.
  D. It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.

• Question 297:

  The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.

  The table has two rows whose CUST_LAST_NAME values are Anderson and Ausson.

  Which query produces output for CUST_LAST_NAME containing Oder for the first row and Aus for the second?

  A. SELECT REPLACE(REPLACE(cust_last_name, `son', `'), `An', `O') FROM customers;
  B. SELECT REPLACE(TRIM(TRAILING `son' FROM cust_last_name), `An', `O') FROM customers;
  C. SELECT REPLACE(SUBSTR(cust_last_name, -3), `An', `O') FROM customers;
  D. SELECT INITCAP(REPLACE(TRIM(`son' FROM cust_last_name), `An', `O')) FROM customers;
  A. SELECT REPLACE(REPLACE(cust_last_name, `son', `'), `An', `O') FROM customers;

• Question 298:

  Which three statements are true regarding subqueries? (Choose three.)

  A. Multiple columns or expressions can be compared between the main query and subquery.
  B. Subqueries can contain ORDER BY but not the GROUP BY clause.
  C. Main query and subquery can get data from different tables.
  D. Subqueries can contain GROUP BY and ORDER BY clauses.
  E. Main query and subquery must get data from the same tables.
  F. Only one column or expression can be compared between the main query and subquery.
  A. Multiple columns or expressions can be compared between the main query and subquery.
  C. Main query and subquery can get data from different tables.
  D. Subqueries can contain GROUP BY and ORDER BY clauses.

  ---

  Explanation/Reference:

  References: http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html

• Question 299:

  SELECT *

  FROM bricks,colors;

  Which two statements are true?

  A. You can add an ON clause with a join condition.
  B. You can add a WHERE clause with filtering criteria.
  C. It returns the number of rows in BRICKS plus the number of rows in COLORS.
  D. You can add a USING clause with a join condition.
  E. It returnsthe same rows as SELECT * FROM bricks CROSS JOIN colors.
  B. You can add a WHERE clause with filtering criteria.
  E. It returnsthe same rows as SELECT * FROM bricks CROSS JOIN colors.

• Question 300:

  Examine the description of the EMPLOYEES table:

  

  Examine these requirements:

  1.

  Display the manager id and salary of the lowest paid employee for that manager.

  2.

  Exclude anyone whose manager is not known.

  3.

  Exclude any managers where the minimum salary is 6000 or less.

  4.

  Sort the output by minimum salary with the highest salary shown first.

  Which statement will do this?

  

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