Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 191:

  The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER.

  Which two queries execute successfully?

  A. SELECT COUNT(prod_id) FROM sales WHERE quantity_sold>55000 GROUP BY prod_id;
  B. SELECT prod_id FROM sales WHERE quantity_sold> 55000 GROUP BY prod_id HAVING COUNT(*)> 10;
  C. SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold> 55000;
  D. SELECT prod_id FROM sales WHERE quantity_sold> 55000 AND COUNT(*)> 10 GROUP BY COUNT(*)> 10;
  E. SELECT prod_id FROM sales WHERE quantity_sold> 55000 AND COUNT(*)> 10 GROUP BY prod_id HAVING COUNT(*)> 10;
  A. SELECT COUNT(prod_id) FROM sales WHERE quantity_sold>55000 GROUP BY prod_id;
  B. SELECT prod_id FROM sales WHERE quantity_sold> 55000 GROUP BY prod_id HAVING COUNT(*)> 10;

• Question 192:

  Which statement will execute successfully?

  A. SELECT 1, 2 FROM DUAL UNION SELECT 3, 4 FROM DUAL ORDER BY 1, 2;
  B. SELECT 3 FROM DUAL UNION SELECT 4 FROM DUAL ORDER BY 3 ;
  C. SELECT 1, 2 FROM DUAL UNION SELECT 3, 4 FROM DUAL ORDER BY 3, 4;
  D. SELECT 1 FROM DUAL UNION SELECT 2 FROM DUAL ORDER BY 1, 2;
  A. SELECT 1, 2 FROM DUAL UNION SELECT 3, 4 FROM DUAL ORDER BY 1, 2;

• Question 193:

  View the exhibit and examine the description of the PRODUCT_INFORMATION table.

  

  Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL?

  A. SELECT COUNT (DISTINCT list_price) FROM product_information WHERE list_price is NULL
  B. SELECT COUNT (NVL(list_price, 0)) FROM product_information WHERE list_price is NULL
  C. SELECT COUNT (list_price) FROM product_information WHERE list_price i= NULL
  D. SELECT COUNT (list_price) FROM product_information WHERE list_price is NULL
  B. SELECT COUNT (NVL(list_price, 0)) FROM product_information WHERE list_price is NULL

• Question 194:

  Which two tasks require subqueries?

  A. Display the total number of products supplied by supplier 102 Which have a product status of obsolete.
  B. Display suppliers whose PROD_LIST_PRICE is less than 1000.
  C. Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE.
  D. Display the minimum PROD_LIST_PRICE for each product status.
  E. Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable.
  C. Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE.
  E. Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable.

• Question 195:

  You must create a SALES table with these column specifications and data types: (Choose the best answer.)

  SALESID: Number STOREID: Number ITEMID: Number QTY: Number, should be set to 1 when no value is specified SLSDATE: Date, should be set to current date when no value is specified PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified

  Which statement would create the table?

  A. CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT = 1, slsdate DATE DEFAULT SYSDATE, payment VARCHAR2(30) DEFAULT = "CASH");
  B. CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT 1, slsdate DATE DEFAULT 'SYSDATE', payment VARCHAR2(30) DEFAULT CASH);
  C. CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT = 1, slsdate DATE DEFAULT SYSDATE, payment VARCHAR2(30) DEFAULT = "CASH");
  D. CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT 1, slsdate DATE DEFAULT SYSDATE, payment VARCHAR2(30) DEFAULT 'CASH');
  D. CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT 1, slsdate DATE DEFAULT SYSDATE, payment VARCHAR2(30) DEFAULT 'CASH');

• Question 196:

  Examine this statement:

  

  Which two statements are true? (Choose two.)

  A. The names of employees earning the maximum salary will appear first in an unspecified order.
  B. All remaining employee names will appear in descending order.
  C. All remaining employee names will appear in an unspecified order.
  D. All remaining employee names will appear in ascending order.
  E. The names of employees earning the maximum salary will appear first in ascending order.
  F. The names of employees earning the maximum salary will appear first in descending order.
  D. All remaining employee names will appear in ascending order.
  F. The names of employees earning the maximum salary will appear first in descending order.

• Question 197:

  Which three statements are true about GLOBAL TEMPORARY TABLES? (Choose three.)

  A. A GLOBAL TEMPORARY TABLE can have only one index.
  B. A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view
  C. DML on GLOBAL TEMPORARY TABLES generates no REDO.
  D. A GLOBAL TEMPORARY TABLE cannot have a PUBLIC SYNONYM.
  E. A GLOBAL TEMPORARY TABLE can have multiple indexes.
  F. A trigger can be created on a GLOBAL TEMPORARY TABLE.
  B. A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view
  E. A GLOBAL TEMPORARY TABLE can have multiple indexes.
  F. A trigger can be created on a GLOBAL TEMPORARY TABLE.

• Question 198:

  View the Exhibit and examine the structure of the EMP table Which is not partitioned and not an index-organized table. (Choose two.)

  

  Evaluate this SQL statement:

  ALTER TABLE emp

  DROP COLUMN first_name;

  Which two statements are true?

  A. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.
  B. The FIRST_NAME column would be dropped provided at least one column remains in the table.
  C. The FIRST_NAME column would be dropped provided it does not contain any data.
  D. The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.
  B. The FIRST_NAME column would be dropped provided at least one column remains in the table.

• Question 199:

  Which two are true about a SQL statement using SET operators such as UNION? (Choose two.)

  A. The data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query.
  B. The names and number of columns must be identical for all select statements in the query.
  C. The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query.
  D. The data type of each column returned by the second query must exactly match the data type of the corresponding column returned by the first query.
  E. The number, but not names, of columns must be identical for all select statements in the query.
  A. The data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query.
  E. The number, but not names, of columns must be identical for all select statements in the query.

• Question 200:

  Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?

  A. SELECT deptno,LISTAGG(ename, ' , ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno;
  B. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno ORDER BY ename;
  C. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (GROUP BY deptno) AS employee_list FROM emp ORDER BY ename;
  D. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;
  D. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;