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

Oracle Interview Questions for Professionals

|| 0

Oracle Interview Questions for Professionals
Stechies

Q1. You added a PHONE-NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table Some of the employees may not have a phone number available. Which data manipulation operation do you perform?

A. MERGE

B. INSERT

C. UPDATE

D. ADD

E. ENTER

F. You cannot enter the phone number for the existing employee records

Answer: C

Q2. Which two statements about subqueries are true? (Choose two)

A. A single row subquery can retrieve data from only one table.

B. A SQL query statement cannot display data from table B that is refered to in its subquery, unless table B is included in the main query’s FROM clause.

C. A SQL query statement cannot display data from table B that is refered to in its subquery, without including table B in its own FROM clause.

D. A single row subqery can retrieve data from more than one table

E. A single row subqery cannot be used in a condition where the LIKE operator is used for comparison.

F. A multiple-row subquery cannot be used in a condition where the LIKE operation is used for comparison.

Answer: BD

Q3. Examine the structure of the STUDENTS table

STUDENT_ID  NUMBER   NOT NULL., Primary Key
STUDENT_NAME  VARCHAR2(30) 
COURSE_ID  VARCHAR2(10) NOT NULL
MARKS   NUMBER 
START_DATE  DATE
FINISH_DATE  DATE

You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999. Which SQL statement accomplishes this task?

A. SELECT student_id, marks, ROWNUM “Rank” FROM student WHERE ROWNUM <= 10 AND finish_data BETWEEN ’01-JAN-99’ AND ’31-DEC-99’ AND course_id=’INT_SQL’ ORDER BY marks DESC;

B. SELECT student_id, marks, ROWID “Rank” FROM students WHERE ROWID <= 10 AND finish_data BETWEEN ’01-JAN-99’ AND ’31-DEC-99’ AND course_id=’INT_SQL’ ORDER BY marks;

C. SELECT student_id, marks ROWNUM “Rank” FROM (SELECT student_id, marks) FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN ’01-JAN99’ AND ’31-DEC-99’ AND COURSE_ID = ‘INT_SQL’ ORDER BY marks desc:

D. SELECT student_id, marks, ROWNUM ” Rank” FROM   (SELECT student_id.marks  FROM students  ORDER BY marks DESC )  WHERE ROWNUM <= 10 AND finish_date BETWEEN ’01-JAN99’ AND ’31-DEC-99’  AND course_id = ‘INT_SQL;’

Answer: D

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

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

Q6. 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 CD 

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

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

Q9. 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 | SQL*Plus

Answer : A

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

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

Q12. 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 employees 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

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

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

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

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

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

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

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

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

Get More Questions and Answers with Explanation at Oracle Forums.


Advertisement
Advertisement
Advertisement