Part - 2 Oracle Interview

Question: 11. (A)

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

Which statement finds the rows in the CUSTOMERS table that do not have a postal code?

A. SELECT customer_id, customer_name
FROM customers
WHERE postal_code CONTAINS NULL;

B. SELECT customer_id, customer_name
FROM customers
WHERE postal_code = '________';

C. SELECT customer_id, customer_name
FROM customers
WHERE postal_code IS NULL;

D. SELECT customer_id, customer_name
FROM customers
WHERE postal code IS NVL;

E. SELECT customer_id, customer_name
FROM customers
WHERE postal_code = NULL;

Answer: C

Explanation:

This statement returns the rows in the CUSTOMERS table that do not have a postal code. The correct syntax to check NULL values is usage of 􀂳IS NULL􀂴 clause.

Incorrect Answers

A: "CONTAINS NULL" is incorrect clause in Oracle.
B: This statement will just check if postal code equals to string "________';
D: Usage of 􀂳IS NVL􀂴 is incorrect in Oracle. But there is a function NVL() you can use to processNULL values.
E: You can not use equal comparison to check whether value is NULL or not. Use construction
"IS NULL" or "IS NOT NULL" to do that.

Question: 12. (A)

Evaluate these two SQL statements:
SELECT last_name, salary , hire_date

FROM EMPLOYEES
ORDER BY salary DESC;
SELECT last_name, salary, hire_date
FROM EMPLOYEES
ORDER BY 2 DESC;

What is true about them?

A. The two statements produce identical results.

B. The second statement returns a syntax error.

C. There is no need to specify DESC because the results are sorted in descending
order by default.

D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.

Answer: A

Explanation:

These two statements produce identical results, because it is possible even to use numbers to indicate the column position where Oracle should order the output from a statement.

Incorrect Answers

B: Second statement is correct and it will not return a syntax error.
C: The results are sorted in ascending order by default.
D: There is no corrections need to be made for the statements. They will return identical results.

Question: 13. (A)

Evaluate the set of SQL statements:
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCNAR2(14),
loc VARCNAR2(13));
ROLLBACK;
DESCRIBE DEPT

What is true about the set?

A. The DESCRIBE DEPT statement displays the structure of the DEPT table.

B. The ROLLBACK statement frees the storage space occupies by the DEPT table.

C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.

D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if the us a COMMIT statement introduced before the ROLLBACK statement..

Answer: A

Explanation:

The structure of the DEPT table will be displayed because the CREATE TABLE statement is DDL operation and it cannot be rolled back because implicit commit occurs on the database when a user exits SQL*Plus or issues a data-definition language (DDL) command such as a create table statement, user to create a database object, or an lter table statement, used to alter a database object.

Incorrect Answers

B: The ROLLBACK statement has nothing to do with the storage space of the DEPT table.
C: The DESCRIBE DEPT statement does not produce the error. It displays the structure of the DEPT table.
D: The COMMIT statement does not need to be introduced because implicit commit occurs on the database after creation of the table.

Question: 14. (A)

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, m.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?

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

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

C.
EMP_id EMPLOYEE SALARY Mgr_id Manager
------- ---------- --------- ------------- --------------
110 Bob 800
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.

Answer: B

Explanation:

This statement lists the ID, name, and salary of the employee, and the ID and name of the employee's manager, for all the employees who have a manager and earn more than 4000

Incorrect Answers

A: This output will be provided by different query.
C: This output will be provided by different query.
D: This output will be provided by different query.
E: This SQL query will not produce error, it will show results as in answer B.

Question: 15. (A)

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

Explanation:

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

Incorrect Answers

B: There is no replacement variables SQL*Plus feature in Oracle.
C: There is no prompt variables SQL*Plus feature in Oracle.
D: There is no instead-of variables SQL*Plus feature in Oracle.
E: This feature is implemented in the SQL*Plus with lexical substitution variables.

Question: 16. (A)

You are formulating queries in a SQL*Plus. Which of the following statement correctly describes how to specify a column alias?

A. Place the alias at the beginning of the statement to describe the table.

B. Place the alias after each column separated by a space to describe the column.

C.Place the alias after each column separated by a comma to describe the column.

D. Place the alias at the end of the statement to describe the table.

Answer: B

Explanation:

Aliases do not describe the tables they describe columns so the alias should be place at the end of each column and separated by a space to describe the column.

Question: 17. (A)

You want to use a function in you column clause of a SQL statement. The NVL function
accomplishes which of the following tasks?

A. Assists in the distribution of output across multiple columns.

B. Enables you to specify alternate output for non-NULL column values.

C. Enables you to specify alternated out for NULL column values.

D. Nullifies the value of the column out put.

Answer: C

Explanation:

NVL function is simple if_then operation that test column values out to see whether it is NULL and if it find it is null then NVL substitutes the specified default value for the NULL value.

Question: 18. (A)

You want to use SQL*Plus to connect to the oracle database. Which of the following choices does not indicate a component you must specify when logging into the oracle?

A. The SQL*Plus Keyword.

B. The username

C. The password.

D. The database name.

Answer: D

Explanation:

When connecting to the database you don􀂶t need to specify the name of the database and when you are not specifying the name of the database then you will be connected to the local database.

Question: 19. (A)

The EMPLOYEE_HISTORY table contains these columns:
EMPLOYEE_ID NUMBER
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER
POSITION VARCHAR2(30)
SALARY NUMBER(6,2)
HIRE_DATE DATE
DEPART_DATE DATE

The EMPLOYEE_HISTORY table contains only former employees.
You need to create a report to display all former employees that were hired on or
after January 1, 1996. The data should display in this format:
Former - Employee - Term of Employment
---------------------------- ----------------------------------
14837 - SMITH 10-MAY-92 / 01-JUN-01

Which SELECT statement could you use?

A. SELECT employee_id||' - '||last_name AS Former Employee,
hire_date||' / '||depart_date AS Term of Employment
FROM employee_history
WHERE hire_date > '31-DEC-95';

B. SELECT employee_id||' - '||last_name "AS Former Employee",
hire_date||' / '||depart_date "AS Term of Employment"
FROM employee_history
WHERE hire_date > '31-DEC-95';

C. SELECT employee_id||' - '||last_name 'Former Employee',
hire_date||' / '||depart_date 'Term of Employment'
FROM employee_history
WHERE hire_date > '31-DEC-95'
AND depart_date > NULL;

D. SELECT employee_id||' - '||last_name "Former Employee",
hire_date||' / '||depart_date "Term of Employment"
FROM employee_history
WHERE hire_date > '31-DEC-95'
AND depart_date <> NULL;

E. SELECT employee_id||' - '||last_name "Former Employee",
hire_date||' / '||depart_date "Term of Employment"
FROM employee_history
WHERE hire_date > '31-DEC-95'
AND depart_date IS NOT NULL;

Ans. E


Question: 20. (A)

The EMPloyee table contains these columns:
Empno Number(4)
Ename Varchar2(10)
job varchar2(10)
sal Varchar2(10)
You need to display the employees information by using this query.
How many columns are presented after executing this query:
SELECT Empno||','||Ename||','||Job "Employee Information" FROM employee;

A) 1

B) 2

C) 3

D) 0

E) 4

Answer: A.

Explanation

When we used Concatenation operator between diferent no of columns so then one Column as a resultant column is to be apperead.

Incorrect answers

B.Not 2
C.Not 3
D.Not 0
E.Not 4