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

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

The queries are as follows:

1)

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

SELECT Count(Dep_Id), City, Region_Name FROM Departments A
JOIN Employees B on A. Dep_Id=B. Dep_Id
JOIN Locations on A. Location_Id=B. Location_Id
JOIN Countries on A. Country_Id=B. Country_Id
JOIN Regions on A. Region_ID=B. 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 on A. Manager_Id =B. Manager_Id
JOIN Employee_History on A. Dep_Id=B. Dep_Id
JOIN Jobs on A. Job_ID=B. Job_ID
WHERE last_date>getdate() AND 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

JOIN Employees on A.Emp_ID=B.Emp_ID

JOIN Departments on A. Dep_Id=B. Dep_Id
JOIN Locations on A. Location_Id=B. Location_Id
WHERE count(A.Emp_ID)<Min(B.Emp_Id) AND Street_address=’ Vancouver’

4)

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

SELECT top 1 DATEPART(yyyy, 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.

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.