Online Tutorials & Training Materials | STechies.com
Register Login

Oracle Certified Professional Exams IZ007 Questions Set 27

|| || 0

Oracle Certified Professional Exams IZ007 Questions Set 27
Stechies

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

Ans: E

Explanation:

The GROUP BY clause can be place only after the WHERE clause, or after FROM clause if there is no the WHERE clause in the statement.
Incorrect Answers:

A: It is not possible to place the GROUP BY clause immediately after the SELECT clause.

B: It is not possible to place the GROUP BY clause before the WHERE clause, it can be done only after it.

C: It is not possible to place the GROUP BY clause before the FROM clause.

D: It is not possible to place the GROUP BY clause after the ORDER BY clause.


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.

Ans: C, D


Explanation:

It is possible to mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns. Also it is acceptable to pass column names, expressions, constraints, or other functions as parameters to an aggregate function.
Incorrect Answers:

A: You cannot use aggregate functions in any clause of a SELECT statement. For example, they cannot be used with a WHEN statement.

B: It is not possible to use aggregate functions in the WHERE clause of a SELECT statement. But they can be used with a HAVING clause used after the GROUP BY clause, for example.

E: You don’t need to group the whole table as one single group.

F: It is possible to group more than one column while using aggregate functions.


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

Ans: B, E

Explanation:

Indexes are created automatically by Oracle to support integrity constraints that enforce uniqueness. The two types of integrity constraints that enforce uniqueness are PRIMARY KEY and UNIQUE constraints. When the primary key or UNIQUE constraint is declared, a unique index to support the column’s uniqueness is also created, and all values in all columns that were defined as part of the primary key or UNIQUE constraint are placed into the index.
Incorrect Answers:

A: Oracle will not implicitly create an unique index for the NOT NULL constraint.

C: Oracle will not implicitly create an unique index for the FOREIGN KEY constraint.

D: Oracle will not implicitly create an unique index for the FOREIGN KEY constraint.


Q.Check the Exhibit button to examine the structures of the Employees and TAx tables:



EMPLOYEES:


EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR(30)  
JOB_ID VARCHAR2(20)  
SALARY NUMBER  
MGR_ID NUMBER References EMPLOYEE_TO column
DEPARTMENT_ID NUMBER Foreign Employee_ID column of the DEPARTMENT table


     



TAX:


MIN_SALARY NUMBER  
MAX_SALARY NUMBER  
TAX_PERCENT NUMBER Percentage tax for given salary range

You need find the percentage tax applicable for each employee. Which SQL statement would you use?

A. SELECT employee_id, salary, tax_percent FROM employees e, tax t

  WHERE e.salary BETWEEN t.min_salary AND t.max_salary;

B. SELECT employee_id, salary, tax_percent FROM employees e, tax t

  WHERE e.salary > t.min_salary, tax_percent;

C. SELECT employee_id, salary, tax_percent FROM employees e, tax t

  WHERE MIN(e.salary) = t.min_salary AND MAX(e.salary) = t.max_salary;

D. You cannot find the information because there is no common column between the two tables. 

Ans: A

Explanation:

You can find the percentage tax applicable for each employee by using SQL statement in answer A.
Incorrect Answers:

B: Syntax “WHERE e.salary > t.min_salary, tax_percent” is incorrect.

C: Functions, like MIN() and MAX(), cannot be used in the WHERE clause.

D: The SQL statement from the answer A will provide requested information.


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;

ROLLBACK to s2;

DELETE from new_emp where employee_id = 180;

UPDATE new_emp set name = 'James';

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

Ans: A

Explanation:

At the end of this transaction you will not have rows in the table.
Incorrect Answers:

B: All transactions will be roll backed, so it will be no rows in the table.

C: It is possible to roll back to the same savepoint more than once.

D: Your last update will not fail because there is employee with ID 180 in the table and transactions have been rolled back only to the savepoint s2 before issue this update.


Q. Which iSQL*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 iSQL*Plus

Ans: A

Explanation:

Lexical substitution variables can be used to replace values in the WHERE clause.
Incorrect Answers:

B: There is no replacement variables iSQL*Plus feature in Oracle.

C: There is no prompt variables iSQL*Plus feature in Oracle.

D: There is no instead-of variables iSQL*Plus feature in Oracle.

E: This feature is implemented in the iSQL*Plus with lexical substitution variables.


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

Ans: A

Explanation:

Result will be 0. MOD(x,y) function calculates the modulus of x, defined in long division as the integer remainder when x is divided by y until no further whole number can be produced. TRUNC() function truncates x to the decimal precision of y. ROUND(x,y) rounds x to the decimal precision of y.
Incorrect Answers:

B: Result will be 0, not 1.

C: Result will be 0, not 0.00 because MOD(1600,10) return 0 and all other functions (TRUNC and ROUND) return 0 also.

D: There is no error in this statement.


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 = b.department_id AND a.salary < b.maxsal;
 

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, MAX (salary) FROM employees b

      GROUP BY department_id ORDER BY department_id);
 

Ans: A

Explanation:

This SQL statement shows correct syntax to build inline views. You must enclose the query text for the inline view in parentheses and also give a label for the inline view so that columns in it can be referenced later. In answer A inline view is marked as B.
Incorrect Answers:

B: This SQL statement will fail because it is not correct syntax for inline views. There is no a label for this inline view also.

C: This SQL statement will fail because it is not correct syntax for inline views. There is no a label for this inline view also.

D: This SQL statement will fail because it is not correct syntax for inline views. There is no a label for this inline view also.


Q. Examine the structure of the EMPLOYEES table:
       


EMPLOYEE_ID NUMBER NOT NULL
EMP_ID VARCHAR2(30)  
JOB_ID VARCHAR2(20) DEFAULT'S_REP
SAL NUMBER  
COMM_PCT NUMBER  
MGR_ID NUMBER  
DEPARTMENT_ID NUMBER  


You need to update the recores of comloyees 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 = DEFAULT 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 job_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 employees 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

  WHERE employee_id IN (103,115);
 

Ans: C

Explanation:

This UPDATE statement is correct to receive desired results. Correct syntax is UPDATE table_name SET column_name1 = value, column_name2 = value2. You can also use the DEFAULT keyword to set a column value to its specified default value in update statements as well.
Incorrect Answers:

A: You cannot use syntax like UPDATE table_name SET column_name1 = value AND column_name2 = value2. Correct syntax is UPDATE table_name SET column_name1 = value, column_name2 = value2.

B: You cannot use syntax like UPDATE table_name SET column_name1 = value AND column_name2 = value2. Correct syntax is UPDATE table_name SET column_name1 = value, column_name2 = value2.

D: Group function is not allowed to use in the SET clause of the UPDATE command.

E: You cannot set column to value DEFAULT OR NULL: this is wrong syntax to use.


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

Ans: D

Explanation:

The USER_COL_PRIVS data dictionary view will show the object privileges granted to the user on specific columns.
Incorrect Answers:

A: There is no USER_TAB_PRIVS_MADE view in Oracle.

B: The USER_TAB_PRIVS data dictionary view is used to show the object privileges granted to the user on the tables, not specific columns.

C: There is no USER_COL_PRIVS_MADE view in Oracle.


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

Ans: A, C, D

Explanation:

TIMESTAMP, INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH can be used to specify column definition.
Incorrect Answers:

B: The INTERVAL MONTH TO DAY data type cannot be used when specifying column definitions there are only INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH data types.

E: The TIMESTAMP WITH DATABASE TIMEZONE data type cannot be used when specifying column definitions, because there are only TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types.


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)

Ans: E

Explanation:

Answer E provides correct syntax to create index: CREATE INDEX index_name ON table_name(list of columns).
Incorrect Answers:

A: You need to use keyword ON also to create index.

B: You cannot use keyword AND to build a list of columns for index. Also this statement is missing the table name on which the index is creating.

C: This statement is missing the table name on which the index is creating.

D: You cannot use keyword AND to build a list of columns for index.

F: You cannot use keyword FOR to create an index.


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

Ans: D

Explanation:

The query fails because the sub-query returns more than one row: it is possible situation when there are some customers with name Smith.
Incorrect Answers:

A: The query fails because the sub-query returns more than one row.

B: The query fails because the sub-query returns more than one row.

C: The query fails because the sub-query returns more than one row.

E: The query fails because the sub-query returns more than one row, not because the outer query and the inner query are using different tables.


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

Ans: A

Explanation:

Selection, projection and join capabilities of a SELECT statement are performed in this view.
Incorrect Answers:

B: Selection is performed in this query, not difference. There is no capability with name difference for a SELECT statement exists.

C: There is no intersection in this SELECT statement used.

D: There is no intersection in this SELECT statement used.

E: There is no difference or product capabilities exist for a SELECT statement.


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

Ans: D

Explanation:

This statement provides correct syntax to add a primary key on the STUDENT_ID column of the STUDENT table.
Incorrect Answers:

A: This ALTER TABLE statement is missing CONSTRAINT keyword and the name of the constraint.

B: This ALTER TABLE statement is missing the name of the constraint.

C: It’s incorrect syntax in the ALTER TABLE command: STUDENT_ID must be used with brackets.

E: We need to add constraint, not to modify existing one. Usage of the MODIFY keyword is incorrect in this case.


Q. Which syntax turns an existing constraint on?

A. ALTER TABLE table_name ENABLE constraint_name

B. ALTER TABLE table_name STATUS = ENABLE CONSTRAINT constraint_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 constraint_name

Ans: C

Explanation:

ALTER TABLE statement with ENABLE CONSTRAINT keywords is correct answer to enable an existing constraint.
Incorrect Answers:

A: This statement is missing CONSTRAINT keyword.

B: “STATUS =” is incorrect syntax to enable constraint for the table.

D: There is no STATUS keyword in the command to enable constraint.

E: There is no TURN ON keywords in the command to enable constraint.

F: There is no TURN ON keywords in the command to enable constraint.


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.

Ans: A, B

Explanation:

A view can be created as read only object. However, it is possible to change data in the underlying table(s) with some restrictions.A view also can be created as a join on two or more tables. This type of view is called complex view. Complex views provide complicated data models where many base tables are drawn together into one virtual table.
Incorrect Answers:

C: Query operations containing ORDER BY clause are alsopermitted, so long as the ORDER BY clause appearsoutside the parentheses. Thefollowing is an example of what I mean: CREATE VIEW my_view AS (SELECT * FROM emp) ORDER BY empno.

D: A view can be created with a GROUP BY clause in the SELECT statement.

E: It is not required to have aliases defined for the column names in the SELECT statement.


Q. The database administrator of your company created a public synonym called HR for the HUMAN_RESOUURCES table of the GENERAL schema, because many users frequently use this table. As a user of the database, you created a table called HR in your schema. 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 the same name as a public synonym.

D. You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a cartesian product.

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

Ans: B

Explanation:

By executing this query you will extract data from the HR table in your own schema, it will not work with HR synonym for the HUMAN_RESOURCES table of the GENERAL schema.
Incorrect Answers:

A: The results will be retrieved from the table in your own schema, not from the GENERAL schema, using synonym HR.

C: There is no error: data from the table in your own schema will be retrieved by this query.

D: This query will not generate Cartesian product from both tables.

E: This query will not retrieve data from both tables as a FULL JOIN.


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, update ON student_grades TO ROLE manager;

C. GRANT select, insert, modify ON student_grades TO ROLE manager;

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

D. GRANT select, insert, update ON 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;

Ans: D

Explanation:

This answer provides correct syntax of GRANT command to give the MANAGER role all asked privileges. Clause WITH GRANT OPTION will allow this role to pass those privileges on to others.
Incorrect Answers:

A: This statement would be correct if it included WITH GRANT OPTION clause to allow this role to pass those privileges on to others.

B: This statement uses incorrect clause TO ROLE.

C: There is no option with name MODIFY in the GRANT command.

E: This statement uses incorrect clause TO ROLE.

F: There is no option with name MODIFY in the GRANT command. And this statement also uses incorrect clause TO ROLE.


Q. What is necessary for your query on an existing view to execute successfully?

A. The underlying tables must have data.

B. You need SELECT privileges on the view.

C. The underlying tables must be in the same schema.

D. You need SELECT privileges only on the underlying tables.

Ans: B

Explanation:

To query an existing view you need to have SELECT privileges on the view.
Incorrect Answers:

A: The underlying tables can be without data: view will work correctly in that case.

C: The underlying tables can be in different user schema.

D: You need SELECT privileges not only on the underlying tables, but on the view also.

 

 

Continue.....


Related Articles