oracle习题

如果错误或者更加优秀的解决方法，欢迎各位大大指教，共同进步，共创和谐社会，哈哈。

emmmmm，本人有点小懒可能不会及时更新或回复，一切随缘吧。

/*
1.查询雇佣时间在1997年之后的员工信息。
*/
select * from employees where to_char(hire_date, ‘yyyy’) > 1997;

/*
2.查询有提成的员工信息（last name, job, salary, and commission），并按工资降序排列
*/
select last_name,job_id,salary,commission_pct
from employees
where commission_pct is not null
order by salary desc;
/*
3.Show the employees that have no commission with a 10% raise in their salary (round off thesalaries).
*/
select ‘the salary of ’ || last_name || ’ after a 10% raise is ’ ||
salary * 1.1 as new_salary
from employees
where commission_pct is null;

/*
4.Show the last names of all employees together with the number of years and the number of completed months that they have been employed.
*/

select last_name,
trunc(months_between(sysdate, hire_date) / 12, 0) as years,
mod((to_char(hire_date, ‘mm’) - to_char(sysdate, ‘mm’) + 12), 12) as months
from employees;

/*
5.Show those employees that have a name starting with J, K, L, or M.
*/
select last_name
from empLoyees
where substr(last_name, 1, 1) IN (‘J’, ‘K’, ‘L’, ‘M’);

/*
6.Show all employees, and indicate with “Yes” or “No” whether they receive a commission.
*/
select last_name,salary,nvl2(commission_pct,’Yes’,’No’)
from employees;

/*
7.Show the department names, locations, names, job titles, and salaries of employees who work in location 1800.
*/

select department_name,location_id,last_name,jobs.job_id,salary
from departments dept,jobs,employees
where dept.department_id=employees.department_id
and employees.job_id = jobs.job_id
and location_id = 1800;
/*
8.How many employees have a name that ends with an n? Create two possible solutions.
*/
select count(*) from employees where substr(last_name, -1) = ‘n’

—
select count(*) from employees where last_name like ‘%n’;

/*
9.Show the names and locations for all departments, and the number of employees working in each department. Make sure that departments without employees are included as well.
*/
select d.department_id, department_name, location_id, nvl(counts, 0)
from departments d,
(select count(employee_id) as counts, department_id
from employees
group by department_id) e
where e.department_id(+) = d.department_id;

/*
10.Which jobs are found in departments 10 and 20?
*/
select distinct job_id from employees where department_id between 10 and 20;

/*
11.Which jobs are found in the Administration and Executive departments, and how many employees do these jobs? Show the job with the highest frequency first.
*/
select job_id, count(*) as fruquency
from (select *
from employees
where department_id in
(select department_id
from d