Online Tutorials & Training Materials | STechies.com
Register Login

Oracle Certified Professional Exams IZ007 Questions Set 5

|| || 3

Oracle Certified Professional Exams IZ007 Questions Set 5
Stechies

Q. Which four are correct guidelines for naming database tables? (Choose four)

A. Must begin with either a number or a letter.
B. Must be 1-30 characters long.
C. Should not be an Oracle Server reserved word.
D. Must contain only A-Z, a-z, 0-+, _, *, and #.
E. Must contain only A-Z, a-z, 0-9, _, $, and #.
F. Must begin with a letter.


Q. Examine the structure of the EMPLOYEES, DEPARTMENTS, and TAX tables.
EMPLOYEES
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2 (30)
JOB_ID VARCHAR2 (20)
SALARY NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of
the DEPARTMENTS table
DEPARTMENTS
DEPARTMENT_ID NUMBER NOT NULL, Primary Key
DEPARTMENT_NAME VARCHAR2 |30|
MGR_ID NUMBER References MGR_ID column of the
EMPLOYEES table
TAX
MIN_SALARY NUMBER
MAX_SALARY NUMBER
TAX_PERCENT NUMBER
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 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 then 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. You need to perform certain data manipulation operations through a view called EMP_DEPT_VU, which you previously created. You want to look at the definition of the view (the SELECT statement on which the view was create.) How do you obtain the definition of the view?

A. Use the DESCRIBE command in the EMP_DEPT VU view.

B. Use the DEFINE VIEW command on the EMP_DEPT VU view.

C. Use the DESCRIBE VIEW command on the EMP_DEPT VU view.

D. Query the USER_VIEWS data dictionary view to search for the EMP_DEPT_VU view.

E. Query the USER_SOURCE data dictionary view to search for the EMP_DEPT_VU view.

F. Query the USER_OBJECTS data dictionary view to search for the EMP_DEPT_VU view.


Q. Which two are character manipulation functions? (Choose two.)

A. TRIM

B. REPLACE

C. TRUNC

D. TO_DATE

E. MOD

F. CASE


Q. You need to calculate the total of all salaries in the accounting department. Which group function should you use?

A. MAX

B. MIN

C. SUM

D. COUNT

E. TOTAL

F. LARGEST


Q. Which clause should you use to exclude group results?

A. WHERE

B. HAVING

C. RESTRICT

D. GROUP BY

E. ORDER BY


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


Q. 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 2000 bytes. The column can currently hold 1000 bytes per value. The table contains 20000 rows. Which statement is valid?

A. ALTER TABLE commercials MODIFY (description CHAR2(2000));

B. ALTER TABLE commercials CHANGE (description CHAR2(2000));

C. ALTER TABLE commercials CHANGE (description VARCHAR2(2000));

D. ALTER TABLE commercials MODIFY (description VARCHAR2(2000));

E. You cannot increase the size of a column if the table has rows.


Q. In which scenario would TOP N analysis be the best solution?

A. You want to identify the most senior employee in the company.

B. You want to find the manager supervising the largest number of employees.

C. You want to identify the person who makes the highest salary for all employees.

D. You want to rank the top three sales representatives who have sold the maximum number of products.


Q. Which substitution variable would you use if you want to reuse the variable without prompting the user each time?

A. &

B. ACCEPT

C. PROMPT

D. &&


Q. 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. Which statement describes the ROWID data type?

A. Binary data up to 4 gigabytes.

B. Character data up to 4 gigabytes.

C. Raw binary data of variable length up to 2 gigabytes.

D. Binary data stored in an external file, up to 4 gigabytes.

E. A hexadecimal string representing the unique address of a row in its table.


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


Q. Examine the structure of the EMPLOYEES table:
Column name Data type Remarks
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
LAST_NAME VARCNAR2(30)
FIRST_NAME VARCNAR2(30)
JOB_ID NUMBER
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER
You need to create an index called NAME_IDX on the first name and last name fields of the
EMPLOYEES table. Which SQL statement would you use to perform this task?

A. CREATE INDEX NAME_IDX (first_name, last_name);

B. CREATE INDEX NAME_IDX (first_name AND last_name);

C. CREATE INDEX NAME_IDX ON (first_name, last_name);

D. CREATE INDEX NAME_IDX ON employees (first_name AND last_name);

E. CREATE INDEX NAME_IDX ON employees(first_name, last_name);

F. CREATE INDEX NAME_IDX FOR employees(first_name, last_name);


Q. Examine the structure of the STUDENTS table:
STUDENT_ID NUMBER NOT NULL, Primary Key
STUDENT_NAME VARCHAR2(30)
COURSE_ID VARCHAR2(10) NOT NULL
MARKS NUMBER
START_DATE DATE
FINISH_DATE DATE
You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.
Which SQL statement accomplishes this task?

A. SELECT student_ id, marks, ROWNUM "Rank" FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks DESC;

B. SELECT student_id, marks, ROWID "Rank" FROM students WHERE ROWID <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks;

C. SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks DESC);

D. SELECT student_id, marks, ROWNUM "Rank: FROM (SELECT student_id, marks FROM students ORDER BY marks) WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL';


Q. Examine the data of the EMPLOYEES table.
EMPLOYEES (EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to
the EMPLOYEE_ID)
EMPLOYEE_ID EMP_NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 HR_MGR 5000
106 Bryan 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA_DIR 6500
Evaluate this SQL statement:
SELECT e.employee_id "Emp_id", e.emp_name "Employee", e.salary,

A. employee_id "Mgr_id", m.emp_name "Manager" FROM employees e, employees m WHERE e.mgr_id = m.employee_id AND e.salary > 4000; What is its output?

B. EMP_id EMPLOYEE SALARY Mgr_id Manager ------- ---------- --------- ------------- -------------- 110 Bob 8000 Bob 120 Ravi 6500 110 Ravi 108 Jennifer 6500 110 Jennifer 103 Chris 4200 120 Chris 105 Diana 5000 108 Diana

C. EMP_id EMPLOYEE SALARY Mgr_id Manager ------- ---------- --------- ------------- -------------- 120 Ravi 6500 110 Bob 108 Jennifer 6500 110 Bob 103 Chris 4200 120 Ravi 105 Diana 5000 108 Jennifer

D. EMP_id EMPLOYEE SALARY Mgr_id Manager ------- ---------- --------- ------------- -------------- 110 Bob 8000 120 Ravi 6500 110 Bob 108 Jennifer 6500 110 Bob 103 Chris 4200 120 Ravi 105 Diana 5000 108 Jennifer D EMP_id EMPLOYEE SALARY Mgr_id Manager ------- ---------- --------- ------------- -------------- 110 Bob 8000 110 Bob 120 Ravi 6500 120 Ravi 108 Jennifer 6500 108 Jennifer 103 Chris 4200 103 Chris 105 Diana 5000 105 Dina

E. The SQL statement produces an error.


Q. Examine the structure of the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables.

EMPLOYEES
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2 (30)
JOB_ID VARCHAR2 (20)
SALARY NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of the
DEPARTMENTS table
DEPARTMENTS
DEPARTMENT_ID NUMBER NOT NULL, Primary Key
DEPARTMENT_NAME VARCHAR2
(30)
MGR_ID NUMBER References NGR_ID column of the EMPLOYEES
table
LOCATION_ID NUMBER Foreign key to LOCATION_ID column of the
LOCATIONS table
LOCATIONS
LOCATION_ID NUMBER NOT NULL, Primary Key
CITY VARCHAR2 |30)
Which two SQL statements produce the name, department name, and the city of all the employees
who earn more then 10000? (Choose two)

A. SELECT emp_name, department_name, city FROM employees e JOIN departments d USING (department_id) JOIN locations 1 USING (location_id) WHERE salary > 10000;

B. SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 JOIN ON(e.department_id = d.department id) AND (d.location_id =1.location_id) AND salary > 10000;

C. SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 WHERE salary > 10000;

D. SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 WHERE e.department_id = d.department_id AND d.location_id = 1.location_id AND salary > 10000;

E. SELECT emp_name, department_name, city FROM employees e NATURAL JOIN departments, locations WHERE salary > 10000;


Q. For which two constraints does the Oracle Server implicitly create a unique index? (Choose two.)

A. NOT NULL

B. PRIMARY KEY

C. FOREIGN KEY

D. CHECK

E. UNIQUE


Q. Examine the SQL statements that creates ORDERS table:
CREATE TABLE orders
(SER_NO NUMBER UNIQUE,
ORDER_ID NUMBER,
ORDER_DATE DATE NOT NULL
STATUS VARCHARD2(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


Q. Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
EMPLOYEES
Column name Data type Remarks
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2 (30)
JOB_ID VARCHAR2 (20)
SALARY NUMBER
MGR_ID NUMBER References EMPLOYEE_ID COLUMN
DEPARTMENT ID NUMBER Foreign key to DEPARTMENT ID
column of the DEPARTMENTS table
DEPARTMENTS
Column name Data type Remarks
DEPARTMENT_ID NUMBER NOT NULL, Primary Key
DEPARTMENT_NAME VARCHAR2(30)
MGR_ID NUMBER References MGR_ID column of the
EMPLOYEES table
Evaluate this SQL statement:
SELECT employee_id, e.department_id, department_name,
salary
FROM employees e, departments d
WHERE e.department_id = d.department_id;
Which SQL statement is equivalent to the above SQL statement?

A. SELECT employee_id, department_id, department_name,salary FROM employees WHERE department_id IN (SELECT department_id FROM departments);

B. SELECT employee_id, department_id, department_name, salary FROM employees NATURAL JOIN departments;

C. SELECT employee_id, d.department_id, department_name, salary FROM employees e JOIN departments d ON e.department_id = d.department_id;

D. SELECT employee_id, department_id, department_name, Salary FROM employees JOIN departments USING (e.department_id, d.department_id);

 

 

Continue.....

 

 


 

 

 


 

 

 


Comments

  • 12 May 2008 9:04 am
    please give me the answers
  • 12 Mar 2009 9:45 am
    thanks a lot
  • 05 Jul 2011 11:41 am
    please send answers for respective question paper

    niyatiragrawal@gmail.com

Related Articles