SHOW search_path;
SET search_path TO public;

drop table employees;
drop table departments;

delete from employees;
delete from departments;

create table departments(
cod int primary key,
name varchar(20),
address varchar(20)
);

create table employees(
ID int primary key,
name varchar(20),
surname varchar(20),
department int references departments(cod)
);

select e.name, e.surname, d.name
from employees e join departments d on e.department = d.cod
where e.surname = 'Rossi';

select e.name, e.surname, d.name
from employees e join departments d on e.department = d.cod
where d.name = 'Mathematics';

select * from departments;
select * from employees;
select * from departments D JOIN employees E on D.cod = E.department order by cod;
select * from employees E JOIN departments D on D.cod = E.department order by id;

insert into departments values(1, 'Mathematics', 'via Roma, Trieste');
insert into departments values(2, 'Computer Science', 'via Italia, Trieste');

insert into employees values (1,'Paolo', 'Rossi', 1);
insert into employees values (2,'Luca', 'Rossi', 2);
insert into employees values (3,'Mario', 'Bianchi', 1);
insert into employees values (4,'Giuseppe', 'Verdi', 2);
insert into employees values (5,'Filippo', 'Neri', 2);
insert into employees values (6,'Giacomo', 'Leopardi', 2);

update departments set cod = 2 where name = 'Computer Science';
