Solution for Informatica problems

Difference Between Primary key and Unique key

2012-12-03T16:57:00.006+05:30

Unique key - It display the unique values which can have one null value where

Primary key - has unique values without null.

Whenever you create the primary key constraints, Oracle by default create a unique index with not null.

SQL Transformation with examples

2012-06-15T17:43:00.005+05:30

=============================================================================================

SQL Transformation with examples

Use: SQL Transformation is a connected transformation used to process SQL queries in the midstream of a pipeline. We can insert, update, delete and retrieve rows from the database at run time using the SQL transformation. Use SQL transformation in script mode to run DDL (data definition language) statements like creating or dropping the tables.

The following SQL statements can be used in the SQL transformation.

Data Definition Statements (CREATE, ALTER, DROP, TRUNCATE, RENAME)
DATA MANIPULATION statements (INSERT, UPDATE, DELETE, MERGE)
DATA Retrieval Statement (SELECT)
DATA Control Language Statements (GRANT, REVOKE)
Transaction Control Statements (COMMIT, ROLLBACK)

Scenario: Let’s say we want to create a temporary table in mapping while workflow is running for some intermediate calculation. We can use SQL transformation in script mode to achieve the same.

Below we will see how to create sql transformation in script mode with an example where we will create a table in mapping and will insert some rows in the same table.

Solution:

Step 1:

Create two text files in the $PMSourceFileDir directory with some sql queries.

1. sql_script.txt

File contains the below Sql queries (you can have multiple sql queries in file separated by semicolon)

create table create_emp_table

(emp_id number,emp_name varchar2(100))

2. sql_script2.txt

File contains the below Sql queries (you can have multiple sql queries in file separated by semicolon)

insert into create_emp_table values (1,'abc')

These are the script files to be executed by SQL transformation on database server.

Step 2:
We need a source which contains the above script file names with a complete path.

So, I created another file in the $PMSourceFileDir directory to store these script file names as Sql_script_input.txt.

File contains the list of files with their complete path:

E:\softs\Informatica\server\infa_shared\SrcFiles\sql_script.txt

E:\softs\Informatica\server\infa_shared\SrcFiles\sql_script2.txt

Step 3:

Now we will create a mapping to execute the script files using the SQL transformation.

Go to the mapping designer tool, source analyzer and Import from file

=>then creates source definition by selecting a file Sql_script_input.txt Located at E:\softs\Informatica\server\infa_shared\SrcFiles.

Source definition will look like

Similarly create a target definition, go to target designer and create a target flat file with result and error ports. This is shown in the below image

Step 4:

Go to the mapping designer and create a new mapping.
Drag the flat file into the mapping designer.
Go to the Transformation in the toolbar, Create, select the SQL transformation, enter a name and click on create.
Now select the SQL transformation options as script mode and DB type as Oracle and click ok.

The SQL transformation is created with the default ports.
Now connect the source qualifier transformation ports to the SQL transformation input port.
Drag the target flat file into the mapping and connect the SQL transformation output ports to the target.
Save the mapping. The mapping flow image is shown in the below picture.

Go to the workflow manager; create a new workflow and session.
Edit the session. For source, enter the source & target file directory.
For the SQL transformation, enter the oracle database relational connection as shown below.

Save the workflow and run it.
Open the target file, you will find the below data.

"PASSED";

"PASSED"; -: for sql_script.txt, where it will crate the table and

"PASSED"; -: For sql_scriptw.txt, where it will insert rows in to the table

Fire a select query on the database to check whether table is created or not.

=============================================================================

Efficient SQL Statements : SQL Tunning Tips

2012-06-07T17:14:00.002+05:30

Efficient SQL Statements

This is an extremely brief look at some of the factors that may effect the efficiency of your SQL and PL/SQL code. It is not intended as a thorough discussion of the area and should not be used as such.

Check Your Stats

The Cost Based Optimizer (CBO) uses statistics to decide which execution plan to use. If these statistics are incorrect the decision made by the CBO may be incorrect. For this reason it is important to make sure that these statistics are refreshed regularly. The following article will help you achieve this aim.

Cost Based Optimizer (CBO) and Database Statistics

Why Indexes Aren't Used

The presence of an index on a column does not guarantee it will be used. The following is a small list of factors that will prevent an index from being used.

The optimizer decides it would be more efficient not to use the index. If your query is returning the majority of the data in a table, then a full table scan is probably going to be the most efficient way to access the table.
You perform a function on the indexed column i.e. WHERE UPPER(name) = 'JONES'. The solution to this is to use a Function-Based Index.
You perform mathematical operations on the indexed column i.e. WHERE salary + 1 = 10001
You concatenate a column i.e. WHERE firstname || ' ' || lastname = 'JOHN JONES'
You do not include the first column of a concatenated index in the WHERE clause of your statement. For the index to be used in a partial match, the first column (leading-edge) must be used. Index Skip Scanning in Oracle 9i and above allow indexes to be used even when the leading edge is not referenced.
The use of 'OR' statements confuses the Cost Based Optimizer (CBO). It will rarely choose to use an index on column referenced using an OR statement. It will even ignore optimizer hints in this situation. The only way of guaranteeing the use of indexes in these situations is to use an INDEX hint.

EXISTS vs. IN

The EXISTS function searches for the presence of a single row meeting the stated criteria as opposed to the IN statement which looks for all occurrences.

TABLE1 - 1000 rows

TABLE2 - 1000 rows

(A)

SELECT t1.id

FROM   table1 t1

WHERE  t1.code IN (SELECT t2.code

                   FROM   table2 t2);

(B)

SELECT t1.id

FROM   table1 t1

WHERE  EXISTS (SELECT '1'

               FROM   table2 t2

               WHERE  t2.code = t1.code)

For query A, all rows in TABLE2 will be read for every row in TABLE1. The effect will be 1,000,000 rows read from items. In the case of query B, a maximum of 1 row from TABLE2 will be read for each row of TABLE1, thus reducing the processing overhead of the statement.
Rule of thumb:

If the majority of the filtering criteria are in the subquery then the IN variation may be more performant.
If the majority of the filtering criteria are in the top query then the EXISTS variation may be more performant.

I would suggest they you should try both variants and see which works the best.
Note. In later versions of Oracle there is little difference between EXISTS and IN operations.

Presence Checking

The first question you should ask yourself is, "Do I need to check for the presence of a record?" Alternatives to presence checking include:

Use the MERGE statement if you are not sure if data is already present.
Perform an insert and trap failure because a row is already present using the DUP_VAL_ON_INDEX exception handler.
Perform an update and test for no rows updated using SQL%ROWCOUNT.

If none of these options are right for you and processing is conditional on the presence of certain records in a table, you may decide to code something like the following.

SELECT Count(*)

INTO   v_count

FROM   items

WHERE  item_size = 'SMALL';

IF v_count = 0 THEN

  -- Do processing related to no small items present

END IF;

If there are many small items, time and processing will be lost retrieving multiple records which are not needed. This would be better written like one of the following.

SELECT COUNT(*)

INTO   v_count

FROM   items

WHERE  item_size = 'SMALL'

AND    rownum = 1;

IF v_count = 0 THEN

  -- Do processing related to no small items present

END IF;

SELECT COUNT(*)

INTO   v_count

FROM   dual

WHERE  EXISTS (SELECT 1

               FROM items

               WHERE item_size = 'SMALL');

IF v_count = 0 THEN

  -- Do processing related to no small items present

END IF;

In these examples only single a record is retrieved in the presence/absence check.

Inequalities

If a query uses inequalities (item_no > 100) the optimizer must estimate the number of rows returned before it can decide the best way to retrieve the data. This estimation is prone to errors. If you are aware of the data and it's distribution you can use optimizer hints to encourage or discourage full table scans to improve performance.
If an index is being used for a range scan on the column in question, the performance can be improved by substituting >= for >. In this case, item_no > 100 becomes item_no >= 101. In the first case, a full scan of the index will occur. In the second case, Oracle jumps straight to the first index entry with an item_no of 101 and range scans from this point. For large indexes this may significantly reduce the number of blocks read.

When Things Look Bad!

If you have a process/script that shows poor performance you should do the following:

Write sensible queries in the first place!
Identify the specific statement(s) that are causing a problem. The simplest way to do this is to use SQL Trace, but you can try running the individual statements using SQL*Plus and timing them (SET TIMING ON)
Use EXPLAIN to look at the execution plan of the statement. Look for any full table accesses that look dubious. Remember, a full table scan of a small table is often more efficient than access by index.
Check to see if there are any indexes that may help performance.
Try adding new indexes to the system to reduce excessive full table scans. Typically, foreign key columns should be indexed as these are regularly used in join conditions. On occasion it may be necessary to add composite (concatenated) indexes that will only aid individual queries. Remember, excessive indexing can reduce INSERT, UPDATE and DELETE performance.

Driving Tables (RBO Only)

The structure of the FROM and WHERE clauses of DML statements can be tailored to improve the performance of the statement. The rules vary depending on whether the database engine is using the Rule or Cost based optimizer. The situation is further complicated by the fact that the engine may perform a Merge Join or a Nested Loop join to retrieve the data. Despite this, there are a few rules you can use to improve the performance of your SQL.
Oracle processes result sets a table at a time. It starts by retrieving all the data for the first (driving) table. Once this data is retrieved it is used to limit the number of rows processed for subsequent (driven) tables. In the case of multiple table joins, the driving table limits the rows processed for the first driven table. Once processed, this combined set of data is the driving set for the second driven table etc. Roughly translated into English, this means that it is best to process tables that will retrieve a small number of rows first. The optimizer will do this to the best of it's ability regardless of the structure of the DML, but the following factors may help.
Both the Rule and Cost based optimizers select a driving table for each query. If a decision cannot be made, the order of processing is from the end of the FROM clause to the start. Therefore, you should always place your driving table at the end of the FROM clause. Subsequent driven tables should be placed in order so that those retrieving the most rows are nearer to the start of the FROM clause. Confusingly, the WHERE clause should be writen in the opposite order, with the driving tables conditions first and the final driven table last. ie.

FROM  d, c, b, a

WHERE a.join_column = 12345

AND   a.join_column = b.join_column

AND   b.join_column = c.join_column

AND   c.join_column = d.join_column;

If we now want to limit the rows brought back from the "D" table we may write the following.

FROM  d, c, b, a

WHERE a.join_column = 12345

AND   a.join_column = b.join_column

AND   b.join_column = c.join_column

AND   c.join_column = d.join_column

AND   d.name = 'JONES';

Depending on the number of rows and the presence of indexes, Oracle my now pick "D" as the driving table. Since "D" now has two limiting factors (join_column and name), it may be a better candidate as a driving table so the statement may be better written as follows.

FROM  c, b, a, d

WHERE d.name = 'JONES'

AND   d.join_column = 12345

AND   d.join_column = a.join_column

AND   a.join_column = b.join_column

AND   b.join_column = c.join_column

This grouping of limiting factors will guide the optimizer more efficiently making table "D" return relatively few rows, and so make it a more efficient driving table.
Remember, the order of the items in both the FROM and WHERE clause will not force the optimizer to pick a specific table as a driving table, but it may influence it's decision. The grouping of limiting conditions onto a single table will reduce the number of rows returned from that table, and will therefore make it a stronger candidate for becoming the driving table.

Caching Tables

Queries will execute much faster if the data they reference is already cached. For small frequently used tables performance may be improved by caching tables. Normally, when full table scans occur, the cached data is placed on the Least Recently Used (LRU) end of the buffer cache. This means that it is the first data to be paged out when more buffer space is required. If the table is cached (ALTER TABLE employees CACHE;) the data is placed on the Most Recently Used (MRU) end of the buffer, and so is less likely to be paged out before it is re-queried. Caching tables may alter the CBO's path through the data and should not be used without careful consideration.

Improving Parse Speed

Execution plans for SELECT statements are cached by the server, but unless the exact same statement is repeated the stored execution plan details will not be reused. Even differing spaces in the statement will cause this lookup to fail. Use of bind variables allows you to repeatedly use the same statements whilst changing the WHERE clause criteria. Assuming the statement does not have a cached execution plan it must be parsed before execution. The parse phase for statements can be decreased by efficient use of aliasing. If an alias is not present, the engine must resolve which tables own the specified columns. The following is an example.

Bad Statement

Good Statement

SELECT first_name,

       last_name,

       country

FROM   employee,

       countries

WHERE  country_id = id

AND    lastname   = 'HALL';

SELECT e.first_name,

       e.last_name,

       c.country

FROM   employee e,

       countries c

WHERE  e.country_id = c.id

AND    e.last_name  = 'HALL';

Packages Procedures and Functions

When an SQL statement, or anonymous block, is passed to the server it is processed in three phases.

Phase	Actions
Parse	Syntax Check and Object Resolution
Execution	Necessary Reads and Writes performed
Fetch	Resultant rows are Retrieved, Assembled, Sorted and Returned

The Parse phase is the most time and resource intensive. This phase can be avoided if all anonymous blocks are stored as Database Procedures, Functions, Packages or Views. Being database objects their SQL text and compiled code is stored in Data Dictionary and the executable copies reside in the Shared Pool.

Function : NVL2 and COALESCE

2012-06-07T17:06:00.002+05:30

NVL2

The NVL2 function accepts three parameters. If the first parameter value is not null it returns the value in the second parameter. If the first parameter value is null, it returns the third parameter.
The following query shows NVL2 in action.

SQL> SELECT * FROM null_test_tab ORDER BY id;

        ID COL1       COL2       COL3       COL4

---------- ---------- ---------- ---------- ----------

         1 ONE        TWO        THREE      FOUR

         2            TWO        THREE      FOUR

         3                       THREE      FOUR

         4                       THREE      THREE

4 rows selected.

SQL> SELECT id, NVL2(col1, col2, col3) AS output FROM null_test_tab ORDER BY id;

               ID OUTPUT

---------- ----------

                1 TWO

                2 THREE

                3 THREE

                4 THREE

4 rows selected.

SQL>

COALESCE

The COALESCE function was introduced in Oracle 9i. It accepts two or more parameters and returns the first non-null value in a list. If all parameters contain null values, it returns null.

SQL> SELECT id, COALESCE(col1, col2, col3) AS output FROM null_test_tab ORDER BY id;

        ID OUTPUT

---------- ----------

         1 ONE

         2 TWO

         3 THREE

         4 THREE

4 rows selected.

SQL>

Load the session statistics such as Session Start & End Time, Success Rows, Failed Rows and Rejected Rows etc. into a database table for audit/log purpose.

2012-02-08T16:20:00.002+05:30

Scenario:

Load the session statistics such as Session Start & End Time, Success Rows, Failed Rows and Rejected Rows etc. into a database table for audit/log purpose.

Solution:

After performing the below solution steps your end workflow will look as follows:

START => SESSION1 => ASSIGNMENT TASK => SESSION2

SOLUTION STEPS

SESSION1
This session is used to achieve your actual business logic. Meaning this session will perform your actual data load. It can be anything File Table.àFile or TableàTable, File à

WORKFLOW VARIABLES

Create the following workflow variables.

=> $$Workflowname
=> $$SessionStartTime
=> $$SessionEndTime
=> $$TargetSuccessrows
=> $$TargetFailedRows

ASSIGNMENT TASK

Use the Expression tab in the Assignment Task and assign as follows:

$$workflowname = $PMWorkflowName
$$sessionStartTime = $ SESSION1.StartTime
$$SessionEndTime = $ SESSION1.Endtime
$$ TargetSuccessrows = $ SESSION1. TgtSuccessRows
$$ TargetFailedRows = $ SESSION1. TgtFailedRows

SESSION2

This session is used to load the session statistics into a database table.
=> This should call a mapping say ‘m_sessionLog’
=> This mapping m_sessionLog should have mapping variables for the above defined workflow variables such as $$wfname, $$Stime, $$Etime, $$TSRows and $$TFRows.
=> This mapping m_sessionLog should use a dummy source and it must have a expression transformation and a target => database Audit table)
=> Inside the expression you must assign the mapping variables to the output ports
workflowname=$$wfname
starttime=$$Stime
endtime=$$Etime
SucessRows=$$TSRows
FailedRows=$$TFRows
=> Create a target database table with the following columns
Workflowname, start time, end time, success rows and failed rows.
=> Connect all the required output ports to the target which is nothing but your audit table.
PRE-Session Variable
=> Session 2: In the Pre-session variable assignment tab assign the mapping variable = workflow variable
=> In our case
$$wfname=$$workflowname
$$Stime=$$sessionStartTime
$$Etime=$$sessionEndTime
$$TSRows=$$TargetSuccessrows
$$TFRows=$$TargetFailedrows

Workflow Execution

Use Target File Path in Parameter File

2011-12-30T13:34:00.001+05:30

Scenario:

I want to use mapping parameter to store target file path. My question is can define file path in parameter file? If possible can anyone explain how to assign target file path as parameter?

Solution:

You can define the file path in parameter file.

$OutputFileName=your file path here

Give the above mentioned parameter in your parameter file.

Insert and reject records using update strategy.

2011-12-27T14:56:00.003+05:30

Scenario:

Insert and reject records using update strategy.

There is an emp table and from that table insert the data to targt where sal<3000 and reject other rows.

Solution:

1. connect out-puts from SQF to Update Strategy transformation.

2. In properties of Update Strategy write the condition like this
IIF(SAL<3000,DD_INSERT,DD_REJECT)

3. Connectthe Update Strategy to target

Convert Numeric Value to Date Format

2011-12-27T14:01:00.002+05:30

Scenario:

Suppose you are importing a flat file emp.csv and hire_date colummn is in numeric format, like 20101111 .Our objective is convert it to date,with a format 'YYYYMMDD'.
Source
EMPNO       HIRE_DATE(numeric)
-------            -----------
     1                20101111
     2                20090909
Target
EMPNO            HIRE_DATE (date)
------                   -----------
     1                   11/11/2010
     2                    09/09/2009

Solution:

Connect SQF to an expression.
In expression make hire_date as input only and make another port hire_date1 as o/p port with date data type.
In o/p port of hire_date write condition like as below

TO_DATE(TO_CHAR(hire_date),’YYYYMMDD’)

How to change a string to decimal with 2 decimal places in informatica?

2011-12-26T17:05:00.002+05:30

Scenario:

How to change a string to decimal with 2 decimal places in informatica?

Eg:: input data 12345678

I want output as 123456.78

Solution:

output = to_decimal(to_integer(input)/100,2)

SUBSTR(INPUT_FIELD, 1, LENGTH(INPUT_FIELD) - 2) || '.' || SUBSTR(INPUT_FIELD, -2)

Append the data in a flat file for a daily run

2011-12-26T16:54:00.001+05:30

Scenario:

I have the flat file in our server location; I want to append the data in a flat file for a daily run.

Solution:

We have an option in Informatica "Append if exists" in target session properties.

Convert Day No. to corresponding month and date of year

2011-12-22T18:14:00.002+05:30

Scenario:

Suppose you have a source is like this
Source
E_NO    YEAR        DAYNO
   ------   --------- - ---------
1          01-JAN-07     301
2    01-JAN-08     200
Year column is a date and dayno is numeric that represents a day ( as in 365 for 31-Dec-Year). Convert the Dayno to corresponding year's month and date and then send to target.
Target
E_NO           YEAR_MONTH_DAY
   ------            --------- ----------
     1                29-OCT-07
     2                  19-JUL-08

Solution:

Use below date format in exp transformation

Add_to_date(YEAR,’DD’,DAYNO)

How to delete duplicate rows in a table?

2011-12-22T17:38:00.001+05:30

Scenario:

How to delete duplicate rows in a table?

Solution:

delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);

delete from emp a where rowid != (select min(rowid) from emp b where a.empno=b.empno);

How to get nth max salaries ?

2011-12-22T17:37:00.002+05:30

Scenario:

How to get nth max salaries ?

Solution:

select distinct hiredate from emp a where &n = (select count(distinct sal) from emp b where a.sal >= b.sal);

How to get 3 Max & Min salaries?

2011-12-22T17:35:00.001+05:30

Scenario:

How to get 3 Max & Min salaries?

Solution:

Max - select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;

Min - select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal >= b.sal);

Find FIRST & LAST n records from a table.

2011-12-22T17:16:00.001+05:30

Scenario:

Find FIRST & LAST n records from a table.

Solution:

First - select * from emp where rownum <= &n;

Last - select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);

Find the 3rd MAX & MIN salary in the emp table

2011-12-22T17:13:00.002+05:30

Scenario:

Find the 3rd MAX & MIN salary in the emp table

Solution:

Max -

select distinct sal from emp e1 where 3 =

(select count(distinct sal) from emp e2 where e1.sal <= e2.sal);

Min -

select distinct sal from emp e1 where 3 =

(select count(distinct sal) from emp e2 where e1.sal >= e2.sal);

Sql query to find EVEN & ODD NUMBERED records from a table.

2011-12-22T16:58:00.001+05:30

Scenario:

Sql query to find EVEN & ODD NUMBERED records from a table.

Solution:

Even - select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);

Odd - select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);

SQL questions which are the most frequently asked in interviews.

2011-12-22T16:55:00.004+05:30

Complex Queries in SQL ( Oracle )

 To fetch ALTERNATE records from a table. (EVEN NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);

 To select ALTERNATE records from a table. (ODD NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);

 Find the 3rd MAX salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);

 Find the 3rd MIN salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);

 Select FIRST n records from a table.
select * from emp where rownum <= &n;

 Select LAST n records from a table
select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);

 List dept no., Dept name for all the departments in which there are no employees in the department.
select * from dept where deptno not in (select deptno from emp);
alternate solution: select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
altertnate solution: select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;

 How to get 3 Max salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;

 How to get 3 Min salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal >= b.sal);

 How to get nth max salaries ?
select distinct hiredate from emp a where &n = (select count(distinct sal) from emp b where a.sal >= b.sal);

 Select DISTINCT RECORDS from emp table.
select * from emp a where rowid = (select max(rowid) from emp b where a.empno=b.empno);

 How to delete duplicate rows in a table?
delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);

 Count of number of employees in department wise.
select count(EMPNO), b.deptno, dname from emp a, dept b where a.deptno(+)=b.deptno group by b.deptno,dname;

 Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
select ename,sal/12 as monthlysal from emp;
 Select all record from emp table where deptno =10 or 40.
select * from emp where deptno=30 or deptno=10;
 Select all record from emp table where deptno=30 and sal>1500.
select * from emp where deptno=30 and sal>1500;
 Select all record from emp where job not in SALESMAN or CLERK.
select * from emp where job not in ('SALESMAN','CLERK');
 Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.
select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');
 Select all records where ename starts with ‘S’ and its lenth is 6 char.
select * from emp where ename like'S____';
 Select all records where ename may be any no of character but it should end with ‘R’.
select * from emp where ename like'%R';
 Count MGR and their salary in emp table.
select count(MGR),count(sal) from emp;
 In emp table add comm+sal as total sal .
select ename,(sal+nvl(comm,0)) as totalsal from emp;
 Select any salary <3000 from emp table.
select * from emp where sal> any(select sal from emp where sal<3000);
 Select all salary <3000 from emp table.
select * from emp where sal> all(select sal from emp where sal<3000);
 Select all the employee group by deptno and sal in descending order.
select ename,deptno,sal from emp order by deptno,sal desc;
 How can I create an empty table emp1 with same structure as emp?
Create table emp1 as select * from emp where 1=2;
 How to retrive record where sal between 1000 to 2000?
Select * from emp where sal>=1000 And sal<2000
 Select all records where dept no of both emp and dept table matches.
select * from emp where exists(select * from dept where emp.deptno=dept.deptno)
 If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
(Select * from emp) Union (Select * from emp1)
 How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)
 How can I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)
 Count the totalsa deptno wise where more than 2 employees exist.
SELECT deptno, sum(sal) As totalsal
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2

Complex Queries in SQL ( Oracle )

2011-12-22T16:55:00.001+05:30