Oracle 1Z0-007 valid exam dumps : Introduction to Oracle9i: SQL

You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables:
EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME.
The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table.
You want to modify the view by adding a fourth column, MANAGER_ID of NUMBER data type from the EMPLOYEES tables.
How can you accomplish this task?

• A. ALTER VIEW emp_dept_vu (ADD manager_id NUMBER);
• B. MODIFY VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;
• C. ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employee e, departments d WHERE e.department_id = d.department_id;
• D. MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);
• E. You must remove the existing view first, and then run the CREATE VIEW command with a new column list to modify a view.
• F. CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;

You own a table called EMPLOYEES with this table structure:
EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE
What happens when you execute this DELETE statement?
DELETE employees;

• A. The data in the EMPLOYEES table is deleted but not the structure.
• B. You get an error because the statement is not syntactically correct.
• C. You get an error because of a primary key violation.
• D. The data and structure of the EMPLOYEES table are deleted.

Which two statements about sequences are true? (Choose two)

• A. You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column.
• B. If a sequence starting from a value 100 and incremented by 1 is used by more then one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence.
• C. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.
• D. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence.
• E. You use REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.
• F. You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value.

Which three are true regarding the use of outer joins? (Choose three.)

• A. You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator.
• B. You use (*) on both sides of the WHERE condition to perform an outerjoin.
• C. You use an outerjoin to see only the rows that do not meet the join condition.
• D. You use (+) on both sides of the WHERE condition to perform an outerjoin.
• E. In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin.
• F. You cannot use IN operator in a condition that involves an outerjoin.

You need to design a student registration database that contains several tables storing academic information.
The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key.
You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?

• A. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
• B. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
• C. CREATE TABLE student_grades (student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id));
• D. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));