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

Explanation / 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;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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