Free Oracle 1Z0-047 Exam Dumps Questions & Answers

View the Exhibit and examine the structure of the CUST table.
Evaluate the following SQL statements executed in the given order:
ALTER TABLE cust
ADD CONSTRAINT cust_id_pk PRIMARY KEY(cust_id) DEFERRABLE INITIALLY DEFERRED;
INSERT INTO cust VALUES (1,'RAJ1); --row 1
INSERT INTO cust VALUES (1,'SAM); --row 2 COMMIT;
SET CONSTRAINT cust_id_pk IMMEDIATE;
INSERT INTO cust VALUES (1,'LATA '); --row 3 INSERT INTO cust VALUES (2,'KING '); --row 4 COMMIT;
Which rows would be made permanent in the CUST table?

• A.  rows 3 and 4
• B.  rows 2 and 4
• C.  rows 1 and 4
• D.  row 4 only

Which three statements are true regarding group functions? (Choose three.)

• A.  They can be used on columns or expressions.
• B.  They can be used on only one column in the SELECT clause of a SQL statement.
• C.  They can be passed as an argument to another group function.
• D.  They can be used only with a SQL statement that has the GROUP BY clause.
• E.  They can be used along with the single-row function in the SELECT clause of a SQL statement.

Which three statements are true regarding single-row functions? (Choose three.)

• A.  They can be used in SELECT, WHERE, and ORDER BY clauses.
• B.  They can accept only one argument.
• C.  They can return multiple values of more than one data type.
• D.  They can accept a column name, expression, variable name, or a user-supplied constant as arguments.
• E.  They can be nested up to only two levels.
• F.  They can modify the data type of the argument that is referenced.

View the Exhibit and examine the description for EMPLOYEES and DEPARTMENTS tables.
Evaluate the following SQL statement:
SELECT e.department_id, e.job_id, d.location_id, sum(e. salary) total FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY CUBE (e.department_id, e.job_id, d.location_id);
Which two statements are true regarding the output of this command? (Choose two.)

• A.  The output would display the total salary for all the departments.
• B.  The output would display the total salary for all the JOB_IDs in a department.
• C.  The output would display only the grand total of the salary for all JOB_IDs in a LOCATION_ID.
• D.  The output would display the grand total of the salary for only the groups specified in the GROUP BY clause.

View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables.
Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them?
SELECT location_id, city
FROM locations
SELECT location_id, city FROM locations JOIN departments USING(location_id);

• A.  UNION
• B.  MINUS
• C.  UNION ALL
• D.  INTERSECT