Online Tutorials & Training Materials | STechies.com
Register Login

Oracle Certified Professional Exams IZ007 Questions Set 37

|| || 1

Oracle Certified Professional Exams IZ007 Questions Set 37
Stechies

Q. The CUSTOMERS table has these columns:

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_ADDRESSVARCHAR2(50)
COUNTRY_ADDRESS  VARCHAR2(50)
POSTE_CODE  VARCHAR2(12) 
CUSTOMER_PHONE  VARCHAR2(20)

THE CUSTOMER_ID column is the primary key for the table  which two statements find the number of customer? (Choose two.)

A. SELECT TOTAL (*)
FROM customers;

B. SELECT COUNT (*)
FROM customers;

C. SELECT TOTAL (customer_id)
FROM customer;

D. SELECT COUNT(costomer_id)
FROM customer;

E. SELECT COUNT(customers)
FROM customers;

F. SELECT TOTAL (customer_name)
FROM customers;

Anser : BD


Q. In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed statement?

A. immediately after the SELECT clause

B. before the WHERE clause

C. before the FROM clause

D. after the ORDER BY clause

E. after the WHERE clause

Answer : E


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.

Answer BD 


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

A. NOT NULL

B. PRIMARY KEY

C. FOREIGN KEY

D. CHECK

E. UNIQUE

Answer: BE


Q. Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID      NUMBER primary Key
FIRST_NAME         VARCHAR2(25)
LAST_NAME           VARCHAR2(25)
HIRE_DATE            DATE

You issue these statements:
CREATE table new_emp (employee_id NUMBER, name VARCHAR2 (30));
INSERT INTO new_emp SELECT employee_id, last_name from employees;
Savepoint s2;
Delete from new_emp;
Rolback to s2;
Delete from new_emp where employee_id = 180;
UPDATE new_emp set name = ‘James’;
Rolback to s2;
UPDATE new_emp set name = ‘James’ WHERE employee_id = 180;
Rollback;
At the end of this transaction, what is true?

A. You have no rows in the table.

B. You have an employee with the name of James

C. You cannot roll back to the same savepoint more than once.

D. Your last update fails to update any rows because employee ID 180 was already deleted.

Answer : A
 

Q. Which / SQL* Plus feature can be used to replace values in the where clause?

A. Substitution variables

B. replacement variables

C. prompt variables

D. instead-of variables

E. This feature cannot be implemented through / SQL*Plus

Answer : A

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

Answer : A


Q. Examine the structure of the EMPLOYEES table:

EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME                 VARCHAR2(25)
LAST_NAME                  VARCHAR2(25)
DEPARTMENT_ID         NUMBER
SALARY                          NUMBER

 
What is the correct syntax for an inline view?

A. SELECT  a last_name, a salary, a department_id, b.maxsal FROM employees a, (SELECT department_id, max(salary)maxsal FROM employees GROUP BY department_id)b WHERE a department_id = department-id AND a_salary

B. SELECT a. last name, a salary, a. department_id FROM employees a WHERE  a. department_id IN (SELECT department_id FROM employees b GROUP BY department_id having salary = SELECT max(salary) from employees

C. SELECT  a last_name,  a salary, a.department_id FROM employees a WHERE a salary = SELECT max(salary) FROM employees b WHERE a department_id = department_id);

D. SELECT  a last_name, a salary, a.department_id FROM employees a WHERE (a department_id, a salary) IN (SELECT department_id, a salary) IN (SELECT department_id max(salary) FROM employees b GROUP BY department_id ORDER BY department_id);

Answer : A


Q. Examine the structure of the EMPLOYEES table:
       
EMPLOYEE_ID NUMBER NOT NULL
EMP_ID         VARCHAR2(30)
JOB_ID          VARCHAR2(20) DEFAULT ‘SA_REP’
SAL                 NUMBER
COMM_PCT  NUMBER
MGR_ID         NUMBER
DEPARTMENT_ID NUMBER

you need to update the records of emloyees 103 and 115. The UPDATE statement you specify should update the rows with the values specified below:
JOB_ID Default value specified for this column definition SAL maximum salary earned for the_job ID SA_REP COMM_PCT Default value is specified for the column, the value should be NULL
DEPARTMENT_ID: Supplied by the user during run time through substitution variable
which UPDATE statement meets the requirements?

A. UPDATE employees SET job_id=DEFAULT AND Sal=(SELECT MAX(sal) FROM emoployees WHERE job_id='SA_REP' AND comm_pet=DEFALUT AND department_id =&did WHERE employee_id IN (103, 115),

B. UPDATE employees SET job_id = DEFAULT AND Sal = MAX(sal) AND comm_pct = DEFAULT OR NULL AND department _id = & did WHERE employee_id IN (103,115) AND  ob_id = 'SA_REP'

C. UPDATE employees SET job_id = DEFAULT Sal = (SELECT MAX (sal) FROM employees WHERE job_id = 'SA_REP') comm_pct = DEFAULT, department _id = &did WHERE employee_id IN (103,115)

D. UPDATE emplouees SET job_id = DEFAULT sal = MAX (sal) comm_pct = DEFAULT department_id = &did WHERE employee_id IN (103,115) AND job_id = 'SA_REP'

E. UPDATE employees SET job_id = DEFAULT Sal = (SELECT MAX(sal) FROM employees WHERE job_id = 'SA_REP') comm_pct = DEFAULT OR NULL, department_id = &did WHEREemployee_id IN (103,115)

Answer: C


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

Answer:  D


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

Answer:  ACD


Q. Examine the structure of the EMPLOYEES table:
column name   data type  remarks
EMPLOYEE_ID        NUMBER  NOT NULL, primary key
LAST_NAME            VARCHAR2(30) 
FIRST_NAME           VARCHAR2(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)

Answer:  E

Q. Click the Exhibit button and examine the data from the ORDERS and CUSTOMERS tables.
ORDERS
ORD_ID  ORD_DATE    CUST_ID    ORD_TOTAL
100          12.JAN-2000   15                  10000 
101          09-MAR-2000  40                  8000
102          09-MAR-2000  35                 12500
103          15-MAR-2000  15                 12000
104          25-JUN-2000   15                  6000
105          18-JUL-2000   20                  5000
106          18-JUL-2000   35                  7000
107          21-JUL-2000   20                  6500
108          04-AUG-2000  10                  8000

CUSTOMERS
CUST_ID CUST_NAME     CITY
10              Smith               Los Angeles
15              Bob                  San Francisco
20              Martin              Chicago
25              Mary                 New York
30              Rina                  Chicago
35              Smith               New York
40              Linda                New York

Evaluate the SQL statement:
SELECT  * FROM orders WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_name = 'Smith')
What is the result when the query is executed?

A. ORD_ID ORD_DATE       CUST_ID ORD_TOTAL
    102           09-MAR-2000       35            12500
    106           18-JUL-2000        35             7000
    108            04-AUG-2000      10             8000

B. ORD_ID ORD_DATE  CUST_ID ORD_TOTAL
    102         09-MAR-2000     35            12500
    106         18-JUL-2000      35             7000

C. ORD_ID ORD_DATE  CUST_ID ORD_TOTAL
   108         04-AUG-2000         10         8000

D. The query fails because the subquery returns more than one row.

E.  The query fails because the outer query and the inner query are using different tables.
 

Answer:  D


Q. Evaluate this SQL statement:
SELECT e.EMPLOYEE_ID,e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME FROM EMP e.DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID; In the statement, which capabilities of a SELECT statement are performed?

A. selection, projection, join

B. difference, projection, join

C. intersection, projection, join

D. difference, projection, product

Answer:  A


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

Answer: D


Q. Which syntax turns an existing constraint on?

A.  ALTER TABLE table_name
ENABLE constrain_name

B.  ALTER TABLE table_name
STATUS = ENABLE CONSTRAINT constrain_name

C.  ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name

D.  ALTER TABLE table_name
STATUS ENABLE CONSTRAINT constraint_name

E.  ALTER TABLE table_name
TURN ON CONSTRAINT costrant_name

F.  ALTER TABLE table_name
TURN ON CONSTRAINT constraint_name
 

Answer:  C

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.

Answer: AB


Q. The database adminsrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL  schema, because many users frequentlyuse this table.
As a user of the database, you created a table called HR in your chema. What happens when you execute this query?
SELECT *
FROM HR;

A. you obtain the results retrieved from the public synonym HR created by the database administrator

B. you obtain the results retrieved form the HR table that belongs to your schema.

C. you get an error message because you cannot retrieve from a table that has te same ame as a public synonym

D. you obtain the results retrieved from both the public synonym HR and the HR table that belongs to your shema, as a Cartesian product.

E. You obtain the results retrieved form both the public synonym HR and the HR table that belongs to your shema, as a FULL JOIN.
 

Answer:  B

Q.You need to give the MANAGER role the ability to select from insert into and modify existing rows in the STUDENT_GRADES table.  Anyone given this MANAGER role should be able to pass those privileges on to others.  Which statement accomplishes this.

A. GRANT select, insert, update
ON student_grades
TO manager;

B. GRANT  select, insert, updateON student_grades TO ROLE  manager

C. GRANT select, insert, modity ON  student_grades TO manager WITH GRANT OPTION;

D. GRANT select, insert, updateON student_grades TO manager WITH GRANT OPTION

E. GRANT  select, insert, update ON student_grades TO ROLE manager WITH GRANT OPTION;

F. GRANT select, insert, modify ON  student_grades TO ROLE manager WITH GRANT OPTION

Answer:  D

Q. Click the Exhibit button and examine the data in the EMPLOYEES and DEPARTMENTS tables.
EMPLOYEES
LAST_NAME  DEPARTMENT_ID  SALARY
Get z                   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 matchig departments inthe 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.departement_id);

D. SELECT last_name, department_name FROM emplouees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);

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

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

Answer:  F

 

 

Continue.....

 


Comments

  • 25 Nov 2009 10:01 pm
    IT IS BETTER TO GET RAPED THAN STUDY ORACLE

Related Articles