-- Skrypt pobrany z SQLpedia.pl
-- Autor Jakub Kasprzak  v.1 2013-02-22
-- typy i możliwości łączeń zbiorów LEFT, RIGHT, FULL OUTER JOIN, SELF JOIN, INNER JOIN


IF OBJECT_ID('dbo.HIST') is not null drop table dbo.HIST
IF OBJECT_ID('dbo.CAR') is not null drop table dbo.CAR
IF OBJECT_ID('dbo.EMP') is not null drop table dbo.EMP

create table dbo.EMP
(

      IdPrac smallint identity(1,1) PRIMARY KEY,
      Imie varchar(20) not null,
      Nazwisko varchar(20) not null,
      DtZatr date,
      Stanowisko varchar(25),
      IdManager smallint REFERENCES dbo.EMP(IdPrac)
);

create table dbo.CAR
(
      NrRej varchar(10) PRIMARY KEY,
      Marka varchar(20) not null,
      Rocznik smallint,
      IdPrac smallint REFERENCES dbo.EMP(IdPrac)
);

Create table dbo.HIST
(
      IdHist smallint identity(1,1) PRIMARY KEY,
      NrRej varchar(10) not null REFERENCES dbo.CAR(NrRej),
      DtPomiaru smalldatetime not null,
      Przebieg int,
      IdPrac smallint not null REFERENCES dbo.EMP(IdPrac)
);

insert into dbo.EMP
VALUES ('Adam','Nowak','2010-01-01','BOSS',null),
('Piotr','Kowalski','2010-03-15','Kierownik Sprzedaży',1),
('Michał','Pogodny','2010-04-04','Sprzedawca',2),
('Anna','Dymna','2010-05-15','Kierownik Marketingu',1),
('Jan','Mały','2011-08-01','Marketer',4),
('Leopold','Stuff','2011-11-15','Sprzedawca',2),
('Monika','Miła','2012-03-31','Asystent',1),
('Joanna','Wesoła','2012-08-01','Sprzedawca',2)
 
 
insert into dbo.CAR (NrRej,Marka,IdPrac,Rocznik)
VALUES ('PO745AX','Mercedes',1,2013),
('PO64231','VOLVO S40',2,2012),
('PO123AA','VOLVO V60',4,2012),
('PO422VX','FIAT 500',6,2012),
('PO841XY','FIAT 500',null,2013)
 
INSERT INTO dbo.HIST (NrRej,DtPomiaru,Przebieg,IdPrac)
VALUES('PO123AA','2012-03-30',4500,4),
('PO123AA','2012-04-28',4500,4),
('PO123AA','2012-05-30',7800,4),
('PO123AA','2012-06-30',12300,4),
('PO123AA','2012-07-31',23200,4),
('PO123AA','2012-08-31',27000,4),
('PO64231','2012-04-30',12500,2),
('PO64231','2012-05-31',16400,2),
('PO64231','2012-06-30',19900,2),
('PO64231','2012-07-31',21200,2),
('PO64231','2012-08-31',22300,2),
('PO422VX','2012-05-31',41400,6),
('PO422VX','2012-06-30',56900,6),
('PO422VX','2012-07-31',71200,6),
('PO422VX','2012-08-31',84300,6)


select * from dbo.EMP
select * from dbo.CAR 
select * from dbo.HIST