zajicek=> select to_char(to_date('31.12.2001','dd.mm.yyyy'),'day'); to_char ----------- monday (1 row) zajicek=> select * 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) zajicek=> select ename||' '||loc as rok1980 from emp e,dept d where e.deptno=d. deptno and hiredate like '1980%'; rok1980 -------------- SMITH DALLAS (1 row) zajicek=> select ename||' '||loc as rok1980 from emp e,dept d where e.deptno=d. deptno and hiredate like '1981%'; rok1980 ---------------- KING NEW YORK CLARK NEW YORK JONES DALLAS FORD DALLAS BLAKE CHICAGO MARTIN CHICAGO ALLEN CHICAGO TURNER CHICAGO JAMES CHICAGO WARD CHICAGO (10 rows) zajicek=> select p.ename as "Po Blakeovi" from emp e, emp p where e.ename='BLAK E' and p.hiredate>e.hiredate order by p.hiredate; Po Blakeovi ------------- CLARK TURNER MARTIN KING JAMES FORD MILLER SCOTT ADAMS (9 rows) zajicek=> \d List of relations Name | Type | Owner ----------+-------+--------- dept | table | zajicek emp | table | zajicek salgrade | table | zajicek (3 rows) zajicek=> select * from dept zajicek-> ; deptno | dname | loc --------+------------+---------- 10 | ACCOUNTING | NEW YORK 20 | RESEARCH | DALLAS 30 | SALES | CHICAGO 40 | OPERATIONS | BOSTON (4 rows) zajicek=> select * from salgrade; grade | losal | hisal -------+---------+--------- 1 | 700.00 | 1200.00 2 | 1201.00 | 1400.00 3 | 1401.00 | 2000.00 4 | 2001.00 | 3000.00 5 | 3001.00 | 9999.00 (5 rows) zajicek=> select ename, loc, grade from emp,dept,grade where emp.deptno=deptno. zajicek=> select ename, loc, grade from emp,dept,grade where emp.deptno=deptn.d zajicek=> select ename, loc, grade from emp,dept,grade where emp.deptno=dept.de zajicek=> select ename, loc, grade from emp,dept,grade where emp.deptno=dept.de ptno and emp.sal between(losal,hisal); ERROR: parser: parse error at or near "," zajicek=> select ename, loc, grade from emp,dept,grade where emp.deptno=dept.de ptno and emp.sal between losal and hisal; ERROR: Relation 'grade' does not exist zajicek=> select ename, loc, grade from emp,dept,grade where emp.deptno=dept.de zajicek=> select ename, loc, grade from emp,dept,sgrade where emp.deptno=dept.d zajicek=> select ename, loc, grade from emp,dept,sagrade where emp.deptno=dept. zajicek=> select ename, loc, grade from emp,dept,salgrade where emp.deptno=dept zajicek=> select ename, loc, grade from emp,dept,salgrade where emp.deptno=dept .deptno and emp.sal between losal and hisal; ename | loc | grade --------+----------+------- KING | NEW YORK | 5 CLARK | NEW YORK | 3 MILLER | NEW YORK | 2 JONES | DALLAS | 4 FORD | DALLAS | 4 SMITH | DALLAS | 1 SCOTT | DALLAS | 4 ADAMS | DALLAS | 1 BLAKE | CHICAGO | 4 MARTIN | CHICAGO | 2 ALLEN | CHICAGO | 3 TURNER | CHICAGO | 3 JAMES | CHICAGO | 1 WARD | CHICAGO | 2 (14 rows) zajicek=> select dname, ename from dept d, emp e where d.deptno=e.deptno; dname | ename ------------+-------- ACCOUNTING | KING ACCOUNTING | CLARK ACCOUNTING | MILLER RESEARCH | JONES RESEARCH | FORD RESEARCH | SMITH RESEARCH | SCOTT RESEARCH | ADAMS SALES | BLAKE SALES | MARTIN SALES | ALLEN SALES | TURNER SALES | JAMES SALES | WARD (14 rows) zajicek=> select dname, ename dept left outer join emp using (deptno); ERROR: parser: parse error at or near "dept" zajicek=> select dname, ename from dept left outer join emp using (deptno); dname | ename ------------+-------- ACCOUNTING | KING ACCOUNTING | CLARK ACCOUNTING | MILLER RESEARCH | JONES RESEARCH | FORD RESEARCH | SMITH RESEARCH | SCOTT RESEARCH | ADAMS SALES | BLAKE SALES | MARTIN SALES | ALLEN SALES | TURNER SALES | JAMES SALES | WARD OPERATIONS | (15 rows) zajicek=> select p.ename as meno,s.ename as sef from emp p, emp s where p.mgr=s .empno; meno | sef --------+------- 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) zajicek=> select p.ename as meno,s.ename as sef from emp p, emp s where p.mgr=s zajicek=> select p.ename as meno,s.ename as sef from emp p, emp s where p.mgr=; zajicek=> select p.ename as meno,s.ename as sef from emp p, emp s where p.mgr; zajicek=> select p.ename as meno,s.ename as sef from emp p, emp s left outer jo zajicek=> select p.ename as meno,s.ename as sef from emp , emp s left outer joi zajicek=> select p.ename as meno,s.ename as sef from emp s, emp s left outer jo zajicek=> select p.ename as meno,s.ename as sef from emp s, emp left outer joi zajicek=> select p.ename as meno,s.ename as sef from emp s, emp p left outer jo zajicek=> select p.ename as meno,s.ename as sef from emp s, emp left outer joi zajicek=> select p.ename as meno,s.ename as sef from emp s, emp left outer join zajicek=> select p.ename as meno,s.ename as sef from emp s, em left outer join; zajicek=> select p.ename as meno,s.ename as sef from emp s, e left outer join; zajicek=> select p.ename as meno,s.ename as sef from emp p left outer join emp s on p.mgr=s.empno; meno | sef --------+------- 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) zajicek=> select ename,sal from emp where sal=max(sal); ERROR: Aggregates not allowed in WHERE clause zajicek=> select max(sal) from emp; max --------- 5000.00 (1 row) zajicek=> select max(sal),min(sal),sum(sal) from emp; max | min | sum ---------+--------+---------- 5000.00 | 800.00 | 28075.00 (1 row) zajicek=> select max(sal),min(sal),sum(sal),avg(sal) from emp; max | min | sum | avg ---------+--------+----------+----------------- 5000.00 | 800.00 | 28075.00 | 2005.3571428571 (1 row) zajicek=> select max(hiredate),min(hiredate) from emp; max | min ------------+------------ 1983-01-12 | 1980-12-17 (1 row) zajicek=> select count(*); count ------- 1 (1 row) zajicek=> select count(*) from emp; count ------- 14 (1 row) zajicek=> select count(*) from emp where deptno=30; count ------- 6 (1 row) zajicek=> select count(distinct depto) from emp; ERROR: Attribute 'depto' not found zajicek=> select count(distinct deptno) from emp; count ------- 3 (1 row) zajicek=> select count(*) from emp p, emp k where p.mgr=k.empno; count ------- 13 (1 row) zajicek=> shit; ERROR: parser: parse error at or near "shit" zajicek=> select count(*) from emp p, emp k where p.mgr=k.empno and k.ename='KI NG'; count ------- 3 (1 row) zajicek=> select avg(coalesce(comm,0)) from emp; avg ---------------- 157.1428571429 (1 row) zajicek=> select job,avg(sal) from emp group by job; job | avg -----------+----------------- ANALYST | 3000.0000000000 CLERK | 1037.5000000000 MANAGER | 2441.6666666667 PRESIDENT | 5000.0000000000 SALESMAN | 1400.0000000000 (5 rows) zajicek=> select deptno,job,count(*) from emp 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) zajicek=> select deptno,job,count(*) from emp where hiredategroup by deptno,job zajicek=> select deptno,job,count(*) from emp where hiredate group by deptno,jo zajicek=> select deptno,job,count(*) from emp where hiredate lgroup by deptno,j zajicek=> select deptno,job,count(*) from emp where hiredate ligroup by deptno, zajicek=> select deptno,job,count(*) from emp where hiredate likgroup by deptno zajicek=> select deptno,job,count(*) from emp where hiredate likegroup by deptn zajicek=> select deptno,job,count(*) from emp where hiredate like'group by dept zajicek=> select deptno,job,count(*) from emp where hiredate like'1group by dep zajicek=> select deptno,job,count(*) from emp where hiredate like'19group by de zajicek=> select deptno,job,count(*) from emp where hiredate like'198group by d zajicek=> select deptno,job,count(*) from emp where hiredate like'1980group by zajicek=> select deptno,job,count(*) from emp where hiredate like'1980%group by zajicek=> select deptno,job,count(*) from emp where hiredate like'1980%'group b zajicek=> select deptno,job,count(*) from emp where hiredate like'1980%' group zajicek=> select deptno,job,count(*) from emp where hiredate like'1980%' group by deptno,job; deptno | job | count --------+-------+------- 20 | CLERK | 1 (1 row) zajicek=> select deptno,job,count(*) from emp where hiredate like'1980%' group zajicek=> select deptno,job,count(*) from emp group by deptno,job; zajicek=> select deptno,hiredate,count(*) from emp group by hiredate; ERROR: Attribute emp.deptno must be GROUPed or used in an aggregate function zajicek=> select deptno,job,count(*) from emp group by deptno,job having count> 1; ERROR: Attribute 'count' not found zajicek=> select deptno,job,count(*) from emp group by deptno,job having count> zajicek=> select deptno,job,count(*) from emp group by deptno,job having count( *)>1; deptno | job | count --------+----------+------- 20 | ANALYST | 2 20 | CLERK | 2 30 | SALESMAN | 4 (3 rows) zajicek=> select deptno,job,count(*) from emp group by deptno,job having count( zajicek=> zajicek=> select deptno,job,count(*) from emp group by deptno,job having count( zajicek=> select deptno,jo,count(*) from emp group by deptno,job having count(* zajicek=> select deptno,j,count(*) from emp group by deptno,job having count(*) zajicek=> select deptno,,count(*) from emp group by deptno,job having count(*)> zajicek=> select deptno,count(*) from emp group by deptno,job having count(*)>1 zajicek=> select deptno,acount(*) from emp group by deptno,job having count(*)> zajicek=> select deptno,avcount(*) from emp group by deptno,job having count(*) zajicek=> select deptno,avgcount(*) from emp group by deptno,job having count(* zajicek=> select deptno,avg(count(*) from emp group by deptno,job having count( zajicek=> select deptno,avg(scount(*) from emp group by deptno,job having count zajicek=> select deptno,avg(sacount(*) from emp group by deptno,job having coun zajicek=> select deptno,avg(salcount(*) from emp group by deptno,job having cou zajicek=> select deptno,avg(sal)count(*) from emp group by deptno,job having co zajicek=> select deptno,avg(sal),count(*) from emp group by deptno,job having c zajicek=> select deptno,avg(sal),count(*) from emp group by deptno,jo having co zajicek=> select deptno,avg(sal),count(*) from emp group by deptno,j having cou zajicek=> select deptno,avg(sal),count(*) from emp group by deptno, having coun zajicek=> select deptno,avg(sal),count(*) from emp group by deptno having count zajicek=> select deptno,avg(sal),count(*) from emp group by deptno having count (*)>1; deptno | avg | count --------+-----------------+------- 10 | 2600.0000000000 | 3 20 | 2175.0000000000 | 5 30 | 1566.6666666667 | 6 (3 rows) zajicek=> select deptno,avg(sal),count(*) from emp group by deptno having count zajicek=> select deptno,avg(sal),count(*) from emp group by deptno having coun zajicek=> select deptno,avg(sal),count(*) from emp w group by deptno having cou zajicek=> select deptno,avg(sal),count(*) from emp wh group by deptno having co zajicek=> select deptno,avg(sal),count(*) from emp whe group by deptno having c zajicek=> select deptno,avg(sal),count(*) from emp wher group by deptno having zajicek=> select deptno,avg(sal),count(*) from emp where group by deptno having zajicek=> select deptno,avg(sal),count(*) from emp where group by deptno havin zajicek=> select deptno,avg(sal),count(*) from emp where j group by deptno havi zajicek=> select deptno,avg(sal),count(*) from emp where jo group by deptno hav zajicek=> select deptno,avg(sal),count(*) from emp where job group by deptno ha zajicek=> select deptno,avg(sal),count(*) from emp where job= group by deptno h zajicek=> select deptno,avg(sal),count(*) from emp where job=' group by deptno zajicek=> select deptno,avg(sal),count(*) from emp where job='M group by deptno zajicek=> select deptno,avg(sal),count(*) from emp where job='MA group by deptn zajicek=> select deptno,avg(sal),count(*) from emp where job='MAN group by dept zajicek=> select deptno,avg(sal),count(*) from emp where job='MANA group by dep zajicek=> select deptno,avg(sal),count(*) from emp where job='MANAG group by de zajicek=> select deptno,avg(sal),count(*) from emp where job='MANAGE group by d zajicek=> select deptno,avg(sal),count(*) from emp where job='MANAGER group by zajicek=> select deptno,avg(sal),count(*) from emp where job='MANAGER' group by zajicek=> select deptno,javg(sal),count(*) from emp where job='MANAGER' group b zajicek=> select deptno,joavg(sal),count(*) from emp where job='MANAGER' group zajicek=> select deptno,jobavg(sal),count(*) from emp where job='MANAGER' group zajicek=> select deptno,job,avg(sal),count(*) from emp where job='MANAGER' grou zajicek=> select deptno,jobavg(sal),count(*) from emp where job='MANAGER' group zajicek=> select deptno,joavg(sal),count(*) from emp where job='MANAGER' group zajicek=> select deptno,javg(sal),count(*) from emp where job='MANAGER' group b zajicek=> select deptno,avg(sal),count(*) from emp where job='MANAGER' group by zajicek=> select deptno,avg(sal),count(*) from emp where job='MANAGER' group by deptno having count(*)>1; deptno | avg | count --------+-----+------- (0 rows) zajicek=> select deptno,avg(sal),count(*) from emp where job='MANAGER' group by zajicek=> zajicek=> select * from emp where job='manager'; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-------+-----+-----+----------+-----+------+-------- (0 rows) zajicek=> select * from emp where job='MANAGER'; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-------+---------+------+------------+---------+------+-------- 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 (3 rows) zajicek=> select deptno,avg(sal),count(*) from emp where job='MANAGER' group by deptno having count(*)>1; deptno | avg | count --------+-----+------- (0 rows) zajicek=> select deptno,avg(sal),count(*) from emp where job='MANAGER' group by deptno having count(*)>0; deptno | avg | count --------+-----------------+------- 10 | 1500.0000000000 | 1 20 | 2975.0000000000 | 1 30 | 2850.0000000000 | 1 (3 rows)