important questions in oracle, developer

7/29/2019 Important Questions in Oracle, Developer

1/27

Important Questions in Oracle, Developer /2000(Form 4.5 and Reports 2.5)

Oracle

1) What are the Back ground processes in Oracle and what are they.1) This is one of the most frequently asked question.There are basically 9 Processes but in ageneral system we need to mention the first five background processes.They do the house

keeping

activities for the Oracle and are common in any system.

The various background processes in oracle are

a) Data Base Writer(DBWR) :: Data Base Writer Writes Modified blocks from Database buffer

cache to Data Files.This is required since the data is not written whenever a transaction is

commited.

b)LogWriter(LGWR) :: LogWriter writes the redo log entries to disk. Redo Log data is generat

in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries

into a online redo log file.

c) System Monitor(SMON) :: The System Monitor performs instance recovery at instance

startup.This is useful for recovery from system failured)Process Monitor(PMON) :: The Process Monitor peforms process recovery when user Process

fails. Pmon Clears and Frees resources that process was using.

e) CheckPoint(CKPT) :: At Specified times, all modified database buffers in SGA are written to

data files by DBWR at Checkpoints and Updating all data files and control files of database to

indicate themost recent checkpoint

f)Archieves(ARCH) :: The Archiver copies online redo log files to archival storal when they are

busy.

g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed transaction in network

h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecture

i) Lckn :: We can have upto 10 lock processes for inter instance locking in parallel sql.

2) How many types of Sql Statements are there in Oracle2) There are basically 6 types of sql statments.They are

a) Data Defination Language(DDL) :: The DDL statments define and maintain objects and dro

objects.

b) Data Manipulation Language(DML) :: The DML statments manipulate database data.

c) Transaction Control Statements :: Manage change by DML

d) Session Control :: Used to control the properties of current session

enabling and disabling roles and changing .e.g :: Alter Statements,Set Role

e) System Control Statements :: Change Properties of Oracle Instance .e.g:: Alter

System

f) Embedded Sql :: Incorporate DDL,DML and T.C.S in Programmin

Language.e.g:: Using the Sql Statements in languages such as 'C', Open,Fetch, execute andclose

3) What is a Transaction in Oracle3) A transaction is a Logical unit of work that compromises one or more SQL Statements

executed by a single User. According to ANSI, a transaction begins with first executable

statment and ends when it is explicitly commited or rolled back.

4) Key Words Used in Oracle


2/27

4) The Key words that are used in Oracle are ::

a) Commiting :: A transaction is said to be commited when the transaction makes permanent

changes resulting from the SQL statements.

b) Rollback :: A transaction that retracts any of the changes resulting from SQL statemen

in Transaction.

c) SavePoint :: For long transactions that contain many SQL statements, intermediate

markers or savepoints are declared. Savepoints can be used to divide a transactino into smallepoints.

d) Rolling Forward :: Process of applying redo log during recovery is called rolling forward.

e) Cursor :: A cursor is a handle ( name or a pointer) for the memory associated

with a specific stament. A cursor is basically an area allocated by Oracle for executing the Sql

Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explcit cursfor a multi row query.

f) System Global Area(SGA) :: The SGA is a shared memory region allocated by the Oracle tha

contains Data and control information for one Oracle Instance.It consists of Database Buffer

Cache and Redo log Buffer.

g) Program Global Area (PGA) :: The PGA is a memory buffer that contains data and control

information for server process.

g) Database Buffer Cache :: Databese Buffer of SGA stores the most recently used blocks of

datatbase data.The set of database buffers in an instance is called Database Buffer Cache.

h) Redo log Buffer :: Redo log Buffer of SGA stores all the redo log entries.

i) Redo Log Files :: Redo log files are set of files that protect altered database data in

memory that has not been written to Data Files. They are basically used for backup when a

database crashes.

j) Process :: A Process is a 'thread of control' or mechansim in Operating System

that executes series of steps.

5) What are Procedure,functions and Packages

5) Procedures and functions consist of set of PL/SQL statements that are grouped together as

unit to solve a specific problem or perform set of related tasks.

Procedures do not Return values while Functions return one One Value

Packages :: Packages Provide a method of encapsulating and storing related procedures,functions, variables and other Package Contents

6) What are Database Triggers and Stored Procedures

6) Database Triggers :: Database Triggers are Procedures that are automatically executed as a

result of insert in, update to, or delete from table.

Database triggers have the values old and new to denote the old value in the table before it is

deleted and the new indicated the new value that will be used. DT are useful for implementing

complex business rules which cannot be enforced using the integrity rules.We can have the

trigger as Before trigger or After Trigger and at Statement or Row level.

e.g:: operations insert,update ,delete 3

before ,after 3*2 A total of 6 combinatons

At statment level(once for the trigger) or row level( for every execution ) 6 * 2 A

total of 12.

Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been

lifted from Oracle 7.3 Onwards.

Stored Procedures :: Stored Procedures are Procedures that are stored in Compiled form in th

database.The advantage of using the stored procedures is that many users can use the same

procedure in compiled and ready to use format.

7) How many Integrity Rules are there and what are they


3/27

7) There are Three Integrity Rules. They are as follows ::

a) Entity Integrity Rule :: The Entity Integrity Rule enforces that the Primary key cannot be N

b) Foreign Key Integrity Rule :: The FKIR denotes that the relationship between the foreign ke

and the primary key has to be enforced.When there is data in Child Tables the Master tables

cannot be deleted.

c) Business Integrity Rules :: The Third Intigrity rule is about the complex business processes

which cannot be implemented by the above 2 rules.

8) What are the Various Master and Detail Relation ships.

8) The various Master and Detail Relationship are

a) NonIsolated :: The Master cannot be deleted when a child is exisiting

b) Isolated :: The Master can be deleted when the child is exisitingc) Cascading :: The child gets deleted when the Master is deleted.

9) What are the Various Block Coordination Properties

9) The various Block Coordination Properties are

a) Immediate

Default Setting. The Detail records are shown when the Master Record are shown.

b) Deffered with Auto Query

Oracle Forms defer fetching the detail records until the operator navigates to the detail block.

c) Deffered with No Auto Query

The operator must navigate to the detail block and explicitly execute a query

10) What are the Different Optimisation Techniques

10) The Various Optimisation techniques are

a) Execute Plan :: we can see the plan of the query and change it accordingly based on the

indexes

b) Optimizer_hint ::

set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS');

Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from dept

where (Deptno > 25)

c) Optimize_Sql ::By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL

statements.This slow downs the processing because for evertime the SQL must be parsedwhenver they are executed.

f45run module = my_firstform userid = scott/tiger optimize_sql = No

d) Optimize_Tp ::

By setting the Optimize_Tp= No, Oracle Forms assigns seperate cursor only for each query

SELECT statement. All other SQL statements reuse the cursor.

f45run module = my_firstform userid = scott/tiger optimize_Tp = No

11) How do u implement the If statement in the Select Statement

11) We can implement the if statement in the select statement by using the Decode statement

e.g select DECODE (EMP_CAT,'1','First','2','Second'Null);

Here the Null is the else statement where null is done .

12)How many types of Exceptions are there

12) There are 2 types of exceptions. They are

a) System Exceptions

e.g. When no_data_found, When too_many_rows

b) User Defined Exceptions

e.g. My_exception exception


4/27

When My_exception then

13) What are the inline and the precompiler directives

13) The inline and precompiler directives detect the values directly

14) How do you use the same lov for 2 columns

14) We can use the same lov for 2 columns by passing the return values in global values andusing the global values in the code

15) How many minimum groups are required for a matrix report

15) The minimum number of groups in matrix report are 4

16) What is the difference between static and dynamic lov

16) The static lov contains the predetermined values while the dynamic lov contains values th

come at run time

17) What are snap shots and views

17) Snapshots are mirror or replicas of tables. Views are built using the columns from one or

more tables. The Single Table View can be updated but the view with multi table cannot be

updated

18) What are the OOPS concepts in Oracle.

18) Oracle does implement the OOPS concepts. The best example is the Property Classes. We

can categorise the properties by setting the visual attributes and then attach the property

classes for the

objects. OOPS supports the concepts of objects and classes and we can consider the peroperty

classes as classes and the items as objects

19) What is the difference between candidate key, unique key and primary key

19) Candidate keys are the columns in the table that could be the primary keys and the primar

key

is the key that has been selected to identify the rows. Unique key is also useful for identifyingthe distinct rows in the table.

20)What is concurrency

20) Cuncurrency is allowing simultaneous access of same data by different users. Locks usefu

for accesing the database are

a) Exclusive

The exclusive lock is useful for locking the row when an insert,update or delete is being

done.This lock should not be applied when we do only select from the row.

b) Share lock

We can do the table as Share_Lock as many share_locks can be put on the same resource.

21) Previleges and Grants

21) Previleges are the right to execute a particulare type of SQL statements.

e.g :: Right to Connect, Right to create, Right to resource

Grants are given to the objects so that the object might be accessed accordingly.The grant has

to be

given by the owner of the object.

22)Table Space,Data Files,Parameter File, Control Files

22)Table Space :: The table space is useful for storing the data in the database.When a


5/27

database is created two table spaces are created.

a) System Table space :: This data file stores all the tables related to the system and dba table

b) User Table space :: This data file stores all the user related tables

We should have seperate table spaces for storing the tables and indexes so that the access is

fast.

Data Files :: Every Oracle Data Base has one or more physical data files.They store the data fo

the database.Every datafile is associated with only one database.Once the Data file is createdthe size cannot change.To increase the size of the database to store more data we have to add

data file.

Parameter Files :: Parameter file is needed to start an instance.A parameter file contains the lis

of instance configuration parameters e.g.::

db_block_buffers = 500db_name = ORA7

db_domain = u.s.acme lang

Control Files :: Control files record the physical structure of the data files and redo log files

They contain the Db name, name and location of dbs, data files ,redo log files and time stamp.

23) Physical Storage of the Data

23) The finest level of granularity of the data base are the data blocks.

Data Block :: One Data Block correspond to specific number of physical database space

Extent :: Extent is the number of specific number of contigious data blocks.

Segments :: Set of Extents allocated for Extents. There are three types of Segments

a) Data Segment :: Non Clustered Table has data segment data of every table is stored in

cluster data segment

b) Index Segment :: Each Index has index segment that stores data

c) Roll Back Segment :: Temporarily store 'undo' information

24) What are the Pct Free and Pct Used

24) Pct Free is used to denote the percentage of the free space that is to be left when creating

table. Similarly Pct Used is used to denote the percentage of the used space that is to be used

when creating a table

eg.:: Pctfree 20, Pctused 40

25) What is Row Chaining25) The data of a row in a table may not be able to fit the same data block.Data for row is stor

in a chain of data blocks .

26) What is a 2 Phase Commit

26) Two Phase commit is used in distributed data base systems. This is useful to maintain the

integrity of the database so that all the users see the same values. It contains DML statements

or Remote Procedural calls that reference a remote object. There are basically 2 phases in a 2

phase commit.

a) Prepare Phase :: Global coordinator asks participants to prepare

b) Commit Phase :: Commit all participants to coordinator to Prepared, Read only or abort Rep

27) What is the difference between deleting and truncating of tables

27) Deleting a table will not remove the rows from the table but entry is there in the

database dictionary and it can be retrieved But truncating a table deletes it completely and it

cannot be retrieved.

28) What are mutating tables

28) When a table is in state of transition it is said to be mutating. eg :: If a row has been


6/27

deleted then the table is said to be mutating and no operations can be done on the table excep

select.

29) What are Codd Rules

29) Codd Rules describe the ideal nature of a RDBMS. No RDBMS satisfies all the 12 codd rules

and Oracle Satisfies 11 of the 12 rules and is the only Rdbms to satisfy the maximum number

rules.

30) What is Normalisation

30) Normalisation is the process of organising the tables to remove the redundancy.There are

mainly 5 Normalisation rules.

a) 1 Normal Form :: A table is said to be in 1st Normal Form when the attributes are atomicb) 2 Normal Form :: A table is said to be in 2nd Normal Form when all the candidate keys are

dependant on the primary key

c) 3rd Normal Form :: A table is said to be third Normal form when it is not dependant

transitively

31) What is the Difference between a post query and a pre query

31) A post query will fire for every row that is fetched but the pre query will fire only once.

32) Deleting the Duplicate rows in the table

32) We can delete the duplicate rows in the table by using the Rowid

33) Can U disable database trigger? How?

33) Yes. With respect to table

ALTER TABLE TABLE

[ DISABLE all_trigger ]

34) What is pseudo columns ? Name them?

34) A pseudocolumn behaves like a table column, but is not actually

stored in the table. You can select from pseudocolumns, but youcannot insert, update, or delete their values. This section

describes these pseudocolumns:

* CURRVAL

* NEXTVAL

* LEVEL

* ROWID

* ROWNUM

35) How many columns can table have?

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

36) Is space acquired in blocks or extents ?

In extents .

37) what is clustered index?

In an indexed cluster, rows are stored together based on their cluster key values .Can not applied for HASH.

38) what are the datatypes supported By oracle (INTERNAL)?


7/27

Varchar2, Number,Char , MLSLABEL.

39 ) What are attributes of cursor?

%FOUND , %NOTFOUND , %ISOPEN,%ROWCOUNT

40) Can you use select in FROM clause of SQL select ?

Yes.

Forms 4.5 Questions

1) Which trigger are created when master -detail rela?

1) master delete property

* NON-ISOLATED (default)

a) on check delete master

b) on clear details

c) on populate details

* ISOLATED

a) on clear details

b) on populate details

* CASCADE

a) per-delete

b) on clear details

c) on populate details


8/27

2) which system variables can be set by users?

2)

SYSTEM.MESSAGE_LEVEL

SYSTEM.DATE_THRESHOLD

SYSTEM.EFFECTIVE_DATE

SYSTEM.SUPPRESS_WORKING

3) What are object group?

3)

An object group is a container for a group of objects. You define an object group when you wan

package related objects so you can copy or reference them in another module.

4) What are referenced objects?

4)

Referencing allows you to create objects that inherit their functionality and appearance from ot

objects.

Referencing an object is similar to copying an object, except that the resulting reference objec

maintains a

link to its source object. A reference object automatically inherits any changes that have been

the

source object when you open or regenerate the module that contains the reference object.

5) Can you store objects in library?

5)


objects. Referencing an object is similar to copying an object, except that the resulting referen

object maintains a link to its source object. A reference object automatically inherits any chang

have been made to the source object when you open or regenerate the module that contains t

reference object.

6) Is forms 4.5 object oriented tool ? why?6)

yes , partially. 1) PROPERTY CLASS - inheritance property

2) OVERLOADING : procedures and functions.

7) Can you issue DDL in forms?

7)

yes, but you have to use FORMS_DDL.


objects. Referencing an object is similar to copying an object, except that the resulting referen

maintains a link to its source object. A reference object automatically inherits any changes tha

been made to the source object when you open or regenerate the module that contains the ref

object.

Any string expression up to 32K:

a literal

an expression or a variable representing the text of a block of dynamically created PL/S

a DML statement or

a DDL statement


9/27

Restrictions:

The statement you pass to FORMS_DDL may not contain bind variable references in the string,

values of bind variables can be concatenated into the string before passing the result to FORMS

8) What is SECURE property?

8)- Hides characters that the operator types into the text item. This setting is typically used fopassword protection.

9 ) What are the types of triggers and how the sequence of firing in text item

9)

Triggers can be classified as Key Triggers, Mouse Triggers ,Navigational Triggers.Key Triggers :: Key Triggers are fired as a result of Key action.e.g :: Key-next-field, Key-up,Ke

Mouse Triggers :: Mouse Triggers are fired as a result of the mouse navigation.e.g. When-mou

button-presed,when-mouse-doubleclicked,etc

Navigational Triggers :: These Triggers are fired as a result of Navigation. E.g : Post-Text-item

text-item.

We also have event triggers like when new-form-instance and when-new-block-instance.

We cannot call restricted procedures like go_to(my_block.first_item) in the Navigational trigg

But can use them in the Key-next-item.

The Difference between Key-next and Post-Text is an very important question. The key-next is

a result of the key action while the post text is fired as a result of the mouse movement. Key

not fire unless there is a key event.

The sequence of firing in a text item are as follows ::

a) pre - text

b) when new item

c) key-next

d) when validate

e) post text

10 ) Can you store pictures in database? How?

10)Yes , in long Raw datatype.

11) What are property classes ? Can property classes have trigger?11) Property class inheritance is a powerful feature that allows you to quickly define objects th

conform to

your own interface and functionality standards. Property classes also allow you to make global

to

applications quickly. By simply changing the definition of a property class, you can change the

definition

of all objects that inherit properties from that class.

Yes . All type of triggers .

* 12 a) If you have property class attached to an item and you have same trigger written for t

Which will fire first?

12)Item level trigger fires , If item level trigger fires, property level trigger won't fire. Trigger

lowest level are always given the first preference. The item level trigger fires first and then the

and then the Form level trigger.

13) What are record groups ? * Can record groups created at run-time?

13)A record group is an internal Oracle Forms data structure that has a column/row framework

to a


10/27

database table. However, unlike database tables, record groups are separate objects that belo

form module in which they are defined. A record group can have an unlimited number of colum

type

CHAR, LONG, NUMBER, or DATE provided that the total number of columns does not exceed 64

Record group column names cannot exceed 30 characters.

Programmatically, record groups can be used whenever the functionality offered by a two-dime

array of multiple data types is desirable.TYPES OF RECORD GROUP:

Query Record Group A query record group is a record group that has an associated SELECT sta

The columns in a query record group derive their default names, data types, and lengths f

database columns referenced in the SELECT statement. The records in a query record group

rows retrieved by the query associated with that record group.Non-query Record Group A non-query record group is a group that does not have an associa

query, but whose structure and values can be modified programmatically at runtime.

Static Record Group A static record group is not associated with a query; rather, you define its

structure and row values at design time, and they remain fixed at runtime.

14) What are ALERT?

14)An ALERT is a modal window that displays a message notifiying operator of some applicatio

condition.

15) Can a button have icon and lable at the same time ?

15) -NO

16) What is mouse navigate property of button?

16)

When Mouse Navigate is True (the default), Oracle Forms performs standard navigation to mov

focus

to the item when the operator activates the item with the mouse.

When Mouse Navigate is set to False, Oracle Forms does not perform navigation (and the resulvalidation) to move to the item when an operator activates the item with the mouse.

17) What is FORMS_MDI_WINDOW?

17) forms run inside the MDI application window. This property is useful for calling a form fro

another one.

18) What are timers ? when when-timer-expired does not fire?

18) The When-Timer-Expired trigger can not fire during trigger, navigation, or transaction pro

19 ) Can object group have a block?

19)Yes , object group can have block as well as program units.

20)How many types of canvases are there.

20)There are 2 types of canvases called as Content and Stack Canvas. Content canvas is the d

and the one that is used mostly for giving the base effect. Its like a plate on which we add item

stacked canvas is used for giving 3 dimensional effect.

The following questions might not be asked in an Average Interview and could be asked when

the Interviewer wants to trouble u and go deepppppppppppppHe cannot go further..


11/27

1) What are user-exits?

1) It invokes 3GL programs.

2) Can you pass values to-and-fro from foreign function ? how ?

2) Yes . You obtain a return value from a foreign function by assigning the return value to an OForms

variable or item. Make sure that the Oracle Forms variable or item is the same data type as th

value

from the foreign function.

After assigning an Oracle Forms variable or item value to a PL/SQL variable, pass the PL/SQL vas

a parameter value in the PL/SQL interface of the foreign function. The PL/SQL variable that is

as

a parameter must be a valid PL/SQL data type; it must also be the appropriate parameter type

defined

in the PL/SQL interface.

3) What is IAPXTB structure ?

3) The entries of Pro * C and user exits and the form which simulate the proc or user_exit are

in IAPXTB table in d/b.

4) Can you call WIN-SDK thruo' user exits?

4) YES.

5) Does user exits supports DLL on MSWINDOWS ?

5) YES .

6) What is path setting for DLL?

6) Make sure you include the name of the DLL in the FORMS45_USEREXIT variable of the ORAC

file, or rename the DLL to F45XTB.DLL. If you rename the DLL to F45XTB.DLL, replace the exiF45XTB.DLL in the \ORAWIN\BIN directory with the new F45XTB.DLL.

7) How is mapping of name of DLL and function done?

7) The dll can be created using the Visual C++ / Visual Basic Tools and then the dll is put in th

path that is defined the registery.

8) what is precompiler?

8) It is similar to C precompiler directives.

9) Can you connect to non - oracle datasource ? How?

9) Yes .

10 ) what are key-mode and locking mode properties? level ?

10) Key Mode : Specifies how oracle forms uniquely identifies rows in the database.This is prop

includes

for application that will run against NON-ORACLE datasources .

Key setting unique (default.)

dateable

n-updateable.


12/27

Locking mode :

Specifies when Oracle Forms should attempt to obtain database locks on rows that correspond

queried records in the form.

a) immediate b) delayed

11) What are savepoint mode and cursor mode properties ? level?

11) Specifies whether Oracle Forms should issue savepoints during a session. This property is iprimarily for applications that will run against non-ORACLE data sources. For applications tha

against ORACLE, use the default setting.

Cursor mode - define cursur state across transaction

Open/close.

12) Can you replace default form processing ? How ?

13) What is transactional trigger property?

13) Identifies a block as transactional control block. i.e. non - database block that oracle form

manage as transactional block.(NON-ORACLE datasource) default - FALSE.

14) What is OLE automation ?

14) OLE automation allows an OLE server application to expose a set of commands and functio

can be

invoked from an OLE container application. OLE automation provides a way for an OLE contain

application to use the features of an OLE server application to manipulate an OLE object from

container environment. (FORMS_OLE)

15) What does invoke built-in do?

15) This procedure invokes a method.

Syntax:

PROCEDURE OLE2.INVOKE

(object obj_type,

method VARCHAR2,

list list_type := 0);Parameters:

object Is an OLE2 Automation Object.method Is a method (procedure) of the OLE2 object.

list Is the name of an argument list assigned to the OLE2.CREATE_ARGLIST function.

16) What are OPEN_FORM,CALL_FORM,NEW_FORM? diff?

16) CALL_FORM : It calls the other form. but parent remains active, when called form complete

operation , it releases lock and control goes back to the calling form.

When you call a form, Oracle Forms issues a savepoint for the called form. If the CLEAR_FORM

causes a rollback when the called form is current, Oracle Forms rolls back uncommitted change

savepoint.

OPEN_FORM : When you call a form, Oracle Forms issues a savepoint for the called form. If th

CLEAR_FORM function causes a rollback when the called form is current, Oracle Forms rolls bac

uncommitted changes to this savepoint.

NEW_FORM : Exits the current form and enters the indicated form. The calling form is termin

the parent form. If the calling form had been called by a higher form, Oracle Forms keeps the

call

active and treats it as a call to the new form. Oracle Forms releases memory (such as databa

cursors)

that the terminated form was using.


13/27

Oracle Forms runs the new form with the same Runform options as the parent form. If the pa

was

a called form, Oracle Forms runs the new form with the same options as the parent form.

17 ) What is call form stack?17) When successive forms are loaded via the CALL_FORM procedure, the resulting module hie

known as the call form stack.

18) Can u port applictions across the platforms? how?

18) Yes we can port applications across platforms.Consider the form developed in a windowssystem.The form would be generated in unix system by using f45gen my_form.fmb scott/tiger

GUI

1) What is a visual attribute?

1) Visual attributes are the font, color, and pattern properties that you set for form and menu

that appear in your application's interface.

2) Diff. between VAT and Property Class? imp

2)Named visual attributes define only font, color, and pattern attributes; property classes can c

these and any other properties.

You can change the appearance of objects at runtime by changing the named visual attribute

programmatically; property class assignment cannot be changed programmatically.

When an object is inheriting from both a property class and a named visual attribute, the name

attribute settings take precedence, and any visual attribute properties in the class are ignored

3 ) Which trigger related to mouse?

3) When-Mouse-Click

When-Mouse-DoubleClick

When-Mouse-DownWhen-Mouse-Enter

When-Mouse-Leave

When-Mouse-Move

When-Mouse-Up

4) What is Current record attribute property?

4) Specifies the named visual attribute used when an item is part of the current record.

Current Record Attribute is frequently used at the block level to display the current row in a m

record

If you define an item-level Current Record Attribute, you can display a pre-determined item in

color

when it is part of the current record, but you cannot dynamically highlight the current item, asinput focus changes.

5) Can u change VAT at run time?

5) Yes. You can programmatically change an object's named visual attribute setting to change

color,and pattern of the object at runtime.

6) Can u set default font in forms?


14/27

6) Yes. Change windows registry(regedit). Set form45_font to the desired font.

7) Can u have OLE objects in forms?

7) Yes.

8) Can u have VBX and OCX controls in forms ?

8) Yes.

9) What r the types of windows (Window style)?

9) Specifies whether the window is a Document window or a Dialog window.

10) What is OLE Activation style property?

10) Specifies the event that will activate the OLE containing item.

11) Can u change the mouse pointer ? How?

11) Yes. Specifies the mouse cursor style. Use this property to dynamically change the shape

cursor.

Reports 2.5

1) How many types of columns are there and what are they

1) Formula columns :: For doing mathematical calculations and returning one value

Summary Columns :: For doing summary calculations such as summations etc.

Place holder Columns :: These columns are useful for storing the value in a variable

2) Can u have more than one layout in report

2) It is possible to have more than one layout in a report by using the additional layout optio

in the layout editor.

3) Can u run the report with out a parameter form

3) Yes it is possible to run the report without parameter form by setting the PARAM value toNull

4) What is the lock option in reports layout

4) By using the lock option we cannot move the fields in the layout editor outside the frame.

This is useful for maintaining the fields .

5) What is Flex

5) Flex is the property of moving the related fields together by setting the flex property on

6) What are the minimum number of groups required for a matrix report

6) The minimum of groups required for a matrix report are 4


15/27

SQL

1. Which is the subset of SQL commands used to manipulate Oracle Database structurincluding tables?

Data Definition Language (DDL)

2. What operator performs pattern matching?LIKE operator

3. What operator tests column for the absence of data?IS NULL operator

4. Which command executes the contents of a specified file?START or @

5. What is the parameter substitution symbol used with INSERT INTO command?&

6. Which command displays the SQL command in the SQL buffer, and then executes it?RUN

7. What are the wildcards used for pattern matching?_ for single character substitution and % for multi-character substitution

8. State true or false. EXISTS, SOME, ANY are operators in SQL.True

9. State true or false. !=, , ^= all denote the same operation.True

10. What are the privileges that can be granted on a table by a user to others?Insert, update, delete, select, references, index, execute, alter, all

11. What command is used to get back the privileges offered by the GRANT command?REVOKE

12. Which system tables contain information on privileges granted and privileges obtained?USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD

13. Which system table contains information on constraints on all the tables created?


16/27

USER_CONSTRAINTS

14. TRUNCATE TABLE EMP;DELETE FROM EMP;

Will the outputs of the above two commands differ?Both will result in deleting all the rows in the table EMP.

15. What is the difference between TRUNCATE and DELETE commands?TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELE

operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause cbe used with DELETE and not with TRUNCATE.

16. What command is used to create a table by copying the structure of another table?Answer :

CREATE TABLE .. AS SELECT commandExplanation :

To copy only the structure, the WHERE clause of the SELECT command should containFALSE statement as in the following.

CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;

If the WHERE condition is true, then all the rows or rows satisfying the condition will copied to the new table.

17. What will be the output of the following query?

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

TROUBLETHETROUBLE

18. What will be the output of the following query?SELECT DECODE(TRANSLATE('A','1234567890','1111111111'), '1','YES', 'NO' );

Answer :NO

Explanation :

The query checks whether a given string is a numerical digit.

19. What does the following query do?SELECT SAL + NVL(COMM,0) FROM EMP;This displays the total salary of all employees. The null values in the commission colu

will be replaced by 0 and added to salary.

20. Which date function is used to find the difference between two dates?MONTHS_BETWEEN

21. Why does the following command give a compilation error?DROP TABLE &TABLE_NAME;

Variable names should start with an alphabet. Here the table name starts with an symbol.

22. What is the advantage of specifying WITH GRANT OPTION in the GRANT command?The privilege receiver can further grant the privileges he/she has obtained from t

owner to any other user.

23. What is the use of the DROP option in the ALTER TABLE command?It is used to drop constraints specified on the table.


17/27

24. What is the value of comm and sal after executing the following query if the initial valuesal is 10000?

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

25. What is the use of DESC in SQL?Answer :DESC has two purposes. It is used to describe a schema as well as to retrieve rows fro

table in descending order.Explanation :

The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted ENAME in descending order.

26. What is the use of CASCADE CONSTRAINTS?When this clause is used with the DROP command, a parent table can be dropped ev

when a child table exists.

27. Which function is used to find the largest integer less than or equal to a specificvalue?

FLOOR

28. What is the output of the following query?SELECT TRUNC(1234.5678,-2) FROM DUAL;1200

SQL QUERIES

I. SCHEMAS

Table 1 :STUDIES

PNAME (VARCHAR), SPLACE (VARCHAR), COURSE (VARCHAR), CCOST (NUMBER)

Table 2 : SOFTWARE

PNAME (VARCHAR), TITLE (VARCHAR), DEVIN (VARCHAR), SCOST (NUMBER), DCOST (NUMBESOLD (NUMBER)

Table 3 : PROGRAMMER

PNAME (VARCHAR), DOB (DATE), DOJ (DATE), SEX (CHAR), PROF1 (VARCHAR), PROF2 (VARCHASAL (NUMBER)

LEGEND :

PNAME Programmer Name, SPLACE Study Place, CCOST Course Cost, DEVIN Developed SCOST Software Cost, DCOST Development Cost, PROF1 Proficiency 1

QUERIES :


18/27

1. Find out the selling cost average for packages developed in Oracle.2. Display the names, ages and experience of all programmers.3. Display the names of those who have done the PGDCA course.4. What is the highest number of copies sold by a package?5. Display the names and date of birth of all programmers born in April.

6. Display the lowest course fee.7. How many programmers have done the DCA course.8. How much revenue has been earned through the sale of packages developed in C.9. Display the details of software developed by Rakesh.10. How many programmers studied at Pentafour.11. Display the details of packages whose sales crossed the 5000 mark.12. Find out the number of copies which should be sold in order to recover the developme

cost of each package.13. Display the details of packages for which the development cost has been recovered.14. What is the price of costliest software developed in VB?15. How many packages were developed in Oracle ?16. How many programmers studied at PRAGATHI?17. How many programmers paid 10000 to 15000 for the course?

18. What is the average course fee?19. Display the details of programmers knowing C.20. How many programmers know either C or Pascal?21. How many programmers dont know C and C++?22. How old is the oldest male programmer?23. What is the average age of female programmers?24.Calculate the experience in years for each programmer and display along with th

names in descending order.25. Who are the programmers who celebrate their birthdays during the current month?26. How many female programmers are there?27. What are the languages known by the male programmers?28. What is the average salary?29. How many people draw 5000 to 7500?

30. Display the details of those who dont know C, C++ or Pascal.31. Display the costliest package developed by each programmer.32. Produce the following output for all the male programmers

ProgrammerMr. Arvind has 15 years of experience

KEYS:

1. SELECT AVG(SCOST) FROM SOFTWARE WHERE DEVIN = 'ORACLE';2. SELECT PNAME,TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12) "AG

TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) "EXPERIENCE" FROM PROGRAMMER;3. SELECT PNAME FROM STUDIES WHERE COURSE = 'PGDCA';4. SELECT MAX(SOLD) FROM SOFTWARE;

5. SELECT PNAME, DOB FROM PROGRAMMER WHERE DOB LIKE '%APR%';6. SELECT MIN(CCOST) FROM STUDIES;7. SELECT COUNT(*) FROM STUDIES WHERE COURSE = 'DCA';8. SELECT SUM(SCOST*SOLD-DCOST) FROM SOFTWARE GROUP BY DEVIN HAVING DEVIN

'C';9. SELECT * FROM SOFTWARE WHERE PNAME = 'RAKESH';10. SELECT * FROM STUDIES WHERE SPLACE = 'PENTAFOUR';11. SELECT * FROM SOFTWARE WHERE SCOST*SOLD-DCOST > 5000;12. SELECT CEIL(DCOST/SCOST) FROM SOFTWARE;13. SELECT * FROM SOFTWARE WHERE SCOST*SOLD >= DCOST;


19/27

14. SELECT MAX(SCOST) FROM SOFTWARE GROUP BY DEVIN HAVING DEVIN = 'VB';15. SELECT COUNT(*) FROM SOFTWARE WHERE DEVIN = 'ORACLE';16. SELECT COUNT(*) FROM STUDIES WHERE SPLACE = 'PRAGATHI';17. SELECT COUNT(*) FROM STUDIES WHERE CCOST BETWEEN 10000 AND 15000;18. SELECT AVG(CCOST) FROM STUDIES;19. SELECT * FROM PROGRAMMER WHERE PROF1 = 'C' OR PROF2 = 'C';

20. SELECT * FROM PROGRAMMER WHERE PROF1 IN ('C','PASCAL') OR PROF2 IN ('C','PASCAL21. SELECT * FROM PROGRAMMER WHERE PROF1 NOT IN ('C','C++') AND PROF2 NOT ('C','C++');

22. SELECT TRUNC(MAX(MONTHS_BETWEEN(SYSDATE,DOB)/12)) FROM PROGRAMMER WHESEX = 'M';

23. SELECT TRUNC(AVG(MONTHS_BETWEEN(SYSDATE,DOB)/12)) FROM PROGRAMMER WHESEX = 'F';

24.SELECT PNAME, TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) FROM PROGRAMMORDER BY PNAME DESC;

25. SELECT PNAME FROM PROGRAMMER WHERE TO_CHAR(DOB,'MON') TO_CHAR(SYSDATE,'MON');

26. SELECT COUNT(*) FROM PROGRAMMER WHERE SEX = 'F';27. SELECT DISTINCT(PROF1) FROM PROGRAMMER WHERE SEX = 'M';

28. SELECT AVG(SAL) FROM PROGRAMMER;29. SELECT COUNT(*) FROM PROGRAMMER WHERE SAL BETWEEN 5000 AND 7500;30. SELECT * FROM PROGRAMMER WHERE PROF1 NOT IN ('C','C++','PASCAL') AND PROF2 N

IN ('C','C++','PASCAL');31.SELECT PNAME,TITLE,SCOST FROM SOFTWARE WHERE SCOST IN (SELECT MAX(SCOS

FROM SOFTWARE GROUP BY PNAME);32.SELECT 'Mr.' || PNAME || ' - has ' || TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) || ' yea

of experience' Programmer FROM PROGRAMMER WHERE SEX = 'M' UNION SELECT 'M|| PNAME || ' - has ' || TRUNC (MONTHS_BETWEEN (SYSDATE,DOJ)/12) || ' years experience' Programmer FROM PROGRAMMER WHERE SEX = 'F';

II . SCHEMA :

Table 1 :DEPT

DEPTNO (NOT NULL , NUMBER(2)), DNAME (VARCHAR2(14)),LOC (VARCHAR2(13)

Table 2 :EMP

EMPNO (NOT NULL , NUMBER(4)), ENAME (VARCHAR2(10)),JOB (VARCHAR2(9)), MGR (NUMBER(4)), HIREDATE (DATE),SAL (NUMBER(7,2)), COMM (NUMBER(7,2)), DEPTNO (NUMBER(2))

MGR is the empno of the employee whom the employee reports to. DEPTNO is a foreign key.QUERIES

1. List all the employees who have at least one person reporting to them.2. List the employee details if and only if more than 10 employees are present in department 10.3. List the name of the employees with their immediate higher authority.4. List all the employees who do not manage any one.5. List the employee details whose salary is greater than the lowest salary of an employ


20/27

belonging to deptno 20.6. List the details of the employee earning more than the highest paid manager.7. List the highest salary paid for each job.8. Find the most recently hired employee in each department.9. In which year did most people join the company? Display the year and the number employees.

10. Which department has the highest annual remuneration bill?11. Write a query to display a * against the row of the most recently hired employee.12. Write a correlated sub-query to list out the employees who earn more than the average salaof their department.13. Find the nth maximum salary.14. Select the duplicate records (Records, which are inserted, that already exist) in the EMP tab15.Write a query to list the length of service of the employees (of the form n years and months).

KEYS:

1. SELECT DISTINCT(A.ENAME) FROM EMP A, EMP B WHERE A.EMPNO = B.MGR; or SELECTENAME FROM EMP WHERE EMPNO IN (SELECT MGR FROM EMP);

2. SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM EMP GROUP BY DEPTNOHAVING COUNT(EMPNO)>10 AND DEPTNO=10);3. SELECT A.ENAME "EMPLOYEE", B.ENAME "REPORTS TO" FROM EMP A, EMP B WHEREA.MGR=B.EMPNO;4. SELECT * FROM EMP WHERE EMPNO IN ( SELECT EMPNO FROM EMP MINUS SELECT MGR FROEMP);5. SELECT * FROM EMP WHERE SAL > ( SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO HAVINDEPTNO=20);6. SELECT * FROM EMP WHERE SAL > ( SELECT MAX(SAL) FROM EMP GROUP BY JOB HAVING JO= 'MANAGER' );7. SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB;8. SELECT * FROM EMP WHERE (DEPTNO, HIREDATE) IN (SELECT DEPTNO, MAX(HIREDATE) FROEMP GROUP BY DEPTNO);

9. SELECT TO_CHAR(HIREDATE,'YYYY') "YEAR", COUNT(EMPNO) "NO. OF EMPLOYEES" FROM EMGROUP BY TO_CHAR(HIREDATE,'YYYY') HAVING COUNT(EMPNO) = (SELECT MAX(COUNT(EMPNOFROM EMP GROUP BY TO_CHAR(HIREDATE,'YYYY'));10. SELECT DEPTNO, LPAD(SUM(12*(SAL+NVL(COMM,0))),15) "COMPENSATION" FROM EMPGROUP BY DEPTNO HAVING SUM( 12*(SAL+NVL(COMM,0))) = (SELECTMAX(SUM(12*(SAL+NVL(COMM,0)))) FROM EMP GROUP BY DEPTNO);11. SELECT ENAME, HIREDATE, LPAD('*',8) "RECENTLY HIRED" FROM EMP WHERE HIREDATE =(SELECT MAX(HIREDATE) FROM EMP) UNION SELECT ENAME NAME, HIREDATE, LPAD(' ',15)"RECENTLY HIRED" FROM EMP WHERE HIREDATE != (SELECT MAX(HIREDATE) FROM EMP);12. SELECT ENAME,SAL FROM EMP E WHERE SAL > (SELECT AVG(SAL) FROM EMP F WHEREE.DEPTNO = F.DEPTNO);13. SELECT ENAME, SAL FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT(SAL)) FROM EMPWHERE A.SAL1) AND A.ROWID!=MIN (ROWID));15. SELECT ENAME "EMPLOYEE",TO_CHAR(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)YEARS '|| TO_CHAR(TRUNC(MOD(MONTHS_BETWEEN (SYSDATE, HIREDATE),12)))||' MONTHS '"LENGTH OF SERVICE" FROM EMP;


21/27

2. Homework questions

Notice anything about the following questions?

Write a query to get the second highest amount in the table

List last name and hire date of any employee in the same department as Zlotkey

I always get puzzled up when asked some typical questions in interview like the nthhighest salary in the EMP table

In SQL from two different tables select the first five highest salary

How can we find the second highest salary from three departments named D1, D2 and D

How to select last ten max(sal) from EMP

List the highest salary, the lowest salary, the average salary and the total salaries ofemployees

What are the different way of selecting 3rd highest salary from EMP table?

How to get Top 5th, 6th, Nth salary from emp table of oracle

Get the doctor with the highest count of visits

How to find the 2nd, 3rd, 4th, ... nth max of a column in a table

How to find out the fifth max salary from employee table

I want to know how to retrieve third highest and lowest from a column

Need help finding the second largest pay and second smallest pay from the salary table

How do I get the top 3 highest salary from the emp table

Dear Sir! How can I get the second maximum salary?

We will only occasionally answer a homework question. However, sometimes thanswer will not really be what the student is looking for. For example,

Find the three smallest numbers from a column of numbers.Answer:The three smallest numbers (16 December, 2005)

How to select nth row from a table if they are not in any order?Answer:The Nth row in a table (30 September, 2005)

What is the query to select empname and his managername from an emptable?Answer:Select empname and managername (17 January, 2006)

How can I select the third, fourth and fifth rows, out of a 100 rows?Answer:Select 3rd, 4th, 5th of 100 rows (10 April, 2002)

Then there are more "substantial" homework questions, which require moreadvanced SQL, such as complex joins and subqueries. We usually don't touch
http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci1152742,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_gci1130578_tax301455,00.htmlhttp://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1159587_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid461604_tax301455,00.htmlhttp://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci1152742,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_gci1130578_tax301455,00.htmlhttp://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1159587_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid461604_tax301455,00.html


22/27

these, for obvious reasons. Hint: no, it's not because they always somehow seeto involve bosses.

Which are the workers that live in the same city as their bosses?

Display the name of the manager who is having maximum number of subordinates.

Find employees in each department who make more money than their immediatemanager.

We answer obvious homework questions only if there's something interesting fall readers, or some subtle nuance about SQL that's worth discussing. Forexample,

I want to display all the employees who report to a person.Answer:All employees under a given manager (23 April, 2007). Thisquestion was selected because it's a very common scenario (especially ifnot involving bosses and employees, but similar structures). It's a toughproblem, and there is comprehensive discussion.

Finally, there are the "esoteric" homework questions. We speculate that theseare questions from university assignments. We never answer these, either.

Why are query languages such as SQL based on relational calculus rather than relationaalgebra?

SQL is a set oriented language. Explain this statement and outline why in somecircumstances, a navigational query language might be prefered.

Explain what is meant by query decomposition and query optimization.
http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1252554,00.htmlhttp://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1252554,00.html


23/27

3. Serious SQL questions

Not surprisingly, there are several common if not "classic" types of complex SQquestions.

Duplicates

Finding "duplicates" is probably the #1 or #2 most common SQL question. Thescenario usually involves an autonumbering surrogate key, with no additionalunique constraint on the "real" (candidate) key.

How I can extract the all the duplicate rows in a table?

Hi sir, I want to delete duplicate data from the table

How do I find duplicate entries in a table?

Lots of solutions exist, each involving a number of steps, many of which aredictated by what you mean by "duplicates" and how you want to handledisparities and missing data. So the solution must be tailored to your specific

scenario, and is of little use to other people.Occasionally, though, the problem is tricky, and therefore interesting:

How to get a list of employees who have the same last and first name?Employees with the same first and last names in SQL (11 May, 2007)

Accidentally the key got dropped and some duplicate records were inserteinto the table....Removing duplicate rows (9 February, 2005)

Top N

Often a popular homework question, the "Top N" problem also seems to happen

to everyone in real life. I need to display the first 'X' number of rows in a query.

My select query comes back with 30,000 rows, and I just want the first 300.

I need to get only 100 records from the bottom of the database based on specific field.

It is such a common question that we created a mini-FAQ for it years ago:

FIRST N rows, TOP N rows, LAST N rows, BOTTOM N rows... (25 November 2002)

Top N for each X

More complex than simple Top N questions, but easy to recognize because they

invariably involve the word everyor each.

How will I find the first 5 highest salaried employees in each dept?How to find the first 5 highest salaried employees in each department (25April, 2001)Top 5 salaried employees, not using TOP (13 July, 2001)

I want a query that will give me the top ten sales for each salesman.Top ten sales for each salesman (17 May, 2004)
http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1254654,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid901349_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid504195_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid504195_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid391167_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid404896_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid587917_tax301455,00.htmlhttp://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1254654,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid901349_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid504195_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid391167_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid404896_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid587917_tax301455,00.html


24/27

How do I retrieve the top 10 brands(sorted by units) for each category-sizcombination?Top N rows for each X (21 March, 2005)

Latest X for each Y

A subtle variation of the "Top N for each X" problem, involving dates: ... the most current wage for each employee.

Rows having maximum group value in MySQL (15 November 2002)

... only latest record from each group.Latest row for each group (17 December 2003)

... three latest payments for each account.Latest three payments for each account (13 April, 2007)

As you can see, even though these are common questions, sometimes weanswer them again.

Pagination

Sadly, pagination seems to give everyone trouble. Sometimes the best solutionare implemented with caching, at the application or middle tier level. Paginatiowith SQL is positively fraughtwith difficulty.

Paging through a result set with SQL (16 July 2002)

Paging through SQL query results (14 February 2005)

Paging through a result set without TOP or LIMIT (24 October 2002)

Comma-delimited string questions

For some reason, denormalizing one-to-many data into comma-delimited stringis a very common requirement:

Concatenate into a single row (28 June 2006)

Concatenate values into comma-delimited string (14 February 2005)

Denormalizing on output isn't a sin, by the way. Storing comma-delimited stringis a bad idea, though.

Miscellaneous questions

Finally, a few oddball questions that seem to come up often:

More about ORDER BY for a specified sequence (13 March, 2007)

COUNT(*) or COUNT(1) (17 January, 2007)

Two "copies" of a lookup table in the same query (17 May 2004)

The "any" option in dynamic search SQL (8 April 2003)
http://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid914058_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid502092_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid564937_tax301455,00.htmlhttp://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1251424,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid480945_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid902743_tax301455,00.htmlhttp://searchoracle.com/ateQuestionNResponse/0,289625,sid41_cid498398_tax301455,00.htmlhttp://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1196434,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid902739_tax301455,00.htmlhttp://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1247202,00.htmlhttp://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1239516,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid587913_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid525758_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid914058_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid502092_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid564937_tax301455,00.htmlhttp://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1251424,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid480945_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid902743_tax301455,00.htmlhttp://searchoracle.com/ateQuestionNResponse/0,289625,sid41_cid498398_tax301455,00.htmlhttp://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1196434,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid902739_tax301455,00.htmlhttp://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1247202,00.htmlhttp://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1239516,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid587913_tax301455,00.htmlhttp://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid525758_tax301455,00.html


25/27

Remember, please do keep sending in your questions. We love em.
http://searchoracle.techtarget.com/expert/KnowledgebasePoseQuestion/0,289624,sid41_tax301455,00.htmlhttp://searchoracle.techtarget.com/expert/KnowledgebasePoseQuestion/0,289624,sid41_tax301455,00.html


26/27

EXPERT RESPONSE

To celebrate our 600th "Ask The Expert" SQL column, we thought we'd run aspecial three-part answer, to highlight some of the common or frequently askedSQL questions we've seen over the past six years.

Please do keep sending in your questions. We love em.

Types of common SQL questions

Common SQL questions fall into three broad categories:

1. Novice questions

2. Homework questions

3. Serious SQL questions

1. Novice questions

Novice questions are usually very simple. They immediately reveal only limiteddegrees of familiarity with SQL. For example,

How can I delete all rows before a particular date?Answer: With a WHERE condition in the DELETE statement. Every basicSQL tutorial covers this.

I want only rows which have the same IDs in both tablesAnswer: Use an INNER JOIN. Actually not a bad question, if you've neverseen or heard of joins, which many new programmers apparently have no

What is the difference between Distinct and Unique?

Answer: Also quite a reasonable question. They aren't synonyms in SQL,because each can be used only in very specific places in SQL statementsDISTINCT in a SELECT clause or COUNT(DISTINCT) expression, and UNIQUwhen declaring a constraint. On the other hand, they are synonyms whentalking aboutSQL, because when you use SELECT DISTINCT, you do getunique rows. So what to answer?

"What is the difference between ..." seems to be a common question.

What is the difference between "inner" and "outer" joins?Answer: Outer joins return unmatched rows. What more is there to say?

What is the difference between GROUP BY and ORDER BY clauses?Answer: One does grouping, the other does ordering. That might soundflippant, but it is not meant to be.

We really do like the short questions, because it gives us so much latitude inchoosing the examples with which to illustrate the topic. But sometimes thequestion just isn't specific enough.Then there are the "It depends" questions. These questions often exhibitreasonable knowledge of SQL. For example,
http://searchoracle.techtarget.com/expert/KnowledgebasePoseQuestion/0,289624,sid41_tax301455,00.htmlhttp://searchoracle.techtarget.com/expert/KnowledgebasePoseQuestion/0,289624,sid41_tax301455,00.html


27/27

Which is faster, Functions Or Procedures, and why? Thanks, it's urgent!Answer: Both functions and procedures require more than a beginner'slevel of knowledge of SQL. Knowing which is faster mightmightsway tdecision to use one or the other. However, too much depends on factorscompletely outside the question, such as what you're trying to accomplish

Furthermore, it is nave to think there might be a difference in performanfor all contexts. That's about all we can reasonably say on the subject.Try some benchmarks on your own database system. As for it being urgethis does not influence whether we will answer your question.

Several "it depends" questions seem to come up over and over, and we try toanswer them at least once. For example, here's one we've seen many times:

JOIN, SUB-QUERY, which one is more efficient...Answer:Which is faster, subquery or join? (4 November, 2005)

We get a steady supply of novice questions, and this is good. It indicates that new blood isconstantly coming into the world of relational databases. While most of the questions are notinteresting enough to be selected for an answer, we do read them all.
http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1140812_tax301455,00.htmlhttp://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1140812_tax301455,00.html

important questions in oracle, developer

Documents