Oracle Sql Introduction to Databases I

--TASK #1: Find data for all of the IT Programmers who were hired on or before the year 1996.

SELECT (first_name||' '||last_name) AS Name, job_id "Job", hire_date "Hire Date"

FROM employees

WHERE job_id = 'IT_PROG'

AND hire_date &salary;

--TASK #7: Show all employees who were hired on the 17th day of any month.

SELECT last_name, hire_date

FROM employees

WHERE TO_CHAR (hire_date, 'DD')=17

ORDER BY first_name;

--TASK #8: Find the job titles in departments 90 and 110. Create a report to display the job IDs for those departments.

SELECT DISTINCT job_id

FROM employees

WHERE department_id IN (90, 110);

--TASK #9: Find the number of employees who have a last name that ends with the letter “n.”

SELECT COUNT (*) AS COUNT

FROM employees

WHERE last_name LIKE '%n';

--TASK #10: Create a report that shows the department_id, department name, location and number of employees for each department.

SELECT d.department_id, d.department_name,

d.location_id, COUNT(e.employee_id) AS COUNT

FROM employees e RIGHT OUTER JOIN departments d

ON (e.department_id= d.department_id)

GROUP BY d.department_id, d.department_name, d.location_id;

--TASK #11: Create a report that displays the jobs that are found in the IT and Executive departments. Also display the number of employees for these jobs.

-- Show the job with the highest number of employees first.

SELECT e.job_id, COUNT(e.job_id) FREQUENCY

FROM employees e JOIN departments d

ON (e.department_id=d.department_id)

WHERE d.department_name IN ('IT', 'Executive')

GROUP BY e.job_id

ORDER BY frequency DESC;

--TASK #12: Show all employees who have managers with a salary less than $11,000.

SELECT e.last_name, m.last_name manager, TO_CHAR(m.salary,'$99,999') AS SALARY, j.grade_level

FROM employees e JOIN employees m

ON (e.manager_id=m.employee_id)

JOIN job_grades j

ON m.salary BETWEEN j.lowest_sal AND j.highest_sal

AND m.salary