Oracle 1Z0-007(4)

FROM EMP;

B. SELECT ROUND(hire_date) FROM EMP;

C. SELECT sysdate-hire_date FROM EMP;

D. SELECT TO_NUMBER(hire_date + 7) FROM EMP;

Q: 43 Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

EMPLOYEES __________

EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE NEW_EMPLOYEES _______________

EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60)

Which DELETE statement is valid? A. DELETE FROM employees

WHERE employee_id = (SELECT employee_id FROM employees);

B. DELETE * FROM employees

WHERE employee_id = (SELECT employee_id FROM new_employees);

C. DELETE FROM employees

WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name ='Carrey');

D. DELETE * FROM employees

WHERE employee_id IN (SELECT employee_id FROM new_employees

WHERE last_name ='Carrey');

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

Q: 45 In which two cases would you use an outer join? (Choose two.) A. The tables being joined have NOT NULL columns. B. The tables being joined have only matched data. C. The columns being joined have NULL values. D. The tables being joined have only unmatched data.

E. The tables being joined have both matched and unmatched data. F. Only when the tables have a primary key-foreign key relationship.

Q: 46 Which two statements are true about WHERE and HAVING clauses? (Choose two.)

A. A WHERE clause can be used to restrict both rows and groups. B. A WHERE clause can be used to restrict rows only.

C. A HAVING clause can be used to restrict both rows and groups. D. A HAVING clause can be used to restrict groups only.

E. A WHERE clause CANNOT be used in a query if the query uses a HAVING clause.

F. A HAVING clause CANNOT be used in subqueries. Q: 47 Examine the structure of the EMPLOYEES table: Column name Data type Remarks

EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30)

JOB_ID VARCHAR2(20) NOT NULL SAL NUMBER

MGR_ID NUMBER References EMPLOYEE_ID column

DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of the DEPARTMENTS table

You need to create a view called EMP_VU that allows the users to insert rows through the view.

Which SQL statement, when used to create the EMP_VU view, allows the users to insert rows?

A. CREATE VIEW emp_vu AS SELECT employee_id, emp_name, department_id FROM employees

WHERE mgr_id IN (102, 120); B. CREATE VIEW emp_vu AS

SELECT employee_id, emp_name, job_id, department_id FROM employees

WHERE mgr_id IN (102, 120); C. CREATE VIEW emp_vu AS

SELECT department_id, SUM(sal) TOTALSAL FROM employees

WHERE mgr_id IN (102, 120) GROUP BY department_id; D. CREATE VIEW emp_vu AS

SELECT employee_id, emp_name, job_id, DISTINCT department_id FROM employees;

Q: 48 Evaluate this SQL statement:

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.

Q: 49 Which four are attributes of single row functions? (Choose four.)

A. cannot be nested

B. manipulate data items C. act on each row returned D. return one result per row

E. accept only one argument and return only one value

F. accept arguments which can be a column or an expression

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

Q: 51 Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

EMPLOYEES ___________

EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE NEW_EMPLOYEES ________________

EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60)

Which UPDATE statement is valid?

A. UPDATE new_employees SET name = (SELECT last_name|| first_name

FROM employees WHERE employee_id =180)

WHERE employee_id =180;

B. UPDATE new_employees SET name = (SELECT last_name||first_name FROM employees )

WHERE employee_id =180;

C. UPDATE new_employees SET name = (SELECT last_name || first_name

FROM employees WHERE employee_id =180)

WHERE employee_id =(SELECT employee_id FROM new_employees);

D. UPDATE new_employees SET name = (SELECT last_name||

first_name

FROM employees

WHERE employee_id = (SELECT employee_id FROM new_employees)) WHERE employee_id =180;

Q: 52 Which two statements about subqueries are true? (Choose two.) A. A subquery should retrieve only one row. B. A subquery can retrieve zero or more rows.

C. A subquery can be used only in SQL query statements. D. Subqueries CANNOT be nested by more than two levels.

E. A subquery CANNOT be used in an SQL query statement that uses group functions.

F. When a subquery is used with an inequality comparison operator in the outer SQL statement, the column

list in the SELECT clause of the subquery should contain only one column.

Q: 53 You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. Which statement accomplishes this task? A. ALTER TABLE students

ADD PRIMARY KEY student_id; B. ALTER TABLE students

ADD CONSTRAINT PRIMARY KEY (student_id); C. ALTER TABLE students

ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id; D. ALTER TABLE students

ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id); E. ALTER TABLE students

MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id); Q: 54 You need to change the definition of an existing table. The

COMMERCIALS table needs its DESCRIPTION column changed to hold varying length characters up

to 1000 bytes. The column can currently hold 500 bytes per value. The table contains 20000 rows. Which statement is valid?

A. ALTER TABLE commercials

MODIFY (description CHAR2(1000)); B. ALTER TABLE commercials

CHANGE (description CHAR2(1000)); C. ALTER TABLE commercials

CHANGE (description VARCHAR2(1000)); D. ALTER TABLE commercials

MODIFY (description VARCHAR2(1000));

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