Oracle 1Z0-051 問題集

質問 1：
Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
Which statement produces the number of different departments that have employees with last name Smith?
A. SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith';
B. SELECT COUNT(*) FROM employees WHERE last_name='Smith';
C. SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith';
D. SELECT COUNT (dept_id) FROM employees WHERE last_name='Smith';
E. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';
正解：E

質問 2：
Evaluate the following SQL statements: Exhibit:

Which is the correct output of the above query?
A. +00-300,+00-650,+00 11:12:10.123457
B. +25-00,+00-650,+00 11:12:10.123457
C. +00-300, +54-02,+00 11:12:10.123457
D. +25-00, +54-02, +00 11:12:10.123457
正解：D

質問 3：
View the Exhibit and examine the structure of the CUSTOMERS table.
You have been asked to produce a report on the CUSTOMERS table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city. Which query would accomplish this task?
A. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_income_level desc, cust_city DESC;
B. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city desc, cust_income_level DESC;
C. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city, cust_income_level DESC;
D. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY (cust_city, cust_income_level) DESC;
正解：B

質問 4：
View the Exhibit to examine the description for the SALES table. Which views can have all DML operations performed on it? (Choose all that apply.)

A. CREATE VIEW v1 AS SELECT * FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION;
B. CREATE VIEW v3 AS SELECT * FROM SALES WHERE cust_id = 2034 WITH CHECK OPTION;
C. CREATE VIEW v4 AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES WHERE time_id <= SYSDATE - 2*365 GROUP BY prod_id, cust_id WITH CHECK OPTION;
D. CREATE VIEW v2 AS SELECT prod_id, cust_id, time_id FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION;
正解：A,B
解説: (Topexam メンバーにのみ表示されます)

質問 5：
You are currently located in Singapore and have connected to a remote database in
Chicago.
You issue the following command:
Exhibit:

PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table.
What is the outcome?
A. An error because the WHERE condition specified is invalid
B. Number of days since the promo started based on the current Chicago data and time
C. An error because the ROUND function specified is invalid
D. Number of days since the promo started based on the current Singapore data and time.
正解：B

質問 6：
View the Exhibit and examine the structure of the PRODUCTS table.
You want to display only those product names with their list prices where the list price is at least double the minimum price. The report should start with the product name having the maximum list price satisfying this
condition.
Evaluate the following SQL statement:
SQL>SELECT prod_name,prod_list_price
FROM products
WHERE prod_list_price >= 2 * prod_min_price
Which ORDER BY clauses can be added to the above SQL statement to get the correct output?
(Choose all that apply.)

A. ORDER BY prod_list_price DESC, prod_name;
B. ORDER BY prod_list_price DESC, prod_name DESC;
C. ORDER BY prod_name DESC, prod_list_price DESC;
D. ORDER BY (2*prod_min_price)DESC, prod_name;
E. ORDER BY prod_name, (2*prod_min_price)DESC;
正解：A,B
解説: (Topexam メンバーにのみ表示されます)

質問 7：
You need to calculate the number of days from 1st Jan 2007 till date:
Dates are stored in the default format of dd-mm-rr.
Which two SQL statements would give the required output? (Choose two.)
A. SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL
B. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;
C. SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')-'01-JAN-2007' FROM DUAL;
D. SELECT TO_DATE(SYSDATE,'DD/MONTH/YYYY')-'01/JANUARY/2007' FROM DUAL;
E. SELECT SYSDATE - '01-JAN-2007' FROM DUAL
正解：A,B