Subject: Oracle DBA Problem A SQL SELECT Given a table definition: Employees( em
ID: 3851317 • Letter: S
Question
Subject: Oracle DBA
Problem A SQL SELECT
Given a table definition:
Employees( employee_id, first_name, last_name, salary,
manager_id, hire_date, commission_pct, department_id)
write the following SQL statements.
A.1 Write a query that displays the first eight characters of the employees’ last
names and indicates the amounts of their salaries with asterisks. Each asterisk
signifies a thousand dollars. Sort the data in descending order of salary.
A.2 Write a query to display the manager number and the salary of the lowestpaid
employee for that manager. Exclude anyone whose manager is not known.
Exclude any groups where the minimum salary is $6,000 or less. Sort the output
in descending order of salary.
A.3 Write a query to display employees’ last names and employee number along
with their managers’ last names and manager number.
A.4 The HR department wants to determine the names of all employees who
were hired after Davies. Create a query to display the name and hire date of any
employee hired after employee Davies.
A.5 Display the employee number, last name, and salary of all employees who
earn more than the average salary and who work in a department with any
employee whose last name contains a “w”.
Explanation / Answer
A.1 Write a query that displays the first eight characters of the employees’ last
names and indicates the amounts of their salaries with asterisks. Each asterisk
signifies a thousand dollars. Sort the data in descending order of salary.
SELECT substr(last_name,1,8) first_eight_characters,rpad('*',floor(salary/1000),'*') salary
FROM Employees
ORDER BY salary desc;
-- floor functions returns the number of 1000s and rpad functions places that number of asterisks to the right side of the number.
A.2 Write a query to display the manager number and the salary of the lowestpaid
employee for that manager. Exclude anyone whose manager is not known.
Exclude any groups where the minimum salary is $6,000 or less. Sort the output
in descending order of salary.
SELECT manager_id, MIN(salary)
FROM Employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>6000
ORDER BY MIN(salary) DESC;
A.3 Write a query to display employees’ last names and employee number along
with their managers’ last names and manager number.
select e.last_name Emp_last_name,e.employee_id eid,m.manager_id mid,m.last_name mgr_last_name
from Employees e,Employees m
where e.employee_id=m.manager_id
A.4 The HR department wants to determine the names of all employees who
were hired after Davies. Create a query to display the name and hire date of any
employee hired after employee Davies.
SELECT e.last_name, e.hire_date
FROM employees e, employees davies
WHERE davies.last_name = 'Davies'
AND davies.hire_date < e.hire_date
A.5 Display the employee number, last name, and salary of all employees who
earn more than the average salary and who work in a department with any
employee whose last name contains a “w”.
SELECT employee_id, last_name
FROM employees
WHERE department_id IN
(SELECT department_id
FROM employees
WHERE last_name like '%w%');