Oracle Certified Professional Exams IZ007 Questions Set 6

Q. You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMERS table has these columns:
CUST_ID NUMBER(4) NOT NULL
CUST_NAME VARCHAR2(100)
CUST_ADDRESS VARCHAR2(150)
CUST_PHONE VARCHAR2(20)
Which SELECT statement accomplishes this task?

A. SELECT* FROM customers;

B. SELECT name, address FROM customers;

C. SELECT id, name, address, phone FROM customers;

D. SELECT cust_name, cust_address FROM customers;

E. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers;

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

Q. Which two statements about creating constraints are true? (Choose two)

A. Constraint names must start with SYS_C.

B. All constraints must be defines at the column level.

C. Constraints can be created after the table is created.

D. Constraints can be created at the same time the table is created.

E. Information about constraints is found in the VIEW_CONSTRAINTS dictionary view.

Q. Examine the data in the EMPLOYEES and DEPARTMENTS tables.
EMPLOYEES
LAST_NAME DEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
Kochhar 5000
DEPARTMENTS
DEPARTMENT_ID DEPARTMENT_NAME
10 Sales
20 Marketing
30 Accounts
40 Administration
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 , 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 =

E. department_id);

F. SELECT last_name, department_name FROM employees(+) , departments ON (e.department_id = d.department_id);

G. SELECT last_name, department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);

Q. Examine the data in the EMPLOYEES table:
LAST_NAME DEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
…
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. 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.

Q. Management has asked you to calculate the value 12*salary* comossion_pct for all the employees in the EMP table. The EMP table contains these columns:
LAST NAME VARCNAR2(35) NOT NULL
SALARY NUMBER(9,2) NOT NULL
COMMISION_PCT NUMBER(4,2)
Which statement ensures that a value is displayed in the calculated columns for all employees?

A. SELECT last_name, 12*salary*commison_pct FROM emp;

B. SELECT last_name, 12*salary* (commission_pct,0) FROM emp;

C. SELECT last_name, 12*salary*(nvl(commission_pct,0)) FROM emp;

D. SELECT last_name, 12*salary*(decode(commission_pct,0)) FROM emp;

Q. The EMPLOYEE tables has these columns:
LAST_NAME VARCHAR2(35)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(5,2)
You want to display the name and annual salary multiplied by the commission_pct for all employees.
For records that have a NULL commission_pct, a zero must be displayed against the calculated column. Which SQL statement displays the desired results?

A. SELECT last_name, (salary * 12) * commission_pct FROM EMPLOYEES;

B. SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0) FROM EMPLOYEES;

C. SELECT last_name, (salary * 12) * NVL2(commission_pct, 0) FROM EMPLOYEES;

D. SELECT last_name, (salary * 12) * NVL(commission_pct, 0) FROM EMPLOYEES;

Q. Evaluate the SQL statement:
1 SELECT a.emp_name, a.sal, a.dept_id, b.maxsal
2 FROM employees a,
3 (SELECT dept_id, MAX(sal) maxsal
4. FROM employees
5 GROUP BY dept_id) b
6 WHERE a.dept_id = b.dept_id
7 AND a.sal < b.maxsal;
What is the result of the statement?

A. The statement produces an error at line 1.

B. The statement produces an error at line 3.

C. The statement produces an error at line 6.

D. The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all departments that pay less salary then the maximum salary paid in the company.

E. The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.

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

Q. Which data dictionary table should you query to view the object privileges granted to the user on specific columns?

A. USER_TAB_PRIVS_MADE

B. USER_TAB_PRIVS_RECD

C. USER_COL_PRIVS_MADE

D. USER_COL_PRIVS_RECD

Q. Evaluate the SQL statement:
SELECT ROUND(TRUNC(MOD(1600,10),-1),2)
FROM dual;
What will be displayed?

A. 0

B. 1

C. 0.00

D. An error statement

Q. You want to display the titles of books that meet these criteria:
1. Purchased before January 21, 2001
2. Price is less then $500 or greater than $900
You want to sort the results by their data of purchase, starting with the most recently bought book. Which statement should you use?

A. SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < ’21-JAN-2001’ ORDER BY purchase_date;

B. SELECT book_title FROM books WHERE price IN (500,900) AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date ASC;

C. SELECT book_title FROM books WHERE price < 500 or > 900 AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date DESC;

D. SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date > ’21-JAN-2001’ ORDER BY purchase date DESC;

Q. You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables:
EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME.
The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key
DEPARTMENT_ID column of the DEPARTMENTS table.
You want to modify the view by adding a fourth column, MANAGER_ID of NUMBER data type
from the EMPLOYEES tables. How can you accomplish this task?

A. ALTER VIEW emp_dept_vu (ADD manager_id NUMBER);

B. MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);

C. ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employee e, departments d WHERE e.department_id = d.department_id;

D. MODIFY VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;

E. CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;

F. You must remove the existing view first, and then run the CREATE VIEW command with a new column list to modify a view.

Q. Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHARD2(25)
LAST_NAME VARCHARD2(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. The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(4,3)
The registrar has asked for a report on the average grade point average (GPA) for students enrolled during semesters that end in the year 2000. Which statement accomplish this?

A. SELECT AVERAGE(gpa) FROM student_grades WHERE semester_end > ’01-JAN-2000’ and semester end < 31-DEC-2000’;

B. SELECT COUNT(gpa) FROM student grades WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;

C. SELECT MIN(gpa) FROM student grades WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;

D. SELECT AVG(gpa) FROM student_grades WHERE semester_end BETWEEN ’01-JAN-2000’ and ’31.DEC.2000’;

E. SELECT SUM(gpa) FROM student grades WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;

F. SELECT MEDIAN(gpa) FROM student_grades WHERE semester end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;

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

Q. The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(4,3)
The registrar has requested a report listing the students' grade point averages (GPA), sorted from highest grade point average to lowest within each semester, starting from the earliest date. Which statement accomplishes this?

A. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end DESC, gpa DESC;

B. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end ASC, gpa ASC;

C. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end, gpa DESC;

D. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end DESC;

E. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;

Q. Which two statements about subqueries are true? (Choose two.)

A. A single row subquery can retrieve data from only one table.

B. A SQL query statement cannot display data from table B that is referred to in its subquery, unless table B is included in the main query's FROM clause.

C. A SQL query statement can display data from table B that is referred to in its subquery, without including table B in its own FROM clause.

D. A single row subquery can retrieve data from more than one table.

E. A single row subquery cannot be used in a condition where the LIKE operator is used for comparison.

F. A multiple-row subquery cannot be used in a condition where the LIKE operator is used for comparison.

Q. You added a PHONE_NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table. Some of the employees may not have a phone number available. Which data manipulation operation do you perform?

A. MERGE

B. INSERT

C. UPDATE

D. ADD

E. ENTER

F. You cannot enter the phone numbers for the existing employee records.