1.

Please Write A Query To Get All The Departments, And The Count Of Employee In Those Department, Which Have More Than 50 Employees And Are Based Out Of State New York. Following Are The Table Structures:

Answer»

Department table: Department_ID, Department_Name, STATE (Note: Department ID is Unique here and more than one department can belong to one State) Employee Table: Employee_ID, Employee_Name, Department_ID (Note: Employee ID is unique here and more than one Employee can belong to one department, but one employee does not belong to more than one department)

Following should be the query

SELECT DEPT.department_id,
Max(dept.department_name),
COUNT(emp.employee_id)
FROM department dept JOIN employee emp
ON dept.department_id = emp.department_id
WHERE dept.state = “new YORK”
GROUP BY dept.department_id
HAVING Count(emp.employee_id) > 50

Department table: Department_ID, Department_Name, State (Note: Department ID is Unique here and more than one department can belong to one State) Employee Table: Employee_ID, Employee_Name, Department_ID (Note: Employee ID is unique here and more than one Employee can belong to one department, but one employee does not belong to more than one department)

Following should be the query

SELECT dept.department_id,
Max(dept.department_name),
Count(emp.employee_id)
FROM department dept JOIN employee emp
ON dept.department_id = emp.department_id
WHERE dept.state = “new york”
GROUP BY dept.department_id
HAVING Count(emp.employee_id) > 50


Discussion

No Comment Found

Related InterviewSolutions