CREATE TABLE employee  (emp_no INTEGER  NOT NULL primary key,
                        emp_fname CHAR(20) NOT NULL,
                        emp_lname CHAR(20) NOT NULL,
                        dept_no CHAR(4) NULL
                        )
CREATE TABLE department(dept_no char(4) NOT NULL primary key,
                        dept_name CHAR(25) NOT NULL,
                        location CHAR(30) NULL)

/*
alter table employee
add constraint h foreign key (dept_no) references department(dept_no) on update cascade on delete cascade;
*/
CREATE TABLE project   (project_no char(4) NOT NULL primary key,
                        project_name CHAR(15) NOT NULL,
                        budget FLOAT NULL)

insert into employee values(25348, 'Matthew', 'Smith','d3')
insert into employee values(10102, 'Ann', 'Jones','d3')
insert into employee values(18316, 'John', 'Barrimore', 'd1')
insert into employee values(29346, 'James', 'James', 'd2')
insert into employee values(9031, 'Elsa', 'Bertoni', 'd2')
insert into employee values(2581, 'Elke', 'Hansel', 'd2')
insert into employee values(28559, 'Sybill', 'Moser', 'd1')
insert into department values ('d1', 'research','Dallas')
insert into department values ('d2', 'accounting', 'Seattle')
insert into department values ('d3', 'marketing', 'Dallas')
insert into project values ('p1', 'Apollo', 120000.00)
insert into project values ('p2', 'Gemini', 95000.00)
insert into project values ('p3', 'Mercury', 186500.00)
insert into project values ('4', 'Mercurycc', 186400.00)
insert into project values ('6', 'Mercuryc', 186406.00)


create table works_on ( emp_no int,
                        constraint f foreign key (emp_no) references employee(emp_no) on update cascade on delete cascade,
                        project_no char(4),
                        constraint g foreign key (project_no) references project(project_no) on update cascade on delete cascade,
                        job char(20) not null,
                        enter_date date
                        )
alter table employee
add constraint name_constraint unique(emp_fname, emp_lname);

alter table works_on
add constraint enter_date_default
default GETDATE() for enter_date;

alter table project
add constraint budget_value check(budget between 0 and 250000);

alter table employee
drop constraint name_constraint;

insert into works_on (emp_no, project_no, job, enter_date)
values ('1', '123', 'sth', '03-01-1998'), ('1', '123', 'sth','03-01-1998'),('1', '123', 'sth','03-01-1998'), ('1', '123', 'sth', '03-01-1998'),('1', '123', 'sth', '03-01-1998');

exec sp_RENAME 'employee.emp_fname', 'emp_surname', 'column';

update employee
set emp_lname = 'Bond'
where emp_surname = 'Elsa';

update project
set project_name = 'jakaswartosc'
where project_no between '3' and '5';