Scribd
Upload
17 views4 pages

Employee Hiring and Salary Analysis

The document contains 5 questions regarding SQL queries on employee data. Question 1 selects employee data including last name, job code, hire date, and salary for non-VP employees hired after June 1995 earning outside $8,000 to $14,000 range, sorted by job and salary. Question 2 selects employee last name, job, and salary less than the average for their city/department if not in marketing, sorted by salary then name. Question 3 selects job positions with more than 2 people in IT and marketing departments. Question 4 selects employees whose job does not exist in job history, sorted by name. Question 5 displays sample output format for name, salary, and hire date.

Uploaded by

Bùi Trọng Thành
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
17 views4 pages

Employee Hiring and Salary Analysis

The document contains 5 questions regarding SQL queries on employee data. Question 1 selects employee data including last name, job code, hire date, and salary for non-VP employees hired after June 1995 earning outside $8,000 to $14,000 range, sorted by job and salary. Question 2 selects employee last name, job, and salary less than the average for their city/department if not in marketing, sorted by salary then name. Question 3 selects job positions with more than 2 people in IT and marketing departments. Question 4 selects employees whose job does not exist in job history, sorted by name. Question 5 displays sample output format for name, salary, and hire date.

Uploaded by

Bùi Trọng Thành
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

-- Question 1:

/*Show last name, job code, hire date and salary for non VP’s who have been hired AFTER June 1995

and who are earning outside the range from $8,000 to $14,000.

Hire date should be in the format like

SATURDAY , Twenty-Seventh of January , 2000

Sort the output according to the job firstly and then by top salaries.

Here are shown headings and first two rows:

Lname Job Title Start Date Pay

---------- --------------- -------------------------------------------------------- -----------

Lorentz IT_PROG SUNDAY, Seventh of February, 1999 4200

Fay MK_REP SUNDAY, Seventeenth of August, 1997 6000*/

SELECT

last_name AS "Lname",

job_id AS "Job Title",

to_char(hire_date, 'DAY", "fmDdspth" of "Month", "YYYY') AS "Start Date",

salary AS "Pay"

FROM employee

WHERE (salary < 8000 OR salary > 14000)

AND (hire_date >= '95-07-01')

ORDER BY 2, 4 DESC;

-- Question 2:

/*Show employee’s last name, job and salary if they earn less than the average amount earned in the
city of OXFORD

and if their job is NOT related to Marketing (it does not start on MK)

Sort the output according to the top salaries first and then by the last name.

Use just a Subquery method (multiple times). Do not use a Join method !

Here are shown headings and first two rows:

This study source was downloaded by 100000844792446 from [Link] on 10-18-2022 03:38:37 GMT -05:00

[Link]
LAST_NAME JOB_ID SALARY

---------------------- ------------- ------------

Hunold IT_PROG 9000

Taylor SA_REP 8600*/

SELECT

last_name,

job_id,

salary

FROM employee

WHERE salary <

(SELECT AVG(salary)

FROM employee

WHERE department_id IN

(SELECT department_id

FROM department

WHERE location_id IN

(SELECT location_id

FROM location

WHERE city = 'Oxford')))

AND job_id NOT LIKE 'MK%'

ORDER BY 3 DESC, 1;

-- Question 3;

/*Which jobs can be found in the company and how many people do these jobs?

This report should include only departments for IT and MARKETING and will show only those jobs that
involve more than two persons.

Two components displayed should have headings Position and # of People, while the output will show
firstly the job positions with most people.

This study source was downloaded by 100000844792446 from [Link] on 10-18-2022 03:38:37 GMT -05:00

[Link]
Here are shown headings and first row:

Position # of People

------------- ------------------

IT_PROG 3 */

SELECT

job_id AS "Postion",

COUNT(*) AS "# of People"

FROM employee

GROUP BY job_id

HAVING (job_id LIKE 'IT%'

OR job_id LIKE 'MK%'

AND COUNT(*) >= 2)

ORDER BY 2 DESC;

-- Question 4:

/*Display last name and job id of employees who are holding jobs that do not exist in

the Job_History of all employees. Use a Subquery and Set operator.

Sort the output by last name alphabetically.

Here are shown headings and first two rows:

LAST_NAME JOB_ID

------------------ ---------------

De Haan AD_VP

Hartstein MK_MAN*/

SELECT

last_name,

job_id

FROM employee

This study source was downloaded by 100000844792446 from [Link] on 10-18-2022 03:38:37 GMT -05:00

[Link]
WHERE job_id IN

(SELECT job_id

FROM employee

MINUS

SELECT job_id

FROM job_history)

ORDER BY last_name;

-- Question 5:

/*

SURNAME SALARY Hire Date

Jones 2975 On the 03rd of APR in the year 1981

Lopez 2200 On the 08th of SEP in the year 2001

*/

This study source was downloaded by 100000844792446 from [Link] on 10-18-2022 03:38:37 GMT -05:00

[Link]
Powered by TCPDF ([Link])

Common questions

Powered by AI

You employ a set operation (MINUS) to differentiate job roles in the current employees list from those in job history: SELECT last_name, job_id FROM employee WHERE job_id IN (SELECT job_id FROM employee MINUS SELECT job_id FROM job_history) ORDER BY last_name;

To list employees with unique job IDs, use: SELECT last_name, job_id FROM employee WHERE job_id IN (SELECT job_id FROM employee MINUS SELECT job_id FROM job_history) ORDER BY last_name;

Utilize this query: SELECT last_name, job_id, salary FROM employee WHERE salary < (SELECT AVG(salary) FROM employee WHERE department_id IN (SELECT department_id FROM department WHERE location_id IN (SELECT location_id FROM location WHERE city = 'Oxford'))) AND job_id NOT LIKE 'MK%' ORDER BY 3 DESC, 1;

Applying multiple subqueries without using JOINs addresses the problem. For instance: the conditions apply subquery to filter department_id by location and another for filtering the salary comparing with the average such as: SELECT last_name, job_id, salary FROM employee WHERE salary < (SELECT AVG(salary) FROM employee WHERE department_id IN (SELECT department_id FROM department WHERE location_id IN (SELECT location_id WHERE city = 'Oxford'))) AND job_id NOT LIKE 'MK%';

Apply filtering in the WHERE clause for both conditions: salary being outside the range (<8000 OR >14000) and hire_date being greater than or equal to the date (e.g., '1995-07-01') to restrict employees hired only after June 1995.

To achieve this, use the query: SELECT last_name AS "Lname", job_id AS "Job Title", to_char(hire_date, 'DAY', 'fmDdspth' of 'Month', 'YYYY') AS "Start Date", salary AS "Pay" FROM employee WHERE (salary < 8000 OR salary > 14000) AND (hire_date >= '95-07-01') ORDER BY 2, 4 DESC;

Use GROUP BY to group jobs and HAVING to set a condition on employee count, shown by: SELECT job_id AS "Position", COUNT(*) AS "# of People" FROM employee GROUP BY job_id HAVING (job_id LIKE 'IT%' OR job_id LIKE 'MK%' AND COUNT(*) > 2) ORDER BY 2 DESC;

The TO_CHAR function is well-suited for this purpose, specifically using a format string like 'DAY', 'fmDdspth' of 'Month', 'YYYY' to get an easily understandable format.

Use the TO_CHAR function in SQL with the format specified as: to_char(hire_date, 'DAY', 'fmDdspth' of 'Month', 'YYYY'). This formats the date into verbose English phrases.

The query to find job positions in IT and Marketing departments with more than two employees is: SELECT job_id AS "Postion", COUNT(*) AS "# of People" FROM employee GROUP BY job_id HAVING (job_id LIKE 'IT%' OR job_id LIKE 'MK%' AND COUNT(*) >= 2) ORDER BY 2 DESC;

You might also like