Online Tutorials & Training Materials | STechies.com
Register Login
Advertisement

Oracle Interview Questions for 5 Years Experience

|| 0

Oracle Interview Questions for 5 Years Experience
Stechies

Q1. You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES  and DEPARTMENTS  tables EMPLOYEE_ID, EMPLOYEE_NAME  AND DEPARTMENT_NAME
The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table. You want to modify the view by adding a fourth column, MANAGER_Id of NUMBER data type from the EMPLOYEES table.  How can you accomplish this task?

A. ALTER VIEW emp_dept_vu (ADD manager_id NUMBER);

B. MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);

C. ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name Department_name, manager_id
FROM employees e, departments d WHERE  department_id = d.department_id;

D. MODIFY VIEW emp_depat_vu AS SELECT employee_id, employee_name, Department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;

E. CREATE OR REPLACE VIEW emp_dept_vu AS SELECT emplouee_id, employee_ name, Department_name, manager _id FROM employees e, departments d WHERE e.department_id=d.department_id;

F. You must remove the existing view first, and then run the CRATE VIEW command with a new column list to modify a view.

Answer:  E

Q2. Which three SELECT statements display 2000 in the format “$2,000.00”? (Choose Three).

A. SELECT TO_CHAR (2000,  ‘$#,###.##’)  FROM dual;

B. SELECT TO_CHAR (2000, ‘$0,000.00’) FROM dual

C. SELECT TO_CHAR (2000, ‘$9,999.00’) FROM dual;

D. SELECT TO_CHAR (2000, ‘$9,999.99’) FROM dual;

E. SELECT TO_CHAR (2000, ‘$2,000.00’) FROM dual;

F. SELECT TO_CHAR (2000, ’$N, NNN.NN’) FROM dual

Answer:  BCD

Q3. Evaluate the SQL statement   DROP TABLE DEPT; Which four statements are true of the SQL statement? (Choose four)

A. You cannot roll back this statement

B. All pending transactions are committed

C. All views based on the DEPT table are deleted

D. All indexes based on the DEPT table are dropped

E. All data in the table is deleted, and the table structure is also deleted

F. All data in the table is deleted, but the structure of the table is retained

G. All synonyms based on the DEPT table are deleted

Answer:  ABDE

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

Answer:  E

Q5. Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

EMPLOYEES
EMPLOYEE_ID NUMBER   Primary Key
FIRST_NAME      VARCHAR2(25)
LAST_NAME       VARCHAR2(25)
HIRE_DATE      DATE

NEW EMPLOYEES
EMPLOYEE_ID NUMBER   Primary Key 
NAME        VARCHAR2(60)

Which UPDATE statement is valid?

A. UPDATE new_employees SET
name=(SELECT last_name||First_name FROM employees  WHERE employee_id = 180)
WHERE employee_id = 180

B. UPDATE new_employees SET name = (SELECT Last_name || first_name FROM employees)
WHERE employee_id = 180;

C. UPDATE new_employees SET name = (SELECT last_name|| First_name FROM employees
WHERE employee_id = 180
WHERE employee_id = (SELECT  employee_id  FROM new employees);

D. UPDATE new_employees SET name = (SELECT last name|| First_name FROM employees
WHERE employee_id= (SELECT employee_id WHERE employee_id FROM new_employees))
WHERE employee_id = 180,

Answer:  A

Q6. You need to produce a report for mailing labels for all customers.  The mailing label must have only the customer name and address.  The CUSTOMER table has these columns:

CUST_ID  NUMBER(4)  NOT NULL
CUST_NAME VARCHAR2(100) NOT NULL
CUST_ADDRESS VARCHAR2(150) 
CUST_PHONE VARCHAR(20)

Which SELECT statement accomplishes this task?

A. SELECT * FROM customers

B. SELECT name, address FROM customers;

C. SELECT id, name, address, phone FROM customers;

D. SELECT cust_name, cust_address FROM customers;

E. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers;

Answer:  D

Q7. Which two statements complete a transaction? (Choose two)

A. DELETE employees;

B. DESCRIBE employees

C. ROLLBACK TO SAVEPOINT C;

D. GRANT TABLE employees

E. ALTER TABLE employees SET UNUSED COLUMN sal;

F. SELECT MAX (sal) FROM employees
WHERE department_id = 20;

Answer:  CE

Q8. Examine the description of the EMPLOYEES table:

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

Which statement shows the department ID, minimum salary, and maximum salary paid in that department, only if the minimum salary is less than 5000 and maximum salary is more than 15000?

A. SELECT  dept_id, MIN (salary), MAX (salary) FROM employees WHERE MIN(salary) < 5000 AND MAX (salary) > 15000;

B. SELECT dept_id, MIN (salary), MAX (salary) FROM employees WHERE MIN (salary) < 5000 AND MAX (salary)  15000 GROUP BY dept_id;

C. SELECT dept_id, MIN(salary), MAX(salary) FROM employees  HAVING MIN (salary) < 5000 AND MAX (salary)

D. SELECT dept_id MIN (salary), MAX (salary) FROM employees GROUP BY dept_id HAVING MIN(salary) < 5000 AND MAX (salary) > 15000

E. SELECT dept_id,MIN (salary), MAX (salary) FROM employees  GROUP BY dept_id, salary HAVING MIN (salary) < 5000 AND MAX (salary) > 15000;

Answer: D

Q9. The DBA issues this SQL command:

CREATE USER scott  INDENTIFIED by tiger;

What privileges does the user Scott have at this point?

A. no privileges

B. only the SELECT  privilege

C. only the CONNECT privilege

D. all the privileges of a default user

Answer: A

Q10. The EMPLOYEES table has these columns

LAST_NAME VARCHAR2 (35)
SALARY  NUMBER (8,2)   
HIRE_DATE DATE

Management wants to add a default value to the SALARY column.  You plan to alter the table by using this SQL statement:

ALTER TABLE EMPLOYEES
MODIFY (SALARY DEFAULT 5000);

Which is true about your ALTER statement?

A. Column definitions cannot be altered to add DEFAULT values

B. A change to the DEFAULT value affects only subsequent insertions to the table

C. Column definitions cannot be altered to add DEFAULT values for columns with a NUMBER data type.

D. All the rows that have a NULL value for the SALARY column will be updated with the value 5000.

Answer:  B

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

A. &

B. ACCEPT

C. PROMPT

D. &&

Answer: D

Q12. Examine the structure of the EMPLOYEES table:

Column name  Data type Remarks
EMPOYEE_ID NUMBER   NOT NULL, Primary Key
EMP_NAME VARCHAR2(30) 
JOB_ID  VARCHAR2(20) NOT NULL
SAL    NUMBER    
MGR_ID  NUMBER  References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER  Foreign key to DEPARTMENT_ID column Of the DEPARTMENTS table

You need to create a view called EMP_VU that allows the users to insert rows through the view.
Which SQL statement, when used to create the EMP_VU view, allows the users to insert rows?

A. CREATE VIEW  emp_Vu AS SELECT employee_id, emp_name, Department_id FROM employees WHERE mgr_id IN (102,120);

B. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, Department_id FROM employees WHERE mgr_id IN (102, 120);

C. CREATE VIEW emp_Vu AS SELECT department_id, SUM(sal) TOTAL SAL FROM  employees WHERE mgr_id IN (102, 120) GROUP BY department_id;

D. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, DISTINCT department_id
FROM employees

Answer:  B

Q13. What is true about the WITH GRANT OPTION clause?

A. It allows a grantee DBA privileges

B. It is required syntax for object privileges

C. It allows privileges on specified columns of tables

D. It is used to grant an object privilege on a foreign key column

E. It allows the grantee to grant object privileges to other users and roles

Answer:  E

Q14. The STUDENT_GRADES table has these columns

STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA  NUMBER (4,3)

The registrar has asked for a report on the average grade point average (GPA) for students enrolled during semesters that end in the year 2000.  Which statement accomplishes this?

A. SELECT AVERAGE(gpa) FROM student_grades
WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’

B. SELECT COUNT (gpa) FROM student grades
WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’

C. SELECT MID (gpa) FROM  student_grades
WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’

D. SELECT AVG (gpa) FROM student_grades
WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’

E. SELECT SUM (gpa) FROM student_grades
WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’

F. SELECT MEDIAN (gpa) FROM student_grades
WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’

Answer:  D

Q15. Which constraint can be defined only at the column level?

A. UNIQUE

B. NOT NULL

C. CHECK

D. PRIMARY KEY

E. FOREIGN KEY

Answer:  B

Q16. 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 of all employees

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

Q17. Examine the structure of the EMPLOYEES  and NEW EMPOYEES tables:

EMPLOYEE_ID   NUMBER  Primary Key
FIRST_NAME  VARCHAR2(25)
LAST_NAME  VARCHAR2(25)
HIRE_DATE   DATE

NEW EMPLYEES
EMPLOYEE_ID  NUMBER  Primary Key
NAME    VARCHAR2(60)

Which MERGE statement is valid?

A. MERGE INTO new_employees e USING employees e ON (e.employee_id = e.employee_id)
WHEN MATCHED THEN  UPDATE SET e.name = e.first_name ||’,’|| e.last_name
WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name||’,‘||e.last_name);

B. MERGE new_employee c USING  employees e ON (c.employee_id = e.employee_id)
WHEN EXISTS THEN UPDATE SET c.name = e first_name||’,’|| e.last_name
WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name||’.‘||e.last_name);

C. MERGE INTO new employees c USING  employees e ON (c.employee_id = e.employee_id)
WHEN EXISTS THEN  UPDATE SET e.name = e.fist ||’,’|| e.last_name
WHEN NOT MATCHES THEN INSERT VALUES (e.employee_id, e.first _name||’,‘||e.last_name);

D. MERGE new_employees c FROM employees c ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN  UPDATE SET e.name = e.first_name ||’,’|| e.last_name
WHEN NOT MATCHED THEN  INSERT INTO new_employees VALUES (e.employee_id, e.first_name||”.‘||e.last_name);

Answer:  A

Q18. Which three are true regarding the use of outer joins? (Choose three.)

A. You cannot use IN operator in a condition that involves an outerjoin

B. You use (+) on both sides of the WHERE condition to perform an outerjoin

C. You use (*) on both sides of the WHERE condition to perform an outerjoin.

D. You use an outerjoin to see only the rows that do not meet the join condition

E. In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin

F. You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator

Answer:  AEF

Q19. Which statement creates a new user?

A. CREATE USER susan

B. CREATE OR REPLACE USER susan

C. CREATE NEW USER susan DEFAULT,

D. CREATE USER susan INDENTIFIED BY blue

E. CREATE NEW USER susan IDENTIFIED BY blue

F. CREATE OR REPLACE USER susan IDENTIFIED BY blue;

Answer: D

Q20. The STUDENT_GRADES table has these columns

STUDENT_ID NUMBER (12)
SEMESTER_END DATE
GPA  NUMBER (4,3)

The registrar has requested a report listing the students’ grade point averages (GPA), stored from highest grade point average to lowest within each semester, starting from the earliest date.  Which statement accomplishes this?

A. SELECT student)_id, semester_end, gpa FROM student_grades
ORDER BY semester_end DESC, gpa DESC;

B. SELECT  student_id, semester_end, gpa FROM student_grades
ORDER BY semester_end ASC, gpa ASC;

C. SELECT  student _id, semester_end, gpa FROM  student_grades
ORDER BY semester_end, gpa DESC;

D. SELECT student_id, semester_end, gpa FROM  student_grades
ORDER BY gpa DESC, semester_end DESC;

E. SELECT student-id, semester_end, gpa FROM student_grades
ORDER BY gpa DESC, semester_end ASC;

Answer:  C

Get More Questions and Answers with Explanation at Oracle Forums.


Advertisement
Advertisement
Advertisement