Zadanie #1, Bazy Danych, 28.05.2015 r.

--1a)
create table LeaveTypes(
LeaveTypeID int not null,
Name varchar(15) not null,
IsPaid bit not null

primary key (LeaveTypeID));

create table Leaves(
LeaveID int not null,
EmployeeID int not null,
FirstDay date not null,
LastDay date null,
LeaveTypeID int not null,
DeputyEmployeeID int null,
AvailableByPhone bit not null,
PaidPercent real null

primary key (LeaveID)
foreign key (EmployeeID) references Employees(EmployeeID),
foreign key (LeaveTypeID) references LeaveTypes(LeaveTypeID),
foreign key (DeputyEmployeeID) references Employees(EmployeeID));

--1b)
alter table Employees
add HolidayLeaveDaysRemains smallint;

--1c)
alter table LeaveTypes
alter column Name varchar(20);

--1d)
insert into LeaveTypes
(LeaveTypeID, Name, IsPaid)
values(1, 'macierzyński', 1),
(2, 'chorobowy', 1),
(3, 'wypoczynkowy', 0);

select * from LeaveTypes;

--1e)
insert into Leaves
(LeaveID, EmployeeID, FirstDay, LastDay, LeaveTypeID, AvailableByPhone)
values(1, 1, '2014-05-01', '2014-05-10', 1, 1),
(2, 1, '2015-05-01', '2015-05-10', 1, 0);

select e.LastName, e.FirstName, lt.Name from Leaves l
join Employees e on e.EmployeeID = l.EmployeeID
join LeaveTypes lt on lt.LeaveTypeID = l.LeaveTypeID;

--1f)
alter table Leaves
drop column AvailableByPhone;