Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 81:

  You work as a database administrator at ABC.com. You study the exhibit carefully.

  Exhibit:

  

  and examine the structure of CUSTOMRS AND SALES tables:

  Evaluate the following SQL statement:

  Exhibit:

  

  Which statement is true regarding the execution of the above UPDATE statement?

  A. It would not execute because the SELECT statement cannot be used in place of the table name
  B. It would execute and restrict modifications to only the column specified in the SELECT statement
  C. It would not execute because a sub query cannot be used in the WHERE clause of an UPDATE statement
  D. It would not execute because two tables cannot be used in a single UPDATE statement
  B. It would execute and restrict modifications to only the column specified in the SELECT statement

• Question 82:

  You work as a database administrator at ABC.com. You study the exhibit carefully.

  Exhibit

  

  Using the PROMOTIONS table, you need to display the names of all promos done after January 1, 2001 starting with the latest promo. Which query would give the required result? (Choose all that apply.)

  A. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY 1 DESC;
  B. SELECT promo_name,promo_begin_date "START DATE" FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC;
  C. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY 2 DESC;
  D. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY promo_name DESC;
  B. SELECT promo_name,promo_begin_date "START DATE" FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC;
  C. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY 2 DESC;

• Question 83:

  View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables.

  

  You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007. You issue the following query:

  

  Which statement is true regarding the above query?

  A. It executes successfully and gives the required result.
  B. It executes successfully but does not give the required result.
  C. It produces an error because the join order of the tables is incorrect.
  D. It produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECT statement.
  B. It executes successfully but does not give the required result.

• Question 84:

  Which object privileges can be granted on a view?

  A. none
  B. DELETE, INSERT,SELECT
  C. ALTER, DELETE, INSERT, SELECT
  D. DELETE, INSERT, SELECT, UPDATE
  D. DELETE, INSERT, SELECT, UPDATE

• Question 85:

  Which statements are true regarding single row functions? (Choose all that apply.)

  A. MOD : returns the quotient of a division
  B. TRUNC : can be used with NUMBER and DATE values
  C. CONCAT : can be used to combine any number of values
  D. SYSDATE : returns the database server current date and time
  E. INSTR : can be used to find only the first occurrence of a character in a string
  F. TRIM : can be used to remove all the occurrences of a character from a string
  B. TRUNC : can be used with NUMBER and DATE values
  D. SYSDATE : returns the database server current date and time

• Question 86:

  Evaluate the following SQL statements: Exhibit:

  

  You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization. Exhibit:

  

  Which two statements are true regarding the above view? (Choose two.)

  A. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table
  B. It allows you to delete details of the existing sales staff from the EMPLOYEES table
  C. It allows you to insert rows into the EMPLOYEES table
  D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements
  B. It allows you to delete details of the existing sales staff from the EMPLOYEES table
  D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements

• Question 87:

  View the Exhibit and examine the structure of the PRODUCTS table. You want to display only those product names with their list prices where the list price is at least double the minimum price. The report should start with the product name

  having the maximum list price satisfying this condition.

  Evaluate the following SQL statement:

  SQL>SELECT prod_name,prod_list_price

  FROM products

  WHERE prod_list_price >= 2 * prod_min_price

  Which ORDER BY clauses can be added to the above SQL statement to get the correct output? (Choose all that apply.)

  

  A. ORDER BY prod_list_price DESC, prod_name;
  B. ORDER BY (2*prod_min_price)DESC, prod_name;
  C. ORDER BY prod_name, (2*prod_min_price)DESC;
  D. ORDER BY prod_name DESC, prod_list_price DESC;
  E. ORDER BY prod_list_price DESC, prod_name DESC;
  A. ORDER BY prod_list_price DESC, prod_name;
  E. ORDER BY prod_list_price DESC, prod_name DESC;

• Question 88:

  View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.

  

  The CUSTOMERS table contains the current location of all currently active customers. The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.

  You need to find those customers who have never changed their address. Which SET operator would you use to get the required output?

  A. INTERSECT
  B. UNION ALL
  C. MINUS
  D. UNION
  C. MINUS

• Question 89:

  Examine the structure and data in the PRIC E_LIST table: Name Null? Type ------------ ------------- ------------------PROD_D NOT NULL NUMBER(3)

  PROD_PRICE VARCHAR2(10)

  PROD_ID PROD PRICE

  100 $234.55 101 $6,509.75 102 $1,234 in the same format as the PROD_PRICE. Which SQL statement would give the required result?

  A. SELECT TO_CHAR(prod_price* .25.'$99.999.99') FROM PRICEJLIST:
  B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25.'$99.999.00') FROM PRICE_LIST;
  C. SELECT TO_CRAR(TO_NUMBER(prod_price.'S99.999.99')* .25.'$99.999.00') FROM PRICE_LIST:
  D. SELECT TO_NUMBER(TO_NUMBER(prod_price.,$99.999.99')* .25/$99.999.00') FROM PRICE_LIST:
  C. SELECT TO_CRAR(TO_NUMBER(prod_price.'S99.999.99')* .25.'$99.999.00') FROM PRICE_LIST:

• Question 90:

  You need to calculate the number of days from 1st Jan 2007 till date:

  Dates are stored in the default format of dd-mm-rr.

  Which two SQL statements would give the required output? (Choose two.)

  A. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;
  B. SELECT TO_DATE(SYSDATE,'DD/MONTH/YYYY')-'01/JANUARY/2007' FROM DUAL;
  C. SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL
  D. SELECT SYSDATE - '01-JAN-2007' FROM DUAL
  E. SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')-'01-JAN-2007' FROM DUAL;
  A. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;
  C. SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL