Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 141:

  Which two statements are true regarding views? (Choose two.)

  A. A sub query that defines a view cannot include the GROUP BY clause
  B. A view is created with the sub query having the DISTINCT keyword can be updated
  C. A Data Manipulation Language (DML) operation can be performed on a view that is created with the sub query having all the NOT NULL columns of a table
  D. A view that is created with the sub query having the pseudo column ROWNUM keyword cannot be updated
  C. A Data Manipulation Language (DML) operation can be performed on a view that is created with the sub query having all the NOT NULL columns of a table
  D. A view that is created with the sub query having the pseudo column ROWNUM keyword cannot be updated

• Question 142:

  See the Exhibit and examine the structure of the CUSTOMERS table:

  

  Using the CUSTOMERS table, you need to generate a report that shown the average credit limit for customers in WASHINGTON and NEW YORK. Which SQL statement would produce the required result?

  A. SELECT cust_city, AVG(cust_credit_limit) FROM customers WHERE cust_city IN ('WASHINGTON','NEW YORK') GROUP BY cust_credit_limit, cust_city;
  B. SELECT cust_city, AVG(cust_credit_limit) FROM customers WHERE cust_city IN ('WASHINGTON','NEW YORK') GROUP BY cust_city,cust_credit_limit;
  C. SELECT cust_city, AVG(cust_credit_limit) FROM customers WHERE cust_city IN ('WASHINGTON','NEW YORK') GROUP BY cust_city;
  D. SELECT cust_city, AVG(NVL(cust_credit_limit,0)) FROM customers WHERE cust_city IN ('WASHINGTON','NEW YORK');
  C. SELECT cust_city, AVG(cust_credit_limit) FROM customers WHERE cust_city IN ('WASHINGTON','NEW YORK') GROUP BY cust_city;

• Question 143:

  See the Exhibit and examine the structure of ORD table: Exhibit:

  

  Evaluate the following SQL statements that are executed in a user session in the specified order:

  CREATE SEQUENCE ord_seq;

  SELECT ord_seq.nextval

  FROM dual;

  INSERT INTO ord

  VALUES (ord_seq.CURRVAL, '25-jan-2007,101);

  UPDATE ord

  SET ord_no= ord_seq.NEXTVAL

  WHERE cust_id =101;

  What would be the outcome of the above statements?

  A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101.
  B. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.
  C. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified.
  D. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20.
  A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101.

• Question 144:

  The user Sue issues this SQL statement:

  GRANT SELECT ON sue.EMP TO alice WITH GRANT OPTION;

  The user Alice issues this SQL statement:

  GRANT SELECT ON sue.EMP TO reena WITH GRANT OPTION;

  The user Reena issues this SQL statement:

  GRANT SELECT ON sue.EMP TO timber;

  The user Sue issues this SQL statement:

  REVOKE select on sue.EMP FROM alice;

  For which users does the revoke command revoke SELECT privileges on the SUE.EMP table?

  A. Alice only
  B. Alice and Reena
  C. Alice, Reena, and Timber
  D. Sue, Alice, Reena, and Timber
  C. Alice, Reena, and Timber

• Question 145:

  View the Exhibit and examine the structure of the SALES and PRODUCTS tables.

  

  In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You want to list each product ID and the number of times it has been sold.

  Evaluate the following query:

  SQL>SELECT p.prod_id, COUNT(s.prod_id)

  FROM products p _____________ sales s

  ON p.prod_id = s.prod_id

  GROUP BY p.prod_id;

  Which two JOIN options can be used in the blank in the above query to get the required output? (Choose two.)

  A. JOIN
  B. FULL OUTER JOIN
  C. LEFT OUTER JOIN
  D. RIGHT OUTER JOIN
  B. FULL OUTER JOIN
  C. LEFT OUTER JOIN

• Question 146:

  Which four are types of functions available in SQL? (Choose 4)

  A. string
  B. character
  C. integer
  D. calendar
  E. numeric
  F. translation
  G. date
  H. conversion
  B. character
  E. numeric
  G. date
  H. conversion

• Question 147:

  SLS is a private synonym for the SH.SALES table.

  The user SH issues the following command:

  DROP SYNONYM sls;

  Which statement is true regarding the above SQL statement?

  A. Only the synonym would be dropped.
  B. The synonym would be dropped and the corresponding table would become invalid.
  C. The synonym would be dropped and the packages referring to the synonym would be dropped.
  D. The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid.
  A. Only the synonym would be dropped.

• Question 148:

  Examine the data in the PROMO_BEGIN_DATE column of the PROMOTIONS table: PROMO_BEGIN _DATE

  04-jan-00 10-jan-00 15-dec-99 18-oct-98 22-aug-99

  You want to display the number of promotions started in 1999 and 2000. Which query gives the correct output?

  A. SELECT SUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0)) "2000", SUM(DECODE(SUBSTR (promo_begin_date,8),'99',1,0)) "1999" FROM promotions;
  B. SELECT SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0 END) "1999",SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE 0 END) "2000" FROM promotions;
  C. SELECT COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0 END) "1999", COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE 0 END) "2000" FROM promotions;
  D. SELECT COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8), '1999', 1, 0)) "1999", COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8),'2000', 1, 0)) "2000" FROM promotions;
  A. SELECT SUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0)) "2000", SUM(DECODE(SUBSTR (promo_begin_date,8),'99',1,0)) "1999" FROM promotions;

• Question 149:

  Which one is a system privilege?

  A. SELECT
  B. DELETE
  C. EXECUTE
  D. ALTER TABLE
  E. CREATE TABLE
  E. CREATE TABLE

• Question 150:

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

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