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.

5 Transaction->

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;

9.FUNCTINS->

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);

10.SUB QUERIES

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

11.SET OPERATORS:

- 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

12.PSEUDO COLUMNS:

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.

14.INDEXES:

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);

15.CLUSETER:

Cluster is a method of storing tables that are intimately related and are often joined

Together into the same area on disk

16.SYNONYM:

- 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

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.

20.Explain plan

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.

21.Sql trace:

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.

22.MATERIALLIZED VIEWS:

- 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

23.TWO-PHASE COMMIT:

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

24.SITE AUTOMONY:

- It means that each database participating in a distributed database is administered separately and independently from other database.

25.ROLLING FORWARD:

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

26.Eliminate Duplicate records:

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.

29. PVCS:

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?

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?

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?

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?

Oracle Apps Technical

Sunday, 28 October 2012

pl sql record and pl sql table simple example

Thursday, 25 October 2012

SQL*Loader-522: lfiopn failed for file

Wednesday, 10 October 2012

Sql and plsql Material

Forall example with plsql table type in Oracle

Forall Example with Record Type In Oracle

Monday, 8 October 2012

counting no of characters in a given string in Oracle

Friday, 28 September 2012

Sql Interview Question

6Locks

Duration of locks

Portioned table

30. TOAD

32. Normalization