- QUESTION 5:

--Show a list of all employees, their department and their jobs,

--Soluton 5--

SELECT
id,
name,
dept,
job
FROM
STAFF
WHERE
dept =
(
SELECT
dept
FROM
STAFF
WHERE
name = 'Graham'
)
MINUS
SELECT
id,
name,
dept,
job
FROM
STAFF
WHERE
name = 'Graham'
ORDER BY
name ASC;

-- QUESTION 6:
-- Show the list of employee names, job and variable pay, from
the employeetable, who have the lowest and highest variable pay
(includes commission and bonus) by job category.
--The name should be formatted: lastname, firstname with the
first character capitalized and all other characters in lower
case.
--(ie: King, Les). The title of this column should be .

This study source was downloaded by 100000853163764 from [Link] on 02-20-2024 09:40:49 GMT -06:00

[Link]
 --The variable pay column should be called .Order the results by
highest variable pay to lowest variable pay--

SELECT
(initcap(lastname) || ', ' || initcap( firstname)) AS "name", job
,
salary + nvl(comm, 0) + bonus AS "VARIABLE PAY"

FROM
employee
WHERE
salary + nvl(comm, 0) + bonus IN
(
SELECT
MAX(salary + nvl(comm, 0) + bonus)
FROM
employee
GROUP by job
)
UNION
SELECT
(initcap(lastname) || ', ' || initcap( firstname)) AS "name", job
,
salary + nvl(comm, 0) + bonus AS "VARIABLE PAY"
FROM
employee
WHERE
salary + nvl(comm, 0) + bonus IN
(
SELECT
MIN(salary + nvl(comm, 0) + bonus)
FROM
employee
GROUP by job
)

ORDER BY

"VARIABLE PAY" DESC ;

--
-- QUESTION 7:
-- Using the staff table, show all employees who have an 'il' in

This study source was downloaded by 100000853163764 from [Link] on 02-20-2024 09:40:49 GMT -06:00

[Link]
 their name - or - their name ends with an 's'. Make sure your
query is case insensitive.
-- You just need to display the name of the employee in your
output. Order them alphabetically.
--
--Soluton 7--

SELECT
name
FROM
STAFF
WHERE
NAME LIKE '%il%'
OR Name LIKE '%s'
ORDER BY
name ASC;

-- QUESTION 8:
--Using the staff table, display the employee name, job, salary
and commissionfor all employeeswith
--a salary less than the salary of all people with a managerjob
--or full compensation less than the full compensation of all the
people with a salesjob.
--Full compensation is the sum of both salary and
[Link] people with a sales job from the output.
--Soluton 8--

SELECT
NAME,
job,
salary,
comm
FROM
staff
WHERE
SALARY < (
SELECT
MIN(SALARY)
FROM
STAFF
WHERE
job = 'Mgr') MINUS
SELECT

This study source was downloaded by 100000853163764 from [Link] on 02-20-2024 09:40:49 GMT -06:00

[Link]
 NAME,
job,
salary,
comm
FROM
staff
WHERE
job = 'Sales' ;

-- QUESTION 9:
-- From the employee table, calculate the average compensation
for each job category where the employee has 16 or more years of
education.
-- Display the job and average compensation in the result set.
-- Exclude people who are clerks
-- Make sure to include salary, commission and bonus when looking
at employee compensation
-- Order the output by the average salary in ascending order

--SOLUTION 9---
SELECT
job,
ROUND(AVG(salary + comm + bonus), 2) AS "AVERAGE COMPENSATION"
FROM
EMPLOYEE
WHERE
edlevel >= '16'
AND job NOT IN UPPER('clerk')
GROUP BY
job
ORDER BY
AVG(salary + comm + bonus) ASC;
--
-- QUESTION 10:
--Show the first name, last name, hire date, birth date,
--education level andyears of service for employees who are both
in the staff table and the employee tableAn individual
--is the same individual if a case insensitive comparison of last
name matches.
--
--SOLUTION 10---

SELECT
firstname,
lastname,
hiredate,

This study source was downloaded by 100000853163764 from [Link] on 02-20-2024 09:40:49 GMT -06:00

[Link]
 birthdate,
EXTRACT(YEAR
FROM
(
sysdate - hiredate
)
YEAR TO MONTH) AS "YEAR",
edlevel
FROM
employee
INNER JOIN
staff
ON [Link] = UPPER([Link]) ;
--

This study source was downloaded by 100000853163764 from [Link] on 02-20-2024 09:40:49 GMT -06:00

[Link]
Powered by TCPDF ([Link])