Oracle Introduction to Oracle9i: SQL 認定 1Z0-007 試験問題:

1. Examine the structure of the EMPLOYEES table:

You need to create an index called NAME_IDX on the first name and last name fields of the EMPLOYEES table. Which SQL statement would you use to perform this task?

A) CREATE INDEX NAME_IDX (first_name, last_name);
B) CREATE INDEX NAME_IDX
ON (first_name, last_name);
C) CREATE INDEX NAME_IDX
ON employees(first_name, last_name);
D) CREATE INDEX NAME_IDX
ON employees (first_name AND last_name);
E) CREATE INDEX NAME_IDX
FOR employees(first_name, last_name);
F) CREATE INDEX NAME_IDX (first_name AND last_name);

2. Which SQL statement defines the FOREIGN KEY constraint on the DEPTNO column of the EMP table?

A) CREATE TABLE EMP
(empno NUMBER(4)
ename VARCHAR2(35),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno)
FOREIGN KEY (deptno));
B) CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2)
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
C) CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2) NOT NULL
CONSTRAINT emp_deptno_fk FOREIGN KEY deptno
REFERENCES dept deptno);
D) CREATE TABLE EMP (empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2) FOREIGN KEY
CONSTRAINT emp deptno fk REFERENCES dept (deptno));

3. Which two tasks can your perform by using the TO_CHAR function? (Choose two)

A) Convert 'TEN' to 10
B) Convert a date to a character expression
C) Convert a character expression to a date
D) Convert '10' to 10
E) Convert 10 to 'TEN'
F) Convert 10 to '10'

4. You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty.
Which statement accomplishes this task?

A) ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
B) ALTER TABLE students
MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
C) ALTER TABLE students
ADD CONSTRAINT PRIMARY KEY (student_id);
D) ALTER TABLE students
ADD PRIMARY KEY student_id;
E) ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;

5. Exhibit

Examine the data in the EMPLOYEES and DEPARTMENTS tables.
You want to retrieve all employees' last names, along with their manager's last names and their department names. Which query would you use?

A) SELECT e.last_name, m.last_name, department_name
FROM employees e
RIGT OUTER JOIN employees m on ( e.manager_id = m.employee_id)
LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
B) SELECT e.last_name, m.last_name, department_name
FROM employees e
LEFT OUTER JOIN employees m on ( e.managaer_id = m.employee_id)
LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
C) SELECT e.last_name, m.last_name, department_name
FROM employees e
LEFT OUTER JOIN employees m on ( e.manager_id = m.employee_id)
RIGT OUTER JOIN departments d ON (e.department_id = d.department_id);
D) SELECT e.last_name, m.last_name, department_name
FROM employees e
RIGHT OUTER JOIN employees m on ( e.manager_id = m.employee_id)
RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id)
E) SELECT last_name, manager_id, department_name
FROM employees e
FULL OUTER JOIN departments d ON (e.department_id = d.department_id);
F) SELECT last_name, manager_id, department_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id) ;

質問と回答：