quinta-feira, 23 de agosto de 2012

Usando Joins


Joins

join is a query that combines rows from two or more tables, views, or materialized views. Oracle performs a join whenever multiple tables appear in the query's FROM clause. The query's select list can select any columns from any of these tables. If any two of these tables have a column name in common, you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

Join Conditions

Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition. To execute a join, Oracle combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE. The columns in the join conditions need not also appear in the select list.

Equijoins

An equijoin is a join with a join condition containing an equality operator ( = ). An equijoin combines rows that have equivalent values for the specified columns.

For example the following query returns empno,name,sal,deptno and department name and city from department table.

select emp.empno,emp.ename,emp.sal,emp.deptno,dept.dname,dept.city from emp,dept where emp.deptno=dept.deptno;

The above query can also be written like, using aliases, given below.

select e.empno, e.ename, e.sal, e.deptno, d.dname, d.city from emp e, dept d where emp.deptno=dept.deptno;

The above query can also be written like given below without using  table qualifiers.

select empno,ename,sal,dname,city from emp,dept where emp.deptno=dept.deptno;

And if you want to see all the columns of both tables then the query can be written like this.

select * from emp,dept where emp.deptno=dept.deptno;

Non Equi Joins.

Non equi joins is used to return result from two or more tables where exact join is not possible.
For example we have emp table and salgrade table. The salgrade table contains grade and their low salary and high salary. Suppose you want to find the grade of employees based on their salaries then you can use NON EQUI join.

select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.lowsal and s.hisal

Self Joins

self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle combines and returns rows of the table that satisfy the join condition.
For example the following query returns employee names and their manager names for whom they are working.

Select e.empno, e.ename, m.ename  “Manager” from emp e,
emp m where e.mgrid=m.empno

Inner Join

An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition.

Outer Joins

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
  • To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the ANSI LEFT [OUTERJOIN syntax, or apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B.
  • To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the ANSI RIGHT [OUTER] syntax, or apply the outer join operator (+) to all columns of A in the join condition. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.
  • To write a query that performs an outer join and and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the ANSIFULL [OUTERJOIN syntax.
For example the following query returns all the employees and department names and even those department names where no employee is working.

select e.empno,e.ename,e.sal,e.deptno,d.dname,d.city  from emp e, dept d where e.deptno(+)=d.deptno;

That is specify the (+) sign to the column which is lacking values.

Cartesian Products

If two tables in a join query have no join condition, Oracle returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows. Always include a join condition unless you specifically need a Cartesian product.

For example:

select a.posto_name, b.posto_name from sq_tmp a, sq_tmp b

Fonte:http://www.oracle-dba-online.com/sql/join_queries.htm

terça-feira, 21 de agosto de 2012

Códigos de Bancos


Código de Compensação 120 Bancos Associados
246 Banco ABC Brasil S.A.
25 Banco Alfa S.A.
641 Banco Alvorada S.A.
29 Banco Banerj S.A.
38 Banco Banestado S.A.
M01 Banco Bankpar S.A.
740 Banco Barclays S.A.
107 Banco BBM S.A.
31 Banco Beg S.A.
96 Banco BM&F de Serviços de Liquidação e Custódia S.A
318 Banco BMG S.A.
752 Banco BNP Paribas Brasil S.A.
248 Banco Boavista Interatlântico S.A.
36 Banco Bradesco BBI S.A.
237 Banco Bradesco S.A.
225 Banco Brascan S.A.
44 Banco BVA S.A.
263 Banco Cacique S.A.
222 Banco Calyon Brasil S.A.
40 Banco Cargill S.A.
Banco Carrefour S.A.
745 Banco Citibank S.A.
M08 Banco Citicard S.A.
M19 Banco CNH Capital S.A.
215 Banco Comercial e de Investimento Sudameris S.A.
756 Banco Cooperativo do Brasil S.A. – BANCOOB
748 Banco Cooperativo Sicredi S.A.
505 Banco Credit Suisse (Brasil) S.A.
229 Banco Cruzeiro do Sul S.A.
3 Banco da Amazônia S.A.
707 Banco Daycoval S.A.
M06 Banco de Lage Landen Brasil S.A.
24 Banco de Pernambuco S.A. – BANDEPE
456 Banco de Tokyo-Mitsubishi UFJ Brasil S.A.
214 Banco Dibens S.A.
1 Banco do Brasil S.A.
27 Banco do Estado de Santa Catarina S.A.
47 Banco do Estado de Sergipe S.A.
37 Banco do Estado do Pará S.A.
41 Banco do Estado do Rio Grande do Sul S.A.
4 Banco do Nordeste do Brasil S.A.
265 Banco Fator S.A.
M03 Banco Fiat S.A.
224 Banco Fibra S.A.
626 Banco Ficsa S.A.
394 Banco Finasa BMC S.A.
175 Banco Finasa S.A.
252 Banco Fininvest S.A.
M18 Banco Ford S.A.
233 Banco GE Capital S.A.
734 Banco Gerdau S.A.
M07 Banco GMAC S.A.
612 Banco Guanabara S.A.
M22 Banco Honda S.A.
63 Banco Ibi S.A. Banco Múltiplo
M11 Banco IBM S.A.
604 Banco Industrial do Brasil S.A.
320 Banco Industrial e Comercial S.A.
653 Banco Indusval S.A.
630 Banco Intercap S.A.
249 Banco Investcred Unibanco S.A.
184 Banco Itaú BBA S.A.
652 Banco Itaú Holding Financeira S.A.
341 Banco Itaú S.A.
479 Banco ItaúBank S.A
M09 Banco Itaucred Financiamentos S.A.
376 Banco J. P. Morgan S.A.
74 Banco J. Safra S.A.
217 Banco John Deere S.A.
65 Banco Lemon S.A.
600 Banco Luso Brasileiro S.A.
389 Banco Mercantil do Brasil S.A.
755 Banco Merrill Lynch de Investimentos S.A.
746 Banco Modal S.A.
151 Banco Nossa Caixa S.A.
45 Banco Opportunity S.A.
623 Banco Panamericano S.A.
611 Banco Paulista S.A.
643 Banco Pine S.A.
Banco Porto Real de Investimentos S.A.
638 Banco Prosper S.A.
747 Banco Rabobank International Brasil S.A.
356 Banco Real S.A.
633 Banco Rendimento S.A.
M16 Banco Rodobens S.A.
72 Banco Rural Mais S.A.
453 Banco Rural S.A.
422 Banco Safra S.A.
33 Banco Santander S.A.
250 Banco Schahin S.A.
749 Banco Simples S.A.
366 Banco Société Générale Brasil S.A.
637 Banco Sofisa S.A.
464 Banco Sumitomo Mitsui Brasileiro S.A.
M20 Banco Toyota do Brasil S.A.
634 Banco Triângulo S.A.
208 Banco UBS Pactual S.A.
116 Banco Único S.A.
M14 Banco Volkswagen S.A.
655 Banco Votorantim S.A.
610 Banco VR S.A.
370 Banco WestLB do Brasil S.A.
21 BANESTES S.A. Banco do Estado do Espírito Santo
719 Banif-Banco Internacional do Funchal (Brasil)S.A.
204 Bankpar Banco Multiplo S.A..
73 BB Banco Popular do Brasil S.A.
78 BES Investimento do Brasil S.A.-Banco de Investimento
69 BPN Brasil Banco Múltiplo S.A.
70 BRB – Banco de Brasília S.A.
104 Caixa Econômica Federal
477 Citibank N.A.
Concórdia Banco S.A.
487 Deutsche Bank S.A. – Banco Alemão
751 Dresdner Bank Brasil S.A. – Banco Múltiplo
62 Hipercard Banco Múltiplo S.A.
399 HSBC Bank Brasil S.A. – Banco Múltiplo
492 ING Bank N.V.
488 JPMorgan Chase Bank
409 UNIBANCO – União de Bancos Brasileiros S.A.
230 Unicard Banco Múltiplo S.A.
Related Posts Plugin for WordPress, Blogger...