Question & Answer: Consider a DB schema consisting of the following relation schemes: Regions (Region ID, Region_Name)…..

Question 1 100 marks] 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) Emplovee History (Emp ID, Joining date, last date, Job ID, Dep ID) * Keys are underlined Now, express the following queries in SQL: 1. 2. 3. 4. 5. Find the count of departments, region name(s) and cities for the department(s) that have more than 500 employees. 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. Find month and year which witnessed lowest count of employees joining a department located in Vancouver. With the help of schema find the year which witnessed maximum number of employee intake For the year in query-4, find how many joined in each month in that specific year

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 are underlined 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.

Expert Answer

 

Here is the solution as per the given criteria, please have a look-

======================================================

Part 1)

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

=================================================

Part 2)

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

=================================================

Part 3)

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’

==========================================================

Part 4)

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;

====================================================

Part 5)

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;

Still stressed from student homework?
Get quality assistance from academic writers!