Free Oracle 1Z1-051 Exam Dumps Questions & Answers

What is true regarding sub queries?

• A.  The outer query must return a value to the outer query
• B.  The inner query returns a value to the outer query
• C.  The outer query always sorts the results of the inner query
• D.  The inner query always sorts the results of the outer query
• E.  The inner query must always return a value or the outer query will give an error

Evaluate the following SQL statements:
Exhibit:

Exhibit:

The above command fails when executed. What could be the reason?

• A.  ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY
• B.  The CHECK constraint cannot be placed on columns having the DATE data type
• C.  SYSDATE cannot be used with the CHECK constraint
• D.  The BETWEEN clause cannot be used for the CHECK constraint

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
NEW_EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which MERGE statement is valid?

• A.  MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
• B.  MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id, e.first_name ||', '||e.last_name);
• C.  MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);
• D.  MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);

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

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

• A.  SELECT promo_begin_date
  FROM promotions
  WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98';
• B.  UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000',8));
• C.  UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000';
• D.  SELECT TO_CHAR(promo_begin_date,'dd/month')
  FROM promotions
  WHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98'));

The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.
Which statement would create a synonym ORD so that HR can execute the following query successfully?
SELECT * FROM ord;

• A.  CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.
• B.  CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
• C.  CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
• D.  CREATE SYNONYM ord FOR orders; This command is issued by OE.