Oracle 1Z0-007(2)

on this view. Which option enables Scott to eliminate the need to qualify the view with the name

MARY.EMP_DEPT_LOC_VU each time the view is referenced?

A. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE PRIVATE SYNONYM EDL_VU FOR mary.EMP_DEPT_LOC_VU;

then he can prefix the columns with this synonym.

B. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE SYNONYM EDL_VU FOR mary.EMP_DEPT_LOC_VU;

then he can prefix the columns with this synonym.

C. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE LOCAL SYNONYM EDL_VU FOR mary.EMP_DEPT_LOC_VU;

then he can prefix the columns with this synonym.

D. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command

CREATE SYNONYM EDL_VU ON mary(EMP_DEPT_LOC_VU);

then he can prefix the columns with this synonym.

E. Scott cannot create a synonym because synonyms can be created only for tables. F. Scott cannot create any synonym for Mary's view. Mary should create a private synonym for the view and

grant SELECT privilege on that synonym to Scott. Q: 17 A subquery can be used to ___. A. create groups of data

B. sort data in a specific order

C. convert data to a different format

D. retrieve data based on an unknown condition

Q: 18 Click the Exhibit button to examine the data of the EMPLOYEES table. Which statement lists the ID, name, and salary of the employee, and the ID and name of the employee's

manager, for all the employees who have a manager and earn more than 4000? A. SELECT employee_id \salary,

employee_id \FROM employees

WHERE salary > 4000;

B. SELECT e.employee_id \e.salary,

m.employee_id \FROM employees e JOIN employees m WHERE e.mgr_id = m.mgr_id AND e.salary > 4000;

C. SELECT e.employee_id \

e.salary,

m.employee_id \FROM employees e JOIN employees m ON (e.mgr_id = m.employee_id) AND e.salary > 4000;

D. SELECT e.employee_id \e.salary,

m.mgr_id \FROM employees e SELF JOIN employees m WHERE e.mgr_id = m.employee_id AND e.salary > 4000;

E. SELECT e.employee_id \e.salary,

m.mgr_id \FROM employees e JOIN employees m www.CertificationKing.com - 14 -

USING (e.employee_id = m.employee_id) AND e.salary > 4000;

Q: 19 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.

Q: 20 Examine the description of the CUSTOMERS table: 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.

Which statement returns the city address and the number of customers in the cities Los Angeles or San Francisco?

A. SELECT city_address, COUNT(*) FROM customers

WHERE city_address IN ('Los Angeles', 'San Francisco'); B. SELECT city_address, COUNT(*) FROM customers

WHERE city_address IN ('Los Angeles', 'San Francisco') GROUP BY city_address;

C. SELECT city_address, COUNT(customer_id) FROM customers

WHERE city_address IN ('Los Angeles', 'San Francisco') GROUP BY city_address, customer_id;

D. SELECT city_address, COUNT(customer_id) FROM customers

GROUP BY city_address IN ('Los Angeles', 'San Francisco');

Q: 21 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

Q: 22 The EMP table contains these columns: LAST_NAME VARCHAR2 (25) SALARY NUMBER (6,2)

DEPARTMENT_ID NUMBER (6)

You need to display the employees who have not been assigned to any department. You write the SELECT statement:

SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP

WHERE DEPARTMENT_ID = NULL; What is true about this SQL statement ?

A. The SQL statement displays the desired results.

B. The column in the WHERE clause should be changed to display the desired results.

C. The operator in the WHERE clause should be changed to display the desired results.

D. The WHERE clause should be changed to use an outer join to display the desired results.

Q: 23 Which two statements about sequences are true? (Choose two.)

A. You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a

sequence, without actually retrieving the value.

B. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without

affecting the further values to be generated from the sequence.

C. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually

retrieving the value from the sequence.

D. You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a

specified database column.

E. If a sequence starting from a value 100 and incremented by 1 is used by more than one application, then all

of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence.

F. You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum

value defined for the sequence.

Q: 24 What is true of using group functions on columns that contain NULL values?

A. Group functions on columns ignore NULL values.

B. Group functions on columns returning dates include NULL values. C. Group functions on columns returning numbers include NULL values.

D. Group functions on columns cannot be accurately used on columns that contain NULL values.

E. Group functions on columns include NULL values in calculations if you use the keyword INC_NULLS.

Q: 25 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 \FROM employees;

C. SELECT ename, salary*12 AS Annual Salary FROM employees;

D. SELECT ename, salary*12 AS INITCAP(\FROM employees

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

You want to retrieve all employees, whether or not they have matching departments in the departments table. Which query would you use? A. SELECT last_name, department_name

FROM employees NATURAL JOIN departments; B. SELECT last_name, department_name FROM employees JOIN departments ; C. SELECT last_name, department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id); D. SELECT last_name, department_name FROM employees e

RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id); E. SELECT last_name, department_name FROM employees FULL JOIN departments ON (e.department_id = d.department_id); F. SELECT last_name, department_name FROM employees e LEFT OUTER

JOIN departments d ON (e.department_id = d.department_id);

Q: 27 Which SQL statement defines a 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 FOREIGN KEY deptno REFERENCES dept 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 REFERENCES dept (deptno) FOREIGN KEY (deptno)); D. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35),

deptno NUMBER(7,2) FOREIGN KEY

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库Oracle 1Z0-007(2)在线全文阅读。