Oracle SQL Faqs

1.      What is Where clause?

A.      Where clause is used to compare the record values. It is used to filter the record in SQL statement for the specific condition

2.      What is Group by Clause?

A.    Group by clause is used to devide the rows into similar groups in a table.

3.      What is having clause?

A.      Having clause is used to display the specific group of records. This is used to filter the groups of records.

4.      What is set operators?

A.      Set operators are Union, Unionall, Intersect and Minus.

5.      What are special operators?

A.    Special operators are IS, IN, NOT IN, LIKE, NOTLIKE, BETWEEN, NOT BETWEEN, EXISTS & SOUNDEX.

6.      What are the constraints?

A.    Constraints are there types Entity, Domain and Referential Constraints.

Domain: Not Null and Check Constraints.

Entity: Primary Key and Unique.

Referential: Foreign Key and Reference Key.

7.      What is Sub query and Co-related sub query?

A.      Sub query: The Main query will depend the sub query results. The sub query will execute first then the main query will execute.

Co-related sub query: The sub query will depend the main query results. The main query will execute first then the sub query will execute.

8.      What is joins? What are they?

A.      1. Self join: We will compare the records with in single table.

       2. Eque join: We can compare the records with equal condition only.

       3. Non eque join: We can compare the records with out equal condition.

       4. Inner join: Eque join and Non eques come under the Inner join.

                   5. Outer join: We will make the condition with (+) operator after the column name before equal or after equal. This join will use only equal join only. Ex: Suppose I have two tables T1 & T2. In T1 having 8 records and T2 having 5 records. These two tables’ column values are matching only 3 records. I want to display that all records with the 3 records in table T1. At that time we will put the Outer join condition Ex: where T1.a = T2.a(+)

   9.What is a view? What are they?

A.   A view is a logical window it is logically related to database tables, Synonyms and Views There are three types.

      1. Simple view

      2. Complex view

      3. Materialized view

      1. Simple view: We can create a view by using single table is called simple view it can’t hold the data. When we are not using primary key columns in the view we can’t do DML operations other wise we can do any DML operations by using simple view.

      2. Complex view: We can create a view from more then one table or more then views is called complex view. It can’t hold the data. We can’t do any DML operations when we will use all primary key columns in our view we will do DML operations. Other wise we can’t do any DML operations. By using Instead of trigger we can do any DML operations from these views.

      3. Materialized view: We can create a view from one or more database or more then one table. It can hold the data. It is like a snap short. We can’t do any DML & DDL except Truncate and Drop comment. By specifying refresh statement in this view we can refresh data from data base.

10. What is Force view?

A.  We can create view with out existing columns or tables by using force.

11. What is Index?

A.   Index is a database object. It is used to get the data from the database fastly. It is used to improve the performance of the getting the data from the database. Index used the binary search to get data from database. They are mainly two types that are: Column level Index and Table level index. Column level index are when we are creating table at the time we will specify or when we are specified primary key or unique is called column level index. Table level index are we are create the index externally mean after creating the table we will create the index.     

      1. Simple Index: It is called a primary key. We will create index on primary key column.

      2. Composite Index: We can create index on multiple columns.

      3. Unique Index: It is called a unique key. We will create index on unique key column.

      4. Function based index: We will create index on function based column like UPPER (column name).

      5. Bitmap Index: We will create the index on bitmap image column.

      6. Force Index: We will create index temporarily for the sql statement only.

       Ex:  select /*+ index  <table_name>(column_name)*/ remaining columns and from statement.

12.  What is performance tuning?

     A.   Performance tuning is used to improve the performance of the statement or object. Mainly we will use to get the execution plan of the statement or object. There are mainly two types:

            1. Explain Plan: It gives the execution plan of the single sql statement.

            Step1: EXPLAIN PLAN SET STATEMENT_ID = ‘<id name>’

                       INTO PLAN_TABLE FOR <sql statement>.

                        Step2: The explain plan statement will be generated into plan_table if plan_table is not available in your system we can run the UTLXPLAN.sql file the plan_table will create.

            Step3: SELECT <column names> FROM PLAN_TABLE WHERE STATEMENT_ID = ‘<statement_id>’

                        It will show the explain of our sql query.

2. Sql Trace: It gives the execution plan of the multiple statements. We can get a trace file from sql trace, report level and form level. In sql level

Step1: ALTER SESSION SET SQL_TRACE = ‘TRUE’;

Step2: We will execute our queries.

Step3: ALTER SESSION SET SQL_TRACE = ‘FALSE’;

            Step4: Internally the trace file is generated but it is not a readable mode. We will convert the trace file into readable mode by using TKPROF. We will find our trace file.

            Step5: SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘user_dump_dest’; it will show the path of the trace file.

            Step6: We will go to the path and find out our trace file according to the date and time.

            Step7: Go to DOS mode and go to that specified path

            TKPROF <tkprof file name> <destination file name>

            Step8: It will convert trace file to readable mode.

                 

            13. What   are Pseudo columns? What are they?

A.   Pseudo columns behave like a table columns but we are not create this columns they are:

      1. Rowid: It wills genarete when we are inserting the record into table.

      2. Rownum: It will populate when we are executing the select statement.

      3. Next_Val: It will use when we are calling the next value from the sequence.

      4. Curr_val: It will use when we are calling the current value from the sequence.

      5. Level:

14. What is sequence? Explain?

A.   Sequence is used to generate primary key values. Syntax: CREATE SEQUENCE <sequence name> START WITH <no> INCREMENT BY <no> MIN VAL<no> MAX VAL<no> [CYCLE/NOCYCLE] [CATCH/NOCATCH].

15. What is a synonym?

A.  Synonym is a alternative name of the database tables or objects.

16. What is inline view?

A.  Inline view is a sub query. It is not a database object. We will define a view temporary in a select statement. We will write the select statement in from clause is called inline view. When we are given alias name is called a view name.