Oracle Interview Questions II

Find more oracle material at http://download.35mb.com/kvreddy83

SQL – 2 Marks each

1. 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', ' ');


2. 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 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;

3. Which SELECT statement will get the result 'elloworld' from the string 'HelloWorld'?
A. SELECT SUBSTR( 'HelloWorld',1) FROM dual;
B. SELECT INITCAP(TRIM ('HelloWorld', 1,1)) FROM dual;
C. SELECT LOWER(SUBSTR('HelloWorld', 1, 1) FROM dual;
D. SELECT LOWER(SUBSTR('HelloWorld', 2, 1) FROM dual;
E. SELECT LOWER(TRIM ('H' FROM 'HelloWorld')) FROM dual;

4. 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 VARCHAR2(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;


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


6. Which SQL statement defines a FOREIGN KEY constraint on the DEPTNO column of the EMP table?
A. CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno);

B. CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2)
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

C. CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));



D. CREATE TABLE EMP (empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2) FOREIGN KEY
CONSTRAINT emp deptno fk REFERENCES dept (deptno));


7. Evaluate the set of SQL statements:
CREATE TABLE dept
(deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(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 occupied 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 there is a COMMIT
statement introduced before the ROLLBACK statement.


8. Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
A. SELECT ename, salary*12 'Annual Salary' FROM employees;
B. SELECT ename, salary*12 "Annual Salary" FROM employees;
C. SELECT ename, salary*12 AS Annual Salary FROM employees;
D. SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY") FROM employees;


9. You need to display the last names of those employees who have the letter "A" as the second
character in their names.
Which SQL statement displays the required results?
A. SELECT last_name FROM EMP WHERE last_name LIKE '_A%';
B. SELECT last_name FROM EMP WHERE last name ='*A%'
C. SELECT last_name FROM EMP WHERE last name ='_A%';
D. SELECT last_name FROM EMP WHERE last name LIKE '*A%'


10. Which two statements about creating constraints are true? (Choose two.)
A. Constraint names must start with SYS_C.
B. All constraints must be defined at the column level.
C. Constraints can be created after the table is created.
D. Constraints can be created at the same time the table is created.
E. Information about constraints is found in the VIEW_CONSTRAINTS dictionary view


11. Examine the SQL statement that creates ORDERS table:
CREATE TABLE orders
(SER_NO NUMBER UNIQUE,
ORDER_ID NUMBER,
ORDER_DATE DATE NOT NULL,
STATUS VARCHAR2(10)
CHECK (status IN ('CREDIT','CASH')),
PROD_ID NUMBER REFERENCES PRODUCTS(PRODUCT_ID),
ORD_TOTAL NUMBER,
PRIMARY KEY (order_id, order date));

For which columns would an index be automatically created when you execute the above SQL statement? (Choose two.)

A. SER_NO
B. ORDER_ID
C. STATUS
D. PROD_ID
E. ORD_TOTAL
F. composite index on ORDER_ID and ORDER_DATE


12. Which constraint can be defined only at the column level?
A. UNIQUE
B. NOT NULL
C. CHECK
D. PRIMARY KEY
E. FOREIGN KEY


13. What does the TRUNCATE statement do?
A. removes the table
B. removes all rows from a table
C. shortens the table to 10 rows
D. removes all columns from a table
E. removes foreign keys from a table


14. Which SELECT statement should you use to extract the year from the system date and display it in the
format "1998"?
A. SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual;
B. SELECT TO_DATE(SYSDATE,'yyyy') FROM dual;
C. SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual;
D. SELECT DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual;
E. SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy') FROM dual;




15. Which clause should you use to exclude group results?
A. WHERE
B. HAVING
C. RESTRICT
D. GROUP BY
E. ORDER BY


16. What is true about joining tables through an equijoin?
A. You can join a maximum of two tables through an equijoin.
B. You can join a maximum of two columns through an equijoin.
C. You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
D. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.
E. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.


17. You would like to display the system date in the format "Monday, 01 June, 2001".
Which SELECT statement should you use?
A. SELECT TO_DATE(SYSDATE, 'FMDAY, DD Month, YYYY') FROM dual;
B. SELECT TO_CHAR(SYSDATE, 'FMDD, DY Month, 'YYY') FROM dual;
C. SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') FROM dual;
D. SELECT TO_CHAR(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;
E. SELECT TO_DATE(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;


18. Which two are character manipulation functions? (Choose two.)
A. TRIM
B. REPLACE
C. TRUNC
D. TO_DATE
E. MOD
F. CASE


19. You need to create a view EMP_VU. The view should allow the users to manipulate the records of only the employees that are working for departments 10 or 20.
Which SQL statement would you use to create the view EMP_VU?
A. CREATE VIEW emp_vu AS
SELECT * FROM employees
WHERE department_id IN (10,20);

B. CREATE VIEW emp_vu AS
SELECT * FROM employees
WHERE department_id IN (10,20)
WITH READ ONLY;

C. CREATE VIEW emp_vu AS
SELECT * FROM employees
WHERE department_id IN (10,20)
WITH CHECK OPTION;

D. CREATE FORCE VIEW emp_vu AS
SELECT * FROM employees
WHERE department_id IN (10,20);

E. CREATE FORCE VIEW emp_vu AS
SELECT * FROM employees
WHERE department_id IN (10,20)
NO UPDATE;


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


21. 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 numbers for the existing employee records.






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

The CUSTOMER_ID column is the primary key for the table.
Which two statements find the number of customers? (Choose two.)

A. SELECT TOTAL(*) FROM customers;
B. SELECT COUNT(*) FROM customers;
C. SELECT TOTAL(customer_id) FROM customers;
D. SELECT COUNT(customer_id) FROM customers;
E. SELECT COUNT(customers) FROM customers;
F. SELECT TOTAL(customer_name) FROM customers;


23. In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed in the
SELECT 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


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


PL/SQL

1. Assume a situation whereby we have to maintain a track on all DML operations done on EMP table and store the tracked information along with Userid and Event date time and type of DML operation done. Use suitable PL/SQL object to achieve it. (10 Marks)

2. Difference between Implicit and Explicit Cursors, with cursor attributes explanations. (5 marks).

3. Have a look at the following table structures. (12 Marks)
Customer Table
Acctno Number(8)
CustName Varchar2(25)
Balance Number(7,2)
MinBalance Number(7,2)
Acc_Op_Date date
AccType Varchar2(15) - (SAVINGS or CURRENT or RECCURING)

Transaction Table
Acctno Number(8)
TransType Varchar2(15) - (WITHDRAWAL or DEPOSIT)
Amount number(7,2)
TranDate Date

Whenever a Transaction occurs an entry is made in the transaction table and in the Customer table the Balance is updated to the current balance (i.e. after the last transaction).
Also while making a transaction a check has to be maintained that the balance should not go less than the MINBALANCE value.
Write suitable PL/SQL Object(s) to achieve the following:
• Updating the Customer table on occurrence of any transaction.
• Keeping a check on the Balance not going low than Minbalance.


4. What are Packages and what is its significance? Explain with example. (10 Marks)

5. What are Exceptions? What are the type of Exceptions and its difference? Explain with Examples on each. (7 Marks)

6. Write a suitable PL/SQL block to pick all employees (EMP Table) in a particular department and raise their salary by 10% if they have completed 3 years of service in the organization. (8 Marks)



*********** Best Of Luck ***********

Find more oracle material at http://download.35mb.com/kvreddy83