It creates an SQL statement to find the project that has the most employees from the same department. If more than one project meets the condition, they need to be all displayed in the output. The output should display proj_name, dept_name, and total employees that satisfy the condition. It creates a function called DeptDate to return a table containing dept_no, dept_name, emp_no, emp_fname, and job_begin. This function has a date value as input parameter and it finds employees who start a job on a date later than the input date and puts their data into the return table including dept_no and dept_name of their department. In this same file, you must also include three statements with an input of May 05 of 2016, 2015, and 2014, respectively, to test the function you create.

Question

Computers & Technology

Timothy Cisneros

2 December, 20:28

It creates an SQL statement to find the project that has the most employees from the same department. If more than one project meets the condition, they need to be all displayed in the output. The output should display proj_name, dept_name, and total employees that satisfy the condition. It creates a function called DeptDate to return a table containing dept_no, dept_name, emp_no, emp_fname, and job_begin. This function has a date value as input parameter and it finds employees who start a job on a date later than the input date and puts their data into the return table including dept_no and dept_name of their department. In this same file, you must also include three statements with an input of May 05 of 2016, 2015, and 2014, respectively, to test the function you create.

+4

Answers (1)

Know the Answer?

Kinsley Duffy · Answer 1

Check the explanation

Explanation:

As per requirement submitted above kindly find below solution.

This demonstration is using SQL Server.

Table Names used as

department

project

works_on

SQL query:

select proj_name, dept_name, count (emp_no) as 'most employees ' from

project, department, works_on

where

project. proj_no=works_on. proj_no and

department. dept_no=works_on. dept_no

group by proj_name, dept_name

having count (emp_no) = (

select max (empCount) from (

select proj_name, dept_name, count (emp_no) empCount from project, department, works_on where project. proj_no=works_on. proj_no and department. dept_no=works_on. dept_no

group by proj_name, dept_name) as emp);