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,empno from emp where empno in(select mgr from emp) and m
gr is not null;
 ename | empno 
-------+-------
 BLAKE |  7698
 CLARK |  7782
 JONES |  7566
 FORD  |  7902
 SCOTT |  7788
(5 rows)

zajicek=> select mgr,avg(sal) as priem from emp group by mgr;
 mgr  |      priem      
------+-----------------
 7566 | 3000.0000000000
 7698 | 1310.0000000000
 7782 | 1300.0000000000
 7788 | 1100.0000000000
 7839 | 2441.6666666667
 7902 |  800.0000000000
      | 5000.0000000000
(7 rows)

zajicek=> select ename,empno,priem from emp,(select mgr,avg(sal) as priem from 
emp group by mgr)tt where empno=tt.mgr
zajicek-> ;
 ename | empno |      priem      
-------+-------+-----------------
 JONES |  7566 | 3000.0000000000
 BLAKE |  7698 | 1310.0000000000
 CLARK |  7782 | 1300.0000000000
 SCOTT |  7788 | 1100.0000000000
 KING  |  7839 | 2441.6666666667
 FORD  |  7902 |  800.0000000000
(6 rows)

zajicek=> select ename,empno,priem from emp,(select mgr,avg(sal) as priem from 
zajicek=> ;
zajicek=> select ename,empno,priem from emp,(select mgr,avg(sal) as priem from 
zajicek=> select ename,empno,priem from emp,(select mgr,avg(sal) as priem from 
zajicek=> ;
zajicek=> select ename,job,sal from emp 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)

zajicek=> select ename,job,sal from emp where sal>all(select avg(sal) from emp 
zajicek=> 
zajicek=> select ename from emp x where sal < any(select sal from emp where job
=x.job);
 ename  
--------
 BLAKE
 CLARK
 MARTIN
 TURNER
 JAMES
 WARD
 SMITH
 ADAMS
(8 rows)

zajicek=> select deptno,count(*) from(select deptno,job from emp group by deptn
o,job)tt group by deptno;
 deptno | count 
--------+-------
     10 |     3
     20 |     3
     30 |     3
(3 rows)

zajicek=> select deptno,count(*) from(select deptno,job from emp group by deptn
o,job)tt group by deptno having count(*)=(select count(distinct job) from emp);
o,job)tt group by deptno having count(*)=(select count(distinct job) from emp);
 deptno | count 
--------+-------
(0 rows)

zajicek=> select deptno,count(*) from(select deptno,job from emp group by deptn
o,job)tt group by deptno having count(*)=(select count(distinct job) from emp);
o,job)tt group by deptno having count(*)=(select count(distinct job) from emp)
o,job)tt group by deptno having count(*)>=all(select count(*) from(select deptn
o,job from emp group by deptno,job)tt group by deptno);
 deptno | count 
--------+-------
     10 |     3
     20 |     3
     30 |     3
(3 rows)

zajicek=> \d emp
                 Table "emp"
 Attribute |         Type          | Modifier 
-----------+-----------------------+----------
 empno     | numeric(4,0)          | not null
 ename     | character varying(10) | 
 job       | character varying(9)  | 
 mgr       | numeric(4,0)          | 
 hiredate  | date                  | 
 sal       | numeric(7,2)          | 
 comm      | numeric(7,2)          | 
 deptno    | numeric(2,0)          | not null

zajicek=> create table pp(icp numeric(4,0),empno numeric
zajicek(> ;
zajicek(> drop table pp
zajicek(> ;
zajicek(> ]d
zajicek(> \d
     List of relations
   Name   | Type  |  Owner  
----------+-------+---------
 dept     | table | zajicek
 emp      | table | zajicek
 salgrade | table | zajicek
(3 rows)

zajicek-> create table pp(icp numeric(4,0),empno numeric(4,0));
ERROR:  parser: parse error at or near ";"
zajicek=> create table pp(icp numeric(4,0),empno numeric(4,0)) 
zajicek-> ;
CREATE
zajicek=> \d
     List of relations
   Name   | Type  |  Owner  
----------+-------+---------
 dept     | table | zajicek
 emp      | table | zajicek
 pp       | table | zajicek
 salgrade | table | zajicek
(4 rows)

zajicek=> insert pp(100,7698);               
ERROR:  parser: parse error at or near "pp"
zajicek=> insert into pp(100,7698);
ERROR:  parser: parse error at or near "100"