USA: +1 (725) 712-1730 
Sign in
Consider a DB schema consisting of the following relation schemes:
USING MYSQL
Regions (Region_ID, Region_Name)
Countries (Country_id, Country_Name, Region_Id)
Locations (Location_Id, Street_address, Postal_code, City, State_Province, Country_Id)
Jobs (Job_Id, Job_title, Min_Salary, Max_salary)
Departments (Dep_Id, Dep_Name, Manager_Id, Location_Id)
Employees (Emp_ID, FirstName, Last_Name, E-mail, Phone_number, Hire_date, Job_Id, Salary, Comsn_pct, Manager_Id, Dep_Id)
Employee_History (Emp_ID, Joining_date, last_date, Job_ID, Dep_ID)
6. Create a trigger to ensure that a salary of an employee cannot exceed the salary of his/her manager. If the employee does not have a manager, then his/her salary cannot be more than 10% of the highest salary in the database.
7. For changes in the job of an employee, updated details provided below must be written to Employee History: hire date of the employee for start date, old job ID, old department ID, Employee ID, todays’ system date for end date. In case a row is already present for employee job history then the start date must be the end date of that (row +1).
8. Make a Trigger to ensure that the salary of the employee is never decreased while working in an organization.
9. Create a trigger to ensure that an increase of salary for an employee is conform with the following rules: If experience is more than 8 years, increase salary by max 20%; If experience is greater than 3 years, increase salary by max of 10%; Otherwise a max increase of 5%.
10. Create a trigger to ensure that Min_salary cannot exceed Max_salary for any job.
Expert Answer
ANSWER::
CREATE AND INSER SYNTAX:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....);
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
CREATE [ OR ALTER ] TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }
<ddl_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
-- Trigger on a LOGON event
CREATE [ OR ALTER ] TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR| AFTER } LOGON
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }
<logon_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
Syntax
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement [ ; ] [ ,...n ] [ ; ] > }
<dml_trigger_option> ::=
[ EXECUTE AS Clause ]
