Free 1z0-071 – Oracle Datbase 12c SQL Practice Test Question

Select all that apply, then click Submit answer.

• ○

  A subquery can appear on either side of a comparison operator.

• ○

  Only two subqueries can be placed at one level.

• ○

  A subquery can retrieve zero or more rows.

• ○

  A subquery can be used only in SQL query statements.

• ○

  There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.
  
  

Select all that apply, then click Submit answer.

• ○

  5 01-MAR-2019

• ○

  3

• ○

  1

• ○

  4 01-FEB-2019

• ○

  2

• ○

  5

• ○

  3 01-JAN-2019
  
  

Select all that apply, then click Submit answer.

• ○

  The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.

• ○

  The OR REPLACE option is used to change the definition of an existing view without dropping and re-creating it.

• ○

  Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.

• ○

  Rows added through a view are deleted from the table automatically when the view is dropped.

• ○

  A simple view in which column aliases have been used cannot be updated.

• ○

  A subquery used in a complex view definition cannot contain group functions or joins.
  
  

Select all that apply, then click Submit answer.

• ○

  It can include the CREATE..INDEX statement for creating an index to enforce the primary key constraint

• ○

  It implicitly executes a commit

• ○

  A user must have the CREATE ANY TABLE privilege to create tables

• ○

  It implicitly rolls back any pending transactions

• ○

  The owner of the table should have space quota available on the tablespace where the table is defined

• ○

  The owner of the table must have the UNLIMITED TABLESPACE system privilege
  
  

Select all that apply, then click Submit answer.

• ○

  DELETE
  FROM sales
  WHERE promo_id = (SELECT promo_id
  FROM promo_name = ‘blowout sale’)
  AND promo_id = (SELECT promo_id
  FROM promotions
  WHERE promo_name = ‘everyday low price’)
  FROM promotions
  WHERE promo_name = ‘everyday low price’);

• ○

  DELETE
  FROM sales
  WHERE promo_id = (SELECT promo_id
  FROM promotions
  WHERE promo_name = ‘blowout sale’)
  OR promo_id = (SELECT promo_id
  FROM promotions
  WHERE promo_name = ‘everyday low price’)

• ○

  DELETE
  FROM sales
  WHERE promo_id = (SELECT promo_id
  FROM promotions
  WHERE promo_name = ‘blowout sale’)
  OR promo_name = ‘everyday low price’);

• ○

  DELETE
  FROM sales
  WHERE promo_id IN (SELECT promo_id
  FROM promotions
  WHERE promo_name IN = ‘blowout sale’,‘everyday low price’));
  
  

Select all that apply, then click Submit answer.

• ○

  It can be used to display the structure of an existing view

• ○

  It can be used only from SQL*Plus

• ○

  It displays the PRIMARY KEY constraint for any column or columns that have that constraint

• ○

  It can be used from SQL Developer

• ○

  It displays all constraints that are defined for each column

• ○

  It displays the NOT NULL constraint for any columns that have that constraint
  
  

Select all that apply, then click Submit answer.

• ○

  The RR date format automatically calculates the century from the SYSDATE function but allows the session user to enter the century.

• ○

  The RR date format automatically calculates the century from the SYSDATE function and does not allow a session user to enter the century.

• ○

  The default internal storage of dates is in character format.

• ○

  The default internal storage of dates is in numeric format.
  
  

Select an option, then click Submit answer.

• ○

  1-d, 2-b, 3-a, and 4-c

• ○

  1-c, 2-d, 3-a, and 4-b

• ○

  1-a, 2-b, 3-c, and 4-d
  

• ○

  1-c, 2-a, 3-b, and 4-d
  
  

Select an option, then click Submit answer.

• ○

  A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.

• ○

  Each Data Definition Language (DDL) statement executed forms a single transaction.

• ○

  A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.

• ○

  A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.
  
  

Select an option, then click Submit answer.

• ○

  SELECT cust_first_name
  FROM customers
  WHERE INSTR(cust_first_name, ‘e’)<>0 AND SUBSTR(cust_first_name, -2, 1)=’a’;

• ○

  SELECT cust_first_name
  FROM customers
  WHERE INSTR(cust_first_name, ‘e’)<>'' AND SUBSTR(cust_first_name, -2, 1)=’a’;

• ○

  SELECT cust_first_name
  FROM customers
  WHERE INSTR(cust_first_name, ‘e’)IS NOT NULL AND
  SUBSTR(cust_first_name, 1, -2)=’a’;

• ○

  SELECT cust_first_name
  FROM customers
  WHERE INSTR(cust_first_name, ‘e’)<>0 AND
  SUBSTR(cust_first_name, LENGTH(cust_first_name), -2)=’a’;
  
  

Select an option, then click Submit answer.

• ○

  The names of columns in all SELECT statements must be identical.

• ○

  It ignores NULL values.

• ○

  Reversing the order of the intersected tables alters the result.

• ○

  The number of columns and data types must be identical for all SELECT statements in the query.
  
  

Select an option, then click Submit answer.

• ○

  SELECT &col1, '&col2'
  FROM &table
  WHERE &&condition = '&cond';

• ○

  SELECT &col1, &col2
  FROM "&table"
  WHERE &condition = &cond;

• ○

  SELECT &col1, &col2
  FROM &&table
  WHERE &condition = &cond;

• ○

  SELECT &col1, &col2
  FROM &&table
  WHERE &condition = &&cond
  
  

Select an option, then click Submit answer.

• ○

  GRANT UPDATE (title, address) ON customers TO andrew WITH GRANT OPTION;

• ○

  GRANT UPDATE (title, address) ON customers TO andrew WITH ADMIN OPTION;

• ○

  GRANT UPDATE ON customers.title, customers.address TO andrew WITH ADMIN OPTION;

• ○

  GRANT UPDATE (title, address) ON customers TO andrew;

• ○

  GRANT UPDATE ON customers.title, customers.address TO andrew WITH GRANT OPTION;

• ○

  GRANT UPDATE ON customers.title, customers.address TO andrew;
  
  

Select an option, then click Submit answer.

• ○

  SUBTRACT

• ○

  INTERSECT

• ○

  UNION ALL

• ○

  UNION

• ○

  MINUS
  
  

Select an option, then click Submit answer.

• ○

  The statement would execute successfully to produce the required output.

• ○

  The statement would not execute because inline views and outer joins cannot be used together.

• ○

  The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.

• ○

  The statement would not execute because the GROUP BY clause cannot be used in the inline.