Oracle SQL interview questions and answers

      The following SQL Interview Question and Answers created based on my person experience, I hope it will use for you while you are facing SQL interview, These are very frequently asking question and answers (FAQS) in SQL interview.
 

1.     What is Normalization?

Ans: Normalization is the process of organizing the tables to remove the redundancy. There are mainly 5 Normalization rules.

1^st Normal Form: A table is said to be in 1^st normal form when the attributes are atomic and there is no repeating groups

2^nd Normal Form: A table is said to be in 2^nd Normal Form when it is in 1^st normal form and all the non-key columns are functionally dependant on the primary key. .

3^rd Normal Form: A table is said to be in 3^rd Normal form when it is in 2^nd normal form and all non key attributes not dependant transitively.

4^th Normal Form: A table is said to be in 4^th normal form when it is in 3^rd normal form and has no multi -valued dependencies.

5^th Normal Form: A table is said to be in 5^th normal form when it is in 4^th normal forma and every join dependency for the entity is a consequence of its candidate keys.

2.     What is the Purpose of the Distinct Clause in SQL?

     

    Distinct Clause allows you to display unique from the result set. This can be used with only select statements.

3.     What are the DDL Commands and the Purpose of these commands?

           DDL (DataDefinition Language) command is used for defining the structure of the Data. DDL Statements are auto commit.

     CREATE - to create objects in the database

     ALTER   - alters the structure of the database

     DROP    - delete objects from the database

     TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

     COMMENT - add comments to the data dictionary

     RENAME    - rename an object

4. What are the DML commands and Use Of these commands?

                      

          DML (DataManipulation Language) statements are used for managing data within schema objects.

     INSERT - insert data into a table

     UPDATE - updates existing data within a table

     DELETE - deletes all records from a table, the space for the records remain

·        DML Statements can be roll backed.

·        DML Statements can’t be roll backed When DDL Statement Executed immediately after the DML statement.

 

5. What are the DCL Commands and purpose of it?

        DCL is Data Control Language statements.

     GRANT - gives user's access privileges to database

     REVOKE - withdraw access privileges given with the GRANT command         

6. What are the TCL Commands and Purpose of it?

  

     (Transaction Control Language) Manages the changes made by DML statements. These commands allow statements to be grouped together into logical transactions.

     COMMIT - save work done

     SAVEPOINT - identify a point in a transaction to which you can later roll back

     ROLLBACK - restore database to original since the last COMMIT

7. What is the Difference between TRUNCATE and DROP?

        Truncate Delete the entire data from the table and keeps the structure.

                TRUCATE  TABLE table name

        DROP drops the table structure also.

                 DROP TABLE Table name

8. What is the Difference between TRUNCATE and DELETE?

TRUNCATE	DELETE
It’s  DDL Statement	It’s DDL Statement
Auto commit, we can’t retrieve the data back	we can Retrieve the data back
We can delete entire rows (No condition)	we can delete rows conditional wise

9. What is NULL?

       NULL in Oracle is an Absence of information. A NULL can be assigned but not evaluated by it self also.

·        NULL not equal to null

·        NULL Can not be Not equal to NULL (Neither Equal Not Not Equal)

·        NULL Does not equal to empty String or doe not equal to ZERO.

10. How can we sort the rows in SQL?

        We can Sort the rows Using ORDER By clause.

-    ASC : Ascending Order is Default order

-    DESC : Sorting in Descending

 Null Values Displayed At last in ascending Order

 11. How can we convert NULL Value?

         Using NVL Function we can convert Null Value to an Actual Value.

          NVL(exp1, exp2).

                If exp1 is NULL then it returns the exp2.

12. Purpose and Syntax of NVL2 ?

       Convert the Null values in to  Actual Value.

      NVL2 (Exp1,exp2,exp3). If exp1 is NULL it returns the exp3 . if exp1 is not null then it returns the exp2.

13. When Cartesian product formed?

  A join condition Omitted

  A Join condition Invalid

  To Avoid Cartesian Product, always include Valid Join condition 

14. What type of joins using in SQL?

1) EQUI JOIN: The equi join is normally used to join tables with primary key foreign key relation ships.

2) NON-EQUI JOIN:
A join condition where any relation operator other than "=" equal to
operator is used.

3) OUTER JOIN:

In EQUI JOIN rows that does not satisfy specified condition would not be displayed. Example: consider EMO and DEPT table as Example

 DEPTNO 40 is not displayed in the Equi example because there are no employees in it. If we want to diplay its detail also then we have to use OUTER JOIN.Otherwise OUTER JOIN is imilar to EQUI JOIN except for the difference it uses outer join (+) operator. (A plus within parenthesis) towards the side not having required data. Outer join operator will substitute null values when there are no values available.

SQL> SELECT E.DEPTNO,ENAME,DNAME FROM EMP E , DEPT D
2 WHERE E.DEPTNO (+) = D.DEPTNO;

4) SELF JOIN:
When we join a table to itself it is called self join.To join a table itself means that each row of the table is combined with itself and with every other row of the table. The self join can be seen as join of two copies of the same table.

SQL> SELECT E.ENAME,M.ENAME FROM EMP E,EMP
WHERE E.MGR=M.EMPNO;

15. What are the Group Functions?

          Group Functions Operate on Sets of rows to give one result per group. The types of group functions

              AVG,COUNT,MAX,MIN,SUM,STDDEV,VARIANCE

·        All columns in SELECT List that are not in group functions must be in the GROUP BY clause.

16. Can you Use Group functions in the Where Clause?

   

           NO, We Can’t.

17. How can we restrict the Group Results?

    

           Using HAVINGClause.  We can’t  use WHERE for these results.

18. What is difference Between SUBQUERY and CORRELATED SUBQUERY?

         SUBQUERY :
           A query within another quey. A select statement whose output is substituted in the condition of another select statement .(A query is a statement written for returning specific data). The subquery is executed only once. A subquery is enclosed in parenthesis.

EX: SQL> SELECT ENAME FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP
WHERE ENAME = 'SMITH');

CORRELATED QUERY:
          In a correlated subquery the table used in outer query refers to the table used in the inner query. The correlated subquery is executed repeatedly once
for each row of the main query table.

Query to diplay name of highest salary taker.

SQL> SELECT EMPNO, ENAME FROM EMP A
WHERE 1 > ( SELECT COUNT(*) FROM EMP B
WHERE A.SAL < B.SAL)

19. What are the Multiple-Row comparisons Operators?

        IN      :EQUAL to any member in the list.

        ANY   : Compare value to each value returned by the sub query

        ALL    :Compare Value to Every value returned by the sub query.

20. You are updating the table, you ask some another user to logon to database to check your changes before you issue the commit command ? Can he see the changes done by you?

            Another user can’t see the the changes done by you until you have given commit.

21. What is MERGE Statement do?

           Provides the ability to conditionally update or insert data into a database table.

           Performs update if the row exists and an insert if it is a new row.

22. What is the Use of SAVEPOINT?

          A SAVEPOINT is a marker within a transaction that allows for a partial rollback. As changes are made in a transaction, we can create SAVEPOINT to mark different points within the transaction. If we encounter an error, we can rollback to a SAVEPOINT or all the way back to the beginning of the transaction.

Ex :  Insert Statement

         

       SAVEPOINT A

 

       UPDATE Statement

       SAVEPOINT B

      DELETE Statement

      SAVEPOINT C

  Roll Back to SAVEPOINT B (means it roll backs to till UPDATE statement)

23. What is the Use of ALTER Statement?

     
          To Add new column

          To modify the datatype and size of the existing column

           To Drop a column

24. What are the Difference between UNION and UNION ALL?

       UNION Query displays the Unique rows ( No duplicate rows)

       UNION ALL Query displays the  Duplicate rows also.

25. If you a Table A, You want to create table B  having the same fields in TABLE A ? That means you have to copy only structure not the data?

 CREATE TABLE TABLEB AS (SELECT * FROM TABLE A where 1=2);

26. What is Synonym?

            A synonym is an alternative permanent name for objects such as tables,views,sequences,storedProcedures

27. What is view?

           A View is a virtual table, it does not physically exist rather, it is created by a query joining one or more tables.

28. Can we Update the Data in View?

          A view is created by joining one or more tables. When you update record(s) in a view, it updates the records in the underlying tables that make up the view.

So, yes, you can update the data in a view providing you have the proper privileges to the underlying tables.

29. What is Sequence?

            Sequence is for generating auto number field. This can be useful when you need to create a unique number to act as primary key.

Syntax:

    CREATE SEQUENCE sequence_name
    MINVALUE value
    MAXVALUE value
    START WITH value
    INCREMENT BY value
    CACHE value;

30. How do we set the LASTVALUE value in an Oracle Sequence?

     You can change the LASTVALUE for an Oracle sequence, by executing an ALTER SEQUENCE command.

31. What is pseudo columns ? Name them?

              A pseudocolumn behaves like a table column, but is not actually stored in the table.  You can select from pseudocolumns, but you cannot insert, update, or delete their values.  This section describes these pseudocolumns:

    * CURRVAL

    * NEXTVAL

    * LEVEL

    * ROWID

    * ROWNUM

Rowid

Rowid is pseudo column that uniquely identifies a row with in the table but not with in the database.

It is possible for two rows of different tables stored in the same cluster have the same row id.

Connect By Prior

A condition that identifies the relationship between parent rows and child rows of the heirarchy.

Level

For each row returned by heirachical query the level pseudo columns returns 1 for root row , 2 for child row of the root and so on.

32. How many columns can table have?

   The number of columns  in a table can range from 1 to 254.

33. How many rows and Columns in DUAL table?

          One Row and One Column Only

34. What is the Use of CASE and DECODE?

          CASE and DECODE statements Both perform procedural logic inside a SQL statement without having to resort to PL/SQL.

           Syntax:

           DECODE (F1,E2,E3,E4)  { If  F1=E2 Then E3 else E4}

           Syntax:

             CASE 

              WHEN E1 THEN E2     {If E1 True E2 Else E3   CASE evaluated the Expression only once with

                   ELSE E3                                       that result values be compared}

              END

             

·        It is Best to use CASE Statement when comparing ranges or more complex logic

    

35. What is Inline View?

      

       SQL Statement in the FROM clause of SQL statement called Inline View. Oracle treats the data set that is returned from the inline view as if it were a table.

This is not a schema Object.

A common use for inline views in oracle sql is to simplify the complex queries by removing join operations and condensinfg several separate queries into single query.

        SELECT * from (SELECT * from table);

36. What is the Purpose of Index?

          SQL indexes are used because they can provide the following benefits / functions:

     Rapid access of information

     Efficient access of information

     Enforcement of uniqueness constraints

         CREATE INDEX (Index_name) on TABLE_NAME(Field1,field2);

37.  What are Constraints? And what are the constraint Types?

                  

·        Constraints Enforced rules at the table level.

·        Constraints prevent the deletion of a table if there are decencies.

Following are the Constraint Types

NOTNULL:

            Ensures the null values are not permitted for the column. Defined at column Level.

           

           CREATE TABLE Tablename( Empname VARCHAR2(10) NOTNULL)

UNIQUE:

            Not allow already existing value

            Is defined either table level or column level

        

            CREATE TABLE  T1( X1 NUMBER,

                                         X2 VARCHAR2(10),

                                        CONSTRAINT  x2_UK UNIQUE(X2))

PRIMARY KEY:

            Doesn’t allow  Nulls and already existing values.

                        CREATE TABLE  T1( X1 NUMBER,

                                         X2 VARCHAR2(10),

                                        CONSTRAINT  x2_UK PRIMARY KEY(X2))

FOREIGN KEY:

              Foreign Key defines the column in the child table at table constraint level.

                                  CREATE TABLE  T1( X1 NUMBER,

                                         X2 VARCHAR2(10),

                                        CONSTRAINT  x2_UK FOREIGN KEY(X2)

                                       REFERENCES TABLE2(field2))

CHECK

           Defines a condition that each row must satisfy

             CONSTRAINT emp_salary  CHECK(SAL>0)  

  38. What is the Purpose ON DELETE CASCADE?

                Deletes the dependent rows in the child table, when a row in parent table is deleted.

39. How can you view the constraints?

                User_constraints table

40. Can we perform the DML Operations On View?

·        You can perform DML operations on Simple view (selecting view from one table and also all not null columns selected).

              

·        If view is complex View and View contains the following then we can’t modify the view

                     --GROUP Functions

                     -- A Group By clause

                     -- DISTINCT Keyword

                     -- Not null columns in Base table that are not selected by View

41. How to deny the DML operation on simple View?

           CREATE a view with ‘WITH READ ONLY’ option

42. When to create an Index?

       You should create an index if :

·        A Column contains a wide range of values

·        A Column contains a large number of null values

·        One or more columns frequently used together in join condition 

·        Table is large and most queries expected to retrieve less than 2 to 4% of the rows.

43. When Not create an Index?

        

You should not create an index if:

     TABLE is Small

     The columns are not often used as a condition in the query

     The table is updated frequently

44. What is Functional Based Index?

         A functional Based index is an index based on expression

         CREATE INDEX ind_name table_name (UPPER(field_name))

45. What is the result of the following Command?

         SELECT 1 FROM DUAL

         UNION

         SELECT ‘A’ FROM DUAL

       Error : Expression Must have the same datatype as corresponding expression.

46. What is the difference between alias and Synonym?

         Alias is temporary and used in one query. Synonym is Permanent aliasing

47. Can Dual table be deleted or dropped or altered or inserted?

          YES,we can do

48. What Is the result for the following queries?

       1) SELECT * from emp where rownum  < 3

       2) SELECT * from emp where rownum =3

1)  2 rows selected

2)  No rows selected

                  

49. Can we create index on View?

     We can’t create index on view.

50. How to eliminate the duplicate rows?

         Using ROWID we can delete duplicate rows.

        

         DELETE FROM EMPMASTER A WHERE A.ROWID> (SELECT MIN(B.ROWID) FROM EMPMASTER B WHERE A.EMPNO=B.EMPNO);

51. How can we find the nth salary in the table?

         SELECT DISTINCT (A.SAL) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (B.SAL)) FROM EMP B WHERE A.SAL<=B.SAL)

   

52. What is the ROWID?

          ROWID is pseudo column. It uniquely identifies a row with in a table, but not with in the database.

53. What is the ROWNUM?

          ROWNUM is pseudo column to limit the number of returned rows. This behaves like a table column but is not actually stored in tables.

          

54. What is the difference between Group by and Order by?

          Group by is used to group set of values based on one or more values.

           Order by is to sort values either in ascending or descending order.

55. How Many CODD rules Oracle satisfies?

          Out of 12 , 11 rules Oracle satisfying .

56. What is the difference Between Primary Key and Unique Key?

     

Primary Key	Unique Key
You can have only one primary key in a table	You can have more than one Unique key in a table
Primary disallows the duplicates and Nulls also	Unique key disallows only duplicates, it accepts the Nulls.

You May Like:

PL/SQL interview questions and answers

Oracle Interview Questions and Answers - FAQs   

Oracle Reports Interview Questions and Answers

Oracle Forms Interview Questions and Answers  

SQL Practice Queries