SQL statements and RA queries

feeding !*>animal

select distinct *
from animal a
where not exists
  (select * from feeding f where f.id_animal=a.id_animal);

species(name='elephant')
[species.id_species=animal.id_species>animal
(name='Jumbo')
[animal.id_celebrity=celebrity.id_celebrity>celebrity

select c.*
from animal a join species s on a.id_species = s.id_species
     join celebrity c using (id_celebrity)
where s.name='elephant' and a.name='Jumbo';

{employee(personal_number=215)*>doctor[id_employee=assisted_id_employee>examination*>animal}
\
{employee(personal_number!=215)*>doctor[id_employee=assisted_id_employee>examination*>animal}

select a.*
from employee em join doctor d using (id_employee)
     join examination ex on d.id_employee = ex.assisted_id_employee
     join animal a on ex.id_animal = a.id_animal
where em.personal_number=215
except
select a.*
from employee em join doctor d using (id_employee)
     join examination ex on d.id_employee = ex.assisted_id_employee
     join animal a on ex.id_animal = a.id_animal
where em.personal_number!=215;

{feeding[id_animal, id_employee]÷keeper[id_employee]}*animal

-- formulation using double negation and existential quantifier
-- thus: instead of "P" being true for all "x",
--       we use the formulation there is no "x"
--       such that the negation "P" is true for it

select * from animal a where not exists(
    select * from keeper k where not exists(
        select * from feeding f where f.id_animal=a.id_animal and f.id_employee=k.id_employee
    )
);

-- using aggregation function count
-- CAREFUL: count(distinct id_employee), "distinct" is essential here

select * from animal a where
 (select count(distinct id_employee) from feeding f where f.id_animal=a.id_animal)
 =
 (select count(id_employee) from keeper);

 -- using with statement and a universe construciton (by cross join)
with
animals as (select id_animal from animal),
keepers as (select id_employee from keeper),
possible_feedings as (select * from animals cross join keepers),
real_feedings as (select id_animal,id_employee from feeding),
non_realized_feedings as (select * from possible_feedings
                          except
			  select * from real_feedings),
animal_not_fed_by_everybody as (select id_animal from non_realized_feedings),
animal_fed_by_everybody as (select * from animals
                            except
			    select * from animal_not_fed_by_everybody)
select * from animal_fed_by_everybody join animal using (id_animal);

keeper
\
{{feeding[id_animal, id_employee]÷keeper[id_employee]}*animal*>feeding*>keeper}

-- exactly same formulation as in Q5 using RA
select * from keeper                       -- all keepers
except                                     -- except
select * from keeper where id_employee in( -- such keepers who fed
select id_employee from feeding where id_employee=keeper.id_employee and id_animal in(
select id_animal from (                        -- such animals
-- Q4 formulation                                -- fed by every keeper
select * from animal a where
 (select count(distinct id_employee) from feeding f where f.id_animal=a.id_animal)
 =
 (select count(id_employee) from keeper)
 ) a))
 ;

-- using aggregation functions an group by / having clouses
-- i.e. for each id_animal from the set of animals fed by every keeper
--      return id_animal and an amount of feedings by DISTINCT kepers
--       but exclude (filter) such cases where
--         amount of feeding by DISTICT keepers is difeerent from
--           -- amount of all keepers

select id_animal ,count(distinct feeding.id_employee)
from feeding where id_animal in(
select id_animal from (
-- Q4 formulation
select * from animal a where
 (select count(distinct id_employee) from feeding f where f.id_animal=a.id_animal)
 =
 (select count(id_employee) from keeper)
 ) a)
 -- end of Q4
group by id_animal
having count(distinct feeding.id_employee) !=
        (select count(id_employee) from keeper)
 ;
 -- the query should return an empty result if Q4 was correct

examination(purpose='nausea')!*>animal

select * from animal
except
select animal.* from animal join examination using(id_animal)
where purpose='nausea';

{{species(name='hippopotamus')[animal.id_species=species.id_species>animal*>examination[made_id_employee=id_employee>doctor*>employee}
∪
{species(name='elephant')[animal.id_species=species.id_species>animal*>examination[made_id_employee=id_employee>doctor*>employee}}
\
{{species(name='hippopotamus')[animal.id_species=species.id_species>animal*>examination[made_id_employee=id_employee>doctor*>employee}
∩
{species(name='elephant')[animal.id_species=species.id_species>animal*>examination[made_id_employee=id_employee>doctor*>employee}}

((select distinct e.*
from employee e join doctor d using (id_employee)
     join examination ex on (ex.made_id_employee = d.id_employee)
     join animal a using (id_animal)
     join species s using(id_species)
where s.name='hippopotamus')
union
(select distinct e.*
from employee e join doctor d using (id_employee)
     join examination ex on (ex.made_id_employee = d.id_employee)
     join animal a using (id_animal)
     join species s using(id_species)
where s.name='elephant'))                      -- fed hippopotamus OR elephant
except
((select distinct e.*
from employee e join doctor d using (id_employee)
     join examination ex on (ex.made_id_employee = d.id_employee)
     join animal a using (id_animal)
     join species s using(id_species)
where s.name='hippopotamus')
intersect
(select distinct e.*
from employee e join doctor d using (id_employee)
     join examination ex on (ex.made_id_employee = d.id_employee)
     join animal a using (id_animal)
     join species s using(id_species)
where s.name='elephant'))                    -- fed hippopotamus AND elephant
;

select
sum(price) as total,
round(avg(price),4) as avarage,
max(price) as maximal,
min(price) as minimal,
count(id_ticket) as amount_of_tickets,
count(distinct price) as amount_of_dirrerent_prices
from ticket;

-- using JOIN operation
select distinct c.*
from celebrity c join vip_celebrity using (id_celebrity)
order by name asc, surname desc;

-- using EXISTS oprator
-- not we doesn't care about data in inner select
--   we only need to check that the return set is not empty (i.e. exists a record that...)
select distinct c.*
from celebrity c
where exists (select 'X' from vip_celebrity v
              where v.id_celebrity = c.id_celebrity)
order by name asc, surname desc;

--usin IN operator
select distinct c.*
from celebrity c
where id_celebrity in
      (select id_celebrity from vip_celebrity )
order by name asc, surname desc;

-- surprisingly the statement bellew will not work:
select distinct e.name, e.surname, e.personal_number
from keeper k join employee e using(id_employee)
     join feeding f using(id_employee)
     join animal a using(id_animal)
     join species s using(id_species)
where s.name in('hippopotamus', 'elephant', 'goose', 'pitbul', 'buzzard')
order by e.salary
-- it returns error: for SELECT DISTINCT, ORDER BY expressions must appear in select list

-- of course, we can add it
select distinct e.name, e.surname, e.personal_number, e.salary
from keeper k join employee e using(id_employee)
     join feeding f using(id_employee)
     join animal a using(id_animal)
     join species s using(id_species)
where s.name in('hippopotamus', 'elephant', 'goose', 'pitbul', 'buzzard')
order by e.salary
;

-- or we can wrap the query into from statement a project only to wanted columns
select distinct name, surname, personal_number
from (
select distinct e.name, e.surname, e.personal_number, e.salary
from keeper k join employee e using(id_employee)
     join feeding f using(id_employee)
     join animal a using(id_animal)
     join species s using(id_species)
where s.name in('hippopotamus', 'elephant', 'goose', 'pitbul', 'buzzard')
order by e.salary
) as original_select
;

select count(*) as needed_feedings,
       count(distinct id_species) as different_species
from keeper cross join
     (animal join species using (id_species))
;

-- let us insert a keeper without feeding to show it works completely
-- let us do it inside a transaction, so the insert is not commited permanently

begin;
insert into keeper(id_employee) values (7);

select name, surname, personal_number,
       coalesce(
	  to_char(
	     (select min(feeding_date)
	      from feeding f
	      where k.id_employee=f.id_employee),
	    'dd.mm.yyyy'),
	   'NOT FED YET') as earliest_feeding
from keeper k join employee e using (id_employee)
order by earliest_feeding
;

-- rollback will rollback of keeper with id 7

rollback;

-- let us insert a keeper without feeding to show it works completely
-- let us do it inside a transaction, so the insert is not commited permanently

begin;
insert into keeper(id_employee) values (7);

select name, surname, personal_number,
       coalesce(
	to_char( min (feeding_date),
	         'dd.mm.yyyy'),
	   'NOT FED YET') as earliest_feeding
from keeper k join employee e using (id_employee)
     left outer join feeding using (id_employee)
group by id_employee, name, surname, personal_number
order by earliest_feeding
;

-- rollback will rollback of keeper with id 7

rollback;

select food.name, sum(amount) fed_total, count(id_feeding) as feeding_amount
from employee e join feeding f using(id_employee)
     right outer join food
      on (food.id_food = f.id_food and e.personal_number=201)
group by food.name, food.id_food
having count(id_feeding) <=5
order by feeding_amount desc;

select c.name, c.surname, a.name animal_name ,a.cage_name section, s.name species
from celebrity c full outer join animal a using (id_celebrity)
     left join species s using (id_species)
order by c.name, c.surname, a.name;

-- CAREFULL for amount of data when you are using to use cross join
-- Let us do everything in a scope of transaction without persistent storage newly inserted data.
begin;
--check amount of feedings before insert
select count(*) from feeding;

--let us insert only 5 new rows (see clouse limit bellow)

insert into feeding (id_food, id_animal, id_feeding_type, id_employee, feeding_date, amount)
select id_food, id_animal, id_feeding_type, id_employee, feeding_date, amount
from(
select id_food, id_animal, id_feeding_type, id_employee,
       now() - random() * INTERVAL '50 years' as feeding_date ,
       round(random()*100)+1 as amount
from food cross join animal cross join feeding_type cross join keeper
) command order by random() limit 5;

--check we really insert expected amount of rows
select count(*) from feeding;
-- let us rollbacke the transaction
rollback;
-- and, again, check the amount of records did not changed
select count(*) from feeding;

create or replace view rich_keepers as
select *
from employee e
where salary > 20000
   and exists(select 1
              from keeper k
	      where e.id_employee=k.id_employee)
with check option
;
--let us do a check ;-)
select * from rich_keepers;

select *
from feeding f
where exists
(select 1 from rich_keepers k where
 k.id_employee=f.id_employee);

-- let us do it in a transaction only and rollback at the end
begin;
-- check how many rich_keepers there are
-- it allows as to check we really delete them
select count(id_employee) from rich_keepers;
-- also check amount of feedings (see NOTE bellow)
select count(*) from feeding;
--let us delete the rich_keepers
delete from keeper
where id_employee
       in (select id_employee from rich_keepers);
-- check the delete was succesfull
select count(id_employee) from rich_keepers;
-- also check amount of feedings (see NOTE bellow)
select count(*) from feeding;
-- rolback changes
rollback;

select food.name, sum(amount) fed_total, count(id_feeding) as feeding_amount
from employee e join feeding f using(id_employee)
     right outer join food
      on (food.id_food = f.id_food)
where e.personal_number=201
group by food.name, food.id_food
having count(id_feeding) <=5
order by feeding_amount desc;

--začneme transakci
begin;
--zkontrolujeme data která budemě měnit
select e.id_employee,e.name, e.surname, e.salary
from employee e join keeper k using(id_employee)
     join feeding f using (id_employee)
     join animal a using (id_animal)
     join species s using (id_species)
where s.name='jaguar';
-- let us do update
update employee
set salary=salary-500
where id_employee in(
  select k.id_employee
  from keeper k
     join feeding f using (id_employee)
     join animal a using (id_animal)
     join species s using (id_species)
  where s.name='jaguar');
-- check if all salaries were affected
select e.id_employee,e.name, e.surname, e.salary
from employee e join keeper k using(id_employee)
     join feeding f using (id_employee)
     join animal a using (id_animal)
     join species s using (id_species)
where s.name='jaguar';
--rollback all changes
rollback;

examination(examination_date<'18.3.2012')*measure

select *
from examination join measure using(id_examination)
where examination_date < to_date('18.3.2012','dd.mm.yyyy');

select a.*
from animal a left join examination e
   on (e.id_animal = a.id_animal and purpose = 'nausea')
where e.id_animal is null;


/*
-- CAREFULLL
-- formulation bellow will not work
select a.*
from animal a left join examination e
   on (e.id_animal = a.id_animal)
where e.id_animal is null and purpose = 'nausea';
*/

{doctor(specialization='laceration')[id_employee=made_id_employee >examination*>measure}[description]

select distinct description
from measure natural join examination natural join doctor
where specialization = 'laceration';

{employee[id_address,name->name1,surname->surname1,id_employee->id_employee1]*
 employee[id_address,name->name2,surname->surname2,id_employee->id_employee2]}
 (id_employee1<id_employee2)[id_address,name1,surname1,name2,surname2]

select id_address,
       e1.name as name1, e1.surname as surname1,
       e2.name as name2, e2.surname as surname2
from employee e1 join employee e2 using(id_address)
where e1.id_employee < e2.id_employee
;