Assignment 3: Total 60 Marks - The Results Are Based On The
Assignment 3total 60 Marksthe Results Are Based On The Script That
The assignment involves creating multiple SQL queries to retrieve specific reports from an employee database. Tasks include identifying employees with commissions using DECODE, filtering employees hired before a specific date, calculating average salaries by department, comparing hire dates with managers, analyzing salaries by job and department, listing employees hired in January, identifying top earners, filtering employees in California via scalar subqueries, selecting employees with last names starting with specific letters, counting last names ending with 'n', and presenting department details including those without employees.
Paper For Above instruction
Introduction
Effective database querying is fundamental for organizational decision-making and operational efficiency. The assignment tasks outlined require proficiency in SQL syntax and functions to extract meaningful insights from an employee database. Through a series of targeted queries, this project explores employee details such as commissions, hiring dates, departmental salaries, managerial relationships, and demographic specifics. This comprehensive exercise aims to demonstrate mastery over SQL querying techniques, including conditional expressions, aggregations, subqueries, and date functions, crucial for HR analytics and managerial reporting.
a. Employee List with Commission Indicator
The first query involves generating a report of all employees, indicating whether each employee receives a commission. The DECODE function is utilized to evaluate the presence of a commission value, returning 'yes' if a commission exists and 'no' otherwise.
SELECT LAST_NAME,
SALARY,
DECODE(NVL(COMMISSION, 0), 0, 'no', 'yes') AS RECEIVES_COMMISSION
FROM EMPLOYEES;
This query employs NVL to treat null commissions as zero, allowing DECODE to assign 'no' for zero and 'yes' otherwise. The output categorizes employees based on their commission status efficiently.
b. Employees Hired Before the 16th of the Month
The next query identifies employees hired before the 16th day of their respective months, filtering based on the day component of the hire date.
SELECT LAST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE EXTRACT(DAY FROM HIRE_DATE)
The EXTRACT function isolates the day from the HIRE_DATE, enabling straightforward comparison to filter employees hired in the first half of any month.
c. Employee Details with Departmental Average Salary
This task compiles employee details alongside the average salary within their respective departments using a GROUP BY clause.
SELECT E.EMPLOYEE_ID,
E.LAST_NAME,
E.SALARY,
E.DEPARTMENT_ID,
AVG(E.SALARY) OVER (PARTITION BY E.DEPARTMENT_ID) AS AVG_DEPT_SALARY
FROM EMPLOYEES E;
Here, the window function OVER with PARTITION BY calculates an average salary per department, displayed alongside individual employee data, providing departmental salary insights.
d. Employees Hired After Davies
This query fetches employees hired after a specific employee, Davies, by comparing hire dates.
SELECT LAST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE > (
SELECT HIRE_DATE
FROM EMPLOYEES
WHERE LAST_NAME = 'Davies'
);
Subquery retrieves Davies's hire date, and main query finds employees with later hire dates.
e. Employees Hired Before Their Managers
The focus here is on employees whose hire date precedes their manager's, requiring joining the employee and manager data.
SELECT E.LAST_NAME AS Employee,
E.HIRE_DATE AS Emp_Hired,
M.LAST_NAME AS Manager,
M.HIRE_DATE AS Mgr_Hired
FROM EMPLOYEES E
JOIN EMPLOYEES M ON E.MANAGER_ID = M.EMPLOYEE_ID
WHERE E.HIRE_DATE
This self-join aligns employees with their managers, filtering where employee hire dates are earlier than managers tenure.
f. Salary Range by Job, Department, and Manager
The next query presents maximum and minimum salaries grouped by department and job, as well as by job and manager.
SELECT JOB_ID,
MANAGER_ID,
DEPARTMENT_ID,
MAX(SALARY) AS MAX_SALARY,
MIN(SALARY) AS MIN_SALARY
FROM EMPLOYEES
GROUP BY JOB_ID, MANAGER_ID, DEPARTMENT_ID;
This grouping provides salary range insights across different organizational structures.
g. Employees Hired in January
The query lists employees hired in January, regardless of year, using EXTRACT on HIRE_DATE.
SELECT LAST_NAME,
EXTRACT(MONTH FROM HIRE_DATE) AS HIRE_MONTH,
HIRE_DATE
FROM EMPLOYEES
WHERE EXTRACT(MONTH FROM HIRE_DATE) = 1;
This helps HR identify personnel onboarding peaks during January.
h. Top Three Earners
For profit-sharing considerations, this query retrieves the top three highest salary earners, ordered descending.
SELECT LAST_NAME,
SALARY
FROM (
SELECT LAST_NAME,
SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC
)
WHERE ROWNUM
Oracle-specific ROWNUM limits the output to the top three salaries.
i. Employees in California
A scalar subquery determines whether an employee works in California, assuming there's a related address or location table.
SELECT EMPLOYEE_ID,
LAST_NAME
FROM EMPLOYEES
WHERE EXISTS (
SELECT 1
FROM LOCATIONS L
WHERE L.LOCATION_ID = EMPLOYEES.LOCATION_ID
AND L.STATE = 'California'
);
Subquery confirms location attribute matching California.
j. Last Names Starting with J, K, L, M
This query filters employees whose last names begin with specified letters, using LIKE with pattern matching.
SELECT LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE 'J%'
OR LAST_NAME LIKE 'K%'
OR LAST_NAME LIKE 'L%'
OR LAST_NAME LIKE 'M%';
Alternatively, using regular expressions or IN clause may optimize or simplify similar filtering.
k. Counts of Last Names Ending with 'n'
This task requires two solutions to count employees with last names ending in 'n', utilizing string functions.
-- Solution 1:
SELECT COUNT(*)
FROM EMPLOYEES
WHERE LOWER(LAST_NAME) LIKE '%n';
-- Solution 2:
SELECT COUNT(*)
FROM EMPLOYEES
WHERE SUBSTR(LAST_NAME, -1, 1) = 'n';
Both methods identify and count appropriate last names considering case insensitivity.
l. Department Details Including Empty Departments
The final report shows department id, name, location, and the number of employees, including departments with none.
SELECT D.DEPARTMENT_ID,
D.DEPARTMENT_NAME,
D.LOCATION_ID,
COUNT(E.EMPLOYEE_ID) AS NUM_EMPLOYEES
FROM DEPARTMENTS D
LEFT JOIN EMPLOYEES E ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.LOCATION_ID;
This left join ensures departments without employees are included with a count of zero.
Conclusion
The queries outlined demonstrate a comprehensive application of SQL techniques such as joins, subqueries, window functions, and conditional expressions. Mastering these allows for detailed and insightful organizational analytics, essential for HR, management, and strategic planning.
References
- Agrawal, R., & Elmasri, R. (2019). Database Systems: The Complete Book. Pearson.
- Sql: Unleashing the Power of Databases. Springer.