Technorati 标签: oracle, opc, 007, dba

Q: 100  What are two reasons to create synonyms? (Choose two.)
A.   You have too many tables.
B.   Your tables are too long.
C.   Your tables have difficult names.
D.   You want to work on your own tables.
E.   You want to use another schema's tables.
F.   You have too many columns in your tables.
答案:C,E
解析:
同义词,可以隐藏表创建者具体信息,简化表名。

Q: 101  Which SQL statement generates the alias Annual Salary for the calculated
column  SALARY*12?
A.   SELECT ename, salary*12 'Annual Salary'
FROM employees;
B.   SELECT ename, salary*12 "Annual Salary"
FROM employees;
C.   SELECT ename, salary*12 AS Annual Salary
FROM employees;
D.   SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY")
FROM employees
答案:B

Q: 102  A SELECT statement can be used to perform these three functions:
1.  Choose rows from a table.
2.  Choose columns from a table.
3.  Bring together data that is stored in different tables by creating a link between them.
Which set of keywords describes these capabilities?
A.   difference, projection, join
B.   selection, projection, join
C.   selection, intersection, join
D.   intersection, projection, join
E.   difference, projection, product
答案:B
解析:选择,投影,连接

Q: 103  The STUDENT_GRADES table has these columns:
STUDENT_ID    NUMBER(12)
SEMESTER_END  DATE
GPA           NUMBER(4,3)
The registrar requested a report listing the students' grade point averages (GPA) sorted from highest
grade point average to lowest.
Which statement produces a report that displays the student ID and GPA in the sorted order requested
by the registrar?
A.   SELECT student_id, gpa
FROM student_grades
ORDER BY gpa ASC;
B.   SELECT student_id, gpa
FROM student_grades
SORT ORDER BY gpa ASC;
C.   SELECT student_id, gpa
FROM student_grades
SORT ORDER BY gpa;
D.   SELECT student_id, gpa
FROM student_grades
ORDER BY gpa;
E.   SELECT student_id, gpa
FROM student_grades
SORT ORDER BY gpa DESC;
F.   SELECT student_id, gpa
FROM student_grades
ORDER BY gpa DESC;
答案:F

Q: 104  Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
EMPLOYEES
EMPLOYEE_ID         NUMBER
DEPARTMENT_ID    NUMBER
MANAGER_ID           NUMBER
LAST_NAME             VARCHAR2(25)
DEPARTMENTS
DEPARTMENT_ID          NUMBER
MANAGER_ID                 NUMBER
DEPARTMENT_NAME    VARCHAR2(35)
LOCATION_ID                 NUMBER
You want to create a report displaying employee last names, department names, and locations. Which
query should you use?
A.   SELECT e.last_name, d. department_name,  d.location_id
FROM employees e NATURAL JOIN  departments D
USING  department_id ;
B.   SELECT last_name, department_name,   location_id
FROM employees NATURAL JOIN  departments
WHERE e.department_id =d.department_id;
C.   SELECT e.last_name, d.department_name,  d.location_id
FROM employees e NATURAL JOIN  departments d;
D.   SELECT e.last_name, d.department_name,  d.location_id
FROM employees e JOIN  departments d
USING (department_id );
答案:D
解析:A:NATURAL JOIN 不能用using;
B:NATURAL JOIN 的列不能作为where条件限定;
C:NATURAL JOIN 会用department_id 和 manager_id作为连接条件。

Q: 105  Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID  NUMBER       Primary Key
FIRST_NAME   VARCHAR2(25)
LAST_NAME    VARCHAR2(25)
HIRE_DATE    DATE
Which UPDATE statement is valid?
A.   UPDATE employees
SET first_name = 'John'
SET last_name ='Smith'
WHERE employee_id = 180;
B.   UPDATE employees
SET first_name = 'John',
SET last_name ='Smith'
WHERE employee_id = 180;
C.   UPDATE employees
SET first_name = 'John'
AND last_name ='Smith'
WHERE employee_id = 180;
D.   UPDATE employees
SET first_name = 'John', last_name ='Smith'
WHERE employee_id = 180;
答案:D

Q: 106  Which three are DATETIME data types that can be used when specifying
column definitions? (Choose three.)
A.   TIMESTAMP
B.   INTERVAL MONTH TO DAY
C.   INTERVAL DAY TO SECOND
D.   INTERVAL YEAR TO MONTH
E.   TIMESTAMP WITH DATABASE TIMEZONE
答案:A,C,D

 
Q: 107  The STUDENT_GRADES table has these columns:
STUDENT_ID   NUMBER(12)
SEMESTER_END DATE
GPA          NUMBER(4,3)
Which statement finds the highest grade point average (GPA) per semester?
A.   SELECT MAX(gpa)
FROM student_grades
WHERE gpa IS NOT NULL;
B.   SELECT (gpa)
FROM student_grades
GROUP BY semester_end
WHERE gpa IS NOT NULL;
C.   SELECT MAX(gpa)
FROM student_grades
WHERE gpa IS NOT NULL
GROUP BY semester_end;
D.   SELECT MAX(gpa)
GROUP BY semester_end
WHERE gpa IS NOT NULL
FROM student_grades;
E.   SELECT MAX(gpa)
FROM student_grades
GROUP BY semester_end
WHERE gpa IS NOT NULL;
解答:C

Q: 108  Click the Exhibit button to examine the structure of the EMPLOYEES,
DEPARTMENTS, and LOCATIONS tables.

image
Two new departments are added to your company as shown:
DEPARTMENT_ID    DEPARTMENT_NAME    MGR_ID    LOCATION_ID
9998                         Engineering                    123
9999                         Administrative                                     Boston
You need to list the names of employees, the department IDs, the department names, and the cities where
the departments are, even if there are no employees in the departments and even if the departments are
not yet assigned to a location. You need to join the EMPLOYEES, DEPARTMENTS, and LOCATIONS
tables to retrieve this information.
插图
Which statement do you execute to retrieve this information?
A.   SELECT e.last_name, d.department_id,
d.department_name, l.city
FROM departments d
RIGHT OUTER JOIN employees e
ON d.department_id = e.department_id
RIGHT OUTER JOIN locations l
ON d.location_id = l.location_id;
B.   SELECT e.last_name, d.department_id,
d.department_name, l.city
FROM departments d
FULL OUTER JOIN employees e
ON d.department_id = e.department_id
FULL OUTER JOIN locations l
ON d.location_id = l.location_id;
C.   SELECT e.last_name, d.department_id,
d.department_name, l.city
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
LEFT OUTER JOIN locations l
ON d.location_id = l.location_id;
D.   SELECT last_name, department_id
department_name, city
FROM departments d
NATURAL JOIN employees e
NATURAL JOIN locations l;
答案: C

Q: 109  Which two statements about views are true? (Choose two.)
A.   A view can be created as read only.
B.   A view can be created as a join on two or more tables.
C.   A view cannot have an ORDER BY clause in the SELECT statement.
D.   A view cannot be created with a GROUP BY clause in the SELECT statement.
E.   A view must have aliases defined for the column names in the SELECT statement.
答案:A,B
解析:C:当是top-n视图时,可以用order by子句

Q: 110  Which SELECT statement should you use to extract the year from the
system date and display it in the format "1998"?
A.   SELECT TO_CHAR(SYSDATE,'yyyy')
FROM dual;
B.   SELECT TO_DATE(SYSDATE,'yyyy')
FROM dual;
C.   SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY')
FROM dual;
D.   SELECT DECODE(SUBSTR(SYSDATE, 8), 'year')
FROM dual;
E.   SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy')
FROM dual;
答案:A
 
Q: 111  Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID          NUMBER               NOT NULL
EMP_NAME              VARCHAR2(30)
JOB_ID                     VARCHAR2(20)
SAL                          NUMBER
MGR_ID                   NUMBER
DEPARTMENT_ID    NUMBER
You want to create a SQL script file that contains an INSERT statement. When the script is run, the
INSERT statement should insert a row with the specified values into the EMPLOYEES table. The
INSERT statement should pass values to the table columns as specified below:
EMPLOYEE_ID:                Next value from the sequence EMP_ID_SEQ
EMP_NAME and JOB_ID: As specified by the user during run time, through
                                        substitution variables
SAL:                                2000
MGR_ID:                         No value
DEPARTMENT_ID:         Supplied by the user during run time through
                                       substitution variable. The INSERT statement should
                                       fail if the user supplies a value other than 20 or 50.
Which INSERT statement meets the above requirements?
A.   INSERT INTO employees
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
B.   INSERT INTO employees
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid',
2000, NULL, &did IN (20,50));
C.   INSERT INTO (SELECT *
FROM   employees
WHERE  department_id IN (20,50))
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
D.   INSERT INTO (SELECT *
FROM   employees
WHERE  department_id IN (20,50)
WITH CHECK OPTION)
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
E.   INSERT INTO (SELECT *
FROM   employees
WHERE  (department_id = 20 AND
department_id = 50)
WITH CHECK OPTION )
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
答案:D
解析:加了with check option才会对插入数据做检查。

Q: 112  The EMPLOYEES table has these columns:
LAST_NAME   VARCHAR2(35)
SALARY      NUMBER(8,2)
HIRE_DATE   DATE
Management wants to add a default value to the SALARY column. You plan to alter the  table by using
this SQL statement:
       ALTER TABLE EMPLOYEES
       MODIFY (SALARY DEFAULT 5000);
Which is true about your ALTER statement?
A.   Column definitions cannot be altered to add DEFAULT values.
B.   A change to the DEFAULT value affects only subsequent insertions to the table.
C.   Column definitions cannot be altered to add DEFAULT values for columns with a NUMBER data type.
D.   All the rows that have a NULL value for the SALARY column will be updated with the value 5000.
答案:B

Q: 113  Which two statements are true about constraints? (Choose two.)
A.   The UNIQUE constraint does not permit a null value for the column.
B.   A  UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.
C.   The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.
D.   The NOT NULL constraint ensures that null values are not permitted for the column.
答案:B,D

Q: 114  Which clause should you use to exclude group results?
A.   WHERE
B.   HAVING
C.   RESTRICT
D.   GROUP BY
E.   ORDER BY
答案:B

Q: 115  Which SQL statement generates the alias Annual Salary for the calculated
column  SALARY*12?
A.   SELECT ename, salary*12 'Annual Salary'
FROM employees;
B.   SELECT ename, salary*12 "Annual Salary"
FROM employees;
C.   SELECT ename, salary*12 AS Annual Salary
FROM employees;
D.   SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY")
FROM employees
答案:B

Q: 116  Evaluate the SQL statement
        DROP TABLE DEPT;
Which four statements are true of the SQL statement?  (Choose four.)
A.   You cannot roll back this statement.
B.   All pending transactions are committed.
C.   All views based on the DEPT table are deleted.
D.   All  indexes based on the DEPT table are dropped.
E.   All data in the table is deleted, and the table structure is also deleted.
F.   All data in the table is deleted, but the structure of the table is retained.
G.   All synonyms based on the DEPT table are deleted.
答案:A,B,D,E
解析:
A:drop是ddl语言,自动commit,不支持回滚;
B:自动commit
C,G:删除表,对应的视图和同义词不会被删除;

Q: 117  Which view should a user query to display the columns associated with the
constraints on a table owned by the user?
A.   USER_CONSTRAINTS
B.   USER_OBJECTS
C.   ALL_CONSTRAINTS
D.   USER_CONS_COLUMNS
E.   USER_COLUMNS
答案:D

Q: 118  Which SELECT statement will get the result 'elloworld' from the string
'HelloWorld'?
A.   SELECT  SUBSTR( 'HelloWorld',1) FROM dual;
B.   SELECT INITCAP(TRIM ('HelloWorld', 1,1)) FROM dual;
C.   SELECT  LOWER(SUBSTR('HelloWorld', 1, 1) FROM dual;
D.   SELECT  LOWER(SUBSTR('HelloWorld', 2, 1) FROM dual;
E.   SELECT LOWER(TRIM ('H' FROM 'HelloWorld')) FROM dual;
答案:E

Q: 119  Which three statements about subqueries are true?  (Choose three.)
A.   A single row subquery can retrieve only one column and one row.
B.   A single row subquery can retrieve only one row but many columns.
C.   A multiple row subquery can retrieve multiple rows and multiple columns.
D.   A multiple row subquery can be compared using  the ">" operator.
E.   A  single row subquery can use the IN operator.
F.   A multiple row subquery can use the "=" operator.
答案:B,C,E

Q: 120  In which case would you use a FULL OUTER JOIN?
A.   Both tables have NULL values.
B.   You want all unmatched data from one table.
C.   You want all matched data from both tables.
D.   You want all unmatched data from both tables.
E.   One of the tables has more data than the other.
F.   You want all matched and unmatched data from only one table.
答案:D

Q: 121  Which two are attributes of iSQL*Plus?  (Choose two.)
A.   iSQL*Plus commands cannot be abbreviated.
B.   iSQL*Plus commands are accessed from a browser.
C.   iSQL*Plus commands are used to manipulate data in tables.
D.   iSQL*Plus commands manipulate table definitions in the database.
E.   iSQL*Plus is the Oracle proprietary interface for executing SQL statements.
答案:B,E

Q: 122  Which two are character manipulation functions? (Choose two.)
A.   TRIM
B.   REPLACE
C.   TRUNC
D.   TO_DATE
E.   MOD
F.   CASE
答案:A,B

Q: 123  Click the Exhibit button and examine the data in the EMPLOYEES table.
Examine the subquery:
     SELECT last_name
     FROM employees
     WHERE salary IN (SELECT MAX(salary)
                                  FROM employees
                                 GROUP BY department_id);
Which statement is true?
image

A.   The SELECT statement is syntactically accurate.
B.   The SELECT statement does not work because there is no HAVING clause.
C.   The SELECT statement does not work because the column specified in the GROUP BY clause is not in the
SELECT list.
D.   The SELECT statement does not work because the GROUP BY clause should be in the main query and not
in the subquery.
答案:A

Q: 124  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),
CONSTRAINT student_id_fk REFERENCES (student_id)
FOREIGN KEY students(student_id));
B.   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));
C.   CREATE TABLE student_grades
(student_id    NUMBER(12),
semester_end   DATE,
gpa            NUMBER(4,3),
CONSTRAINT FOREIGN KEY (student_id)
REFERENCES students(student_id));
D.   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));
答案:D

Q: 125  Which are DML statements? (Choose all that apply.)
A.   COMMIT
B.   MERGE
C.   UPDATE
D.   DELETE
E.   CREATE
F.   DROP...
答案:B,C,D

Q: 126  Which two statements are true regarding the default behavior of the
ORDER BY clause?  (Choose two.)
A.   Null values are left out of the sort.
B.   Character values are displayed from Z to A.
C.   Date values are displayed with the earliest value first.
D.   Null values are displayed last for descending sequences.
E.   Numeric values are displayed with the lowest values first.
答案:C,E
解析:null值在oracle中排序总是算最大的

Q: 127  Click the Exhibit button and examine the data from the EMP table.
The COMMISSION column shows the monthly commission earned by the employee.
Which two tasks would require subqueries or joins in order to be performed in a single step? (Choose two.)
image

A.   listing the employees who earn the same amount of commission as employee 3
B.   finding the total commission earned by the employees in department 10
C.   finding the number of employees who earn a commission that is higher than the average commission of the
company
D.   listing the departments whose average commission is more than 600
E.   listing the employees who do not earn commission and who are working for department 20 in descending
order of the employee ID
F.   listing the employees whose annual commission is more than 6000
解析:A,C

Q: 128  Which statement adds a constraint that ensures the CUSTOMER_NAME
column of the CUSTOMERS table holds a value?
A.   ALTER TABLE customers
ADD CONSTRAINT cust_name_nn CHECK  customer_name IS NOT NULL;
B.   ALTER TABLE customers
MODIFY CONSTRAINT cust_name_nn CHECK  customer_name IS NOT NULL;
C.   ALTER TABLE customers
MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;
D.   ALTER TABLE customers
MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL;
E.   ALTER TABLE customers
MODIFY name CONSTRAINT cust_name_nn NOT NULL;
F.   ALTER TABLE customers
ADD CONSTRAINT cust_name_nn CHECK  customer_name  NOT NULL;
答案:C
解析:另外一种写法为:alter table customers modify (customer_name not null);

Q: 129  The CUSTOMERS table has these columns:
CUSTOMER_ID              NUMBER(4)           NOT NULL
CUSTOMER_NAME       VARCHAR2(100)   NOT NULL
STREET_ADDRESS      VARCHAR2(150)
CITY_ADDRESS            VARCHAR2(50)
STATE_ADDRESS         VARCHAR2(50)
PROVINCE_ADDRESS  VARCHAR2(50)
COUNTRY_ADDRESS   VARCHAR2(50)
POSTAL_CODE            VARCHAR2(12)
CUSTOMER_PHONE    VARCHAR2(20)
The CUSTOMER_ID column is the primary key for the table.
You need to determine how dispersed your customer base is.  Which expression finds the number of
different countries represented in the CUSTOMERS table?
A.   COUNT(UPPER(country_address))
B.   COUNT(DIFF(UPPER(country_address)))
C.   COUNT(UNIQUE(UPPER(country_address)))
D.   COUNT DISTINCT UPPER(country_address)
E.   COUNT(DISTINCT (UPPER(country_address)))
答案:E

Q: 130  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.   You get an error because of a primary key violation.
B.   The data and structure of the EMPLOYEES table are deleted.
C.   The data in the EMPLOYEES table is deleted but not the structure.
D.   You get an error because the statement is not syntactically correct.
答案:C

Q: 131  Examine the SQL statement that creates ORDERS table:
CREATE TABLE orders
          (SER_NO     NUMBER UNIQUE,
           ORDER_ID    NUMBER,
          ORDER_DATE  DATE NOT NULL,
          STATUS      VARCHAR2(10)
                 CHECK (status IN ('CREDIT', 'CASH')),
          PROD_ID  NUMBER
                 REFERENCES PRODUCTS(PRODUCT_ID),
         ORD_TOTAL  NUMBER,
         PRIMARY KEY (order_id, order_date));  
For which columns would an index be automatically created when you execute the above SQL statement?
(Choose two.)
A.   SER_NO
B.   ORDER_ID
C.   STATUS
D.   PROD_ID
E.   ORD_TOTAL
F.   composite index on ORDER_ID and ORDER_DATE
答案:A,F

Q: 132  You need to create a table named ORDERS that contains four columns:
1.  an ORDER_ID column of number data type
2.  a CUSTOMER_ID column of number data type
3.  an ORDER_STATUS column that contains a character data type
4.  a DATE_ORDERED column to contain the date the order was placed
When a row is inserted into the table, if no value is provided for the status of the order, the value
PENDING should be used instead.
Which statement accomplishes this?
A.   CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status NUMBER(10) DEFAULT 'PENDING',
date_ordered DATE );
B.   CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) = 'PENDING',
date_ordered DATE );
C.   CREATE OR REPLACE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) DEFAULT 'PENDING',
date_ordered DATE );
D.   CREATE OR REPLACE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) = 'PENDING',
date_ordered DATE );
E.   CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) DEFAULT 'PENDING',
date_ordered DATE );
F.   CREATE TABLE orders (
order_id NUMBER(10),
customer_id NUMBER(8),
order_status VARCHAR2(10) DEFAULT 'PENDING',
date_ordered VARCHAR2 );
答案:E

Q: 133  Click the Exhibit button to examine the structures of the EMPLOYEES,
DEPARTMENTS, and TAX tables.
image

For which situation would you use a nonequijoin query?
A.   to find the tax percentage for each of the employees
B.   to list the name, job_id, and manager name for all the employees
C.   to find the name, salary, and the department name of employees who are not working with Smith
D.   to find the number of employees working for the Administrative department and earning less than 4000
E.   to display name, salary, manager ID, and department name of all the employees, even if the employees do
not have a department ID assigned
答案:A

Q: 134  Click the Exhibit button and examine the data in the EMPLOYEES and
DEPARTMENTS tables.
image

You want to retrieve all employees' last names, along with their managers' last names and their
department names. Which query would you use?
A.   SELECT last_name, manager_id,  department_name
FROM employees e
FULL  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.manager_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
RIGHT OUTER JOIN   employees m  on ( e.manager_id = m.employee_id)
LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
D.   SELECT e.last_name, m.last_name,  department_name
FROM employees e
LEFT 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 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);
F.   SELECT last_name, manager_id,  department_name
FROM employees e
JOIN  departments d ON (e.department_id = d.department_id) ;
答案:B

Q: 135  Click the Exhibit button to examine the structures of the EMPLOYEES
and TAX tables.
You need to find the percentage tax applicable for each employee. Which SQL statement would you use?
image
A.   SELECT employee_id, salary, tax_percent
FROM   employees e JOIN tax t
ON  e.salary BETWEEN t.min_salary AND t.max_salary;
B.   SELECT employee_id, salary, tax_percent
FROM   employees e JOIN tax t
WHERE  e.salary &gt; t.min_salary AND < t.max_salary;
C.   SELECT employee_id, salary, tax_percent
FROM   employees e JOIN tax t
ON  (MIN(e.salary) = t.min_salary
AND    MAX(e.salary) = t.max_salary);
D.   You cannot find the information because there is no common column between the two tables.
答案:A

Q: 136  Evaluate this SQL statement:
SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct)
              + (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE
              FROM employees e, sales s
WHERE e.employee_id = s.emp_id;
What will happen if you remove all the parentheses from the calculation?
A.   The value displayed in the CALC_VALUE column will be lower.
B.   The value displayed in the CALC_VALUE column will be higher.
C.   There will be no difference in the value displayed in the CALC_VALUE column.
D.   An error will be reported.
答案:C

Q: 137  You need to create a view EMP_VU. The view should allow the users to
manipulate the records of only the employees that are working for departments 10 or 20. Which SQL
statement would you use to create the view EMP_VU?
A.   CREATE VIEW emp_vu AS
SELECT *
FROM employees
WHERE department_id IN (10,20);
B.   CREATE VIEW emp_vu AS
SELECT *
FROM employees
WHERE department_id IN (10,20)
WITH READ ONLY;
C.   CREATE VIEW emp_vu AS
SELECT *
FROM employees
WHERE department_id IN (10,20)
WITH CHECK OPTION;
D.   CREATE FORCE VIEW emp_vu AS
SELECT *
FROM employees
WHERE department_id IN (10,20);
E.   CREATE FORCE VIEW emp_vu AS
SELECT *
FROM employees
WHERE department_id IN (10,20)
NO UPDATE;
答案:C

Q: 138  What is necessary for your query on an existing view to execute
successfully?
A.   The underlying tables must have data.
B.   You need SELECT privileges on the view.
C.   The underlying tables must be in the same schema.
D.   You need SELECT privileges only on the underlying tables.
答案:B

Q: 139  What are two reasons to create synonyms? (Choose two.)
A.   You have too many tables.
B.   Your tables are too long.
C.   Your tables have difficult names.
D.   You want to work on your own tables.
E.   You want to use another schema's tables.
F.   You have too many columns in your tables.
答案:C,E

Q: 140  Evaluate these two SQL statements:
           SELECT last_name, salary , hire_date
           FROM EMPLOYEES
           ORDER BY salary DESC;
           SELECT last_name, salary  , hire_date
           FROM EMPLOYEES
           ORDER BY 2 DESC;
What is true about them?
A.   The two statements produce identical results.
B.   The second statement returns a syntax error.
C.   There is no need to specify DESC because the results are sorted in descending order by default.
D.   The two statements can be made to produce identical results by adding a column alias for the salary column
in the second SQL statement.
答案:A

Q: 141  Evaluate this SQL statement:
        SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID, d.DEPARTMENT_NAME
        FROM EMPLOYEES e, DEPARTMENTS d
        WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;
In the statement, which capabilities of a SELECT statement are performed?
A.   selection, projection, join
B.   difference, projection, join
C.   selection, intersection, join
D.   intersection, projection, join
E.   difference, projection, product
答案:A

Q: 142  The EMPLOYEES table contains these  columns:
       EMPLOYEE_ID          NUMBER(4)
       LAST_NAME              VARCHAR2 (25)
       JOB_ID                       VARCHAR2(10)
You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use?
A.   SELECT employee_id, last_name, job_id
FROM   employees
WHERE  job_id LIKE '%SA\_%' ESCAPE '\';
B.   SELECT employee_id, last_name, job_id
FROM   employees
WHERE  job_id LIKE '%SA_';
C.   SELECT employee_id, last_name, job_id
FROM   employees
WHERE  job_id LIKE '%SA_' ESCAPE "\";
D.   SELECT employee_id, last_name, job_id
FROM   employees
WHERE  job_id = '%SA_';
答案:A

Q: 143  Evaluate the SQL statement:        
                SELECT LPAD(salary,10,*)
        FROM EMP
        WHERE EMP_ID = 1001;
If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed?
A.   17000.00
B.   17000*****
C.   ****170.00
D.   **17000.00
E.   an error statement
答案:E
解析:*要加引号,正确为:SELECT LPAD(salary,10,'*') FROM EMP WHERE EMP_ID = 1001;

Q: 144  Which two are true about aggregate functions? (Choose two.)
A.   You can use aggregate functions in any clause of a SELECT statement.
B.   You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause
of a SELECT statement.
C.   You can mix single row columns with aggregate functions in the column list of a SELECT statement by
grouping on the single row columns.
D.   You can pass column names, expressions, constants, or functions as parameters to an aggregate function.
E.   You can use aggregate functions on a table, only by grouping the whole table as one single group.
F.   You cannot group the rows of a table by more than one column while using aggregate functions.
答案:C,D

Q: 145  What is true about sequences?
A.   Once created, a sequence belongs to a specific schema.
B.   Once created, a sequence is linked to a specific table.
C.   Once created, a sequence is automatically available to all users.
D.   Only the DBA can control which sequence is used by a certain table.
E.   Once created, a sequence is automatically used in all INSERT and UPDATE statements.
答案:A

Q: 146  Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
EMPLOYEES
EMPLOYEE_ID         NUMBER
DEPARTMENT_ID    NUMBER
MANAGER_ID          NUMBER
LAST_NAME            VARCHAR2(25)
DEPARTMENTS
DEPARTMENT_ID        NUMBER
MANAGER_ID               NUMBER
DEPARTMENT_NAME  VARCHAR2(35)
LOCATION_ID              NUMBER
You want to create a report displaying employee last names, department names, and locations. Which
query should you use?
A.   SELECT e.last_name, d. department_name,  d.location_id
FROM employees e NATURAL JOIN  departments D
USING  department_id ;
B.   SELECT last_name, department_name,   location_id
FROM employees NATURAL JOIN  departments
WHERE e.department_id =d.department_id;
C.   SELECT e.last_name, d.department_name,  d.location_id
FROM employees e NATURAL JOIN  departments d;
D.   SELECT e.last_name, d.department_name,  d.location_id
FROM employees e JOIN  departments d
USING (department_id );
答案:D

Q: 147  Evaluate the set of SQL statements:
    CREATE TABLE dept
    (deptno   NUMBER(2),
     dname     VARCHAR2(14),
      loc       VARCHAR2(13));
       ROLLBACK;
       DESCRIBE DEPT
What is true about the set?
A.   The DESCRIBE DEPT statement displays the structure of the DEPT table.
B.   The ROLLBACK statement frees the storage space occupied by the DEPT table.
C.   The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.
D.   The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT
statement introduced before the ROLLBACK statement.
答案:A

Q: 148  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.   An error is returned.
B.   You are prompted to enter a new value.
C.   A report is produced that matches the first report produced.
D.   You are asked whether you want a new value or if you want to run the report based on the previous value.
答案:C

Q: 149  Examine the description of the STUDENTS table:
STD_ID            NUMBER(4)
COURSE_ID     VARCHAR2(10)
START_DATE  DATE
END_DATE      DATE
Which two aggregate functions are valid on the START_DATE column? (Choose two.)
A.   SUM(start_date)
B.   AVG(start_date)
C.   COUNT(start_date)
D.   AVG(start_date, end_date)
E.   MIN(start_date)
F.   MAXIMUM(start_date)
答案:C,E

Q: 150  Which three SELECT statements display 2000 in the format "$2,000.00"?
(Choose three.)
A.   SELECT TO_CHAR(2000, '$#,###.##')
FROM dual;
B.   SELECT TO_CHAR(2000, '$0,000.00')
FROM dual;
C.   SELECT TO_CHAR(2000, '$9,999.00')
FROM dual;
D.   SELECT TO_CHAR(2000, '$9,999.99')
FROM dual;
E.   SELECT TO_CHAR(2000, '$2,000.00')
FROM dual;
F.   SELECT TO_CHAR(2000, '$N,NNN.NN')
FROM dual;
答案:B,C,D

Q: 151  Click the Exhibit button and examine the data in the EMPLOYEES table.
image
Which three subqueries work?  (Choose three.)
A.   SELECT *
FROM employees
where salary &gt; (SELECT MIN(salary)
FROM employees
GROUP BY department_id);
B.   SELECT *
FROM employees
WHERE salary = (SELECT AVG(salary)
FROM employees
GROUP BY department_id);
C.   SELECT distinct department_id
FROM employees
WHERE salary &gt; ANY (SELECT AVG(salary)
FROM employees
GROUP BY department_id);
D.   SELECT department_id
FROM employees
WHERE salary &gt; ALL (SELECT AVG(salary)
FROM employees
GROUP BY department_id);
E.   SELECT last_name
FROM employees
WHERE salary &gt; ANY (SELECT MAX(salary)
FROM employees
GROUP BY department_id);
F.   SELECT department_id
FROM employees
WHERE salary &gt; ALL (SELECT AVG(salary)
FROM employees
GROUP BY AVG(SALARY));
答案:C,D,E

Q: 152  Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID  NUMBER          Primary Key
FIRST_NAME   VARCHAR2(25)
LAST_NAME    VARCHAR2(25)
HIRE_DATE     DATE  
You issue these statements:
       CREATE table new_emp ( employee_id NUMBER, name VARCHAR2(30));
       INSERT INTO new_emp  SELECT employee_id , last_name from employees
       Savepoint s1;
       UPDATE new_emp set name = UPPER(name);
       Savepoint s2;
       Delete from new_emp;
       Rollback to  s2;
       Delete from new_emp where employee_id =180;
       UPDATE new_emp set name = 'James';
       Rollback to s2;
       UPDATE new_emp set name = 'James'  WHERE employee_id =180;
       Rollback;
At the end of this transaction, what is true?
A.   You have no rows in the table.
B.   You have an employee with the name of James.
C.   You cannot roll back to the same savepoint more than once.
D.   Your last update fails to update any rows because employee ID 180 was already deleted.
答案:A

Q: 153  What is true regarding subqueries?
A.   The inner query always sorts the results of the outer query.
B.   The outer query always sorts the results of the inner query.
C.   The outer query must return a value to the inner query.
D.   The inner query returns a value to the outer query.
E.   The inner query must always return a value or the outer query will give an error.
答案:D

Q: 154  In which scenario would an index be most useful?
A.   The indexed column is declared as NOT NULL.
B.   The indexed columns are used in the FROM clause.
C.   The indexed columns are part of an expression.
D.   The indexed column contains a wide range of values.
答案:D

Q: 155  The CUSTOMERS table has these columns:
CUSTOMER_ID             NUMBER(4)            NOT NULL
CUSTOMER_NAME       VARCHAR2(100)    NOT NULL
STREET_ADDRESS      VARCHAR2(150)
CITY_ADDRESS            VARCHAR2(50)
STATE_ADDRESS        VARCHAR2(50)
PROVINCE_ADDRESS  VARCHAR2(50)
COUNTRY_ADDRESS   VARCHAR2(50)
POSTAL_CODE             VARCHAR2(12)
CUSTOMER_PHONE     VARCHAR2(20)
A promotional sale is being advertised to the customers in France.  Which WHERE clause identifies
customers that are located in France?
A.   WHERE lower(country_address) = "france"
B.   WHERE lower(country_address) = 'france'
C.   WHERE lower(country_address) IS 'france'
D.   WHERE lower(country_address) = '%france%'
E.   WHERE lower(country_address) LIKE %france%
答案:B

Q: 156  The CUSTOMERS table has these columns:
CUSTOMER_ID               NUMBER(4)             NOT NULL
CUSTOMER_NAME         VARCHAR2(100)     NOT NULL
CUSTOMER_ADDRESS  VARCHAR2(150)
CUSTOMER_PHONE       VARCHAR2(20)
You need to produce output that states "Dear Customer customer_name, ".
The customer_name data values come from the CUSTOMER_NAME column in the CUSTOMERS
table.  Which statement produces this output?
A.   SELECT dear customer, customer_name,
FROM customers;
B.   SELECT "Dear Customer", customer_name || ','
FROM customers;
C.   SELECT 'Dear Customer  ' || customer_name ','
FROM customers;
D.   SELECT 'Dear Customer  ' || customer_name || ','
FROM customers;
E.   SELECT "Dear Customer  " || customer_name || ","
FROM customers;
F.   SELECT 'Dear Customer  ' || customer_name || ',' ||
FROM customers;
答案:D