SQL database has 3 simple relations
and 8 SQL commands
Note that the following things must be taken into account:
1. The creation date of any Branch should be less or equal to its start date.
2. An Employee can be the head of the same Branch many times but at different dates and a Branch could be headed by only one Employee at a time.
3. The Loss field represents the amount of money (in Saudi Riyals) lost by the Branch when it was headed by the corresponding Employee. This field should be a number greater or equal to zero.
4. The default value associated with the field C_Start_Date will be the current system (use the function sysdate).
Write SQL commands to:
1) Create the above tables and their different constraints. Each foreign key constraint should allow the deletion of the children if the parent is to be deleted. Populate the tables with enough data- Hint: try to insert records in such a way to allow you to verify the answer of you query
2) For the employee(s) who got the lowest loss, increase their salary with 1% of the average on all employee salaries.
3) Give the employee ID for the employees who started at least one branch administration during the year 2012 and didn’t start any branch administration during the year 2013.
4) Give the Branch ID, its creation date and the employee full-name, SSN and the number of times this employee administrated that branch without a loss.
5) Give all the information of the employees with a salary less than 8000SR, 30 years old or more and who haven’t be head of any branch. The list will be displayed in an ascending order of the SSN.
6) Give the branch ID and its start date along with the employee full-name for the employees who headed that branch exactly twice.
7) Give the employee SSN, the full-name and the birthdate of the employees who headed at least all the branches headed by the employee Mohamed between 2000 and 2013.
8) Create a view named Branch_Summary which gives, for each company created after 2000 the following information: The branch ID, its start date, its maximum loss, its minimum loss and the number of different heads that administrated it.