SASANK@TECHNOMANIA

Oracle Interview Questions and Answers : SQL
1.     To see current user name
 Sql> show user;
2.     Change SQL prompt name
 SQL> set sqlprompt “Manimara > “
Manimara >
Manimara >
3.     Switch to DOS prompt
 SQL> host
4.     How do I eliminate the duplicate rows ?
 SQL> delete from table_name where rowid not in (select max(rowid) from table group by
duplicate_values_field_name);
or
SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from
table_name tb where ta.dv=tb.dv);
Example.
Table Emp
Empno Ename
101             Scott
102             Jiyo
103             Millor
104             Jiyo
105             Smith
delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);
The output like,
Empno Ename
101             Scott
102             Millor
103             Jiyo
104             Smith
5.     How do I display row number with records?
To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;
Output:
1                Scott
2                Millor
3                Jiyo
4                Smith
6.     Display the records between two range
select rownum, empno, ename from emp where rowid in
 (select rowid from emp where rownum <=&upto
 minus
 select rowid from emp where rownum<&Start);
Enter value for upto: 10
Enter value for Start: 7
   ROWNUM        EMPNO ENAME
--------- --------- ----------
      1      7782 CLARK
      2      7788 SCOTT
      3      7839 KING
      4      7844 TURNER
7.     I know the nvl function only allows the same data type(ie. number or char or date
Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of
blank space. How do I write the query?
 SQL> select nvl(to_char(comm.),'NA') from emp;
Output :
NVL(TO_CHAR(COMM),'NA')
-----------------------
NA
300
500
NA
1400
NA
NA
8.      Oracle cursor : Implicit & Explicit cursors
Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly declared.
9.      Explicit Cursor attributes
 There are four cursor attributes used in Oracle
cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN
10. Implicit Cursor attributes
 Same as explicit cursor but prefixed by the word SQL
SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN
Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after
executing SQL statements.
      : 2. All are Boolean attributes.
11. Find out nth highest salary from emp table
 SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B
WHERE a.sal<=b.sal);
Enter value for n: 2
     SAL
---------
    3700
12. To view installed Oracle version information
 SQL> select banner from v$version;
13. Display the number value in Words
 SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
     SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
     800 eight hundred
    1600 one thousand six hundred
    1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",
 (' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))
 "Sal in Words" from emp
/
Salary Sal in Words
------- ------------------------------------------------------
   800 Rs. Eight Hundred only.
  1600 Rs. One Thousand Six Hundred only.
  1250 Rs. One Thousand Two Hundred Fifty only.
14. Display Odd/ Even number of records
 Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6
15. Which date function returns number value?
 months_between
16. Any three PL/SQL Exceptions?
 Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
17. What are PL/SQL Cursor Exceptions?
 Cursor_Already_Open, Invalid_Cursor
18. Other way to replace query result null value with a text
 SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’
19. What are the more common pseudo-columns?
 SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM
20. What is the output of SIGN function?
 1 for positive value,
0 for Zero,
-1 for Negative value.
21. What is the maximum number of triggers, can apply to a single table?
 12 triggers.

1. Explain the difference between a database administrator and a data administrator.

Database Administrator :- A person (or group of people) responsible for the maintenance and performance of a database and responsible for the planning, implementation, configuration, and administration of relational database management systems.

Data Administrator :- The individual or organization responsible for the specification, acquisition, and maintenance of data management software and the design, validation, and security of files or databases. The DA is in charge of the data dictionary and data model.

2. Explain the difference between an explicit and an implicit lock.

Explicit Lock :- Lock is explicitly requested for a record or table.
Implicit Lock :- Lock is implied but is not acquired

3. What is lock granularity?

There are many locks available for the database system to have like
Intent Shared, Shared, Intent exclusive, exclusive and Shared Intent exclusive.
Locking granularity refers to the size and hence the number of locks used to ensure the consistency of a database during multiple concurrent updates.

4. In general, how should the boundaries of a transaction be defined?

A transaction ensures that one or more operations execute as an atomic unit of work. If one of the operations within a transaction fails, then all of them are rolled-back so that the application is returned to its prior state. The boundaries that define a group of operations done within a single transaction.

5. Explain the meaning of the expression ACID transaction.

ACID means Atomic, Consistency, Isolation, Durability, so when any transaction happen it should be Atomic that is it should either be complete or fully incomplete. There should not be anything like Semi complete. The Database State should remain consistent after the completion of the transaction. If there are more than one Transaction then the transaction should be scheduled in such a fashion that they remain in Isolation of one another.Durability means that Once a transaction commits, its effects will persist even if there are system failures.

6. Explain the necessity of defining processing rights and responsibilities. How are such responsibilities enforced?

One of the reason to define rights is the security in the database system. If any user is allowed to define the data or alter the data then the database would just be of no use and so processing rights and responsibilities are clearly defined in any database system. The resposibilities are enforced using the table space provided by the database system.

7. Describe the advantages and disadvantages of DBMS-provided and application-provided security.

DBMS provided security :- Any database system requires you to login and then process the data depending on the rights given by the DBA to the user who has logged in. The advatage of such a system is securing the data and providing the user and the DBA the secured platform. Any user who logs in cannot do whatever he want but his role can be defined very easily. There is no major disadvantage about the DBMS provided security apart from overhead of storing the rights and priviledges about the users.

Application-provided security :- It is much similar to the DBMS provided security but the only difference is that its the duty of the programmer creating the application to provide all the seurities so that the data is not mishandled.

8. Explain how a database could be recovered via reprocessing. Why is this generally not feasible?

If we reprocess the transaction then the database can be made to come to a state where the database is consistent and so reprocessing the log can recover the database. Reprocessing is not very feasible for a very simple reason that its very costly from time point of view and requires lots of rework and many transaction are even rollback giving more and more rework.

9. Define rollback and roll forward.

Rollback :- Undoing the changes made by a transaction before it commits or to cancel any changes to a database made during the current transaction
RollForward :- Re-doing the changes made by a transaction after it commits or to overwrite the chnaged calue again to ensure consistency

10. Why is it important to write to the log before changing the database values?

The most important objective to write the log before the database is changed is if there is any need to rollback or rollforward any transaction then if the log are not present then the rollback rollforward cannot be done accurately.

Question: What are the wildcards used for pattern matching. 

Answer: _ for single character substitution and % for multi-character substitution. 

Question: How can I hide a particular table name of our schema? 

Answer: you can hide the table name by creating synonyms. 

e.g) you can create a synonym y for table x 

create synonym y for x; 

Question: When we give SELECT * FROM EMP; How does oracle respond: 

Answer: When u give SELECT * FROM EMP; 

the server check all the data in the EMP file and it displays the data of the EMP file 

Question: What is the use of CASCADE CONSTRAINTS? 

Answer: When this clause is used with the DROP command, a parent table can be dropped even when a child table exists. 

Question: There are 2 tables, Employee and Department. There are few records in employee table, for which, the department is not assigned. The output of the query should contain all th employees names and their corresponding departments, if the department is assigned otherwise employee names and null value in the place department name. What is the query? 

Answer: What you want to use here is called a left outer join with Employee table on the left side. A left outer join as the name says picks up all the records from the left table and based on the joint column picks the matching records from the right table and in case there are no matching records in the right table, it shows null for the selected columns of the right table. E.g. in this query which uses the key-word LEFT OUTER JOIN. Syntax though varies across databases. In DB2/UDB it uses the key word LEFT OUTER JOIN, in case of Oracle the connector is Employee_table.Dept_id *= Dept_table.Dept_id 

SQL Server/Sybase : 

Employee_table.Dept_id(+) = Dept_table.Dept_id 

Question: on index 

why u need indexing? Where that is stored 

and what u mean by schema object? 

For what purpose we are using view 

Answer: We can?t create an Index on Index. Index is stored in user_index table. Every object that has been created on Schema is Schema Object like Table, View etc. If we want to share the particular data to various users we have to use the virtual table for the Base table...So that is a view. 

Question: How to store directory structure in a database? 

Answer: We can do it by the following command: create or replace directory as 'c: \tmp' 

Question: Why does the following command give a compilation error? 

DROP TABLE &TABLE_NAME; 

Answer: Variable names should start with an alphabet. Here the table name starts with an '&' symbol. 

Question: Difference between VARCHAR and VARCHAR2? 

Answer: Varchar means fixed length character data (size) i.e., min size-1 and max-2000 

Varchar2 means variable length character data i.e., min-1 to max-4000 

Question: Which command displays the SQL command in the SQL buffer, and then executes it 

Answer: You set the LIST or L command to get the recent one from SQL Buffer 

Question: Which system table contains information on constraints on all the tables created? 

Answer: USER_CONSTRAINTS. 

Question: How do I write a program which will run a SQL query and mail the results to a group? 

Answer: Use DBMS_JOB for scheduling a program job and DBMS_MAIL to send the results through email. 

Question: There is an Eno. & gender in a table. Eno. has primary key and gender has a check constraints for the values 'M' and 'F'. 

While inserting the data into the table M was misspelled as F and F as M. 

What is the update? 

Answer: update set gender= 

case where gender='F' Then 'M' 

where gender='M' Then 'F' 

Question: What the difference between UNION and UNIONALL? 

Answer: union will return the distinct rows in two select s, while union all return all rows. 

Question: How can we backup the sql files & what is SAP? 

Answer: You can backup the sql files through backup utilities or some backup command in sql. SAP is ERP software for the organization to integrate the software. 

Question: What is the difference between TRUNCATE and DELETE commands? 

Answer: TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. 

WHERE clause can be used with DELETE and not with TRUNCATE. 

Question: State true or false. !=, <>, ^= all denote the same operation. 

Answer: True. 

Question: State true or false. EXISTS, SOME, ANY are operators in SQL. 

Answer: True. 

Question: What will be the output of the following query? 

SELECT REPLACE (TRANSLATE (LTRIM (RTRIM ('!! ATHEN!!','!'), '!'), 'AN', '**'),'*','TROUBLE') FROM DUAL; 

Answer: TROUBLETHETROUBLE. 

Question: What is the advantage to use trigger in your PL? 

Answer: Triggers are fired implicitly on the tables/views on which they are created. There are various advantages of using a trigger. Some of them are: 

- Suppose we need to validate a DML statement (insert/Update/Delete) that modifies a table then we can write a trigger on the table that gets fired implicitly whenever DML statement is executed on that table. 

- Another reason of using triggers can be for automatic updation of one or more tables whenever a DML/DDL statement is executed for the table on which the trigger is created. 

- Triggers can be used to enforce constraints. For eg: Any insert/update/ Delete statements should not be allowed on a particular table after office hours. For enforcing this constraint Triggers should be used. 

- Triggers can be used to publish information about database events to subscribers. Database event can be a system event like Database startup or shutdown or it can be a user even like User login in or user logoff. 

Question: How write a SQL statement to query the result set and display row as columns and columns as row? 

Answer: TRANSFORM Count (Roll_no) AS Count of Roll_no 

SELECT Academic_Status 

FROM tbl_enr_status 

GROUP BY Academic_Status 

PIVOT Curnt_status; 

Question: Cursor Syntax brief history 

Answer: To retrieve data with SQL one row at a time you need to use cursor processing. Not all relational databases support this, but many do. Here I show this in Oracle with PL/SQL, which is Procedural Language SQL .Cursor processing is done in several steps:1. Define the rows you want to retrieve. This is called declaring the cursor.2. Open the cursor. This activates the cursor and loads the data. Note that declaring the cursor doesn't load data, opening the cursor does.3. Fetch the data into variables.4. Close the cursor. 

Question: What is the data type of the surrogate key? 

Answer: Data type of the surrogate key is either integer or numeric or number 

Question: How to write a sql statement to find the first occurrence of a non zero value? 

Answer: There is a slight chance the column "a" has a value of 0 which is not null. In that case, you?ll loose the information. There is another way of searching the first not null value of a column: 

select column_name from table_name where column_name is not null and rownum<2; 

Question: What is normalazation, types with e.g.\'s. _ with queries of all types 

Answer: There are 5 normal forms. It is necessary for any database to be in the third normal form to maintain referential integrity and non-redundancy. 

First Normal Form: Every field of a table (row, col) must contain an atomic value 

Second Normal Form: All columns of a table must depend entirely on the primary key column. 

Third Normal Form: All columns of a table must depend on all columns of a composite primary key. 

Fourth Normal Form: A table must not contain two or more independent multi-valued facts. This normal form is often avoided for maintenance reasons. 

Fifth Normal Form: is about symmetric dependencies. 

Each normal form assumes that the table is already in the earlier normal form. 

Question: Given an unnormalized table with columns: 

Answer: The query will be: delete from tabname where rowid not in (select max (rowid) from tabname group by name) Here tabname is the table name. 

Question: How to find second maximum value from a table? 

Answer: select max (field1) from tname1 where field1= (select max (field1) from tname1 where field1<(select max(field1) from tname1); 

Field1- Salary field 

Tname= Table name. 

Question: What is the advantage of specifying WITH GRANT OPTION in the GRANT command? 

Answer: The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user. 

Question: What is the main difference between the IN and EXISTS clause in sub queries?? 

Answer: The main difference between the IN and EXISTS predicate in sub query is the way in which the query gets executed. 

IN -- The inner query is executed first and the list of values obtained as its result is used by the outer query. The inner query is executed for only once. 

EXISTS -- The first row from the outer query is selected, then the inner query is executed and, the outer query output uses this result for checking. This process of inner query execution repeats as many no .of times as there are outer query rows. That is, if there are ten rows that can result from outer query, the inner query is executed that many no. of times. 

Question: TRUNCATE TABLE EMP; 

DELETE FROM EMP; 

Will the outputs of the above two commands differ 

Answer: The difference is that the TRUNCATE call cannot be rolled back and all memory space for that table is released back to the server. TRUNCATE is much faster than DELETE and in both cases only the table data is removed, not the table structure. 

Question: What is table space? 

Answer: Table-space is a physical concept. It has pages where the records of the database are stored with a logical perception of tables. So table space contains tables. 

Question: How to find out the 10th highest salary in SQL query? 

Answer: Table - Tbl_Test_Salary 

Column - int_salary 

select max (int_salary) 

from Tbl_Test_Salary 

where int_salary in 

(select top 10 int_Salary from Tbl_Test_Salary order by int_salary) 

Question: Which command executes the contents of a specified file? 

Answer: START or @. 

Question: What is the difference between SQL and SQL SERVER? 

Answer: SQL Server is an RDBMS just like oracle, DB2 from Microsoft 

whereas 

Structured Query Language (SQL), pronounced "sequel", is a language that provides an interface to relational database systems. It was developed by IBM in the 1970s for use in System R. SQL is a de facto standard, as well as an ISO and ANSI standard. SQL is used to perform various operations on RDBMS. 

Question: What is the difference between Single row sub-Query and Scalar Sub-Query? 

Answer: SINGLE ROW SUBQUERY RETURNS A VALUE WHICH IS USED BY WHERE CLAUSE, WHEREAS SCALAR SUBQUERY IS A SELECT STATEMENT USED IN COLUMN LIST CAN BE THOUGHT OF AS AN INLINE FUNCTION IN SELECT COLUMN LIST. 

Question: What does the following query do? 

Answer: SELECT SAL + NVL (COMM, 0) FROM EMP; 

It gives the added value of sal and comm for each employee in the emp table. 

NVL (null value) replaces null with 0. 

Question: How to find second maximum value from a table? 

Answer: select max (field1) from tname1 where field1= (select max (field1) from tname1 where field1< (select max (field1) from tname1);

Field1- Salary field 

Tname= Table name. 

Question: I have a table with duplicate names in it. Write me a query which returns only duplicate rows with number of times they are repeated. 

Answer: SELECT COL1 FROM TAB1 

WHERE COL1 IN 

(SELECT MAX (COL1) FROM TAB1 

GROUP BY COL1 

HAVING COUNT (COL1) > 1) 

Question: How to find out the database name from SQL*PLUS command prompt? 

Answer: Select * from global_name; 

This will give the data base name which u r currently connected to..... 

Question: How to display duplicate rows in a table? 

Answer: select * from emp 

group by (empid) 

having count (empid)>1 

Question: What is the value of comm and sal after executing the following query if the initial value of ?sal? is 10000 

UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1; 

Answer: sal = 11000, comm = 1000. 

Question: 1) What is difference between Oracle and MS Access? 

2) What are disadvantages in Oracle and MS Access? 

2) What are features & advantages in Oracle and MS Access? 

Answer: Oracle's features for distributed transactions, materialized views and replication are not available with MS Access. These features enable Oracle to efficiently store data for multinational companies across the globe. Also these features increase scalability of applications based on Oracle.