Скачать или смотреть 39 - Sub-queries me komanden UPDATE, DELETE, INSERT

create table people
(
id integer primary key,
name varchar(100) not null,
surname varchar(100) not null,
tax integer
);
-------------------------------------------------------------------
create table jobs
(
id integer primary key,
person_id integer not null references people(id),
name varchar(100) not null,
company_name varchar(100) not null,
salary integer not null
);

------------------------------------------------------------------
insert into people values (1, 'Ardit', 'Asllani', 20);
insert into people values (2, 'Artan', 'Meha', 20);
insert into people values (3, 'Megi', 'Ponari', 20);
insert into people values (4, 'Alma', 'Therci', 20);
insert into people values (5, 'Valentina', 'Debrova', 20);
insert into people values (6, 'Bahri', 'Menolla', 20);

insert into jobs values (1, 1, 'Part time sales', 'Martens GmbH', 400);
insert into jobs values (2, 1, 'Part time marketing', 'Bader', 350);
insert into jobs values (3, 2, 'Part time freelancer', 'Diehl', 460);
insert into jobs values (4, 2, 'Part time marketing', 'Jast-Bechtelar', 350);
insert into jobs values (5, 3, 'Sales', 'Schneider-Maggio', 800);
insert into jobs values (6, 4, 'IT', 'Wyman-Bechtelar', 1600);
insert into jobs values (7, 5, 'Principal Engineer', 'Kemmer Group', 1800);
-------------------------------------------------------------------------

select * from people p left join jobs j on p.id = j.person_id;

update people set tax = 25 where id in
(
select person_id from jobs group by person_id having count(*) greater than 2
);

delete from people where id not in (select person_id from jobs);

insert into people (id, name, surname, tax) select id, name, surname, tax from emigrants;
---------------------------------------------------------------------------------------------
CREATE TABLE emigrants
(
id integer primary key,
name varchar(100) not null,
surname varchar(100) not null,
tax integer
);

insert into emigrants values (7, 'Bedar', 'Asllani', 20);
insert into emigrants values (8, 'Bledi', 'Arbri', 20);
insert into emigrants values (9, 'Kristina', 'Agimi', 20);
insert into emigrants values (10, 'Kushtim', 'Deci', 20);
insert into emigrants values (11, 'Valentin', 'Bahi', 20);

select * from emigrants;