Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 241:

  View the Exhibit and examine the structure of the PROMOTIONS table. Using the PROMOTIONS table, you need to find out the names and cost of all the promos done on 'TV' and 'internet' that ended in the time interval 15th March '00 to 15th October '00. Which two queries would give the required result? (Choose two.)

  

  A. SELECT promo_name, promo_cost FROM promotions WHERE promo_category IN ('TV', 'internet') AND promo_end_date BETWEEN '15-MAR-00' AND '15-OCT-00';
  B. SELECT promo_name, promo_cost FROM promotions WHERE promo_category = 'TV' OR promo_category ='internet' AND promo_end_date >='15- MAR-00' OR promo_end_date
  C. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category BETWEEN 'TV' AND 'internet') AND (promo_end_date IN ('15-MAR-00','15-OCT-00'));
  D. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category = 'TV' OR promo_category ='internet') AND (promo_end_date >='15-MAR- 00' AND promo_end_date
  A. SELECT promo_name, promo_cost FROM promotions WHERE promo_category IN ('TV', 'internet') AND promo_end_date BETWEEN '15-MAR-00' AND '15-OCT-00';
  D. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category = 'TV' OR promo_category ='internet') AND (promo_end_date >='15-MAR- 00' AND promo_end_date

• Question 242:

  Which statement is true regarding the COALESCE function?

  A. It can have a maximum of five expressions in a list.
  B. It returns the highest NOT NULL value in the list for all rows.
  C. It requires that all expressions in the list must be of the same data type.
  D. It requires that at least one of the expressions in the list must have a NOT NULL value.
  C. It requires that all expressions in the list must be of the same data type.

• Question 243:

  Exhibit contains the structure of PRODUCTS table:

  

  Evaluate the following query:

  

  What would be the outcome of executing the above SQL statement?

  A. It produces an error
  B. It shows the names of products whose list price is the second highest in the table.
  C. It shown the names of all products whose list price is less than the maximum list price
  D. It shows the names of all products in the table
  B. It shows the names of products whose list price is the second highest in the table.

• Question 244:

  Examine the structure of the EMP_DEPT_VU view: Which SQL statement produces an error?

  

  A. SELECT * FROM emp_dept_vu;
  B. SELECT department_id, SUM(salary) FROM emp_dept_vu GROUP BY department_id;
  C. SELECT department_id, job_id, AVG(salary) FROM emp_dept_vu GROUP BY department_id, job_id;
  D. SELECT job_id, SUM(salary) FROM emp_dept_vu WHERE department_id IN (10,20) GROUP BY job_id HAVING SUM(salary) > 20000;
  E. None of the statements produce an error; all are valid.
  E. None of the statements produce an error; all are valid.

• Question 245:

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

  

  The above query generates an error on execution.

  Which clause in the above SQL statement causes the error?

  

  A. WHERE
  B. SELECT
  C. GROUP BY
  D. ORDER BY
  C. GROUP BY

• Question 246:

  Which is an iSQL*Plus command?

  A. INSERT
  B. UPDATE
  C. SELECT
  D. DESCRIBE
  E. DELETE
  F. RENAME
  D. DESCRIBE

• Question 247:

  Examine the data in the CUSTOMERS table:

  

  You want to list all cities that have more than one customer along with the customer details. Evaluate the following query:

  SQL>SELECT c1.custname, c1.city FROM Customers c1 __________________ Customers c2 ON (c1.city=c2.city AND c1.custname<>c2.custname);

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

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

• Question 248:

  View the Exhibit and examine the data in the PROMOTIONS table.

  

  You need to display all promo categories that do not have 'discount' in their subcategory. Which two SQL statements give the required result? (Choose two.)

  A. SELECT promo_category FROM promotions MINUS SELECT promo_category FROM promotions WHERE promo_subcategory = 'discount';
  B. SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM promotions WHERE promo_subcategory = 'discount';
  C. SELECT promo_category FROM promotions MINUS SELECT promo_category FROM promotions WHERE promo_subcategory 'discount';
  D. SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM promotions WHERE promo_subcategory 'discount';
  A. SELECT promo_category FROM promotions MINUS SELECT promo_category FROM promotions WHERE promo_subcategory = 'discount';
  D. SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM promotions WHERE promo_subcategory 'discount';

• Question 249:

  Which statement is true regarding the INTERSECT operator?

  A. It ignores NULL values
  B. The number of columns and data types must be identical for all SELECT statements in the query
  C. The names of columns in all SELECT statements must be identical
  D. Reversing the order of the intersected tables the result
  B. The number of columns and data types must be identical for all SELECT statements in the query

• Question 250:

  See the Exhibit and examine the structure and data in the INVOICE table: Exhibit:

  

  Which two SQL statements would executes successfully? (Choose two.)

  A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
  B. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;
  C. SELECT (AVG(inv_date) FROM invoice;
  D. SELECT AVG(inv_date - SYSDATE),AVG(inv_amt) FROM invoice;
  A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
  D. SELECT AVG(inv_date - SYSDATE),AVG(inv_amt) FROM invoice;