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

1. Examine this statement:
SELECT student_id, gpa FROM student_grades WHERE gpa > &&value;
You run the statement once, and when prompted you enter a value of 2.0. A report is produced. What happens when you run the statement a second time?

A) You are prompted to enter a new value.
B) A report is produced that matches the first report produced.
C) An error is returned.
D) You are asked whether you want a new value or if you want to run the report based on the previous value.

2. Which syntax turns an existing constraint on?

A) ALTER TABLE table_name
STATUS ENABLE CONSTRAINT constraint_name;
B) ALTER TABLE table_name
STATUS = ENABLE CONSTRAINT constraint_name;
C) ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;
D) ALTER TABLE table_name
TURN ON CONSTRAINT constraint_name;
E) ALTER TABLE table_name
ENABLE constraint_name;
F) ALTER TABLE table_name
TURN ON CONSTRAINT constraint_name;

3. Examine the data from the EMP table:

The COMMISSION column shows the monthly commission earned by the employee.
Which three tasks would require subqueries or joins in order to perform in a single step? (Choose three)

A) Decreasing the commission by 150 for the employees who are working in department 30 and earning a commission of more then 800.
B) Increasing the commission of employee 3 by the average commission earned in department 20.
C) Inserting into the table a new employee 10 who works for department 20 and earns a commission that is equal to the commission earned by employee 3.
D) Creating a table called COMMISSION that has the same structure and data as the columns EMP_ID and COMMISSIONS of the EMP table.
E) Deleting the records of employees who do not earn commission.
F) Finding the number of employees who do NOT earn commission and are working for department 20.

4. Which SELECT statement will the result 'ello world' from the string 'Hello World'?

A) SELECT LOWER(SUBSTR('Hello World', 1, 1) FROM dual;
B) SELECT SUBSTR( 'Hello World',1) FROM dual;
C) SELECT INITCAP(TRIM ('Hello World', 1,1)) FROM dual;
D) SELECT LOWER(TRIM ('H' FROM 'Hello World')) FROM dual;
E) SELECT LOWER(SUBSTR('Hello World', 2, 1) FROM dual;

5. You want to display the titles of books that meet these criteria:
1.Purchased before January 21, 2001
2.Price is less then $500 or greater than $900
You want to sort the results by their data of purchase, starting with the most recently
bought book.
Which statement should you use?

A) SELECT book_title
FROM books
WHERE price < 500 or > 900
AND purchase_date < '21-JAN-2001'
ORDER BY purchase date DESC;
B) SELECT book_title
FROM books
WHERE price IN (500,900)
AND purchase_date < '21-JAN-2001'
ORDER BY purchase date ASC;
C) SELECT book_title
FROM books
WHERE (price < 500 OR price > 900)
AND purchase_date < '21-JAN-2001'
ORDER BY purchase date DESC;
D) SELECT book_title
FROM books
WHERE price between 500 and 900
AND purchase_date < '21-JAN-2001'
ORDER BY purchase_date;

質問と回答：