create table blah(n int);

create table glah(n varchar(2));

select owner,table_name from all_tables;



select owner, table_name from all_tables where owner='ALEXS';


desc all_tables;



create table employee(ename varchar(10),city varchar(10),street varchar(10));
create table works(ename varchar(10),cname varchar(10),salary number(12,2));
create table company(cname varchar(10),city varchar(10));
create table manages(ename varchar(10),mname varchar(10));


select * from manages where mname='John Doe';




-- all companies with 75 employes.

select cname
from works
group by cname
having count(*) = 75;

--- outliers

-- find all employees whose salary is >2sd than avg salary in corp.
select a.*
from works a
    inner join (
       select cname, avg(salary) asal, stddev(salary) sd
       from works 
       group by cname
    ) b 
    on a.cname = b.cname
where 
    a.salary > b.asal + 2*b.sd


-- find all employees whose salary is >95% of all other salaries
-- in their corp.

    select * 
    from (
       select cname, ename, salary,
           row_number() over (partition by cname order by salary) rn,
		count(*) over () cnt,
           row_number() over (partition by cname order by salary) / 
		count(*) over () as pr
       from works 
    ) a
    where pr > 0.95


insert into works values ('a','b',1);
insert into works values  ('b','b',2);
insert into works  values ('c','b',3);
insert into works values  ('e','b',4);
insert into works values  ('d','b',5);
insert into works values  ('f','b',1);
insert into works values  ('g','b',3);
insert into works values  ('h','b',1);
insert into works values  ('i','b',15);
insert into works values  ('j','b',1);
insert into works values  ('k','b',1);
insert into works values  ('l','b',3);
insert into works values  ('m','b',1);
insert into works values  ('n','b',5);
insert into works values  ('o','b',1);
insert into works  values ('p','b',6);
insert into works  values ('q','b',1);
insert into works values  ('r','b',1);
insert into works values  ('s','b',9);
insert into works values  ('t','b',1);
insert into works values  ('u','b',1);
insert into works values  ('v','b',4);
insert into works  values ('w','b',1);
insert into works values  ('x','b',4);
insert into works values  ('y','b',1);
insert into works values  ('z','b',12);