Free 1Z0-047 Questions for Oracle 1Z0-047 Exam

Question #1

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 /LATA1); --row 3
INSERT INTO cust VALUES (2 .KING1); --row 4
COMMIT;
Which rows would be made permanent in the CUST table?

A. rows 1 and 4

B. rows 2 and 4

C. row 4 only

D. rows 3 and 4

Discussion 0

Correct Answer: D Vote an answer

Question #2

Which statements are correct regarding indexes? (Choose all that apply.)

A. Indexes should be created on columns that are frequently referenced as part of an expression.

B. When a table is dropped, the corresponding indexes are automatically dropped.

C. For each DML operation performed, the corresponding indexes are automatically updated.

D. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.

Discussion 0

Correct Answer: B,C,D Vote an answer

Question #3

View the Exhibit and examine the description of the EMPLOYEES table.
You executed the following SQL statement:
SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, first_name, salary desc;
Which two statements are true regarding the output of the above query? (Choose two.)

A. The values in all the columns would be sorted in the descending order.

B. The values in the FIRST_NAME column would be sorted in ascending order for all the employees having the same value in the DEPARTMENT_ID column.

C. The values in the SALARY column would be sorted in descending order for all the employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.

D. The values in the FIRST_NAME column would be sorted in the descending order for all the employees having the same value in the DEPARTMENT_ID column.

E. The values in the SALARY column would be sorted in descending order for all the employees having the same value in the DEPARTMENT_ID column.

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for ExamDiscuss members. You can sign-up / login (it's free).

Question #4

Evaluate the following SQL statement:
SELECT 2 col1,ycol2 FROM dual UNION SELECT 1 ,'x' FROM dual
UNION SELECT 3 .NULL FROM dual ORDER BY 2;
Which statement is true regarding the output of the SQL statement?

A. It would execute and the order of the values in the first column would be 1,2,3.

B. It would execute and the order of the values in the first column would be 3,2,1.

C. It would not execute because the column alias name has not been used in the ORDER BY clause.

D. It would not execute because the number 2 in the ORDER BY clause would conflict with the value 2 in the first SELECT statement.

Discussion 0

Correct Answer: A Vote an answer

Question #5

View the Exhibit and examine the structure of EMPLOYEES and JOB_IHISTORY tables.
The EMPLOYEES table maintains the most recent information regarding salary, department, and job for all the employees. The JOB_HISTORY table maintains the record for all the job changes for the employees. You want to delete all the records from the JOB_HISTORY table that are repeated in the EMPLOYEES table.
Which two SQL statements can you execute to accomplish the task? (Choose two.)

A DELETE FROM job_history j WHERE employee_id = (SELECT employee_id FROM employees e WHERE j.employee_id = e.employee_id) AND job _id = (SELECT job _id FROM employees e WHERE j.job_id = e.job_id);
B. DELETE FROM job_history j WHERE (employee_id, job _id) = ALL (SELECT employee_id, job_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id)
C. DELETE FROM job_history j WHERE employee_id = (SELECT employee_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id)
D. DELETE FROM job_history j WHERE (employee_id, job _id) = (SELECT employee_id, job_id FROM employees e WHERE j.employee_id = e.employee_id and j.job_id = e.job_id)

Discussion 0

Correct Answer:

CD

Question #6

Which two statements are true regarding operators used with subqueries? (Choose two.

A. The NOT operator can be used with IN, ANY and ALL operators.

B. The IN operator cannot be used in single-row subqueries.

C. =ANY and =ALL operators have the same functionality.

D. The <ANY operator means less than the maximum.

E. The NOT IN operator is equivalent to IS NULL

Discussion 0

Correct Answer: A,D Vote an answer

Question #7

View the Exhibit and examine the structure of the EMP table which is not partitioned and not an
index-organized table.
Evaluate the following SQL statement:
ALTER TABLE emp DROP COLUMN first_name;
Which two statements are true regarding the above command? (Choose two.)

A. The FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the above SQL statement.

B. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is used.

C. The FIRST_NAME column would be dropped provided it does not contain any data.

D. The FIRST_NAME column would be dropped provided at least one or more columns remain in the table.

Discussion 0

Correct Answer: B,D Vote an answer

Question #8

Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)

A. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.

B. You can use column alias in the GROUP BY clause.

C. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause.

D. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.

E. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.

Discussion 0

Correct Answer: C,E Vote an answer

Question #9

Evaluate the following ALTER TABLE statement:
ALTER TABLE orders SET UNUSED order_date;
Which statement is true?

A. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.

B. The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully.

C. The DESCRIBE command would still display the ORDER_DATE column.

D. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.

Discussion 0

Correct Answer: D Vote an answer

Question #10

View the Exhibit and examine the details of the EMPLOYEES table.
You want to generate a hierarchical report for all the employees who report to the employee
whose EMPLOYEE_ID is 100.
Which SQL clauses would you require to accomplish the task? (Choose all that apply.)

A. START WITH

B. WHERE

C. HAVING

D. CONNECT BY

E. GROUP BY

Discussion 0

Correct Answer: A,B,D Vote an answer

Question #11

Which three statements are true? (Choose three.)

A. Only one LONG column can be used per table.

B. The BLOB data type column is used to store binary data in an operating system file.

C. The value for a CHAR data type column is blank-padded to the maximum defined column width.

D. The minimum column width that can be specified for a varchar2 data type column is one.

E. ATIMESTAMP data type column stores only time values with fractional seconds.

Discussion 0

Correct Answer: A,C,D Vote an answer

Question #12

You need to create a table with the following column specifications:
1.Employee ID (numeric data type) for each employee
2.Employee Name, (character data type) which stores the employee name
3.Hire date, to store the date when the employee joined the organization
4.Status (character data type). It should contain the value if no data is entered.
5.Resume (character large object [CLOB] data type), which would contain the resume submitted by the employee
Which is the correct syntax to create this table?

A. CREATE TABLE EMP_1
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
e_status VARCHAR2(10) DEFAULT ACTIVE',
resume CLOB(200));

B. CREATE TABLE 1_EMP
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT ACTIVE',
resume CLOB);

C. CREATE TABLE EMP_1
(emp_id NUMBER, emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT ACTIVE',
resume CLOB);

D. CREATE TABLE 1_EMP
(emp_id NUMBER(4),
emp_name VARCHAR2(25), start_date DATE,
emp_status VARCHAR2(10) DEFAULT "ACTIVE",
resume CLOB);

Discussion 0

Correct Answer: C Vote an answer

Question #13

View the Exhibit and examine the description of the ORDERS table.
Evaluate the following SQL statement:
SELECT order_id, customer_id FROM orders WHERE order_date > 'June 30 2001';
Which statement is true regarding the execution of this SQL statement?

A. It would not execute because 'June 30 2001' in the WHERE condition is not enclosed within double quotation marks.

B. It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_CHAR conversion function for proper execution.

C. It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_DATE conversion function for proper execution.

D. It would execute and would return ORDER_ID and CUSTOMER_ID for all records having ORDER_DATE greater than 'June 30 2001'.

Discussion 0

Correct Answer: C Vote an answer

Question #14

View the Exhibit and examine the description of the EMPLOYEES and DEPARTMENTS tables.
You want to display the LAST_NAME for the employees, LAST_NAME for the manager of the
employees, and the DEPARTMENT_NAME for the employees having 100 as MANAGER_ID.
The following SQL statement was written:
SELECT m.last_name "Manager", e.last_name "Employee", department_name "Department"
FROM employees m JOIN employees e
ON (m.employee_id = e.manager_id)
WHERE e.manager_id=100
JOIN departments d
ON (e.department_id = d.department_id);
Which statement is true regarding the output of this SQL statement?

A. The statement would not execute because the ON clause is written twice.

B. The statement would not execute because the self join uses the ON clause instead of the USING clause.

C. The statement would not execute because the WHERE clause is wrongly placed.

D. The statement would provide the desired results.

Discussion 0

Correct Answer: C Vote an answer

Free 1Z0-047 Questions for Oracle Database SQL Expert 1Z0-047 Exam as PDF & Practice Test Engine

Download Free Oracle 1Z0-047 Demo