Friday 28 September 2012

Sql Interview Question



1) What is SQL and where does it come from?   Home
Structured Query Language (SQL) is a language that provides an interface to Relational database systems. SQL was developed by IBM in the 1970s for use in System, and are a de facto standard, as well as an ISO and ANSI standard. SQL is often pronounced SEQUEL.
In common usage SQL also encompasses DML (Data Manipulation Language), for Inserts, Updates, Deletes and DDL (Data Definition Language), used for creating and modifying tables And other database structures.

The development of SQL is governed by standards. A major revision to the SQL standard was completed in 1992, called SQL2. SQL3 support object extensions and will be (partially?) Implemented in Oracle8.

2) DDL home
DDL is Data Definition Language statements. Some examples:
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
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command
 Difference between Delete and truncate
1) Delete-DML                                                             Truncate
        -It will mark the records to delete                   -It is DDL
        -We can rollback                                                 -WE CAN'T ROLLBACK
        -We can delete the records by condition         -WE CAN'T APPLY CONDITIONS
                                                            -IF ANY LOCKS ARE THREE ON THE TABLE
                                                                                      THEY WILL RELEASED
-It will releases the space and we can latter we use that space From oracle 8i onwards we can a drop a column
Alter table <Table_name> drop column <column_name>
Alter table <table_name> set unused (last_name)
Alter table <table_name> drop unused columns

All this type of values are stored in user_unused_col_tab table

3) DCL  

DCL is Data Control Language statements. Some examples:

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
SET TRANSACTION - Change transaction options like what rollback segment to use
Savepoint
Rollback: Oracle Issues implicit commit before and after any DDL statement. Even if your DDL statement does not execute then also it issues commit statement
If we issue commit all savepoints will be erased and all locks are released.

4) DML  
SELECT - retrieve data from the a database
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
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
MUTATING TABLE: Mutating Table is a table that is currently being modified by a DML statement. For a         Trigger, this is the table on which the table is defined.
DML Statement: Select, Update, Delete, Insert

Commit->it we issue the commit all savepoints are erased and all locks are released; If we disconnect the database then implicit commit statement will be issued.
Diff between POST and COMMIT:
POST puts the data from client to the server’s Rollback segment whereas COMMIT put the data from rollback segment to the Table.


A transaction is an operation against the database, which comprises a series of changes to one or more tables
A transaction begins when the first executable DML and DDL command is encountered and ends when one of the following occurs
* commit/Rollback/DDL command /log off/machine failure

6Locks                                                                 

Shared/exclusive -When 2 transaction wants to read/write from db at the same time.
1) Table level2) row level
 Row Exclusive locks are obtained when updating, inserting or deleting rows

Duration of locks

) All locks acquired during a transaction are released when the transaction is committed
2) All locks are acquired during a transaction are released when the transaction is rolled back
3) All locks are acquired after a savepoint are released when the transaction is rolled back to the savepoint

Dead lock-It is will come in multi-user environment
When the two users issues update statements then dead lock will come
Dead- 1trans updates EMP and dep
2 trans update dep and EMP
Dead locks are released when commit/rollback statements are issued or logging off

7) How does one code a tree-structured query?                                 
Select LEVEL, EMPNO, ENAME, MGR
          From EMP
        Connect by prior EMPNO = MGR
          start with MGR is NULL;

8.JOINS  
 Def: -A join is used when a SQL query requires data from more than one table or the same table on the database.

TYPES OF JOINS:

1) EQUI-JOIN: a join that is formed as a result of an exact match b/w two columns is called as                 equi-join or simple join or inner join.

2) NON-EQUI-JOIN: - NON-EQUI-JOIN is a join condition containing something other than an equality                     operator.
A join is that is formed based on the comparison operators (except "=") is called non-equi join.

EX: - SQL> SELECT e.last_name, e.salary, j.grade_level
          FROM employee e, job_grades j
            WHERE e.salary
                BETWEEN j.lowest_sal AND j.highest_sal; 

3) SELF-JOIN: A join relates a table to itself is called a self-join.

EX: - SQL> SELECT e.empno, e.ename, m.mgr from emp e, emp m where e.empno=m.mgr;

4) OUTER-JOIN: (+) always at the child side.
           --You can use OUTER-JOIN to also see rows that do not meet the join condition.
             --The outer-join operator is the plus sigh (+).
           --The missing rows can be returned if outer-join operator is used in join condition.

EX: -SQL> SELECT E.EMPNO, D.DEPTNO FROM EMP E, DEPT D WHERE E.DEPTNO (+)=D.DEPTNO;

CONVERSION FUNCTIONS:
                             TO_CHAR, TO_DATE, TO_NUMBER.
DATE FUNCTIONS:
ADD_MONTHS,            LAST_DAY,                            MONTHS_BETWEEN,
NEW_TIME,                           NEXT_DAY,                            ROUND,
SYSDATE,                     TRUNC.

DECODE:
                   Nested IF THEN ELSE
                   SELECT DECODE (ename, ‘Smith’, ’a’, ‘Something’);
GROUP FUNCTIONS:

MAX ()                MIN ()                           COUNT () AVG ()                 SUM ()                 VARIANCE ()
STDDEV ()

GROUP FUNCTION WITH having CLAUSE CONDITION:
NOTE: ALL GROUP FUNCTIONS IGNORE MULL VALUES IN THE COLUMN.

EX: SQL> SELECT job_id, SUM (salary) PAYROLL
           FROM        employee
           WHERE job_id NOT LIKE '%REP%'
           GROUP BY job_id
           HAVING SUM (salary)>1000
           ORDER BY SUM (salary);

A Subquery is a SELECT statement that is embedded in a clause of another SELECT statement.
The inner query or the subquery returns a value that is used by the outer query or the                                  main query.
You can write upto 255 subqueries.

TYPES OF SUBQUERIES:
1.Single-Row subquery: Query that returns only one row from the inner SELECT statement.
          Simple Query--Which fires every time for entire stmt
2.Multiple-Row subquery: Query that returns more than one row from the inner SELECT statement.

CORRELATED SUBQUERY:
SQL*Plus performs a subquery repeatedly once for every row of the main query.
Correlated Subquery -Which fires only once/ per row for entire stmt.
Operators:
Logical Operators:      NOT, AND, OR
Comparison Operators:       NOT IN, LIKE
Set Operators: UNION, UNION ALL, INTERSECTION, MINUS

          - The SET operator combine the result of two or more component queries into one result.
            - Queries containing SET operator are called COMPOUND QUERIES.
            - All SET operators have equal precedence.

1.UNION: All distinct rows selected by either query
 EX:   select job from emp where deptno=10
          union
          Select job from emp where deptno=20
2.UNION ALL: All rows selected by either query, including all duplicates.
EX:    select job from emp where deptno=10
          union all
          Select job from emp where deptno=20

3.INTERSECT: All distinct rows selected by both queries.
EX:    select job from emp where deptno=10
          intersect
          Select job from emp where deptno=20

4.MINUS: All distinct rows that are selected by the first SELECT statement and not selected in the          second SELECT statement.
EX:    select job from emp where deptno=10
          minus
          Select job from emp where deptno=20
They behave like table columns, but are not actually stored in the table.
CURRVAL, NEXTVAL, ROWID, ROWNUM, SYSDATE, LEVEL, USER, UID PARSING:
It checks the Syntax of SQL Statements.

13.INTEGRITY CONSTANTS:
Null, Not Null, Default, Check, Primary Key, Foreign Key

PRIMARY KEY: NOT NULL, UNIQUE, IF U WANT CREATE MORE THEN ONE COLUM WE SHOULD DEFINE AT TABLE LEVEL, IT'S ALSO CALLED COMPOSITE PRIMARY KEY, WE CAN'T CREATE MORE THAN ONE PRIMARY KEY.

UNIQUE:IT WILL NOT ALLOW DUPLICATE VALUES AND IT ACCEPT NULL VALUES CAN BE DEFINED AT THE COLUMN LEVEL AND TABLE LEAVEL
CHECK: IT WILL CHECK WITH COLUMN DEFINATION BEFORE ACCEPT.CHECK CONSTRAINT DEFINES A CONDITION THAT EACH ROW MUST SATISFY REFERENCES TO THE CURRVAL, NEXTVAL, LEVEL, ROWNUM PSEUDOCOLUMNS CALLS TO SYSDATE UID USER, AND USERENV. CHECK CONS. CAN BE DEFINED AT THE COLUMN LEVEL AND TABLE LEVEL

FORIEGN KEY:
- foreign KEYS POVIDE REFERENTIAL INTEGRITY RULES WITHER WITHIN A TABLE OR B/W TABLES
- CAN BE DEFINED AT TABLE LEVEL OR COLUMN LEVEL.A COMPOSITE FORIEGN KEY MUST BE CREATED BY USING THE TABLE-LEVEL DEFINATION
- A FORIEGN KEY IS USED IN A RELATIONSHIP WITH EITHER PRIMARY OR UNIQUE KEY.
      
References - identifies the table and column in the parent table.
    
ON DELETE CASCADE - Deletes the dependent rows in the child table when a row in the parent                                                                                                                                                                                        table is deleted.
ON DELETE SET NULL - CONVERTS DEPENDENT FORIEGN KEY VALUES TO NULL. 
CASCADE CONSTRAINT - The CASCADE CONSTRAINTS clause drops all referential integrity constraints that refer to the primary and unique keys defined on the dropped columns.
The CASCADE CONSTRAINTS clause also drops all multicolumn constraints                                                                                                                                                                                         defined in the dropped columns.

CREATE INDEXES IF U FREQUENTLY WANT TO RETRIEVE LESS THAN 15% OF THE ROWS IN A LARGE                     TABLE
       - INDEX CLOUMNS USED FOR JOINS TO IMPROVE THE PERFORMANCE ON JOINS.
      - DONT USE INDEXES ON TABLES HAVING LESS NO ROWS.
          - IF THE COLUMN HAS NON-UNIQUE VALUES U CAN USE INDEXES ON THESE COLUMNS.
          - DONT USE INDEXES IF THE DATATYPE IS LOB, CLOB & BLOB.
          - IF THE TABLE IS read only WE CAN CREATE MORE INDEXES ON THE TABLE.
          - WE CAN'T CREATE INDEXES ON VIEWS.
       *- INDEXES ARE LOGICALLLY & PHISICALLY INDEPENDENT OF DATA
       *- IF THE INDEX IS DEVLOPED ALL APLLICATIONS CONTINUE TO FUNCTION

Index is an ordered list of contents of a column or group of columns in a table. Index created on a single table Simple Index and which is created on multiple tables is called Composite Index.

CREATE INDEX Index_Name ON Table_Name (Column_Name);
DROP INDEX (Index_Name);

 Cluster is a method of storing tables that are intimately related and are often joined                
          Together into the same area on disk
 
          - SYNONYM IS AN ALIAS FOR A TABLE, VIEW, SEQUENCE & PROGRAM UNIT.
          - A SYNONYM IS A NOT A SCHEMA OBJECT, IT WILL REFER SCHEMA OBJECT.
          - MASK THE REAL NAME AND OWNER OF A SCHEMA OBJECT.
          - PROVIDE PUBLIC ACCESS TO SCHEMA OBJECT.
          - PROVIDE LOCATION TRANSPARENCY FOR TABLES, VIEWS, OR PROGRAM UNITS IF A REMOTE DATABASE.
          - SIMPLIFY THE sql STATEMENTS FOR DATABASE USERS
22
PUBLIC SYNONYM & PRIVATE SYNONYM

17.VIEWS:
      - VIEW IS A LOGICAL TABLE BASED ON A TABLE OR ANOTHER VIEW.
      - VIEW CONTAINS NO DATA OF ITS OWN BUT IS LIKE A WINDOW THROUGH WHICH DATA FROM TABLES CAN                   BE VIEWED OR CHANGED.          
      - VIEW IS STORED AS SELECT STATEMENT IN THE DATA DICTIONARY.  
WHY USE VIEWS:
          - TO RESTRICT DATA ACCESS
          - TO MAKE  QUERIES EASY.
          - TO PROVIDE DATA INDEPEMDENCE
          - TO PRESENT DIFFERENT VIEWS OF THE SAME DATA.

SIMPLE VIEW:  DERIVES DATA FROM ONLY ONE TABLE, CONTAINS NO FUNCTIONS OR GROUPS OF DATA, WE CAN  PERFORM dml OPERATIONS THROUGH THE VIEW.

COMPLEX VIEW: DERIEVE DATA FROM MANY TABLES, CONTAINS FUNCTIONS OR GROUPS OF DATA, DOES NOT ALWAYS ALLOW dml OPERATIONS THROUGH THE VIEW.


NOTE: - U CAN'T CREATE INDEXES ON VIEWS.
      - U CAM CREATE A VIEW WITHOUT A TABLE USING force OPTION.
      - CAM CREATE with check option & with read only.
      - We can modify, drop view.
 SYNTAX: CREATE OR REPLACE force/noforce VIEW viewname alias subquery with check option
                   Constraint with read only.
If we use NOT NULL constraint in the base table without default values then we can't insert the row in the base table using the view
 If we use decode function in creating the view we can't update the row in the base table

If we use these words
1) Distinct, avg, count, max, min, stddev, sum, union, union all, intersect, minums, rownum, start with prior to the we can't update the base table through view
If we rename the table then the view become invalid
But one can add the columns and the columns must not be not null columns in the base table

18.Table:
One can rename the table. If we rename the table then all views, synonyms, proceudres, triggers, functions become invalid

If we drop the table all the indexes are dropped
All rows corresponding cluster table are deleted from the blocks of the cluster

We can decrease the length of the column if the data is not there in the table.
I one can change the datatype at any point of time

If we drop the column from the base table if the view is based on that column then the view become invalid.

TO add a not null column to a table which has already some records -
Alter table a
Add (b number default 1 not null)

Portioned table

If we want to store the data of one table depending upon the range in different blocks. By using this we can reduce the network traffic. Table partitioning divides table data between two or more table spaces and physical data file on separate disk.
     Select * from emp partion (p1)

19.TUNING SQL Statements:  
Using TK_PROF, EXPLAIN PLAN.

If you have a system that is performing badly, a good way to identify problem SQL statements is to trace a typical user session and then use TkProf to format the output using the sort functions on the tkprof command line.

Explain plan is a representation of the access path that is taken when a query is executed within Oracle.
1.      SET TIME ON;
2.      ALTER SESSION SET SQL_TRACE = ‘TRUE’;
3.      Then run the required program unit
4.      Sql_trace file will be created in User_dump_dest (dir)
5.      Since this trace_file is in the binary format, run tkprof
6.      $ Tkprof <trace_file.trc> <output_file.txt> [options]
7.      $ Tkprof <trace_file.trc> <output_file.txt> [EXPLAIN=user/password]
TKPROF allows you to analyze a trace file to determine where time is being spent and what query plans are being used on SQL statements. Tkprof is an executable that 'parses' Oracle trace files to produce more readable output. Remember that all the information in TkProf is available from the base trace file.
8.      Elapsed time/num of rows * 1000 --> 'X' Mille Second.

To discover the execution plan for a select statement. The explain plan statement is most often used from sql* plus. Before that you must create the <plan table> to hold the results
Find INDEXES is being used:
                                      By using EXPLAIN PLAN.
                                      Output is put into PLAN_TABLE.
To diagnosing performance problems on running systems.
To know
No of times the sql statement executed.
Total CPU and elapsed time used by the statement
Total no of physical reads trigger by the statement
Total no of records processed by the statement
Total no of the logical reads trigger by the statement

Hints:
They can be placed into your Sql statements to force the optimizers to utilize a particular execution path for absolute best performance.

/*+ ALL_ROWS */
/*+ FIRST_ROWS */
/*+ CHOOSE */
/*+ HASH_SJ */àWhen using subquery after EXISTSàIt improves the response time.



                       - Materialized views provide indirect access to table data by storing the                                                                                             results of a query in a separate schema object.
                       - Another name for materialized view is SNAPSHOT.
                       - MATERIALIZED VIEW CAN BE STORED IN A SAME DATABASE OR DIFF DATABASE.
                       - DATA CAN BE REPLICATED USING MATERIALIZED VIEW.E
                   - It is used in a DISTRIBUTED ENVIRONMENT.
                   - It guarantees that no matter what type of system or network failure might                   occur a distributed transaction either commits on all involve nodes or rollback                   on all involve nodes to maintain data consistency.

          - It means that each database participating in a distributed database is administered           separately and independently from other database.
          - The first step of recovery is to ROLL-FORWARD i.e. reapply to the data files all of the           changes that are recorded in the REDO-LOG FILE.
Rolling Forward -To reapply to Data file to all changes that are recorded in Redo log file

Due to which data file contains committed & uncommitted data.
Forward Declaration-To declare variable and procedures before using it.
2- Tier Arch. Disadv-When Business Rule changes.

11.CAN U SELECT PROCEDURE FROM SQL STATEMENT?
Ans: No we can't select procedure from select statement, but we can select SQL FUNCTIONS AND
          USER DEFINED FUNCTIONS.
How does one eliminate duplicates rows from a table?
ROWID:
ROWID is the fastest way to locate records.
ROWID Uniquely identifies records.

Choose one of the following queries to identify or remove duplicate rows from a table
 Leaving unique records in the table:
Method 1:

   SQL> DELETE FROM table_name A WHERE ROWID > (
     2    SELECT min (rowid) FROM table_name B
     3    WHERE A.key_values = B.key_values);

Method 2:
   SQL> create table table_name2 as select distinct * from table_name1;
   SQL> drop table_name1;
   SQL> rename table_name2 to table_name1;

Method 3: (thanks to Kenneth R Vanluvanee)
   SQL> Delete from my_table where rowid not in (
   SQL> select max (rowid) from my_table
   SQL> group by my_column_name);

Method 4: (thanks to Dennis Gurnick)
   SQL> delete from my_table t1
   SQL> where exists (select 'x' from my_table t2
   SQL> where t2.key_value1 = t1.key_value1
   SQL> and t2.key_value2 = t1.key_value2
   SQL> and t2.rowid      > t1.rowid);

28.LOB Types:                 
          Large Objects Datatypes. Lets us store blocks of unstructured data (text, graphic images,                    video files, audio files) of upto four Gb of size.
          Bfile: BFILE datatype stores large Binary Objects in OS files outside the database.
          Blob: BLOB datatype stores large Binary Objects in the database
          Clob: CLOB datatype stores large blocks of single-byte Character data in the database.
         
          It is a version control utility. PVCS change manager for Oracle can identify the location                                  and impact of changes in Oracle Applications.

30. TOAD

          TOAD is a powerful tool that makes PL/SQL development faster, easier and simpler.

31. INIT.ORA:                                                                                             
          It is a System Parameter

32. Normalization

          It's a technique thru. Which we can design the DB.
          During normalization dependencies can be identified which can cause pbs during deletion &
          Updation .It is used in simplifying the structure of table.
          1NF-Unnorma;ised data transfer to normalized form.
          2NF-Functional dependencies can be find out & decompose the table without loss of data.
          3NF-Transist dependencies, every non-key attribute is functionally dependant on just PK.
          4NF(BCNF)-The relation, which has multiple candidate keys, then we have to go for BCNF.
Denormalization-
          At the same time when information is required from more than one table at faster rate                   then  it is wiser to add some sort of dependencies.

27.FAQ’S

1.      Which command in SQL*Plus is used to save the query output to a file?
          ANS: SPOOL

2.      How would you extract a SQL statement in the SQL buffer of SQL*Plus?
          ANS: Enter a SLASH (/)

3.      What is the default display length of the DATE Datatype column?
          ANS: Eight

4.      Which Clause in a query limits the rows selected?
          ANS: WHERE

5.      Which SQL*Plus command always overwrites a file?
          ANS: SPOOL

6.      Which single-row function could you use to return a specific portion of a character?
          ANS: SUBSTR

7.      Which of the following is not a Group Function? (AVG, COUNT, LEAST, STDDEV, VARIANCE)
          ANS: LEAST

8.      When using multiple tables to query information, in which Clause do you specify the table    names?
          ANS: FROM

9.      What are the special characters allowed in a table name? (&, #, @, $)
          ANS: #, $

10.    What is the default length of a CHAR and Number   Datatype column, if no length is     specified in the table definition?
          ANS: 1 and 9

11.    At a minimum, how many join conditions should be there in the WHERE Clause to avoid a Cartesian join if there are Three Tables in the FROM Clause?
          ANS: 2
          There should be at least (n - 1) join conditions when joining (n) tables to avoid a       Cartesian join

12.    A view can only be used to query and update data, you cannot Insert into or delete from a     view. True or false
          ANS: FALSE

13.    Which option is not available in Oracle when modifying tables? (Add new                              Columns, Rename an Existing Column, Drop an Existing Column) Rename an Existing Column
          ANS: You cannot rename an Existing column using ALTER TABLE

14.    EDIT SAVE FILE SQL*PLUS Default:
          ANS: AFIEDT.buf

15.    Eliminate Duplicate Rows in a Table:
          ANS: ROWID (50).

16.    Eliminate Duplicate Rows in a BASE TABLE BLOCK:
          ANS: Create a SELECT DISTINCT VIEW on the base table

17     Set Transaction -To set a current transaction online offline

18     Optimization-Use of index (HINT)

19     Object Privilege - On a particular object- I/U/D/Exec

20     System Privilege -Entire collection object -C/A/D
         
21       Profile -To control system resources like memory, disk space, and CPU time.

22     Role -Collection of privileges.

23     Type of segment- Rollback, Temp, Data, Index
24     Snapshot-It's a read only table, to improve efficiency of query, which referred remote db, therefore reduce remote traffic.

25.       Describe Oracle database's physical and logical structure?
            Physical: Data files, Redo Log files, Control file.
            Logical: Tables, Views, Table spaces, etc.

26.Can you increase the size of a table space? How?
Yes, by adding datafiles to it.

27.Can you increase the size of datafiles? How?
No (for Oracle 7.0)
Yes (for Oracle 7.3 by using the Resize clause ----- Confirm!!).

28.What is the use of Control files?
Contains pointers to locations of various data files, redo log files, etc.

29.What is the use of Data Dictionary?
Used by Oracle to store information about various physical and logical Oracle structures e.g.
Tables, Tablespaces, datafiles, etc

30.What are the advantages of clusters?
Access time reduced for joins.

31.What are the disadvantages of clusters?
The time for Insert increases.

32.Can Long/Long RAW be clustered?
No.

33.Can null keys be entered in cluster index, normal index?
Yes.

34.Can Check constraint be used for self referential integrity? How?
Yes. In the CHECK condition for a column of a table, we can reference some other
 Column of the same table and thus enforce self referential integrity.

35.What are the min. extents allocated to a rollback extent?
Two

36.What are the states of a rollback segment?
      The various states of a rollback segment are:
      ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and INVALID.

37.What is the difference between unique key and primary key?
     Unique key can be null; Primary key cannot be null.

38.An insert statement followed by a create table statement followed by rollback? Will the rows be inserted?
No.

39.Can you define multiple savepoints?
Yes.

40.Can you Rollback to any savepoint?
Yes.

41.What is the maximum no? Of columns a table can have?
      254.

42.How many rows will the following SQL return?
Select * from emp Where rownum < 10;
9 rows

43.How many rows will the following SQL return?
Select * from emp Where rownum = 10;
No rows

44.Which symbol precedes the path to the table in the remote database?
@

45.Are views automatically updated when base tables are updated?
Yes

46.Can a trigger written for a view?
No

47.A table has the following data: [5, Null, 10]. What will the average function return?
7.5

48.Is Sysdate a system variable or a system function?
System Function

49.Consider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2. Suppose at this point we issue a rollback and again issue a nextval. What will the output be?
3

50.What is the result of the following SQL?
Select 1 from dual
UNION
Select 'A' from dual;
Error

51.Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed.
Yes, database trigger would fire.

52.Can you alter synonym of view or view?
No

53.Can you create index on view
No.

54.What is the difference between a view and a synonym?
Synonym is just a second name of table used for multiple link of database. View can be created with many tables, and with virtual columns and with conditions. But synonym can be on view.

55.What is the difference between alias and synonym?
Alias is temporary and used with one query. Synonym is permanent and not used as alias.

56.What is the effect of synonym and table name used in same Select statement?
Valid

57.What's the length of SQL integer?
32-bit length

58.What is the difference between foreign key and reference key?
Foreign key is the key i.e. attribute which refers to another table primary key.
Reference key is the primary key of table referred by another table.

59.Can dual table be deleted, dropped or altered or updated or inserted?
Yes

60.If content of dual is updated to some value computation takes place or not?
Yes

61.If any other table same as dual were created would it act similar to dual?
Yes

62.For which relational operators in where clause, index is not used?
<>, Like '%...' is NOT functions, field +constant, field || ''
63.Assume that there are multiple databases running on one machine. How can you switch from one to another?
Changing the ORACLE_SID

64.If you insert a row in a table, then create another table and then say Rollback. In this case will the row be inserted?
Yes. Because Create table is a DDL which commits automatically as soon as it is executed. The DDL commits the transaction even if the create statement fails internally (e.g. table already exists error) and not syntactically.

65.What are the various types of queries?
Normal Queries
Sub Queries
Co-related queries
Nested queries
Compound queries

66.Which of the following is not a schema object: Indexes, tables, public synonyms, triggers and packages?
Public synonyms

67.What is the difference between a view and a snapshot?

68 Varrays:
They allow us to associate a single identifier with an entire collection. This association let you manipulate the collection as a whole and reference individual elements easily.

69.Create database link <link name> Test_link
Connect to <user name > apps
Identified by <passwd> fnd
Using <connect string> fintst

70 How do you tell the difference between a regular order and a return order?
      In so_headers you identify by order_category which will be RMA for
     Returns and R for Regular

71 the difference between partly available and needs recovery?