Pichol som sem logy/poznamky z DB cvik, co mi Ivan Kahanec forwardol. Je to tu vsetko na jednej stranke, ale ak niekto chce originalny plaintext format: post 1, post 2, post 3, post 4, post 5, post 6. Este som to necital, iba som poodsekaval hlavicky a signatury. Dalsie logy su na kravicke.
> zaciname: > > min,max,sum,avg.. funkcie.. vid haluze typu > atachment > :-) > > count, > > GROUP BY > > rozdeli tabulku podla stlpca kde sa hodnoty rovnaju > nemozeme si dat vypisat stlpec,kde su swame rozne > hodnoty, ci ako, teda nemoze byt select > deptno,count(empno),SAL to SAL nemoze byt.. > ani podla aliasu > stlpec ktory groupujeme nemusi byt v select.. > > ORDER BY > proste treba pozriet log.. > > HAVING je to priblizne where pre funkcie.. > order by by bolo az po having.. > > treba kukat LOG!! > VNORENY SELECT, vsade kde ocakavame nejaku hodnotu.. > mozeme pouzit vnoreny select > > vo WHERE HAVING .. moze byt aj viac vonrenych > selectov, pricom nemusia pouizivat tu istu tabulku.. > aj vo fom mze byt vonoreny select > > single-row ->vracia jeden riadok.. > multiple-row IN, ANY, ALL > > kukat log, bo sa mi sem nechcelo pisat// > ak je vysledokvnoreneho selectu null, tak aj cely > vysledok je NULL!!!!=> coalesce > > > ENZO > > ===== > > kahanec=> select > min(sal),max(sal),sum(sal),round(avg(sal),0) > kahanec-> from emp; > min | max | sum | round > --------+---------+----------+------- > 800.00 | 5000.00 | 28075.00 | 2005 > (1 row) > > > kahanec=> select count(empno) > kahanec-> from emp > kahanec-> where job='SALESMAN'; > count > ------- > 4 > (1 row) > > kahanec=> select max(sal)-min(sal) as "rozdiel" > kahanec-> from emp; > rozdiel > --------- > 4200.00 > (1 row) > > kahanec=> select deptno,count(empno) > kahanec-> from emp > kahanec-> group by deptno; > deptno | count > --------+------- > 10 | 3 > 20 | 5 > 30 | 6 > (3 rows) > > kahanec=> select deptno,avg(sal) from emp group by > deptno; > deptno | avg > --------+----------------- > 10 | 2600.0000000000 > 20 | 2175.0000000000 > 30 | 1566.6666666667 > (3 rows) > > kahanec=> select deptno,max(sal) from emp group by > deptno order by max(sal); > deptno | max > --------+--------- > 30 | 2850.00 > 20 | 3000.00 > 10 | 5000.00 > (3 rows) > > kahanec=> select deptno,max(sal) from emp group by > deptno order by max(sal) des > c; > deptno | max > --------+--------- > 10 | 5000.00 > 20 | 3000.00 > 30 | 2850.00 > (3 rows) > > kahanec=> select d.dname,count(e.empno) > kahanec-> from emp as e,dept as d > kahanec-> where d.deptno=e.deptno > kahanec-> group by d.deptno,d.dname; > dname | count > ------------+------- > ACCOUNTING | 3 > RESEARCH | 5 > SALES | 6 > (3 rows) > > kahanec=> select d.dname,count(e.empno),d.loc > kahanec-> from emp as e,dept as d > kahanec-> where d.deptno=e.deptno > kahanec-> group by d.deptno,d.dname,d.loc; > dname | count | loc > ------------+-------+---------- > ACCOUNTING | 3 | NEW YORK > RESEARCH | 5 | DALLAS > SALES | 6 | CHICAGO > (3 rows) > > kahanec=> select deptno,job,count(empno) > kahanec-> from emp > kahanec-> group by deptno,job; > deptno | job | count > --------+-----------+------- > 10 | CLERK | 1 > 10 | MANAGER | 1 > 10 | PRESIDENT | 1 > 20 | ANALYST | 2 > 20 | CLERK | 2 > 20 | MANAGER | 1 > 30 | CLERK | 1 > 30 | MANAGER | 1 > 30 | SALESMAN | 4 > (9 rows) > > kahanec=> select mgr,avg(sal) > kahanec-> from emp > kahanec-> group by mgr; > mgr | avg > ------+----------------- > 7566 | 3000.0000000000 > 7698 | 1310.0000000000 > 7782 | 1300.0000000000 > 7788 | 1100.0000000000 > 7839 | 2441.6666666667 > 7902 | 800.0000000000 > | 5000.0000000000 > (7 rows) > > kahanec=> select mgr,avg(sal) from emp > kahanec-> group by mgr > kahanec-> having avg(sal)>2000; > mgr | avg > ------+----------------- > 7566 | 3000.0000000000 > 7839 | 2441.6666666667 > | 5000.0000000000 > (3 rows) > > kahanec=> select deptno,max(sal) > kahanec-> from emp > kahanec-> group by deptno > kahanec-> having max(sal)>2900; > deptno | max > --------+--------- > 10 | 5000.00 > 20 | 3000.00 > (2 rows) > > kahanec=> select job,sum(sal) from emp where > job<>'SALESMAN' group by job havin > g sum(sal)>5000 order by sum(sal); > job | sum > ---------+--------- > ANALYST | 6000.00 > MANAGER | 7325.00 > (2 rows) > > kahanec=> select dname,loc,count(empno),avg(sal) > kahanec-> from emp,dept > kahanec-> where emp.deptno=dept.deptno > kahanec-> group by dname,loc,dept.deptno; > dname | loc | count | avg > ------------+----------+-------+----------------- > ACCOUNTING | NEW YORK | 3 | 2600.0000000000 > RESEARCH | DALLAS | 5 | 2175.0000000000 > SALES | CHICAGO | 6 | 1566.6666666667 > (3 rows) > > kahanec=> select mgr,min(sal) > kahanec-> from emp > kahanec-> where mgr is not null > kahanec-> group by mgr > kahanec-> having min(sal)>1000 > kahanec-> order by min(sal) desc; > mgr | min > ------+--------- > 7566 | 3000.00 > 7839 | 1500.00 > 7782 | 1300.00 > 7788 | 1100.00 > (4 rows) > > kahanec=> select ename from emp > kahanec-> where sal>(select sal from emp where > ename='JONES') > kahanec-> ; > ename > ------- > KING > FORD > SCOTT > (3 rows) > > VNORENY SELECT > > kahanec=> select max(salavg) > kahanec-> from (select avg(sal) as salavg from emp > group by deptno) average; > max > ----------------- > 2600.0000000000 > (1 row) > > kahanec=> select deptno,ename,sal > kahanec-> from emp > kahanec-> where sal IN(select min (sal) from emp > group by deptno) > kahanec-> ; > deptno | ename | sal > --------+--------+--------- > 30 | JAMES | 950.00 > 20 | SMITH | 800.00 > 10 | MILLER | 1300.00 > (3 rows) > > kahanec=> select deptno,ename,sal > kahanec-> from emp > kahanec-> where sal>ANY(select min (sal) from emp > group by deptno) > kahanec-> ; > deptno | ename | sal > --------+--------+--------- > 10 | KING | 5000.00 > 30 | BLAKE | 2850.00 > 10 | CLARK | 1500.00 > 20 | JONES | 2975.00 > 30 | MARTIN | 1250.00 > 30 | ALLEN | 1600.00 > 30 | TURNER | 1500.00 > 30 | JAMES | 950.00 > 30 | WARD | 1250.00 > 20 | FORD | 3000.00 > 20 | SCOTT | 3000.00 > 20 | ADAMS | 1100.00 > 10 | MILLER | 1300.00 > (13 rows) > > kahanec=> select deptno,ename,sal > kahanec-> from emp > kahanec-> where sal>ALL(select min (sal) from emp > group by deptno) > kahanec-> ; > deptno | ename | sal > --------+--------+--------- > 10 | KING | 5000.00 > 30 | BLAKE | 2850.00 > 10 | CLARK | 1500.00 > 20 | JONES | 2975.00 > 30 | ALLEN | 1600.00 > 30 | TURNER | 1500.00 > 20 | FORD | 3000.00 > 20 | SCOTT | 3000.00 > (8 rows) > > > kahanec=> select ename, job, sal > kahanec-> from emp > kahanec-> where job<>'CLERK' and sal<ANY(select sal > from emp where job='CLERK') > ; > ename | job | sal > --------+----------+--------- > MARTIN | SALESMAN | 1250.00 > WARD | SALESMAN | 1250.00 > (2 rows) > > kahanec=> select ename,job,sal from emp > kahanec-> where sal>all(select avg(sal) from emp > group by deptno); > ename | job | sal > -------+-----------+--------- > KING | PRESIDENT | 5000.00 > BLAKE | MANAGER | 2850.00 > JONES | MANAGER | 2975.00 > FORD | ANALYST | 3000.00 > SCOTT | ANALYST | 3000.00 > (5 rows) > > > kahanec=> select deptno,min(sal) from emp group by > deptno having min(sal)>(sele > ct min(sal) from emp where deptno=30); > deptno | min > --------+--------- > 10 | 1300.00 > (1 row) > > kahanec=> select ename,sal > kahanec-> from emp > kahanec-> where sal<=ALL(select sal from emp); > ename | sal > -------+-------- > SMITH | 800.00 > (1 row) > kahanec=> select ename,sal > kahanec-> from emp > kahanec-> where sal=(select min(sal) from emp); > ename | sal > -------+-------- > SMITH | 800.00 > (1 row) > > kahanec=> select ename,deptno,job,sal > kahanec-> from emp > kahanec-> where deptno=(select deptno from emp where > ename='MARTIN') and job = > (select job from emp where ename='MARTIN'); > ename | deptno | job | sal > --------+--------+----------+--------- > MARTIN | 30 | SALESMAN | 1250.00 > ALLEN | 30 | SALESMAN | 1600.00 > TURNER | 30 | SALESMAN | 1500.00 > WARD | 30 | SALESMAN | 1250.00 > (4 rows) > > kahanec=> select ename,deptno,job,sal > kahanec-> from emp > kahanec-> where (deptno,job)=(select deptno,job from > emp where ename='MARTIN'); > > ename | deptno | job | sal > --------+--------+----------+--------- > MARTIN | 30 | SALESMAN | 1250.00 > ALLEN | 30 | SALESMAN | 1600.00 > TURNER | 30 | SALESMAN | 1500.00 > WARD | 30 | SALESMAN | 1250.00 > (4 rows)
> batora=> select max(priemdep) from (select avg(sal)
> as priemdep from emp
> group
> by deptno) priemer;
> max
> -----------------
> 2600.0000000000
> (1 row)
>
> batora=> select deptno, max(priemdep) from (select
> avg(sal) as priemdep
> from em
> p group by deptno) priemer;
> ERROR: Attribute 'deptno' not found
> batora=> select max(priemdep) from (select avg(sal)
> as priemdep from emp
> group
> by deptno) priemer;
> max
> -----------------
> 2600.0000000000
> (1 row)
>
> batora=> select max(priemdep) from (select avg(sal)
> as priemdep from emp
> group
> by deptno where avg(sal)=max(priemdep)) priemer;
> ERROR: parser: parse error at or near "where"
> batora=> select deptno, avg(sal) from emp group by
> deptno having avg(sal)
> >= Al
> l(select avg(sal) from emp group by deptno);
> deptno | avg
> --------+-----------------
> 10 | 2600.0000000000
> (1 row)
>
> batora=> select deptno, avg(sal) from emp group by
> deptno having avg(sal)
> <= Al
> l(select avg(sal) from emp group by deptno);
> deptno | avg
> --------+-----------------
> 30 | 1566.6666666667
> (1 row)
>
> batora=> select deptno, trunc(avg(sal),0) from emp
> group by deptno having
> avg(s
> al) <= All(select avg(sal) from emp group by
> deptno);
> deptno | trunc
> --------+-------
> 30 | 1566
> (1 row)
>
> batora=> select deptno, trunc(avg(sal),2) from emp
> group by deptno having
> avg(s
> al) <= All(select avg(sal) from emp group by
> deptno);
> deptno | trunc
> --------+---------
> 30 | 1566.66
> (1 row)
>
> batora=> select m.ename, e.count(*) from emp as
> m,emp as e where
> e.mgr=m.empno;
> ERROR: parser: parse error at or near "("
> batora=> select m.ename, e.count from emp as m,emp
> as e where
> e.mgr=m.empno;
> ERROR: No such attribute or function 'count'
> batora=> select m.ename, e.ename from emp as m,emp
> as e where
> e.mgr=m.empno;
> ename | ename
> -------+--------
> JONES | FORD
> JONES | SCOTT
> BLAKE | MARTIN
> BLAKE | ALLEN
> BLAKE | TURNER
> BLAKE | JAMES
> BLAKE | WARD
> CLARK | MILLER
> SCOTT | ADAMS
> KING | BLAKE
> KING | CLARK
> KING | JONES
> FORD | SMITH
> (13 rows)
>
> batora=> select m.ename as Sef, count(e.ename) from
> emp as m,emp as e
> where e.m
> gr=m.empno;
> ERROR: Attribute m.ename must be GROUPed or used in
> an aggregate function
> batora=> select m.ename as Sef, count(e.ename) from
> emp as m,emp as e
> where e.m
> gr=m.empno group by m.ename;
> sef | count
> -------+-------
> BLAKE | 5
> CLARK | 1
> FORD | 1
> JONES | 2
> KING | 3
> SCOTT | 1
> (6 rows)
>
> batora=> select m.ename as Sef,
> coalesce(count(e.ename),0) from emp as
> m,emp as
> e where e.mgr=m.empno group by m.ename;
> sef | case
> -------+------
> BLAKE | 5
> CLARK | 1
> FORD | 1
> JONES | 2
> KING | 3
> SCOTT | 1
> (6 rows)
>
> batora=> select m.ename as Sef,
> coalesce(count(e.ename),0) from emp as
> m,emp as
> e where e.mgr=m.empno group by m.ename UNION
> select m.ename, 0 as count
> from
> emp e where NOT EXISTS (select * from emp as P
> where P.mgr=e.empno);
> ERROR: Relation 'm' does not exist
> batora=> select m.ename as Sef,
> coalesce(count(e.ename),0) from emp as
> m,emp as
> e where e.mgr=m.empno group by m.ename UNION
> select e.ename, 0 as count
> from
> emp e where NOT EXISTS (select * from emp as P
> where P.mgr=e.empno);
> sef | case
> --------+------
> ADAMS | 0
> ALLEN | 0
> BLAKE | 5
> CLARK | 1
> FORD | 1
> JAMES | 0
> JONES | 2
> KING | 3
> MARTIN | 0
> MILLER | 0
> SCOTT | 1
> SMITH | 0
> TURNER | 0
> WARD | 0
> (14 rows)
>
> batora=> select m.ename as Sef,
> coalesce(count(e.ename),0) from emp as
> m,emp as
> e where e.mgr=m.empno group by m.ename UNION
> select e.ename, 0 as count
> from
> emp e where NOT EXISTS (select * from emp as P
> where P.mgr=e.empno) desc;
> ERROR: parser: parse error at or near "desc"
> batora=> select m.ename as Sef,
> coalesce(count(e.ename),0) from emp as
> m,emp as
> e where e.mgr=m.empno group by m.ename UNION
> select e.ename, 0 as count
> from
> emp e where NOT EXISTS (select * from emp as P
> where P.mgr=e.empno);
> sef | case
> --------+------
> ADAMS | 0
> ALLEN | 0
> BLAKE | 5
> CLARK | 1
> FORD | 1
> JAMES | 0
> JONES | 2
> KING | 3
> MARTIN | 0
> MILLER | 0
> SCOTT | 1
> SMITH | 0
> TURNER | 0
> WARD | 0
> (14 rows)
>
> batora=> select m.ename as Sef,
> coalesce(count(e.ename),0) from emp as
> m,emp as
> e where e.mgr=m.empno group by m.ename UNION
> select e.ename, 0 as count
> from
> emp e where NOT EXISTS (select * from emp as P
> where P.mgr=e.empno) desc;
> ERROR: parser: parse error at or near "desc"
> batora=> select m.ename as Sef,
> coalesce(count(e.ename),0) from emp as
> m,emp as
> e where e.mgr=m.empno group by m.ename UNION
> select e.ename, 0 as count
> from
> emp e where NOT EXISTS (select * from emp as P
> where P.mgr=e.empno) DESC;
> ERROR: parser: parse error at or near "DESC"
> batora=> select m.ename as Sef,
> coalesce(count(e.ename),0) from emp as
> m,emp as
> e where e.mgr=m.empno group by m.ename UNION
> select e.ename, 0 as count
> from
> emp e where NOT EXISTS (select * from emp as P
> where P.mgr=e.empno) ORDER
> BY c
> ount desc;
> ERROR: Attribute 'count' not found
> batora=> select m.ename as Sef, count(e.ename) as
> count from emp as m,emp
> as e
> where e.mgr=m.empno group by m.ename UNION select
> e.ename, 0 as count
> from em
> p e where NOT EXISTS (select * from emp as P where
> P.mgr=e.empno) ORDER BY
> coun
> t desc;
> sef | count
> --------+-------
> BLAKE | 5
> KING | 3
> JONES | 2
> CLARK | 1
> FORD | 1
> SCOTT | 1
> ADAMS | 0
> ALLEN | 0
> JAMES | 0
> MARTIN | 0
> MILLER | 0
> SMITH | 0
> TURNER | 0
> WARD | 0
> (14 rows)
>
> batora=> select 1980, count(*) from emp where
> hiredate like '1980%';
> ?column? | count
> ----------+-------
> 1980 | 1
> (1 row)
>
> batora=> select 1980, count(*) from emp where
> hiredate like '1990%';
> ?column? | count
> ----------+-------
> 1980 | 0
> (1 row)
>
> batora=> select 1980, count(*) from emp where
> hiredate like '1980%';
> ?column? | count
> ----------+-------
> 1980 | 1
> (1 row)
>
> batora=> select count(e.name) as 1980, count(f.name)
> as 1981,
> count(g.name) as
> 1982, count(h.name) as 1983) from emp as e, emp as
> f, emp as g, emp as h
> where
> e.hiredate like '1980%';
> ERROR: parser: parse error at or near "1980"
> batora=> select * from (select hiredate from emp)
> UNION (select count(*)
> from e
> mp);
> ERROR: sub-SELECT in FROM must have an alias
> For example, FROM (SELECT ...) [AS] foo
> batora=> select * from (select hiredate from emp
> UNION select count(*)
> from emp
> );
> ERROR: sub-SELECT in FROM must have an alias
> For example, FROM (SELECT ...) [AS] foo
> batora=> select * from (select hiredate from emp
> UNION select count(*)
> from emp
> ) as foo;
> ERROR: UNION types "date" and "int4" not matched
> batora=> select count(e.name) as "1980",
> count(f.name) as "1981",
> count(g.name)
> as "1982", count(h.name) as "1983") from emp as e,
> emp as f, emp as g,
> emp as
> h where e.hiredate like '1980%';
> ERROR: parser: parse error at or near ")"
> batora=> select count(e.name) as '1980',
> count(f.name) as '1981',
> count(g.name)
> as '1982', count(h.name) as '1983') from emp as e,
> emp as f, emp as g,
> emp as
> h where e.hiredate like '1980%';
> ERROR: parser: parse error at or near "'"
> batora=> select mgr from emp;
> mgr
> ------
>
> 7839
> 7839
> 7839
> 7698
> 7698
> 7698
> 7698
> 7698
> 7566
> 7902
> 7566
> 7788
> 7782
> (14 rows)
>
> batora=> select mgr from emp group by mgr;
> mgr
> ------
> 7566
> 7698
> 7782
> 7788
> 7839
> 7902
>
> (7 rows)
>
> batora=> select ename,sal,deptno from emp x where
> sal > (select avg(sal)
> from e
> mp where deptno=x.deptno);
> ename | sal | deptno
> -------+---------+--------
> KING | 5000.00 | 10
> BLAKE | 2850.00 | 30
> JONES | 2975.00 | 20
> ALLEN | 1600.00 | 30
> FORD | 3000.00 | 20
> SCOTT | 3000.00 | 20
> (6 rows)
>
> batora=>
> kahanec=> select ename
> kahanec-> from emp
> kahanec-> where ename like '%L%L%' or ename like
> '__A%';
> ename
> --------
> BLAKE
> CLARK
> ALLEN
> ADAMS
> MILLER
> (5 rows)
>
> kahanec=> select ename, job, sal
> kahanec-> from emp
> kahanec-> where job in ('CLERK','ANALYST') and sal
> not in (1000,3000,5000);
> ename | job | sal
> --------+-------+---------
> JAMES | CLERK | 950.00
> SMITH | CLERK | 800.00
> ADAMS | CLERK | 1100.00
> MILLER | CLERK | 1300.00
> (4 rows)
>
> kahanec=> select empno, ename, sal,
> round('1.15'*sal,0) as novyPlat
> kahanec-> from emp;
> empno | ename | sal | novyplat
> -------+--------+---------+----------
> 7839 | KING | 5000.00 | 5750
> 7698 | BLAKE | 2850.00 | 3278
> 7782 | CLARK | 1500.00 | 1725
> 7566 | JONES | 2975.00 | 3421
> 7654 | MARTIN | 1250.00 | 1438
> 7499 | ALLEN | 1600.00 | 1840
> 7844 | TURNER | 1500.00 | 1725
> 7900 | JAMES | 950.00 | 1093
> 7521 | WARD | 1250.00 | 1438
> 7902 | FORD | 3000.00 | 3450
> 7369 | SMITH | 800.00 | 920
> 7788 | SCOTT | 3000.00 | 3450
> 7876 | ADAMS | 1100.00 | 1265
> 7934 | MILLER | 1300.00 | 1495
> (14 rows)
>
> kahanec=> select empno, ename, sal,
> round('1.15'*sal,0) as novyPlat,
> (round('1.15'*sal,0)-sal)as Zvysenie
> kahanec-> from emp;
> empno | ename | sal | novyplat | zvysenie
> -------+--------+---------+----------+----------
> 7839 | KING | 5000.00 | 5750 | 750.00
> 7698 | BLAKE | 2850.00 | 3278 | 428.00
> 7782 | CLARK | 1500.00 | 1725 | 225.00
> 7566 | JONES | 2975.00 | 3421 | 446.00
> 7654 | MARTIN | 1250.00 | 1438 | 188.00
> 7499 | ALLEN | 1600.00 | 1840 | 240.00
> 7844 | TURNER | 1500.00 | 1725 | 225.00
> 7900 | JAMES | 950.00 | 1093 | 143.00
> 7521 | WARD | 1250.00 | 1438 | 188.00
> 7902 | FORD | 3000.00 | 3450 | 450.00
> 7369 | SMITH | 800.00 | 920 | 120.00
> 7788 | SCOTT | 3000.00 | 3450 | 450.00
> 7876 | ADAMS | 1100.00 | 1265 | 165.00
> 7934 | MILLER | 1300.00 | 1495 | 195.00
> (14 rows)
>
> kahanec=> select ename,
> coalesce(to_char(comm,'9999'),'Nedostava premie.')
> as Premie
> kahanec-> from emp;
> ename | premie
> --------+-------------------
> KING | Nedostava premie.
> BLAKE | Nedostava premie.
> CLARK | Nedostava premie.
> JONES | Nedostava premie.
> MARTIN | 1400
> ALLEN | 300
> TURNER | 0
> JAMES | Nedostava premie.
> WARD | 500
> FORD | Nedostava premie.
> SMITH | Nedostava premie.
> SCOTT | Nedostava premie.
> ADAMS | Nedostava premie.
> MILLER | Nedostava premie.
> (14 rows)
>
> kahanec=> select ename,dname
> kahanec-> from emp,dept
> kahanec-> where emp.deptno=dept.deptno;
> ename | dname
> --------+------------
> KING | ACCOUNTING
> CLARK | ACCOUNTING
> MILLER | ACCOUNTING
> JONES | RESEARCH
> FORD | RESEARCH
> SMITH | RESEARCH
> SCOTT | RESEARCH
> ADAMS | RESEARCH
> BLAKE | SALES
> MARTIN | SALES
> ALLEN | SALES
> TURNER | SALES
> JAMES | SALES
> WARD | SALES
> (14 rows)
>
> kahanec=> select e.ename,e.deptno,d.dname
> kahanec-> from emp as e,dept as d
> kahanec-> where e.ename='BLAKE' and
> e.deptno=d.deptno;
> ename | deptno | dname
> -------+--------+-------
> BLAKE | 30 | SALES
> (1 row)
>
>
> kahanec=> select distinct e.ename,d.loc
> kahanec-> from emp as e, dept as d
> kahanec-> where e.deptno=d.deptno and
> d.loc='CHICAGO';
> ename | loc
> --------+---------
> ALLEN | CHICAGO
> BLAKE | CHICAGO
> JAMES | CHICAGO
> MARTIN | CHICAGO
> TURNER | CHICAGO
> WARD | CHICAGO
> (6 rows)
>
> kahanec=> select distinct e.job,d.loc
> kahanec-> from emp as e, dept as d
> kahanec-> where e.deptno=d.deptno and
> d.loc='CHICAGO';
> job | loc
> ----------+---------
> CLERK | CHICAGO
> MANAGER | CHICAGO
> SALESMAN | CHICAGO
> (3 rows)
>
> kahanec=> select e.ename, d.dname,d.loc
> kahanec-> from emp as e, dept as d
> kahanec-> where e.comm is not null and
> e.deptno=d.deptno;
> ename | dname | loc
> --------+-------+---------
> MARTIN | SALES | CHICAGO
> ALLEN | SALES | CHICAGO
> TURNER | SALES | CHICAGO
> WARD | SALES | CHICAGO
> (4 rows)
>
> kahanec=> select e.ename,e.sal,s.grade
> kahanec-> from emp as e,salgrade s
> kahanec-> where e.sal between s.losal and s.hisal;
> ename | sal | grade
> --------+---------+-------
> KING | 5000.00 | 5
> BLAKE | 2850.00 | 4
> CLARK | 1500.00 | 3
> JONES | 2975.00 | 4
> MARTIN | 1250.00 | 2
> ALLEN | 1600.00 | 3
> TURNER | 1500.00 | 3
> JAMES | 950.00 | 1
> WARD | 1250.00 | 2
> FORD | 3000.00 | 4
> SMITH | 800.00 | 1
> SCOTT | 3000.00 | 4
> ADAMS | 1100.00 | 1
> MILLER | 1300.00 | 2
> (14 rows)
>
> kahanec=> select e.ename,e.sal,s.grade,d.loc
> kahanec-> from emp as e, dept as d,salgrade as s
> kahanec-> where e.deptno=d.deptno and e.sal between
> s.losal and s.hisal;
> ename | sal | grade | loc
> --------+---------+-------+----------
> KING | 5000.00 | 5 | NEW YORK
> CLARK | 1500.00 | 3 | NEW YORK
> MILLER | 1300.00 | 2 | NEW YORK
> JONES | 2975.00 | 4 | DALLAS
> FORD | 3000.00 | 4 | DALLAS
> SMITH | 800.00 | 1 | DALLAS
> SCOTT | 3000.00 | 4 | DALLAS
> ADAMS | 1100.00 | 1 | DALLAS
> BLAKE | 2850.00 | 4 | CHICAGO
> MARTIN | 1250.00 | 2 | CHICAGO
> ALLEN | 1600.00 | 3 | CHICAGO
> TURNER | 1500.00 | 3 | CHICAGO
> JAMES | 950.00 | 1 | CHICAGO
> WARD | 1250.00 | 2 | CHICAGO
> (14 rows)
>
> kahanec=> select e.ename,e.sal,s.grade,d.loc
> kahanec-> from emp as e, dept as d,salgrade as s
> kahanec-> where e.deptno=d.deptno and e.sal between
> s.losal and s.hisal and e.ename='MARTIN';
> ename | sal | grade | loc
> --------+---------+-------+---------
> MARTIN | 1250.00 | 2 | CHICAGO
> (1 row)
>
> kahanec=> select e.ename,m.ename
> kahanec-> from emp as e, emp as m
> kahanec-> where e.mgr=m.empno;
> ename | ename
> --------+-------
> FORD | JONES
> SCOTT | JONES
> MARTIN | BLAKE
> ALLEN | BLAKE
> TURNER | BLAKE
> JAMES | BLAKE
> WARD | BLAKE
> MILLER | CLARK
> ADAMS | SCOTT
> BLAKE | KING
> CLARK | KING
> JONES | KING
> SMITH | FORD
> (13 rows)
>
> kahanec=> select e.ename,m.ename
> kahanec-> from emp e left outer join emp m
> kahanec-> on (e.mgr=m.empno);
> ename | ename
> --------+-------
> FORD | JONES
> SCOTT | JONES
> MARTIN | BLAKE
> ALLEN | BLAKE
> TURNER | BLAKE
> JAMES | BLAKE
> WARD | BLAKE
> MILLER | CLARK
> ADAMS | SCOTT
> BLAKE | KING
> CLARK | KING
> JONES | KING
> SMITH | FORD
> KING |
> (14 rows)
>
> kahanec=> select e.ename,d.dname
> kahanec-> from emp as e right outer join dept as d
> kahanec-> on e.deptno=d.deptno;
> ename | dname
> --------+------------
> KING | ACCOUNTING
> CLARK | ACCOUNTING
> MILLER | ACCOUNTING
> JONES | RESEARCH
> FORD | RESEARCH
> SMITH | RESEARCH
> SCOTT | RESEARCH
> ADAMS | RESEARCH
> BLAKE | SALES
> MARTIN | SALES
> ALLEN | SALES
> TURNER | SALES
> JAMES | SALES
> WARD | SALES
> | OPERATIONS
> (15 rows)
>
> kahanec=> select
> e.ename,e.hiredate,m.ename,m.hiredate
> kahanec-> from emp as e,emp as m
> kahanec-> where e.hiredate<m.hiredate and
> e.mgr=m.empno;
> ename | hiredate | ename | hiredate
> -------+------------+-------+------------
> ALLEN | 1981-02-20 | BLAKE | 1981-05-01
> WARD | 1981-02-22 | BLAKE | 1981-05-01
> BLAKE | 1981-05-01 | KING | 1981-11-17
> CLARK | 1981-06-09 | KING | 1981-11-17
> JONES | 1981-04-02 | KING | 1981-11-17
> SMITH | 1980-12-17 | FORD | 1981-12-03
> (6 rows)
>
> kahanec=> select
> e.ename,coalesce(to_char(e.mgr,'9999'),'nema
> managera')
> kahanec-> from emp as e;
> ename | case
> --------+---------------
> KING | nema managera
> BLAKE | 7839
> CLARK | 7839
> JONES | 7839
> MARTIN | 7698
> ALLEN | 7698
> TURNER | 7698
> JAMES | 7698
> WARD | 7698
> FORD | 7566
> SMITH | 7902
> SCOTT | 7566
> ADAMS | 7788
> MILLER | 7782
> (14 rows)
>
> kahanec=> select
> e.ename,coalesce(to_char(e.mgr,'9999'),'nema
> managera') as "cislo managera",d.dname
> kahanec-> from emp as e, dept as d
> kahanec-> where e.deptno=d.deptno;
> ename | cislo managera | dname
> --------+----------------+------------
> KING | nema managera | ACCOUNTING
> CLARK | 7839 | ACCOUNTING
> MILLER | 7782 | ACCOUNTING
> JONES | 7839 | RESEARCH
> FORD | 7566 | RESEARCH
> SMITH | 7902 | RESEARCH
> SCOTT | 7566 | RESEARCH
> ADAMS | 7788 | RESEARCH
> BLAKE | 7839 | SALES
> MARTIN | 7698 | SALES
> ALLEN | 7698 | SALES
> TURNER | 7698 | SALES
> JAMES | 7698 | SALES
> WARD | 7698 | SALES
> (14 rows)
>
> kahanec=> select avg(comm)
> kahanec-> from emp;
> avg
> ----------------
> 550.0000000000
> (1 row)
>
> deli iba poctom premii a nie celkovym poctom ludi..
> ako by to spravilo cez coalesce
> dneska je 17.10 '01 zacina sa cviko z databaz, je
> prave 14.59.
>
> START:
>
> spomenut si na distinct.
> proste na zaciatku opakovanie z minula..
>
> prilozim log subor toho o som robil...
>
> tu iba dolezite..
>
> triedenie: order by, pise sa pod where
> podla stlpca, vyrazu, aliasu (sal as rocnyplat a
> mozem
> dat rocny plat...)
>
> [ASC|DESC] pricom ASC je default
> najskorsi datum - najpozdnejsi
> pismena, najprv velke potom male, podla abecedy
> napriklad Brown, adams
> Nullove hodnoty su VZDY na konci nezavisle podla
> ASC|DESC
>
> podla aliasov az potom podla stlpcov
>
> triedenie podla viacerych stlpcov:
> triedi podla prveho a potom podla prveho dotriedi
> podla druheho argumentu, mozem triedit aj ASC|DESC
> dam
> ku kazdemu ale samostatne ASC|DESC
> mozem triedit aj podla stlpca, ktory si do tabulky
> nedam vypisat..
>
>
> FUNKCIE:
> stranka s manualom, tam je viacej;
>
> vsetkych je veeela, takze budeme hovorit o malo
> f-cie so stringami v pazi..
> ROUND: dva argumenty, cislo na zaokruhlenie,na kolko
> desatinnych miest chceme zaukruhlit.
>
> ROUND(number,n)
>
> n#Z teda ked dame -2 tak zaokruhly na stovky, ked
> nedame cislo, tak zaokruhli na najblizsie cele
> cislo,
> ale lepsie je pisat nulu, bo sa to jebe pri
> round(4.5)
> co zaokruhli na 4 :-) ale ked dame round(4.5,0)tak
> da
> spravne na 5 ...
>
>
> TRUNC, rovnake argumenty, lisi sa od round tym, ze
> usekava desatinne miesta...
>
> MODULO: vracia zvysok po deleni m-ka n-kom
>
> MOD(m,n)
>
> ostatne v manuali,...................
>
> funkcie s datumami:
>
> funkcia na ziskanie aktualneho datumu: CURRENT_DATE
> dalej CURRENT_TIME a CURRENT_TIMESTAMP.
>
> mozeme odcitovat, pricitavat dni...
>
> datove konverzie:
> string na cislo: TO_NUMBER(text,format v akom ma
> ockavat text) ked chcem cislo, tak dame "9" vid
> prilohu
> ak dame do cisla pismena,tak ich ignoruje, bo nevie
> co
> s nima..., ak dame menej "9"niek, tak ostatne za tym
> poctom ignoruje..
>
> cislo na string:
> TO_CHAR(cislo|datum,format[DD-MM|mon|month-YYYY])
> da sa aj DDth-Month-YYYY
>
> funkcia string na datum
>
> TO_DATE(string,format datumu);
>
> dalsia f-cia:
>
> CASE when podmienka then co chceme
>
> viacej when, alebo aj else moze byt konci sa to END!
> blizsie ako sa to robi pozri prilohu..
>
> pozor!
> kahanec=> (
> kahanec(> bla bla
> kahanec(> )
> kahanec-> ' hu huuuu
> kahanec'> ;
> kahanec'> '
> kahanec-> ;
>
> blizsie osobna konzultacia, sa mi nechce pisat.
> KONVERZIA resp PRETYPOVANIE
> sal*numeric '1.1'
> resp sal*'1.1'
>
> function
> COALESCE(...,...,...,...,......) prvy z argumentov,
> ktory je nenullovy
>
> see priloha
> argumenty musia byt rovnakeho typu..
> ak chceme aby napriklad napisal, ze nedostava
> premie... pozri prilohu oznacene PREMIE
>
> potom pokracujuu nejake haluzne priklady co sme
> robili..
>
> kahanec=> select distinct job
> kahanec-> from emp;
> job
> -----------
> ANALYST
> CLERK
> MANAGER
> PRESIDENT
> SALESMAN
> (5 rows)
>
> kahanec=> select *
> kahanec-> from emp;
> empno | ename | job | mgr | hiredate |
> sal | comm | deptno
>
-------+--------+-----------+------+------------+---------+---------+--------
> 7839 | KING | PRESIDENT | | 1981-11-17 |
> 5000.00 | | 10
> 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 |
> 2850.00 | | 30
> 7782 | CLARK | MANAGER | 7839 | 1981-06-09 |
> 1500.00 | | 10
> 7566 | JONES | MANAGER | 7839 | 1981-04-02 |
> 2975.00 | | 20
> 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 |
> 1250.00 | 1400.00 | 30
> 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 |
> 1600.00 | 300.00 | 30
> 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 |
> 1500.00 | 0.00 | 30
> 7900 | JAMES | CLERK | 7698 | 1981-12-03 |
> 950.00 | | 30
> 7521 | WARD | SALESMAN | 7698 | 1981-02-22 |
> 1250.00 | 500.00 | 30
> 7902 | FORD | ANALYST | 7566 | 1981-12-03 |
> 3000.00 | | 20
> 7369 | SMITH | CLERK | 7902 | 1980-12-17 |
> 800.00 | | 20
> 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 |
> 3000.00 | | 20
> 7876 | ADAMS | CLERK | 7788 | 1983-01-12 |
> 1100.00 | | 20
> 7934 | MILLER | CLERK | 7782 | 1982-01-23 |
> 1300.00 | | 10
> (14 rows)
>
> kahanec=> select empno as Employee
> kahanec-> from emp
> kahanec-> ;
> employee
> ----------
> 7839
> 7698
> 7782
> 7566
> 7654
> 7499
> 7844
> 7900
> 7521
> 7902
> 7369
> 7788
> 7876
> 7934
> (14 rows)
>
> kahanec=> select ename, sal
> kahanec-> from emp
> kahanec-> where sal>2550;
> ename | sal
> -------+---------
> KING | 5000.00
> BLAKE | 2850.00
> JONES | 2975.00
> FORD | 3000.00
> SCOTT | 3000.00
> (5 rows)
>
> kahanec=> select ename, deptno
> kahanec-> from emp
> kahanec-> where empno=7566;
> ename | deptno
> -------+--------
> JONES | 20
> (1 row)
>
> kahanec=> select ename, sal
> kahanec-> from emp
> kahanec-> where sal not between 1500 and 2550;
> ename | sal
> --------+---------
> KING | 5000.00
> BLAKE | 2850.00
> JONES | 2975.00
> MARTIN | 1250.00
> JAMES | 950.00
> WARD | 1250.00
> FORD | 3000.00
> SMITH | 800.00
> SCOTT | 3000.00
> ADAMS | 1100.00
> MILLER | 1300.00
> (11 rows)
>
> kahanec=> select *
> kahanec-> from emp
> kahanec-> order by ename;
> empno | ename | job | mgr | hiredate |
> sal | comm | deptno
>
-------+--------+-----------+------+------------+---------+---------+--------
> 7876 | ADAMS | CLERK | 7788 | 1983-01-12 |
> 1100.00 | | 20
> 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 |
> 1600.00 | 300.00 | 30
> 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 |
> 2850.00 | | 30
> 7782 | CLARK | MANAGER | 7839 | 1981-06-09 |
> 1500.00 | | 10
> 7902 | FORD | ANALYST | 7566 | 1981-12-03 |
> 3000.00 | | 20
> 7900 | JAMES | CLERK | 7698 | 1981-12-03 |
> 950.00 | | 30
> 7566 | JONES | MANAGER | 7839 | 1981-04-02 |
> 2975.00 | | 20
> 7839 | KING | PRESIDENT | | 1981-11-17 |
> 5000.00 | | 10
> 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 |
> 1250.00 | 1400.00 | 30
> 7934 | MILLER | CLERK | 7782 | 1982-01-23 |
> 1300.00 | | 10
> 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 |
> 3000.00 | | 20
> 7369 | SMITH | CLERK | 7902 | 1980-12-17 |
> 800.00 | | 20
> 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 |
> 1500.00 | 0.00 | 30
> 7521 | WARD | SALESMAN | 7698 | 1981-02-22 |
> 1250.00 | 500.00 | 30
> (14 rows)
>
> kahanec=> select *
> kahanec-> from emp
> kahanec-> order by ename DESC;
> empno | ename | job | mgr | hiredate |
> sal | comm | deptno
>
-------+--------+-----------+------+------------+---------+---------+--------
> 7521 | WARD | SALESMAN | 7698 | 1981-02-22 |
> 1250.00 | 500.00 | 30
> 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 |
> 1500.00 | 0.00 | 30
> 7369 | SMITH | CLERK | 7902 | 1980-12-17 |
> 800.00 | | 20
> 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 |
> 3000.00 | | 20
> 7934 | MILLER | CLERK | 7782 | 1982-01-23 |
> 1300.00 | | 10
> 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 |
> 1250.00 | 1400.00 | 30
> 7839 | KING | PRESIDENT | | 1981-11-17 |
> 5000.00 | | 10
> 7566 | JONES | MANAGER | 7839 | 1981-04-02 |
> 2975.00 | | 20
> 7900 | JAMES | CLERK | 7698 | 1981-12-03 |
> 950.00 | | 30
> 7902 | FORD | ANALYST | 7566 | 1981-12-03 |
> 3000.00 | | 20
> 7782 | CLARK | MANAGER | 7839 | 1981-06-09 |
> 1500.00 | | 10
> 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 |
> 2850.00 | | 30
> 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 |
> 1600.00 | 300.00 | 30
> 7876 | ADAMS | CLERK | 7788 | 1983-01-12 |
> 1100.00 | | 20
> (14 rows)
>
> kahanec=> select *
> kahanec-> from emp
> kahanec-> order by hiredate;
> empno | ename | job | mgr | hiredate |
> sal | comm | deptno
>
-------+--------+-----------+------+------------+---------+---------+--------
> 7369 | SMITH | CLERK | 7902 | 1980-12-17 |
> 800.00 | | 20
> 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 |
> 1600.00 | 300.00 | 30
> 7521 | WARD | SALESMAN | 7698 | 1981-02-22 |
> 1250.00 | 500.00 | 30
> 7566 | JONES | MANAGER | 7839 | 1981-04-02 |
> 2975.00 | | 20
> 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 |
> 2850.00 | | 30
> 7782 | CLARK | MANAGER | 7839 | 1981-06-09 |
> 1500.00 | | 10
> 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 |
> 1500.00 | 0.00 | 30
> 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 |
> 1250.00 | 1400.00 | 30
> 7839 | KING | PRESIDENT | | 1981-11-17 |
> 5000.00 | | 10
> 7900 | JAMES | CLERK | 7698 | 1981-12-03 |
> 950.00 | | 30
> 7902 | FORD | ANALYST | 7566 | 1981-12-03 |
> 3000.00 | | 20
> 7934 | MILLER | CLERK | 7782 | 1982-01-23 |
> 1300.00 | | 10
> 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 |
> 3000.00 | | 20
> 7876 | ADAMS | CLERK | 7788 | 1983-01-12 |
> 1100.00 | | 20
> (14 rows)
>
> kahanec=> select ename, sal*12 as "rocny plat"
> kahanec-> from emp
> kahanec-> order by "rocny plat" DESC;
> ename | rocny plat
> --------+------------
> KING | 60000.00
> FORD | 36000.00
> SCOTT | 36000.00
> JONES | 35700.00
> BLAKE | 34200.00
> ALLEN | 19200.00
> CLARK | 18000.00
> TURNER | 18000.00
> MILLER | 15600.00
> MARTIN | 15000.00
> WARD | 15000.00
> ADAMS | 13200.00
> JAMES | 11400.00
> SMITH | 9600.00
> (14 rows)
>
> kahanec=> select ename, deptno, sal
> kahanec-> from emp
> kahanec-> order by deptno, sal desc;
> ename | deptno | sal
> --------+--------+---------
> KING | 10 | 5000.00
> CLARK | 10 | 1500.00
> MILLER | 10 | 1300.00
> FORD | 20 | 3000.00
> SCOTT | 20 | 3000.00
> JONES | 20 | 2975.00
> ADAMS | 20 | 1100.00
> SMITH | 20 | 800.00
> BLAKE | 30 | 2850.00
> ALLEN | 30 | 1600.00
> TURNER | 30 | 1500.00
> MARTIN | 30 | 1250.00
> WARD | 30 | 1250.00
> JAMES | 30 | 950.00
> (14 rows)
>
> kahanec=> select ename, job, hiredate
> kahanec-> from emp
> kahanec-> where hiredate between '1981-02-20' and
> '1981-05-01'
> kahanec-> order by hiredate;
> ename | job | hiredate
> -------+----------+------------
> ALLEN | SALESMAN | 1981-02-20
> WARD | SALESMAN | 1981-02-22
> JONES | MANAGER | 1981-04-02
> BLAKE | MANAGER | 1981-05-01
> (4 rows)
>
> kahanec=> select ename, deptno
> kahanec-> from emp
> kahanec-> where deptno in (10,30)
> kahanec-> order by ename;
> ename | deptno
> --------+--------
> ALLEN | 30
> BLAKE | 30
> CLARK | 10
> JAMES | 30
> KING | 10
> MARTIN | 30
> MILLER | 10
> TURNER | 30
> WARD | 30
> (9 rows)
>
>
> kahanec=> select round(42.937,2);
> round
> -------
> 42.94
> (1 row)
>
> kahanec=> select round(42.937,-1);
> round
> -------
> 40
> (1 row)
>
> kahanec=> select trunc(34.4346,2);
> trunc
> -------
> 34.43
> (1 row)
>
> kahanec=> select trunc(34.4346,3);
> trunc
> --------
> 34.434
> (1 row)
>
> kahanec=> select mod(5,2);
> mod
> -----
> 1
> (1 row)
>
> kahanec=> select current_date;
> date
> ------------
> 2001-10-17
> (1 row)
>
> kahanec=> select current_date, current_time;
> date | time
> ------------+----------
> 2001-10-17 | 15:37:37
> (1 row)
>
> kahanec=> select current_timestamp;
> timestamp
> ------------------------
> 2001-10-17 15:38:23+02
> (1 row)
>
> kahanec=> select ename, current_date - hiredate as
> pocetdni
> kahanec-> from emp;
> ename | pocetdni
> --------+----------
> KING | 7274
> BLAKE | 7474
> CLARK | 7435
> JONES | 7503
> MARTIN | 7324
> ALLEN | 7544
> TURNER | 7344
> JAMES | 7258
> WARD | 7542
> FORD | 7258
> SMITH | 7609
> SCOTT | 6887
> ADAMS | 6853
> MILLER | 7207
> (14 rows)
>
> kahanec=> select to_number('42.45',99.99);
> to_number
> -----------
> 42.45
> (1 row)
>
> kahanec=> select ename,
> to_char(hiredate,'DD-month-YY')
> kahanec-> from emp;
> ename | to_char
> --------+-----------------
> KING | 17-november -81
> BLAKE | 01-may -81
> CLARK | 09-june -81
> JONES | 02-april -81
> MARTIN | 28-september-81
> ALLEN | 20-february -81
> TURNER | 08-september-81
> JAMES | 03-december -81
> WARD | 22-february -81
> FORD | 03-december -81
> SMITH | 17-december -80
> SCOTT | 09-december -82
> ADAMS | 12-january -83
> MILLER | 23-january -82
> (14 rows)
>
> kahanec=> select ename,
> to_char(hiredate,'DDth-Month-YYYY')
> kahanec-> from emp;
> ename | to_char
> --------+---------------------
> KING | 17th-November -1981
> BLAKE | 01st-May -1981
> CLARK | 09th-June -1981
> JONES | 02nd-April -1981
> MARTIN | 28th-September-1981
> ALLEN | 20th-February -1981
> TURNER | 08th-September-1981
> JAMES | 03rd-December -1981
> WARD | 22nd-February -1981
> FORD | 03rd-December -1981
> SMITH | 17th-December -1980
> SCOTT | 09th-December -1982
> ADAMS | 12th-January -1983
> MILLER | 23rd-January -1982
> (14 rows)
>
> kahanec=> select to_date('2001-12-22','YYYY-MM-DD');
> to_date
> ------------
> 2001-12-22
> (1 row)
>
> kahanec=> select ename, sal, case when job='CLERK'
> then sal+100
> kahanec-> when job='ANALYST' then sal+200
> kahanec-> else sal
> kahanec-> end
> kahanec-> from emp;
> ename | sal | case
> --------+---------+---------
> KING | 5000.00 | 5000.00
> BLAKE | 2850.00 | 2850.00
> CLARK | 1500.00 | 1500.00
> JONES | 2975.00 | 2975.00
> MARTIN | 1250.00 | 1250.00
> ALLEN | 1600.00 | 1600.00
> TURNER | 1500.00 | 1500.00
> JAMES | 950.00 | 1050.00
> WARD | 1250.00 | 1250.00
> FORD | 3000.00 | 3200.00
> SMITH | 800.00 | 900.00
> SCOTT | 3000.00 | 3200.00
> ADAMS | 1100.00 | 1200.00
> MILLER | 1300.00 | 1400.00
> (14 rows)
>
> kahanec=> select ename,sal*'1.1'
> kahanec-> from emp;
> ename | ?column?
> --------+----------
> KING | 5500.000
> BLAKE | 3135.000
> CLARK | 1650.000
> JONES | 3272.500
> MARTIN | 1375.000
> ALLEN | 1760.000
> TURNER | 1650.000
> JAMES | 1045.000
> WARD | 1375.000
> FORD | 3300.000
> SMITH | 880.000
> SCOTT | 3300.000
> ADAMS | 1210.000
> MILLER | 1430.000
> (14 rows)
>
> kahanec=> select ename, 12*sal+ coalesce(comm,0)as
> rocny
> kahanec-> from emp;
> ename | rocny
> --------+----------
> KING | 60000.00
> BLAKE | 34200.00
> CLARK | 18000.00
> JONES | 35700.00
> MARTIN | 16400.00
> ALLEN | 19500.00
> TURNER | 18000.00
> JAMES | 11400.00
> WARD | 15500.00
> FORD | 36000.00
> SMITH | 9600.00
> SCOTT | 36000.00
> ADAMS | 13200.00
> MILLER | 15600.00
> (14 rows)
>
> PREMIE:
> kahanec=> select ename, coalesce(comm,'nedostava
> premie')
> kahanec-> from emp;
> ERROR: Bad numeric input format 'nedostava premie'
> kahanec=> select ename,
> coalesce(to_char(comm,'9999'),'nedostava premie')
> kahanec-> from emp;
> ename | case
> --------+------------------
> KING | nedostava premie
> BLAKE | nedostava premie
> CLARK | nedostava premie
> JONES | nedostava premie
> MARTIN | 1400
> ALLEN | 300
> TURNER | 0
> JAMES | nedostava premie
> WARD | 500
> FORD | nedostava premie
> SMITH | nedostava premie
> SCOTT | nedostava premie
> ADAMS | nedostava premie
> MILLER | nedostava premie
> (14 rows)
> /PREMIE
>
> kahanec=> select ename,
> coalesce(to_char(mgr,'9999'),'nema manageeera')
> kahanec-> from emp;
> ename | case
> --------+-----------------
> KING | nema manageeera
> BLAKE | 7839
> CLARK | 7839
> JONES | 7839
> MARTIN | 7698
> ALLEN | 7698
> TURNER | 7698
> JAMES | 7698
> WARD | 7698
> FORD | 7566
> SMITH | 7902
> SCOTT | 7566
> ADAMS | 7788
> MILLER | 7782
> (14 rows)
>
> DLASIE PRIKLADY:
>
> kahanec=> select ename, sal, round(sal*'1.15',1)
> kahanec-> from emp;
> ename | sal | round
> --------+---------+--------
> KING | 5000.00 | 5750.0
> BLAKE | 2850.00 | 3277.5
> CLARK | 1500.00 | 1725.0
> JONES | 2975.00 | 3421.3
> MARTIN | 1250.00 | 1437.5
> ALLEN | 1600.00 | 1840.0
> TURNER | 1500.00 | 1725.0
> JAMES | 950.00 | 1092.5
> WARD | 1250.00 | 1437.5
> FORD | 3000.00 | 3450.0
> SMITH | 800.00 | 920.0
> SCOTT | 3000.00 | 3450.0
> ADAMS | 1100.00 | 1265.0
> MILLER | 1300.00 | 1495.0
> (14 rows)
>
> kahanec=> select ename, sal, round(sal*'1.15',0) as
> novy, round(sal*'1.15',0)-sa
> l as rozdiel
> kahanec-> from emp;
> ename | sal | novy | rozdiel
> --------+---------+------+---------
> KING | 5000.00 | 5750 | 750.00
> BLAKE | 2850.00 | 3278 | 428.00
> CLARK | 1500.00 | 1725 | 225.00
> JONES | 2975.00 | 3421 | 446.00
> MARTIN | 1250.00 | 1438 | 188.00
> ALLEN | 1600.00 | 1840 | 240.00
> TURNER | 1500.00 | 1725 | 225.00
> JAMES | 950.00 | 1093 | 143.00
> WARD | 1250.00 | 1438 | 188.00
> FORD | 3000.00 | 3450 | 450.00
> SMITH | 800.00 | 920 | 120.00
> SCOTT | 3000.00 | 3450 | 450.00
> ADAMS | 1100.00 | 1265 | 165.00
> MILLER | 1300.00 | 1495 | 195.00
> (14 rows)
> okey let's start :-) > > zacneme opakovanim.. takze to uz nepopisujem > (like, in, ) > > Vyberanie z viacerych tabuliek: > > EMP a DEPT kde v DEPT su deptno, dname, loc > > mozeme aliasovat aj tabulky > select * > from emp [as] e, dept [as] d > where ...; > pricom to as tam nenmusi byt/... preto je v "[]" > ak raz pouzijeme alias, tak nemozeme pouzit uz dept > alebo emp ale "d" a "e" > teda pozivame e.ename, d.dname a podobne.. > > spajanie tabulky samej so sebou, musia byt aliasy.. > > JOIN > > select t1.a,t2.b > from t1 JOIN t2 > on (t1.a=t2.a) > [using(a)] > > namiesto JOIN moze byt aj INNER JOIN alebo LEFT > OUTER > JOIN(ked je tam riadok ktory nevyhovuje podmienke, > tak > ho len napise..) tak isto je aj RIGHT OUTER JOIN len > ide o to z ktorej tabulky da ten riadok, ktory > nevyhovuje podmienke.. > este existuje aj FULL OUTER JOIN spravi INNERJOIN > potom riadky z lavej tabulke nevyhovujuce podmienke > a > potom z pravej tabulky tiez tie ktore nevyhovuju.. > NATURAL JOIN (rovnakosavolajuce stlpce porovnaVA, > myslim...) > aj tak budeme pouzivat where :-) > > potom opat opakovanie prebrateho > > ... > dalsie veci > > funkcia > AVG([distinct|all] expression) > distinct neduplicitne riadky(napr rovnaky plat iba > raz), all alebo nic tak to zrobi zo seckych > zrata priemer zo vstkych riadkof, ignoruje null > > dalsie SUM, MIN(mozeme pouzit na seckom), MAX(tak > ako > MIN), COUNT(taky isty tvar ako ostatne=>pocet > riadkov > kde hodnota nie je null,alebo [count(*)]=>pocet > seckych riadkof aj vratane duplicitnych ci > nullovych) > syntax je u vsetkych ako u AVG > dalsie su STDDEV(smerodajna odchylka ignoruje null), > VARIANCE(rata disperziu, co je druha mocnina > smerodajnej odchylky, ignoruje null) > tote posledne dve su na PICUUUUUUU :-))) > > pri sum vrati null ak nic nevyhovuje podmienke vo > where > > na picu bo sme zase pretiahli cviko, kurva preverzne > :-) > > nabuduce si povieme pouzivanie funkcii nie na celej > tab a podobne kraviny .. > > papa > > ENZO
> pouzivane databazy: emp, dept, salgrade > > select: > > select [distinct] *|col[...] > from table > > datumy 1982-12-31 :-) > > null : ked nie je definovana nejaka polozka v > aritmetickej operacii, tak vysledok je null <> > > aliasovanie stlpcov: select ename as meno, 12*sal > as > "rocny plat" > > "as" pisat!! > > a between x and y : x<=a<=y :-) > dolna horna hranica > > not (a between x and y) alebo a not between x and y > > ... > > a in (zoznam oddeleny ciarkami) > a not in (..,..) alebo not(a in (..,..)) > > is null, kde dana polozka je null, ked je totiz v > predchadzajucom pripade null ta polozka, tak nam ho > nevypise pri kladnej ani pri zapornej poziadafke.. > > like: stlpec like vzorka > % je nula alebo viac znakov > _ je jeden lkubovolny znak > \ je escape char .. napriklad \% nam porovnava '%' a > neberie ho ako nulu ci viac znakov > > where ename like 'S%'; > > where ename like '_A%'; > > where ename like '%A\_B%'; ked chcem mat string > A_B > > where ename is like '%A$_B%' escape '$'; ked sa nam > nepaci '\' :-) alebo nieco ine ........ > > >where hiredate like '1981%'; > datum kazdeho vypisaneho je 1981 .. > > pri spojkach plati pravidlo: > najprv porovnavacie operatory, not, and, or.. > > > where job='SALESMAN' or deptno=10 and sal<3000; > > where (job='SALESMAN' or deptno=10) and sal<3000; > dve rozdielne podmienky!! > > pred rokom 1982 > > where hiredate<'1982-01-01' ... > > END