Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 121:

  Which three statements are true regarding sub queries? (Choose three.)

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

• Question 122:

  Examine the data in the LIST_PRICE and MIN_PRICE columns of the PRODUCTS table:

  

  Which two expressions give the same output? (Choose two.)

  A. NVL(NULLIF(list_price, min_price), 0)
  B. NVL(COALESCE(list_price, min_price), 0)
  C. NVL2(COALESCE(list_price, min_price), min_price, 0)
  D. COALESCE(NVL2(list_price, list_price, min_price), 0)
  B. NVL(COALESCE(list_price, min_price), 0)
  D. COALESCE(NVL2(list_price, list_price, min_price), 0)

• Question 123:

  Evaluate the following CREATE SEQUENCE statement:

  CREATE SEQUENCE seq1 START WITH 100 INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE;

  The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL statement: SELECT seq1.nextval FROM dual;

  What is displayed by the SELECT statement?

  A. 1
  B. 10
  C. 100
  D. an error
  A. 1

• Question 124:

  You issue the following query:

  SQL> SELECT AVG(MAX(qty))

  FROM ord_items

  GROUP BY item_no

  HAVING AVG(MAX(qty))>50;

  Which statement is true regarding the outcome of this query?

  A. It executes successfully and gives the correct output.
  B. It gives an error because the HAVING clause is not valid.
  C. It executes successfully but does not give the correct output.
  D. It gives an error because the GROUP BY expression is not valid.
  B. It gives an error because the HAVING clause is not valid.

• Question 125:

  Evaluate the following SQL statements:

  Exhibit:

  

  Exhibit:

  

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

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

• Question 126:

  View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statement:

  

  Which statement is true regarding the outcome of the above query?

  

  A. It executes successfully.
  B. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
  C. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
  D. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.
  A. It executes successfully.

• Question 127:

  The PRODUCTS table has the following structure:

  

  Evaluate the following two SQL statements:

  

  Which statement is true regarding the outcome?

  A. Both the statements execute and give the same result
  B. Both the statements execute and give different results
  C. Only the second SQL statement executes successfully
  D. Only the first SQL statement executes successfully
  B. Both the statements execute and give different results

• Question 128:

  Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

  A. SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
  B. SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;
  C. SELECT TO_CHAR(1890.55,'$99,999D99') FROM DUAL;
  D. SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
  E. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;
  A. SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
  D. SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
  E. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;

• Question 129:

  Evaluate the following SQL commands:

  

  The command to create a table fails. Identify the reason for the SQL statement failure? (Choose all that apply.)

  A. You cannot use SYSDATE in the condition of a CHECK constraint.
  B. You cannot use the BETWEEN clause in the condition of a CHECK constraint.
  C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.
  D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD NO is also the FOREIGN KEY.
  A. You cannot use SYSDATE in the condition of a CHECK constraint.
  C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.

• Question 130:

  View the Exhibits and examine the structures of the COSTS and PROMOTIONS tables. Evaluate the following SQL statement:

  SQL> SELECT prod_id FROM costs WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL (SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_datepromo_ begin_date))); What would be the outcome of the above SQL statement?

  A. It displays prod IDs in the promo with the lowest cost.
  B. It displays prod IDs in the promos with the lowest cost in the same time interval.
  C. It displays prod IDs in the promos with the highest cost in the same time interval.
  D. It displays prod IDs in the promos with cost less than the highest cost in the same time interval.
  D. It displays prod IDs in the promos with cost less than the highest cost in the same time interval.