Online Tutorials & Training Materials | STechies.com
Register Login

Oracle Certified Professional Exams IZ007 Questions Set 22

|| || 0

Oracle Certified Professional Exams IZ007 Questions Set 22
Stechies

Q. in self join, because the same table is used twice for comparison, an alias name differentiates the two copies of the table.

a. true
b. false


Q. All but one of these functions does not ignore a null value

a. count(*)

b. sum

c. avg

d. min

e. max


Q. select cCity, vFirstname, vLastname from Candidate group by cCity, vLastname; an error message “not a group by expression” was displayed after the query was executed. Why?

a. all columns specified in the select list are not specified in the group by clause

b. City column does not exist in the table

c. group by clauses are not allowed in a select statement

d. none of the above


Q. in a group by clause, error message will be displayed when?

a. used with having clause

b. used in a select statement

c. column alias is used

d. all columns in the table are queried


Q. All is true about subquery except

a. it is a group of selected statements within another select statement

b. table aliases are not allowed in the subqueries

c. it can be placed in an insert, update and delete statement

d. it must be enclosed within a parenthesis


Q. Select nMonthlysalary from Monthlysalary where count (cEmployeecode) The above query displayed an error after being executed. The error occurred at

a. the select statement

b. where clause

c. from

d. none of the above


Q. in oracle, the number of queries that can be used is determined by  

a. the limited number of subqueries

b. the where clause

c. buffer size used by the query

d. nested subquery


Q. in a where clause, the level of subqueries that are possible are

a. 2

b. 255

c. 100

d. 4

e. 10


Q. in the execution of queries, the direction is in one direction starting from the

a. where clause

b. outermost queries

c. innermost queries

d. select statement


Q. Examine the structure of the EMPLOYEES table:



EMPLOYEE_ID NUMBER, Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)


Which three statements insert a row into the table? (Choose three.)

A. INSERT INTO employees VALUES (NULL,'John','Smith');

B. INSERT INTO employees (first_name,last_name) VALUES ('John','Smith');

C. INSERT INTO employees VALUES (1000,'John',NULL);

D. INSERT INTO employees (first_name,last_name,employee_id) VALUES (1000,'John','Smith');

E. INSERT INTO employees (employee_id) VALUES (1000);

F. INSERT INTO employees (employee_id,first_name,last_name) VALUES (1000,'John','');

Ans: C, E, F

Explanation:

Since EMPLOYEE_ID column is used as primary key, it cannot be NULL, so only INSERT statements in C, E and F are correct. You can insert the row with NULL LAST_NAME as in answer C, or only the row with EMPLOYEE_ID as in answer E, or the row with empty LAST_NAME column.
Incorrect Answers:

A: This answer is incorrect because a primary key cannot be NULL.

B: INSERT statement does not contain primary key value at all, so this answer needs to be eliminated as correct one.

D: This statement shows incorrect order of columns of row which needs to be inserted into the table.


Q. Click the Exhibit button and 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))

Ans: C, D, E

Explanation:

These answers show correct syntax, because they use ANY and ALL keywords for convert multi-row output of sub-query to one-row result.
Incorrect Answers:

A: This SELECT statement is incorrect because of multi-row return of sub-query: it will return minimal salary for EACH department.

B: This SELECT statement is incorrect because of multi-row return of sub-query: it will return average salary for EACH department.

F: This SELECT statement is incorrect because GROUP BY clause cannot contain functions, like AVG(), MIN(), MAX() and so on.


Q.  Examine the description of the EMPLOYEES table:



EM_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)  
DEPT_ID NUMBER(2)  
JOB_CAT VARCHAR2(30)  
SALARY NUMBER(8,2)  



Which statement shows the maximum salary paid in each job category of each department?

A. SELECT dept_id, job_cat, MAX (salary) FROM employees WHERE salary > MAX (salary);

B. SELECT dept_id, job_cat, MAX (salary) FROM employees GROUP BY dept_id, job_cat;


C. SELECT dept_id, job_cat, MAX (salary) FROM employees;

D. SELECT dept_id, job_cat, MAX (salary) FROM employees GROUP BY dept_id;

E. SELECT dept_id, job_cat, MAX (salary) FROM employees GROUP BY dept_id, job_cat, salary;

Ans: B

Explanation:

This answer provides correct syntax and semantics to show the maximum salary paid in each job category of each department.
Incorrect Answers:

A: This query will not return any row because condition SALARY > MAX(SALARY) is FALSE.

C: This query will return error because you cannot show maximum salary with DEPT_ID and JOB_CAT without grouping by these columns.

D: The GROUP BY clause is missing JOB_ID column.

E: You don’t need to group results of query by SALARY in the GROUP BY column.


Q. Which SELECT statement will get the result 'ello world' fromt the string 'Hello World'?

A. SELECT SUBSTR ('Hello World',1) FROM dual;

B. SELECT INITCAP (TRIM ('Hello World', 1,1) FROM dual;

C. SELECT LOWER (SUBSTR ('Hello World', 1,1) FROM dual;

D. SELECT LOWER (SUBSTR ('Hello World', 2,1) FROM dual;

E. SELECT LOWER (TRIM ('H' FROM 'Hello World')) FROM dual;

Ans:  E

Explanation:

This statement will return correct result because function TRIM() will trim letter ‘H’ in the ‘Hello World’ and function LOWER() will return data in string in lowercase..
Incorrect Answers:

A: This statement will return sub-string starting at the character in position number Y to the end.

B: There is incorrect syntax in the TRIM() function.

C: There is incorrect syntax in that statement because of absence right bracket.

D: There is incorrect syntax in that statement because of absence right bracket.

Q. Management has asked you to calculate the value 12 * salary * commission_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
COMMISSION_PCT NUMBER(4,2)  


Which statement ensures that a value is displayed in the calculated column for all employees?

A. SELECT last_name, 12 * salary* commission_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;

Ans: C

Explanation:

This SELECT statement provides correct usage of NVL function to calculate columns for all employees. Oracle give you possibility to substitute a value in place of NULL. The basic syntax for NVL() is NVL(column_name, value_if_null). Notice that the column specified in NVL() contains an actual value. That value is what Oracle returns; when the column is NULL, the special string is returned. The value specified to be returned if the column value is NULL must be the same datatype as the column specified.
Incorrect Answers:

A: This SELECT statement will return NULL value for rows with NULL COMMISION_PCT column.

B: It is incorrect syntax in this query: NVL function needs to be used for correct result.

D: The DECODE function is used as substitution of IF-THEN-ELSE PL/SQL construction in SQL queries. The SELECT statement provides incorrect syntax of it cannot have only two parameters.

 

Q. Examine the description of the STUDENTS table:




STD_ID NUMBER(4)
COURSE_ID VARCHAR2(10)
START_DATE DATE
END_DATE DATE


Which two aggregate functions are valid on the START_DATE column? (Choose Two)

A. SUM (start_date)

B. AVG (start_date)

C. COUNT (start_date)

D. AVG (start_date, end_date)

E. MIN (start_date)

F. MAXIMUM (start_date)

Ans: C, E

Explanation:

It is possible to apply COUNT() and MIN() functions on the column with DATE data type.
Incorrect Answers:

A: Function SUM() cannot be used with DATE data type column.

B: Function AVG() cannot be used with DATE data type column.

D: Function AVG() cannot be used with DATE data type column. And function AVG() just has one parameter X, not two. It averages all X column values returned by the SELECT statement.

F: There is no MAXIMUM() function in Oracle, only MAX() function exists.


Q. From iSQL*Plus, you issue this SELECT statement:

SELECT * FROM orders;

You use this statement to retrieve data from a database table for _______________. (Choose all that apply)

A. updating

B. viewing

C. deleting

D. inserting

E. truncating

Ans: B, D

Explanation:

You can use SELECT statement to display and to insert data into different table.
Incorrect Answers:

A: You cannot update data with SELECT statement. Update command is used for this purpose.

C: You cannot delete data with SELECT statement. Delete command is used for this purpose.

E: You cannot truncate data with SELECT statement. Truncate command is used for this purpose.


Q.  Click the Exhibit button examine the data from the EMP table.



EMP_ID DEPT_ID COMMISSION
1 10 500
2 20 1000
3 10  
4 10 600
5 30 800
6 30 200
7 10  
8 20 300


The COMMISSION column shows the monthly commission earned by the employee. Which three tasks would require subqueries or joins in order to be performed in a single step? (Choose three)

A. deleting the records of employees who do not earn commission

B. increasing the commission of employee 3 by the average commission earned in department 20

C. finding the number of employees who do NOT  earn commission and are working for department 20

D. inserting into the table a new employee 10 who works for deartment 20 and earns a commission that is equal to the commission earned by employee 3

E. creating a table called COMMISSION that has the same structure and data as the columns EMP_ID and COMMISSION  of the EMP table

F. decreasing the commission by 150 for the employees who are working in department 30 and earning a commission of more than 800.

Ans:  B, D, E

Explanation:

Increasing the commission of employee 3 by the average commission earned in department 20 will require sub-queries or joins in order to perform in a single step.
Inserting into the table a new employee 10 who works for department 20 and earns a commission that is equal to the commission earned by employee 3 is correct answer also.
And decreasing the commission by 150 for the employees who are working in department 30 and earning a commission of more then 800.
Incorrect Answers:

A: This query can be done without using sub-queries or joins in order to perform in a single step.

C: It is not required to use sub-queries or joins to create SQL statement for that query.

E: This query can be done without using sub-queries or joins in order to perform in a singlestep.


Q. Which four statements correctly describe functions that are available in SQL? (Choose four)

A. INSTR returns the numeric position of a named character

B. NVL2 returns the first non-null expression in the expression list.

C. TRUNCATE rounds the column, expression, or value to n decimal places

D. DECODE translates an expression after comparing it to each search value

E. TRIM trims the heading or trailing characters (or both) from a character string.

F. NVL compares two expressions and returns null if they are equal, or the first expression if they are not equal.

G. NULLIF compares two expressions and returns null if they are equal, or the first expression if they are not equal.

Ans: A, D, E, G

Explanation:

INSTR returns the numeric position of a named character. DECODE translates an expression after comparing it to each search value. TRIM trims the heading of trailing characters (or both) from a character string. NULLIF compares twp expressions and returns null if they are equal, or the first expression if they are not equal.
Incorrect Answers:

B: This statement is not correct. The following is the Syntax for NVL2 function: NVL2(expr1, expr2, expr3). If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3.

C: Command TRUNCATE is used to remove all row data from the table, while leaving the definition of the table intact, including the definition of constraints and any associated database objects as indexes, constraints, and triggers on the table.

F: NVL returns second parameter value if first one is NULL.


Q. The EMPLOYEES table has these columns:



LAST_NAME VARCNAR2(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;

Ans: D

Explanation:

This SELECT statement provides correct usage of NVL function to calculate columns for all employees. Oracle give you possibility to substitute a value in place of NULL. The basic syntax for NVL() is NVL(column_name, value_if_null). Notice that the column specified in NVL() contains an actual value. That value is what Oracle returns; when the column is NULL, the special string isreturned. The value specified to be returned if the column value is NULL must be the same datatype as the column specified.
Incorrect Answers:

A: This SELECT statement will return NULL value for rows with NULL COMMISION_PCT column.

B: There is no IFNULL() function in Oracle.

C: The NVL2() function requires 3 parameters, not 2. Function NVL2(expr1, expr2, expr3) returns expr2 if expr1 is not NULL. If expr1 is NULL, it returns expr3.


Q. Which two statements are true regarding the ORDER BY clause? (Choose two)

A. The sort is in ascending order by default

B. The sort is in descending order by default 

C. The ORDER BY clause must precede the WHERE clause.

D. The ORDER BY clause is executed on the client side

E. The ORDER BY clause comes last in the SELECT statement

F. The ORDER BY clause is executed first in the query execution.

Ans: A, E

Explanation:

The ORDER BY clause does sort data in ascending order by default. And the ORDER BY clause comes last in the SELECT statement: after FROM or WHERE or GROUP BY clause.
Incorrect Answers:

B: The ORDER BY clause does sort data in ascending, not descending order, by default.

C: The ORDER BY clause must be after the WHERE clause in the SQL statement.

D: The ORDER BY clause is executed on the server side as the whole SQL statement is.

F: The ORDER BY clause is executed last in the query execution, after results are limited with the WHERE and GROUP BY clause conditions

 

 

Continue......


Related Articles