Scribd
Upload
5 views2 pages

SQL Functions for Employee and Project Data

Uploaded by

vmusale908
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views2 pages

SQL Functions for Employee and Project Data

Uploaded by

vmusale908
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

a) Function to accept project name as input and return the number of employees working on the

project:

```sql

CREATE OR REPLACE FUNCTION get_employee_count_on_project(project_name_in VARCHAR)

RETURNS INTEGER AS $$

DECLARE

employee_count INTEGER;

BEGIN

SELECT COUNT(*) INTO employee_count

FROM project_employee pe

JOIN project p ON pe.p_no = p.p_no

WHERE p.p_name = project_name_in;

RETURN employee_count;

END;

$$ LANGUAGE plpgsql;

```

b) Function to find the number of employees whose date of joining is before '07/08/2018':

```sql

CREATE OR REPLACE FUNCTION get_employee_count_join_before(join_date_in DATE)

RETURNS INTEGER AS $$

DECLARE

employee_count INTEGER;

BEGIN

SELECT COUNT(*) INTO employee_count

FROM employee

WHERE join_date < join_date_in;

RETURN employee_count;

END;

$$ LANGUAGE plpgsql;

```

c) Function to find details of a particular project by accepting the project name as an input parameter:

```sql

CREATE OR REPLACE FUNCTION get_project_details(project_name_in VARCHAR)

RETURNS TABLE (
p_no INT,

p_name VARCHAR(30),

p_type VARCHAR(20),

duration INT

) AS $$

BEGIN

RETURN QUERY

SELECT p_no, p_name, p_type, duration

FROM project

WHERE p_name = project_name_in;

END;

$$ LANGUAGE plpgsql;

You might also like