Joins
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Joins as PDF for free.
More details
- Words: 860
- Pages: 6
l> select * from salgrade; grade losal hisal -------- ---------- ---------1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 l> select max(sal),sum(sal) count(*) from emp group by deptno; lect max(sal),sum(sal) count(*) from emp group by deptno * ror at line 1: a-00923: from keyword not found where expected l> select max(sal),sum(sal),count(*) from emp group by deptno; max(sal) sum(sal) count(*) -------- ---------- ---------5000 8750 3 3000 10875 5 2850 9400 6 l> select deptno max(sal),sum(sal),count(*) from emp where sal>2000 group by deptno; elect deptno max(sal),sum(sal),count(*) from emp where sal>2000 group by deptno * ror at line 1: a-00923: from keyword not found where expected l> select deptno,max(sal),sum(sal),count(*) from emp where sal>2000 group by deptno; deptno max(sal) sum(sal) count(*) -------- ---------- ---------- ---------10 5000 7450 2 20 3000 8975 3 30 2850 2850 1 l> select deptno,max(sal),sum(sal),count(*) from emp where sal>2000 group by deptno having sum(sa >2000 order by sum(sal 2 ); deptno max(sal) sum(sal) count(*) -------- ---------- ---------- ---------30 2850 2850 1 10 5000 7450 2 20 3000 8975 3 l> select sum(sal) from emp group by deptno; sum(sal) -------8750
10875 9400 l> select a.sal,b.grade from emp a, salgrade b where a.sal between b.losal and b.hisal; sal grade -------- ---------950 1 800 1 1100 1 1250 2 1250 2 1300 2 1600 3 1500 3 2850 4 2450 4 2975 4 sal grade -------- ---------3000 4 3000 4 5000 5 rows selected. l> select deptno,e.empno,e.ename,d.dname from dept d natural join emp e; deptno empno ename dname -------- ---------- ---------- -------------10 7839 king accounting 30 7698 blake sales 10 7782 clark accounting 20 7566 jones research 30 7654 martin sales 30 7499 allen sales 30 7844 turner sales 30 7900 james sales 30 7521 ward sales 20 7902 ford research 20 7369 smith research deptno empno ename dname -------- ---------- ---------- -------------20 7788 scott research 20 7876 adams research 10 7934 miller accounting rows selected. l> select d.deptno,e.empno,e.ename,d.dname from dept d cross join emp e; deptno empno ename dname -------- ---------- ---------- -------------10 7839 king accounting
20 30 40 10 20 30 40 10 20 30
7839 king 7839 king 7839 king 7698 blake 7698 blake 7698 blake 7698 blake 7782 clark 7782 clark 7782 clark
research sales operations accounting research sales operations accounting research sales
deptno empno ename dname -------- ---------- ---------- -------------40 7782 clark operations 10 7566 jones accounting 20 7566 jones research 30 7566 jones sales 40 7566 jones operations 10 7654 martin accounting 20 7654 martin research 30 7654 martin sales 40 7654 martin operations 10 7499 allen accounting 20 7499 allen research deptno empno ename dname -------- ---------- ---------- -------------30 7499 allen sales 40 7499 allen operations 10 7844 turner accounting 20 7844 turner research 30 7844 turner sales 40 7844 turner operations 10 7900 james accounting 20 7900 james research 30 7900 james sales 40 7900 james operations 10 7521 ward accounting deptno empno ename dname -------- ---------- ---------- -------------20 7521 ward research 30 7521 ward sales 40 7521 ward operations 10 7902 ford accounting 20 7902 ford research 30 7902 ford sales 40 7902 ford operations 10 7369 smith accounting 20 7369 smith research 30 7369 smith sales 40 7369 smith operations deptno empno ename dname -------- ---------- ---------- -------------10 7788 scott accounting
20 30 40 10 20 30 40 10 20 30
7788 scott 7788 scott 7788 scott 7876 adams 7876 adams 7876 adams 7876 adams 7934 miller 7934 miller 7934 miller
research sales operations accounting research sales operations accounting research sales
deptno empno ename dname -------- ---------- ---------- -------------40 7934 miller operations rows selected. l> select 2 d.deptno,e.empno,e.ename,d.dname from dept d natural join emp e; deptno,e.empno,e.ename,d.dname from dept d natural join emp e ror at line 2: a-25155: column used in natural join cannot have qualifier l> select 2 d.deptno,e.empno,e.ename,d.dname from dept d join emp e on(d.deptno=e.deptno); deptno empno ename dname -------- ---------- ---------- -------------10 7839 king accounting 30 7698 blake sales 10 7782 clark accounting 20 7566 jones research 30 7654 martin sales 30 7499 allen sales 30 7844 turner sales 30 7900 james sales 30 7521 ward sales 20 7902 ford research 20 7369 smith research deptno empno ename dname -------- ---------- ---------- -------------20 7788 scott research 20 7876 adams research 10 7934 miller accounting rows selected. l> l> select a.deptno,b.deptno from dept a,emp b where a.deptno=b.empno; rows selected l> select a.deptno,b.deptno from dept a full outer join emp b on(a.deptno=b.empno);
deptno deptno -------- ---------30 40 20 10 10 30 10 20 30 30 30 deptno deptno -------- ---------30 30 20 20 20 20 10 rows selected. l> select a.deptno,b.deptno from dept a left outer join emp b on(a.deptno=b.empno); deptno deptno -------- ---------30 40 20 10 l> select a.deptno,b.deptno from dept a right outer join emp b on(a.deptno=b.empno); deptno deptno -------- ---------10 30 10 20 30 30 30 30 30 20 20 deptno deptno -------- ---------20 20
10 rows selected. l> select a.empno,b.mgr from emp a,emp b where a.empno=b.mgr; empno mgr -------- ---------7839 7839 7839 7839 7839 7839 7698 7698 7698 7698 7698 7698 7698 7698 7698 7698 7566 7566 7902 7902 7566 7566 empno mgr -------- ---------7788 7788 7782 7782 rows selected. l> l>
10875 9400 l> select a.sal,b.grade from emp a, salgrade b where a.sal between b.losal and b.hisal; sal grade -------- ---------950 1 800 1 1100 1 1250 2 1250 2 1300 2 1600 3 1500 3 2850 4 2450 4 2975 4 sal grade -------- ---------3000 4 3000 4 5000 5 rows selected. l> select deptno,e.empno,e.ename,d.dname from dept d natural join emp e; deptno empno ename dname -------- ---------- ---------- -------------10 7839 king accounting 30 7698 blake sales 10 7782 clark accounting 20 7566 jones research 30 7654 martin sales 30 7499 allen sales 30 7844 turner sales 30 7900 james sales 30 7521 ward sales 20 7902 ford research 20 7369 smith research deptno empno ename dname -------- ---------- ---------- -------------20 7788 scott research 20 7876 adams research 10 7934 miller accounting rows selected. l> select d.deptno,e.empno,e.ename,d.dname from dept d cross join emp e; deptno empno ename dname -------- ---------- ---------- -------------10 7839 king accounting
20 30 40 10 20 30 40 10 20 30
7839 king 7839 king 7839 king 7698 blake 7698 blake 7698 blake 7698 blake 7782 clark 7782 clark 7782 clark
research sales operations accounting research sales operations accounting research sales
deptno empno ename dname -------- ---------- ---------- -------------40 7782 clark operations 10 7566 jones accounting 20 7566 jones research 30 7566 jones sales 40 7566 jones operations 10 7654 martin accounting 20 7654 martin research 30 7654 martin sales 40 7654 martin operations 10 7499 allen accounting 20 7499 allen research deptno empno ename dname -------- ---------- ---------- -------------30 7499 allen sales 40 7499 allen operations 10 7844 turner accounting 20 7844 turner research 30 7844 turner sales 40 7844 turner operations 10 7900 james accounting 20 7900 james research 30 7900 james sales 40 7900 james operations 10 7521 ward accounting deptno empno ename dname -------- ---------- ---------- -------------20 7521 ward research 30 7521 ward sales 40 7521 ward operations 10 7902 ford accounting 20 7902 ford research 30 7902 ford sales 40 7902 ford operations 10 7369 smith accounting 20 7369 smith research 30 7369 smith sales 40 7369 smith operations deptno empno ename dname -------- ---------- ---------- -------------10 7788 scott accounting
20 30 40 10 20 30 40 10 20 30
7788 scott 7788 scott 7788 scott 7876 adams 7876 adams 7876 adams 7876 adams 7934 miller 7934 miller 7934 miller
research sales operations accounting research sales operations accounting research sales
deptno empno ename dname -------- ---------- ---------- -------------40 7934 miller operations rows selected. l> select 2 d.deptno,e.empno,e.ename,d.dname from dept d natural join emp e; deptno,e.empno,e.ename,d.dname from dept d natural join emp e ror at line 2: a-25155: column used in natural join cannot have qualifier l> select 2 d.deptno,e.empno,e.ename,d.dname from dept d join emp e on(d.deptno=e.deptno); deptno empno ename dname -------- ---------- ---------- -------------10 7839 king accounting 30 7698 blake sales 10 7782 clark accounting 20 7566 jones research 30 7654 martin sales 30 7499 allen sales 30 7844 turner sales 30 7900 james sales 30 7521 ward sales 20 7902 ford research 20 7369 smith research deptno empno ename dname -------- ---------- ---------- -------------20 7788 scott research 20 7876 adams research 10 7934 miller accounting rows selected. l> l> select a.deptno,b.deptno from dept a,emp b where a.deptno=b.empno; rows selected l> select a.deptno,b.deptno from dept a full outer join emp b on(a.deptno=b.empno);
deptno deptno -------- ---------30 40 20 10 10 30 10 20 30 30 30 deptno deptno -------- ---------30 30 20 20 20 20 10 rows selected. l> select a.deptno,b.deptno from dept a left outer join emp b on(a.deptno=b.empno); deptno deptno -------- ---------30 40 20 10 l> select a.deptno,b.deptno from dept a right outer join emp b on(a.deptno=b.empno); deptno deptno -------- ---------10 30 10 20 30 30 30 30 30 20 20 deptno deptno -------- ---------20 20
10 rows selected. l> select a.empno,b.mgr from emp a,emp b where a.empno=b.mgr; empno mgr -------- ---------7839 7839 7839 7839 7839 7839 7698 7698 7698 7698 7698 7698 7698 7698 7698 7698 7566 7566 7902 7902 7566 7566 empno mgr -------- ---------7788 7788 7782 7782 rows selected. l> l>
