Scribd
Upload
5 views2 pages

Task (1)

The document outlines three tasks related to SQL: retrieving employees hired in the last 30 days using date functions, creating a stored procedure to get employee hours worked based on employee ID, and creating another stored procedure to count employees by department for departments with more than five employees. Each task includes specific SQL instructions and example queries. The document serves as a guide for implementing these SQL functionalities.

Uploaded by

almorshdymohamed161
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

Task (1)

The document outlines three tasks related to SQL: retrieving employees hired in the last 30 days using date functions, creating a stored procedure to get employee hours worked based on employee ID, and creating another stored procedure to count employees by department for departments with more than five employees. Each task includes specific SQL instructions and example queries. The document serves as a guide for implementing these SQL functionalities.

Uploaded by

almorshdymohamed161
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
You are on page 1/ 2

Task 1: Using Date and Time Functions

Question:
Write a SQL query to retrieve all employees who were hired within the last 30 days from the
current date.

Instructions:

1. Use the SELECT statement to choose all relevant columns from the employees table.
2. Utilize a date function such as CURRENT_DATE or GETDATE() depending on your SQL
dialect.
3. Apply date arithmetic to filter rows where the hire date is within the last 30 days.
4. Use the WHERE clause to compare hire dates.

SELECT *

FROM employees

WHERE hire_date >= CURRENT_DATE - INTERVAL '30 days';

Task 2: Creating and Using a Stored Procedure

Question:
Create a stored procedure named sp_get_employee_hours that retrieves the first name, last
name, and total hours worked on projects for a given employee ID.

Instructions:

1. Define the stored procedure using the CREATE PROCEDURE statement.


2. Include a parameter for the employee ID (emp_id).
3. Use a SELECT statement to fetch the required columns (fname, lname, total_hours)
from the employees and works_on tables.
4. Join the tables on the appropriate keys (e.g., SSN = ESSN).
5. Sum the hours worked using the SUM() function and group by employee details.

CREATE PROCEDURE sp_get_employee_hours (

IN emp_id INT

)
BEGIN

SELECT e.fname, e.lname, SUM(w.hours) AS total_hours

FROM employees e

JOIN works_on w ON e.SSN = w.ESSN

WHERE e.SSN = emp_id

GROUP BY e.fname, e.lname;

END;

Task 3: Creating a Stored Procedure for Employee Count by Department

Question:
Create a stored procedure named sp_department_employee_count that retrieves the
department ID, department name, and the number of employees in each department, but only for
departments with more than 5 employees.

Instructions:

1. Define the stored procedure using the CREATE PROCEDURE statement.


2. Use a SELECT statement to fetch the department ID, department name, and count of
employees.
3. Use a JOIN to connect the departments and employees tables on the department ID.
4. Use the GROUP BY clause to group results by department.
5. Apply the HAVING clause to filter departments with more than 5 employees.

You might also like