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.
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? 
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.) 
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. 
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. 
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? 
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 > 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. 
Which three subqueries work? (Choose three.)
A. SELECT *
FROM employees
where salary > (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 > ANY (SELECT AVG(salary)
FROM employees
GROUP BY department_id);
D. SELECT department_id
FROM employees
WHERE salary > ALL (SELECT AVG(salary)
FROM employees
GROUP BY department_id);
E. SELECT last_name
FROM employees
WHERE salary > ANY (SELECT MAX(salary)
FROM employees
GROUP BY department_id);
F. SELECT department_id
FROM employees
WHERE salary > 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
