Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 31:

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

  A. A foreign key cannot contain NULL values.
  B. A column with the UNIQUE constraint can contain NULL values.
  C. A constraint is enforced only for the INSERT operation on a table.
  D. A constraint can be disabled even if the constraint column contains data.
  E. All constraints can be defined at the column level as well as the table level.
  B. A column with the UNIQUE constraint can contain NULL values.
  D. A constraint can be disabled even if the constraint column contains data.

• Question 32:

  You need to display the date ll-oct-2007 in words as "Eleventh of October, Two Thousand Seven'. Which SQL statement would give the required result?

  A. SELECT TO_CHAR('ll-oct-2007'. 'miDdspth "of Month. Year') FROM DUAL:
  B. SELECT TO_CHAR(TO_DATE('ll-oct-2007'X 'miDdspth of month, year') FROM DUAL;
  C. SELECT TO_CHAR(TO_DATE('ll-oct-2007'), 'miDdthsp "of* Month. Year') FROM DUAL;
  D. SELECT TO_DATE(TO_CHAR('ll-oct-20077fiiiDdspth "of" Month. Year')) FROM DUAL:
  A. SELECT TO_CHAR('ll-oct-2007'. 'miDdspth "of Month. Year') FROM DUAL:

• Question 33:

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

  A. The UNIQUE constraint does not permit a null value for the column.
  B. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.
  C. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.
  D. The NOT NULL constraint ensures that null values are not permitted for the column.
  B. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.
  D. The NOT NULL constraint ensures that null values are not permitted for the column.

• Question 34:

  User Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She has the privilege to create a public synonym, and would like to create a synonym for this view that can be used by all users of the database.

  Which SQL statement can Mary use to accomplish that task?

  A. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu;
  B. CREATE PUBLIC SYNONYM EDL:VU FOR mary (emp_dept_loc_vu);
  C. CREATE PUBLIC SYNONYM EDL_VU FOR emp_dept_loc_vu;
  D. CREATE SYNONYM EDL_VU ON emp_dept_loc_vu FOR EACH USER;
  E. CREATE SYNONYM EDL_VU FOR EACH USER ON emp_dept_loc_vu;
  F. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu FOR ALL USERS;
  C. CREATE PUBLIC SYNONYM EDL_VU FOR emp_dept_loc_vu;

• Question 35:

  View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table.

  Evaluate the following two queries:

  SQL>SELECT DISTINCT promo_category to_char(promo_cost)"code" FROM promotions ORDER BY code;

  SQL>SELECT DISTINCT promo_category promo_cost "code"

  FROM promotions

  ORDER BY 1;

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

  

  A. Only the first query executes successfully.
  B. Only the second query executes successfully.
  C. Both queries execute successfully but give different results.
  D. Both queries execute successfully and give the same result.
  B. Only the second query executes successfully.

• Question 36:

  Examine the data in the ORD_ITEMS table:

  ORD_NO ITEM_NO QTY

  1 111 10

  1 222 20

  1 333 30

  2 333 30

  2 444 40

  3 111 40

  Evaluate the following query:

  SQL>SELECT item_no, AVG(qty)

  FROM ord_items

  HAVING AVG(qty) > MIN(qty) * 2

  GROUP BY item_no;

  Which statement is true regarding the outcome of the above query?

  A. It gives an error because the HAVING clause should be specified after the GROUP BY clause.
  B. It gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list.
  C. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.
  D. It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table.
  C. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.

• Question 37:

  What is true about sequences?

  A. Once created, a sequence belongs to a specific schema.
  B. Once created, a sequence is linked to a specific table.
  C. Once created, a sequence is automatically available to all users.
  D. Only the DBA can control which sequence is used by a certain table.
  E. Once created, a sequence is automatically used in all INSERT and UPDATE statements.
  A. Once created, a sequence belongs to a specific schema.

• Question 38:

  View the Exhibit and examine the structure of the CUSTOMERS table. You have been asked to produce a report on the CUSTOMERS table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city. Which query would accomplish this task?

  

  A. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city desc, cust_income_level DESC;
  B. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_income_level desc, cust_city DESC;
  C. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY (cust_city, cust_income_level) DESC;
  D. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city, cust_income_level DESC;
  A. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city desc, cust_income_level DESC;

• Question 39:

  Examine the structure of the ORDERS table:

  

  You want to find the total value of all the orders for each year and issue the following command:

  SQL>SELECT TO_CHAR(order_date,'rr'), SUM(order_total)

  FROM orders

  GROUP BY TO_CHAR(order_date,'yyyy');

  Which statement is true regarding the outcome?

  A. It executes successfully and gives the correct output.
  B. It gives an error because the TO_CHAR function is not valid.
  C. It executes successfully but does not give the correct output.
  D. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.
  D. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

• Question 40:

  View the Exhibit and examine the structure of the SALES table. The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times.

  

  Which statement is true regarding this SQL statement?

  

  A. It executes successfully and generates the required result.
  B. It produces an error because COUNT(*) should be specified in the SELECT clause also.
  C. It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE clause.
  D. It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).
  C. It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE clause.