Rapasoft.eu

use master
go

if exists(select * from sysdatabases where name = 'firma')
 drop database firma;
go

create database firma
go

use firma
go

create table zamestnanec
(
idz			int not null primary key,
meno		varchar(15),
priezvisko	varchar(20),
dat_nar		datetime,
mng_id		int constraint fk_zamestnanec foreign key references zamestnanec
);

go

create table projekt
(
idp			int not null primary key,
nazov		varchar(40),
projekt_mng	int constraint fk_p_zamestnanec foreign key references zamestnanec
);
go

create table zam_na_proj
(
id			int not null primary key,
id_zam		int constraint fk_pz_zamestnanec foreign key references zamestnanec,
id_proj		int constraint fk_pz_projekt	 foreign key references projekt,
od			datetime,
do			datetime
);
go

INSERT INTO zamestnanec VALUES (5,	'Pavol',		'Sovák',	'6.10.1967',	null);
INSERT INTO zamestnanec VALUES (4,	'Gabriel',	'Semanišin','6.10.1970',	5);
INSERT INTO zamestnanec VALUES (9,	'Stanislav',	'Krajči',	'2.3.1970',		4);
INSERT INTO zamestnanec VALUES (2,	'Peter',		'Vojtáš',	'6.1.1957',		4);
INSERT INTO zamestnanec VALUES (1,	'Tomáš',	'Horváth',	'6.10.1977',	4);
INSERT INTO zamestnanec VALUES (3,	'Peter',		'Gurský',	'10.10.1977',	4);
INSERT INTO zamestnanec VALUES (6,	'Jana',		'Pribolová','2.12.1984',	2);
INSERT INTO zamestnanec VALUES (7,	'Róbert',	'Novotný',	'6.6.1982',		2);
INSERT INTO zamestnanec VALUES (8,	'Veronika',	'Vaneková', '7.7.1983',		2);
INSERT INTO zamestnanec VALUES (10, 'Martin',		'Šumák',	'4.1.1984',		9);
INSERT INTO zamestnanec VALUES (11, 'Ondrej',		'Krídlo',	'10.10.1982',	9);
INSERT INTO zamestnanec VALUES (12, 'Marián',	'Novotný',	'10.9.1982',	9);

INSERT INTO projekt VALUES (1,	'VVGS',				1);
INSERT INTO projekt VALUES (2,	'NAZOU',			2);
INSERT INTO projekt VALUES (3,	'Fuzzy koncepty',	9);
INSERT INTO projekt VALUES (4,	'CE',					4);

INSERT INTO zam_na_proj VALUES ( 1,	  1, 1,	 '1.1.2009',	null);
INSERT INTO zam_na_proj VALUES ( 2,	  3, 1,	 '1.1.2009',	null);
INSERT INTO zam_na_proj VALUES ( 3,	  6, 1,	 '1.1.2009',	null);
INSERT INTO zam_na_proj VALUES ( 4,	  7, 1,	 '1.1.2009',	null);
INSERT INTO zam_na_proj VALUES ( 5,	  8, 1,	 '1.1.2009',	null);
INSERT INTO zam_na_proj VALUES ( 6,	10, 1,    '1.1.2009',	null);
INSERT INTO zam_na_proj VALUES ( 7,	11, 1,	 '1.1.2009',	null);
INSERT INTO zam_na_proj VALUES ( 8,	  6, 2,	 '1.1.2009',	null);
INSERT INTO zam_na_proj VALUES ( 9,	  7, 2,	 '1.1.2009',	null);
INSERT INTO zam_na_proj VALUES (10, 2, 2,	 '1.1.2009',	null);
INSERT INTO zam_na_proj VALUES (11, 9, 3,	 '1.1.2009',	null);
INSERT INTO zam_na_proj VALUES (12,10,3,	 '1.1.2009',	null);
INSERT INTO zam_na_proj VALUES (13,11,3,	 '1.1.2009',	null);

use firma
go

--1) vypis vsetky take projekty, na ktorych pracuje nejaky zamestnanec
select nazov from projekt p
	join zam_na_proj z on z.id_zam = p.projekt_mng

--riesenie pomocou any
select nazov from projekt
	where projekt_mng in(select id_zam from zam_na_proj)

--2) vypis meno a priezvisko zam., ktori sefuju nejakemu projektu
select z.meno,z.priezvisko from zamestnanec z
where z.idz = any (select projekt_mng from projekt)
--vypiste aj nazov
select z.meno,z.priezvisko,nazov from zamestnanec z
join projekt on projekt_mng = idz

--3) vypis ludi, ktori su sefom aj projektu aj sefom niektorym inym ludom
select distinct z.meno,z.priezvisko from zamestnanec z
	join projekt p on p.projekt_mng = z.idz
	join zamestnanec z2 on z2.mng_id = z.idz
--alebo
select z.meno,z.priezvisko from zamestnanec z
where (z.idz in (select p.projekt_mng from projekt p))
and (z.idz in (select mng_id from zamestnanec));

--4) vypis zamestnanca, ktory je sefom ludi, ktori su sefmi projektov
select z.meno,z.priezvisko from zamestnanec z
where z.idz in (select pm.mng_id from zamestnanec pm
	where pm.idz in (select projekt_mng from projekt));
--alebo
select distinct z.meno,z.priezvisko from zamestnanec z
	join zamestnanec z2 on z2.mng_id = z.idz
	join projekt p on p.projekt_mng = z2.idz