$1.$ Create a schema employee

$2.$ Write a SQL statement to create a simple table countries including

columns country$\_$id$,$country$\_$name and region$\_$id$.$

$3.$ Write a SQL statement to create a table named jobs including columns

job$\_$id$,$ job$\_$title, min$\_$salary, max$\_$salary and check whether the

max$\_$salary amount exceeding the upper limit $25000.$

$4.$ Write a SQL statement to create a table job$\_$history including columns

employee$\_$id$,$ start$\_$date, end$\_$date, job$\_$id and department$\_$id and make

sure that, the employee$\_$id column does not contain any duplicate value

at the time of insertion and the foreign key column job$\_$id contain only

those values which are exists in the jobs table.

Here is the structure of the table jobs;

$+------------+--------------+------+-----+---------+-------+$

$|$ Field $|$ Type $|$ Null $|$ Key $|$ Default $|$ Extra $|$

$+------------+--------------+------+-----+---------+-------+$

$|$ JOB$\_$ID $|$ varchar$(10)|$ NO $|$ PRI $|||$

$|$ JOB$\_$TITLE $|$ varchar$(35)|$ NO $||$ NULL $||$

$|$ MIN$\_$SALARY $|$ decimal$(6,0)|$ YES $||$ NULL $||$

$|$ MAX$\_$SALARY $|$ decimal$(6,0)|$ YES $||$ NULL $||$

$+------------+--------------+------+-----+---------+-------+$

$5.$ Write a SQL statement to create a table departments

$|$ Field $|$ Type $|$ Null $|$ Key $|$ Default $|$

Extra $|$

$+-----------------+--------------+------+-----+---------+-----$

$--+$

$|$ DEPARTMENT$\_$ID $|$ decimal$(4,0)|$ NO $|$ PRI $|0|$

$|$

$|$ DEPARTMENT$\_$NAME $|$ varchar$(30)|$ NO $||$ NULL $|$

$|$

$|$ MANAGER$\_$ID $|$ decimal$(6,0)|$ NO $|$ PRI $|0|$

$|$

$|$ LOCATION$\_$ID $|$ decimal$(4,0)|$ YES $||$ NULL $|$

$|$

$+-----------------+--------------+------+-----+---------+-----$

$--+$

$6.$ Write a SQL statement to create a table employees including columns

employee$\_$id$,$ first$\_$name, last$\_$name, email, phone$\_$number hire$\_$date,

job$\_$id$,$ salary, commission, manager$\_$id and department$\_$id and make

sure that, the employee$\_$id column does not contain any duplicate value

at the time of insertion and the foreign key columns combined by

department$\_$id and manager$\_$id columns contain only those unique

combination values, which combinations are exists in the departments

table.

$7.$ Write a SQL statement to change the email column of employees table

with 'not available' for all employees.

$8.$ Write a SQL statement to change the email and commission column of

employees table with 'not available' and $0\mathrm{.}10$ for all employees.

$9.$ Write a SQL statement to change the email column of employees table

with 'not available' for those employees whose department$\_$id is $80$ and

gets a commission$\_$pct is less than $\mathrm{.}20.$

$10.$ Write a SQL statement to change the email and commission$\_$pct

column of employees table with 'not available' and $0\mathrm{.}10$ for those

employees whose department$\_$id is $110.$

$11.$ Write a SQL statement to change salary of employee to $8000$

whose ID is $105,$ if the existing salary is less than $5000.$

$12.$ Write a SQL statement to add a primary key for the columns

location$\_$id in the locations table.

Here is the sample table employees.

Sample table: locations

Here is the structure of the table locations.

mysql$>$ SHOW COLUMNS FROM locations;

$+----------------+--------------+------+-----+---------+------$

$-+$

$|$ Field $|$ Type $|$ Null $|$ Key $|$ Default $|$ Extra

$|$

$+----------------+--------------+------+-----+---------+------$

$-+$

$|$ LOCATION$\_$ID $|$ decimal$(4,0)|$ YES $||$ NULL $|$

$|$

$|$ STREET$\_$ADDRESS $|$ varchar$(40)|$ YES $||$ NULL $|$

$|$

$|$ POSTAL$\_$CODE $|$ varchar$(12)|$ YES $||$ NULL $|$

$|$

$|$ CITY $|$ varchar$(30)|$ YES $||$ NULL $|$

$|$

$|$ STATE$\_$PROVINCE $|$ varchar$(25)|$ YES $||$ NULL $|$

$|$

$|$ COUNTRY$\_$ID $|$ varchar$(2)|$ YES $||$ NULL $|$

$|$

$+----------------+--------------+------+-----+---------+------$

$-+$

$13.$ Write a SQL statement to rename the table countries to

country$\_$new.

$14.$ Write a SQL statement to add a column region$\_$id to the table

locations.

$15.$ Write a SQL statement to add a columns ID as the first column of

the table locations.