Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 201:

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

  Exhibit:

  

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

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

• Question 202:

  Which SQL statement returns a numeric value?

  A. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;
  B. SELECT ROUND(hire_date) FROM EMP;
  C. SELECT sysdate-hire_date FROM EMP;
  D. SELECT TO_NUMBER(hire_date + 7) FROM EMP;
  C. SELECT sysdate-hire_date FROM EMP;

• Question 203:

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

  

  Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.

  Which SQL statement would produce the required result?

  A. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT" FROM customers;
  B. SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT" FROM customers;
  C. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT" FROM customers;
  D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT" FROM customers;
  D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT" FROM customers;

• Question 204:

  Examine the structure proposed for the TRANSACTIONS table:

  

  Which statements are true regarding the creation and storage of data in the above table structure? (Choose all that apply.)

  A. The CUST_STATUS column would give an error.
  B. The TRANS_VALIDITY column would give an error.
  C. The CUST_STATUS column would store exactly one character.
  D. The CUST_CREDIT_LIMIT column would not be able to store decimal values.
  E. The TRANS_VALIDITY column would have a maximum size of one character.
  F. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
  B. The TRANS_VALIDITY column would give an error.
  C. The CUST_STATUS column would store exactly one character.

• Question 205:

  View the Exhibits and examine the structures of the CUSTOMERS, SALES, and COUNTRIES tables. You need to generate a report that shows all country names, with corresponding customers (if any) and sales details (if any), for all

  customers.

  Which FROM clause gives the required result?

  A. FROM sales JOIN customers USING (cust_id) FULL OUTER JOIN countries USING (country_id);
  B. FROM sales JOIN customers USING (cust_id) RIGHT OUTER JOIN countries USING (country_id);
  C. FROM customers LEFT OUTER JOIN sales USING (cust_id) RIGHT OUTER JOIN countries USING (country_id);
  D. FROM customers LEFT OUTER JOIN sales USING (cust_id) LEFT OUTER JOIN countries USING (country_id);
  C. FROM customers LEFT OUTER JOIN sales USING (cust_id) RIGHT OUTER JOIN countries USING (country_id);

• Question 206:

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

  A. A simple view in which column aliases have been used cannot be updated.
  B. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
  C. Rows added through a view are deleted from the table automatically when the view is dropped.
  D. The OR REPLACE option is used to change the definition of an existing view without dropping and recreating it.
  E. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.
  B. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
  D. The OR REPLACE option is used to change the definition of an existing view without dropping and recreating it.

• Question 207:

  View the Exhibit and examine the structure of the PROMOTIONS table.

  

  You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category.

  Which query would give you the required output?

  A. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions ) AND promo_category = 'INTERNET';
  B. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category='INTERNET');
  C. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET');
  D. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ANY (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET');
  C. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET');

• Question 208:

  You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command:

  Exhibit:

  

  PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table. What is the outcome?

  A. Number of days since the promo started based on the current Singapore data and time.
  B. An error because the ROUND function specified is invalid
  C. An error because the WHERE condition specified is invalid
  D. Number of days since the promo started based on the current Chicago data and time
  D. Number of days since the promo started based on the current Chicago data and time

• Question 209:

  Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.)

  A. DELETE can be used to remove only rows from only one table at a time.
  B. DELETE can be used to remove only rows from multiple tables at a time.
  C. DELETE can be used only on a table that is a parent of a referential integrity constraint.
  D. DELETE can be used to remove data from specific columns as well as complete rows.
  E. DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having ON DELETE rule.
  A. DELETE can be used to remove only rows from only one table at a time.
  E. DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having ON DELETE rule.

• Question 210:

  You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command: Exhibit:

  

  PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table. What is the outcome?

  A. Number of days since the promo started based on the current Chicago data and time
  B. Number of days since the promo started based on the current Singapore data and time.
  C. An error because the WHERE condition specified is invalid
  D. An error because the ROUND function specified is invalid
  A. Number of days since the promo started based on the current Chicago data and time