Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 151:

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

  A. A foreign key cannot contain NULL values.
  B. The column with a UNIQUE constraint can store NULLS .
  C. A constraint is enforced only for an INSERT operation on a table.
  D. You can have more than one column in a table as part of a primary key.
  B. The column with a UNIQUE constraint can store NULLS .
  D. You can have more than one column in a table as part of a primary key.

• Question 152:

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

  

  You want to create a SALE_PROD view by executing the following SQL statements:

  

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

  A. The view will be created and you can perform DLM operations on the view
  B. The view will not be created because the join statements are not allowed for creating a view
  C. The view will not be created because the GROUP BY clause is not allowed for creating a view
  D. The view will be created but no DML operations will be allowed on the view
  D. The view will be created but no DML operations will be allowed on the view

• Question 153:

  Which is a valid CREATE TABLE statement?

  A. CREATE TABLE EMP9$# AS (empid number(2));
  B. CREATE TABLE EMP*123 AS (empid number(2));
  C. CREATE TABLE PACKAGE AS (packid number(2));
  D. CREATE TABLE 1EMP_TEST AS (empid number(2));
  A. CREATE TABLE EMP9$# AS (empid number(2));

• Question 154:

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

  

  There is only one customer with the CUST_LAST_NAME column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?

  A. INSERT INTO orders VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
  B. INSERT INTO orders (order_id,order_date,order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600),order_total) VALUES(1,'10-mar-2007', 'direct', andandcustomer_id, 1000);
  C. INSERT INTO(SELECT o.order_id, o.order_date,o.order_mode,c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' ANDc.credit_limit=600 ) VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
  D. INSERT INTO orders (order_id,order_date,order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600),order_total) VALUES(1,'10-mar-2007', 'direct', andcustomer_id, 1000);
  A. INSERT INTO orders VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);

• Question 155:

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

  A. Views can be created only from tables.
  B. Views can be created from tables or other views.
  C. Only simple views can use indexes existing on the underlying tables.
  D. Both simple and complex views can use indexes existing on the underlying tables.
  E. Complex views can be created only on multiple tables that exist in the same schema.
  F. Complex views can be created on multiple tables that exist in the same or different schemas.
  B. Views can be created from tables or other views.
  D. Both simple and complex views can use indexes existing on the underlying tables.
  F. Complex views can be created on multiple tables that exist in the same or different schemas.

• Question 156:

  View the Exhibit and examine the structure of the CUSTOMERS table .Which statement would display the highest credit limit available in each income level in each city in the CUSTOMERS table?

  

  A. SELECT cust_city, cust_income_level, MAX(cust_credit_limit ) FROM customers GROUP BY cust_city, cust_income_level, cust_credit_limit;
  B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_city, cust_income_level;
  C. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_credit_limit, cust_income_level, cust_city ;
  D. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_city, cust_income_level, MAX(cust_credit_limit);
  B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_city, cust_income_level;

• Question 157:

  Examine the structure of the PRODUCTS table:

  

  You want to display the names of the products that have the highest total value for UNIT_PRICE *QTY_IN_HAND.

  Which SQL statement gives the required output?

  A. SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products);
  B. SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name);
  C. SELECT prod_name FROM products GROUP BY prod_name HAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name);
  D. SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(SUM(unit_price * qty_in_hand)) FROM products) GROUP BY prod_name;
  A. SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products);

• Question 158:

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

  

  The PROJ_TASK_DETAILS table stores information about tasks involved in a project and the relation between them.

  The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on the completion of any other tasks.

  You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, and the name of the employee in charge of the task it depends on.

  Which query would give the required result?

  A. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.based_on = d.task_id);
  B. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on =
  C. task_id);
  D. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on =
  E. task_id);
  F. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.task_id = d.task_id);
  B. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on =

• Question 159:

  The PRODUCTS table has these columns:

  PRODUCT_ID NUMBER(4)

  PRODUCT_NAME VARCHAR2(45)

  PRICE NUMBER(8,2)

  Evaluate this SQL statement:

  SELECT *

  FROM PRODUCTS

  ORDER BY price, product_name;

  What is true about the SQL statement?

  A. The results are not sorted.
  B. The results are sorted numerically.
  C. The results are sorted alphabetically.
  D. The results are sorted numerically and then alphabetically.
  D. The results are sorted numerically and then alphabetically.

• Question 160:

  View the Exhibit and examine the structure of the PROMOTIONS table. You need to generate a report of all promos from the PROMOTIONS table based on the following conditions:

  1.

  The promo name should not begin with 'T' or 'N'.

  2.

  The promo should cost more than $20000.

  3.

  The promo should have ended after 1st January 2001. Which WHERE clause would give the required result?

  

  A. WHERE promo_name NOT LIKE 'T%' OR promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
  B. WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR promo_cost > 20000 OR promo_end_date > '1-JAN-01'
  C. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
  D. WHERE (promo_name NOT LIKE '%T%' OR promo_name NOT LIKE '%N%') AND(promo_cost > 20000 AND promo_end_date > '1-JAN-01')
  C. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'