Oracle 1Z0-007(3)

CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

Q: 28 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));

Q: 29 Which SQL statement accepts user input for the columns to be displayed, the table name, and the WHERE condition? A. SELECT &1, \FROM &3

WHERE last_name = '&4'; B. SELECT &1, '&2' FROM &3

WHERE '&last_name = '&4''; C. SELECT &1, &2 FROM &3

WHERE last_name = '&4';

D. SELECT &1, '&2' FROM EMP

WHERE last_name = '&4';

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

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

Q: 32 Which SELECT statement should you use to extract the year from the system date and display it in the format \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;

Q: 33 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 COUNT(*)

FROM employees

WHERE last_name='Smith'; B. SELECT COUNT(dept_id) FROM employees

WHERE last_name='Smith';

C. SELECT DISTINCT(COUNT(dept_id)) FROM employees

WHERE last_name='Smith';

D. SELECT COUNT(DISTINCT dept_id) FROM employees

WHERE last_name='Smith'; E. SELECT UNIQUE(dept_id) FROM employees

WHERE last_name='Smith';

Q: 34 Examine the description of the MARKS table: STD_ID NUMBER(4)

STUDENT_NAME VARCHAR2(30) SUBJ1 NUMBER(3) SUBJ2 NUMBER(3) SUBJ3 NUMBER(3)

SUBJ1, SUBJ2, and SUBJ3 indicate the marks (grades) obtained by a student in the three subjects.

Which two statements are valid? (Choose two.) A. SELECT SUM(subj1, subj2, subj3) FROM marks;

B. SELECT SUM(subj1 + subj2 + subj3) FROM marks;

C. SELECT SUM(subj1), SUM(subj2), SUM(subj3) FROM marks;

D. SELECT MAX(subj1, subj2, subj3) FROM marks;

E. SELECT MINIMUM(subj1) FROM marks;

F. SELECT COUNT(std_id) FROM marks

WHERE subj1 >= AVG(subj1);

Q: 35 Which are iSQL*Plus commands? (Choose all that apply.) A. INSERT B. UPDATE C. SELECT D. DESCRIBE E. DELETE F. RENAME

Q: 36 Which three SELECT statements display 2000 in the format \

(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;

Q: 37 What does the FORCE option for creating a view do? A. creates a view with constraints

B. creates a view even if the underlying parent table has constraints C. creates a view in another schema even if you don't have privileges D. creates a view regardless of whether or not the base tables exist

Q: 38 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;

Q: 39 What is true about joining tables through an equijoin? A. You can join a maximum of two tables through an equijoin. B. You can join a maximum of two columns through an equijoin.

C. You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.

D. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.

E. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.

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

Q: 41 Which two statements complete a transaction? (Choose two.) A. DELETE employees; B. DESCRIBE employees;

C. ROLLBACK TO SAVEPOINT C;

D. GRANT SELECT ON employees TO SCOTT; E. ALTER TABLE employees SET UNUSED COLUMN sal; F. SELECT MAX(sal) FROM employees

WHERE department_id = 20;

Q: 42 Which SQL statement returns a numeric value? A. SELECT ADD_MONTHS(MAX(hire_Date), 6)

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