1Z0-071 Online Practice Questions and Answers

The STORES table has a column START_DATE of data type DATE, containing the datethe row was inserted.

You only want to display details of rows where START_DATEis within the last 25 months.Which WHERE clause can be used?

A. WHERE TO_NUMBER(start_date - SYSDATE)<=25

B. WHERE ADD_MONTHS (start date , 25)<= SYSDATE

C. WHERE MONTHS_BETWEEN(SYSDATE, start_date)<=25

D. WHERE MONTHS_BETWEEN (start_date, SYSDATE)<=25

Which three statements are true regarding single row subqueries?

A. They must be placed on the left side of the comparison operator or condition.

B. They must be placed on the right side of the comparison operator or condition.

C. They must return a row to prevent errors in the SQL statement.

D. A SQL statement may have multiple single row subquery blocks.

E. They can be used in the HAVING clause.

F. They can be used in the WHERE clause.

You execute this command:

ALTER TABLE employees SET UNUSED (department_id);

Which two are true?

A. A query can display data from the DEPARTMENT_ID column.

B. The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued.

C. The DEPARTMENT_ID column is set to null for all tows in the table

D. A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table.

E. No updates can be made to the data in the DEPARTMENT_ID column.

F. The DEPARTMENT_ID column can be recovered from the recycle bin

Which two queries execute successfully? (Choose two.)

A. SELECT INTERVAL '1' DAY - INTERVAL '1' MINUTE FROM DUAL

B. SELECT SYSTIMESTAMP + INTERVAL '1' DAY FROM DUAL;

C. SELECT INTERVAL '1' DAY - SYSDATE FROM DUAL;

D. SELECT INTERVAL '1' DAY + INTERVAL '1' MONTH FROM DUAL;

E. SELECT SYSDATE * INTERVAL '1' DAY FROM DUAL;

Which three statements are true about Data Manipulation Language (DML)?

A. UPDATE statements can have different subqueries to specify the values for each updated column.

B. INSERT INTO...SELECT...FROM statements automatically commit.

C. DML statements require a primary key be defined on a table.

D. DELETE statements can remove multiple rows based on multiple conditions.

E. INSERT statements can insert NULLs explicitly into a column.

Which four statements are true about constraints on Oracle tables? (Choose four.)

A. A PRIMARY KEY constraint can be added after a table has been created and populated.

B. A FOREIGN KEY column can contain NULLs.

C. A CHECK constraint can refer to values in other rows.

D. A NOT NULL constraint can be defined at the table level.

E. A UNIQUE constraint can use a pre-existing index on the constrained column or columns.

F. A UNIQUE constraint permits NULLs.

G. A column can have only one CHECK constraint.

You execute the following commands:

SQL > DEFINE hiredate = '01-APR-2011'

SQL >SELECT employee_id, first_name, salary

FROM employees

WHERE hire_date > 'andhiredate'

AND manager_id > andmgr_id;

For Which substitution variables are you prompted for the input?

A. none, because no input required

B. both the substitution variables ''hiredate' and 'mgr_id'.

C. only hiredate'

D. only 'mgr_id'

View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.

Evaluate the following SQL statement:

What would be the outcome of the above SQL statement?

A. It displays prod IDs in the promo with the lowest cost.

B. It displays prod IDs in the promos with the lowest cost in the same time interval.

C. It displays prod IDs in the promos with the highest cost in the same time interval.

D. It displays prod IDs in the promos Which cost less than the highest cost in the same time interval.

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:

You want to generate a report that shows all course IDs irrespective of whether they have corresponding department IDs or not but no department IDs if they do not have any courses. Which SQL statement must you use?

A. SELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN course_details c USING (department_id)

B. SELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN .department_details d ON (c.depatrment_id=d.department_id)

C. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id=d. department_id)

D. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id<>d. department_id)

Which two statements are true about dropping views? (Choose two.)

A. The creator of a view to be dropped must have the DROP ANY VIEW privilege.

B. Data selected by a view's defining query is deleted from its underlying tables when the view is dropped.

C. Views referencing a dropped view become invalid.

D. Read only views cannot be dropped.

E. CASCADE CONSTRAINTS must be specified when referential integrity constraints on other objects refer to primary or unique keys in the view to be dropped.