Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 191:

  View the Exhibit and examine the structure of ORD and ORD_ITEMS tables. The ORD_NO column is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO columns are composite PRIMARY KEY in the ORD_ITEMS table. Which two CREATE INDEX statements are valid? (Choose two.)

  

  A. CREATE INDEX ord_idx1 ON ord(ord_no);
  B. CREATE INDEX ord_idx2 ON ord_items(ord_no);
  C. CREATE INDEX ord_idx3 ON ord_items(item_no);
  D. CREATE INDEX ord_idx4 ON ord,ord_items(ord_no, ord_date,qty);
  B. CREATE INDEX ord_idx2 ON ord_items(ord_no);
  C. CREATE INDEX ord_idx3 ON ord_items(item_no);

• Question 192:

  What are two reasons to create synonyms? (Choose two.)

  A. You have too many tables.
  B. Your tables names are too long.
  C. Your tables have difficult names.
  D. You want to work on your own tables.
  E. You want to use another schema's tables.
  F. You have too many columns in your tables.
  B. Your tables names are too long.
  C. Your tables have difficult names.

• Question 193:

  View the Exhibit and examine the structure of the PROMOTIONS table. 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_begiii_date > '01-JAN-01' ORDER BY 2 DESC;
  B. SELECT promo_name. promo_begiii_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY promo_name DESC:
  C. SELECT promo_name. promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY 1DESC:
  D. SELECT promo_name, promo_begin_date "START DATE" FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC;
  A. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begiii_date > '01-JAN-01' ORDER BY 2 DESC;
  D. SELECT promo_name, promo_begin_date "START DATE" FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC;

• Question 194:

  Examine the description of the CUSTOMERS table:

  

  The CUSTOMER_ID column is the primary key for the table.

  Which statement returns the city address and the number of customers in the cities Los Angeles or San Francisco?

  A. SELECT city_address, COUNT(*) FROM customers WHERE city_address IN ( `Los Angeles', `San Fransisco');
  B. SELECT city_address, COUNT (*) FROM customers WHERE city address IN ( `Los Angeles', `San Fransisco') GROUP BY city_address;
  C. SELECT city_address, COUNT(customer_id) FROM customers WHERE city_address IN ( `Los Angeles', `San Fransisco') GROUP BY city_address, customer_id;
  D. SELECT city_address, COUNT (customer_id) FROM customers GROUP BY city_address IN ( `Los Angeles', `San Fransisco');
  B. SELECT city_address, COUNT (*) FROM customers WHERE city address IN ( `Los Angeles', `San Fransisco') GROUP BY city_address;

• Question 195:

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

  SALE_VW is a view created using the following command:

  SQL>CREATE VIEW sale_vw AS

  SELECT prod_id, SUM(quantity_sold) QTY_SOLD

  FROM sales GROUP BY prod_id;

  You issue the following command to add a row to the SALE_SUMMARY table:

  SQL>INSERT INTO sale_summary

  SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products USING (prod_id) WHERE prod_id = 16;

  What is the outcome?

  A. It executes successfully.
  B. It gives an error because a complex view cannot be used to add data into the SALE_SUMMARY table.
  C. It gives an error because the column names in the subquery and the SALE_SUMMARY table do not match.
  D. It gives an error because the number of columns to be inserted does not match with the number of columns in the SALE_SUMMARY table.
  D. It gives an error because the number of columns to be inserted does not match with the number of columns in the SALE_SUMMARY table.

• Question 196:

  Examine the structure of the EMPLOYEES table:

  

  You want to create a SQL script file that contains an INSERT statement. When the script is run, the INSERT statement should insert a row with the specified values into the EMPLOYEES table. The INSERT statement should pass values to the table columns as specified below:

  

  Which INSERT statement meets the above requirements?

  A. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, 'andename', 'andjobid', 2000, NULL, anddid);
  B. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, 'andename', 'andjobid', 2000, NULL, anddid IN (20,50));
  C. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50)) VALUES (emp_id_seq.NEXTVAL, 'andename', 'andjobid', 2000, NULL, anddid);
  D. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50) WITH CHECK OPTION) VALUES (emp_id_seq.NEXTVAL, 'andename', 'andjobid', 2000, NULL, anddid);
  E. INSERT INTO (SELECT * FROM employees WHERE (department_id = 20 AND department_id = 50) WITH CHECK OPTION ) VALUES (emp_id_seq.NEXTVAL, 'andename', 'andjobid', 2000, NULL, anddid);
  D. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50) WITH CHECK OPTION) VALUES (emp_id_seq.NEXTVAL, 'andename', 'andjobid', 2000, NULL, anddid);

• Question 197:

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

  A. A table name can be of any length.
  B. A table can have any number of columns.
  C. A column that has a DEFAULT value cannot store null values.
  D. A table and a view can have the same name in the same schema.
  E. A table and a synonym can have the same name in the same schema.
  F. The same table name can be used in different schemas in the same database.
  E. A table and a synonym can have the same name in the same schema.
  F. The same table name can be used in different schemas in the same database.

• Question 198:

  View the Exhibit and examine the structure of the PROMOTIONS table. Examine the following two SQL statements:

  

  Which statement is true regarding the above two SQL statements?

  

  A. statement 1 gives an error, statement 2 executes successfully
  B. statement 2 gives an error, statement 1 executes successfully
  C. statement 1 and statement 2 execute successfully and give the same output
  D. statement 1 and statement 2 execute successfully and give a different output
  D. statement 1 and statement 2 execute successfully and give a different output

• Question 199:

  Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose all that apply.)

  A. The HAVING clause can be used with aggregate functions in subqueries.
  B. The WHERE clause can be used to exclude rows after dividing them into groups.
  C. The WHERE clause can be used to exclude rows before dividing them into groups.
  D. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
  E. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
  A. The HAVING clause can be used with aggregate functions in subqueries.
  C. The WHERE clause can be used to exclude rows before dividing them into groups.

• Question 200:

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

  Exhibit:

  

  Which SQL statements are valid? (Choose all that apply.)

  A. SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost, promo_cost * 0.25, 100) "Discount" FROM promotions;
  B. SELECT promo_id, DECODE(promo_cost, 10000, DECODE(promo_category, 'G1', promo_cost *.25, NULL), NULL) "Catcost" FROM promotions;
  C. SELECT promo_id, DECODE(NULLIF(promo_cost, 10000), NULL, promo_cost*.25, 'N/A') "Catcost" FROM promotions;
  D. SELECT promo_id, DECODE(promo_cost, >10000, 'High',
  A. SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost, promo_cost * 0.25, 100) "Discount" FROM promotions;
  B. SELECT promo_id, DECODE(promo_cost, 10000, DECODE(promo_category, 'G1', promo_cost *.25, NULL), NULL) "Catcost" FROM promotions;