* Ocelot exercise database presented in SQL basics 1 will be used in this exercise * Return exercise in .txt format and use formatting presented below (question always followed be the query)
-- Select all employees from table
SELECT * FROM EMPS;
* In this exercise you will add a new table to Ocelot training database, which will be used to describe the history of employee salary.
Create tables
1. Create a new table called salarydata with the following columns:
* salarydataID (primary key)
* salary (the amount of salary described with two decimals)
2. Create a connection between salarydata and emps tables (Tip: First think what column you should bring from emps table and then create a new column for it). Use the following options in foreign key definition:
* UPDATE CASCADE
* DELETE NO ACTION
Editing table properties
* Important: Do not include the following tasks with the CREATE TABLE statement used in the first exercise but rather add them using a separate query!
3. Add a new column called last_update. The current date should be added automatically for this column when new data is inserted.
4. Add automatic counter for salarydataID column so that this field value will be automatically generated each time a new data is inserted.
Input validation
5. Create input validation check with TRIGGER. The following checks has to be made:
* Salary cannot be under 1000 and cannot exceed 12000 euros.
* Salary value must be even (salary % 2 = 0). For example, only salary values 1000, 1002, 1004 etc. are accepted.