Consider a DB schema consisting of the following relation schemes: Regions (Regi

Question

Consider a DB schema consisting of the following relation schemes:

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)

* Keys Now, express the following queries in SQL:

1. Find the count of departments, region name(s) and cities for the department(s) that have more than 500 employees.

2. For a department in which the max salary is greater than 100000 for employees who worked in the past, set the manager name as ‘Picard’.

3. Find month and year which witnessed lowest count of employees joining a department located in ‘Vancouver’.

4. With the help of schema find the year which witnessed maximum number of employee intake.

5. For the year in query-4, find how many joined in each month in that specific year. are underlined

[Part 2] Use triggers for the queries below:

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.

Explanation / Answer

1.    Find the count of departments, region name(s) and cities for the department(s) that have more than 500 employees.

Select Count(A.Dep_Id),C. City,E. Region_Name from Departments A Join   Employees B on A. Dep_Id=B. Dep_Id Join Locations C on A. Location_Id=C. Location_Id join Countries D on C. Country_Id=D. Country_Id Join Regions E on D. Region_ID=E. Region_ID group by B.Emp_ID where Count(B.Emp_ID)>500

2.    For a department in which the max salary is greater than 100000 for employees who worked in the past, set the manager name as ‘Picard’.

Update A Set A. FirstName=‘Picard’ from Employees A join Departments B on A. Manager_Id =B. Manager_Id Join Employee_History C on B. Dep_Id=C. Dep_Id Join Jobs D on C. Job_ID=D. Job_ID where C. last_date>getdate() and D. Max_salary>100000

3.    Find month and year which witnessed lowest count of employees joining a department located in ‘Vancouver’.

Select DATEPART(yyyy, A.Joining_date) AS Year,
DATEPART(mm, A.Joining_date) AS Month from Employee_History A join Employees B on A.Emp_ID=B.Emp_ID

Join Departments C on A. Dep_Id=C. Dep_Id join Locations D

D. Location_Id=C. Location_Id where count(A.Emp_ID)<Min(A.Emp_Id) and D. Street_address=’ Vancouver’

4.    With the help of schema find the year which witnessed maximum number of employee intake.

select top 1 DATEPART(yyyy, A.Joining_date)

from Employee_History A
join Employees B
on A.Emp_ID=B.Emp_ID

group by A.Emp_Id

order by count(*) desc;

5.    For the year in query-4, find how many joined in each month in that specific year. are underlined

select top 1 Count(A.Emp_ID)

from Employee_History A
join Employees B
on A.Emp_ID=B.Emp_ID

group by A.Emp_Id
order by count(*) desc;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.