oracle之40道经典的sql练习

前言

做题首先要有schema，又叫用户，或者说是数据库，也就是表的集合；
用sys账号登录，执行alter user HR identified by oracle account unlock;
再用这个换过密码的HR账号登录，我们就可以看到自带的用户表等等等的表了。
题目都是英文，楼主机翻成中文，顺便改一些机翻的失误。
当然楼主是初学oracle，有错误也请谅解，会不断改正答案。

题目开始

1.Show all data of the clerks who have been hired after the year 1997.

-- 1. 请列出1997年以后受雇的职员的所有数据。
select * from EMPLOYEES t where t.hire_date > to_date('1997', 'YYYY');

2.Show the last name, job, salary, and commission of those employees who earn commission.Sort the data by the salary in descending order.

-- 2. 显示那些赚取佣金的员工的姓氏、工作、薪水和佣金。将数据按薪水降序排序。
select t.last_name, t.job_id, t.salary, t.commission_pct
  from employees t
 where t.commission_pct is not null
 order by t.commission_pct desc;

3.Show the employees that have no commission with a 10% raise in their salary (round off the salaries).

-- 3. 展示那些没有佣金但工资增加10%的员工(把工资四舍五入)。
select 'The salary of ' || t.last_name || ' after a 10% raise is ' ||
       round(t.salary * 1.1) as "new salary"
  from employees t
 where t.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.

-- 4. 显示所有员工的姓氏和他们已经工作了的年份和月份
select t.last_name,
       trunc(months_between(sysdate, t.hire_date) / 12) as "years",
       mod(trunc(months_between(sysdate, t.hire_date)), 12) as "months"
  from employees t;

5.Show those employees that have a name starting with J, K, L, or M.

-- 5. 显示名字以J、K、L或M开头的员工。
select t.last_name
  from employees t
 where t.last_name like 'J%'
    or t.last_name like 'K%'
    or t.last_name like 'L%'
    or t.last_name like 'M%';

6.Show all employees, and indicate with “Yes” or “No” whether they receive a commission.

-- 6. 展示所有的员工，用no或者yes标识是否收到了佣金
select t.last_name, nvl2(t.commission_pct, 'Yes', 'No') as "COM"
  from employees t;

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

-- 7. 显示部门名称、地点、姓名、职务头衔和工作人员的工资，在location_id=1800的条件下
select dep.department_name,
       dep.location_id,
       emp.last_name,
       emp.job_id,
       emp.salary
  from employees emp, departments dep
 where emp.department_id(+) = dep.department_id
   and dep.location_id = 1800;

8.How many employees have a name that ends with an n? Create two possible solutions.

-- 8. 有多少员工的名字以n结尾?创建两个可能的解决方案。
-- 8.1
select count(1) from employees t where t.last_name like '%n';
-- 8.2
select (select count(1) from employees t) -
       (select count(1) from employees t where t.last_name not like '%n') as "COUNT"
  from dual;

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.

-- 9. 显示所有部门的名称和位置，以及在其中工作的每个部门员工人数。确保没有员工的部门也包括在内。
select dep.department_id,
       dep.department_name,
       dep.location_id,
       count(emp.employee_id)
  from employees emp, departments dep
 where emp.department_id(+) = dep.department_id
 group by dep.department_id, dep.department_name, dep.location_id;

10.Which jobs are found in departments 10 and 20?

-- 10. depatment_id =10或者20时，job_id是多少
select e.job_id from employees e where e.department_id in (10, 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.

-- 11. Administration和Executive部门有哪些工作，有多少员工从事这些工作?首先显示频率最高的工作。
select e.job_id, count(1) as frequency
  from employees e
 where e.department_id in
       (select d.department_id
          from departments d
         where d.department_name in ('Administration', 'Executive'))
 group by e.job_id
 order by frequency desc;

12.Show all employees who were hired in the first half of the month (before the 16th of the month).

-- 12. 展示所有雇佣日期在每月16日之前的
select e.last_name, e.hire_date
  from employees e
 where to_number(to_char(e.hire_date, 'dd')) < 16;

13.Show the names, salaries, and the number of dollars (in thousands) that all employees earn.

-- 13. 列出所有员工的姓名、薪水和收入(以千为单位)
select e.last_name, e.salary, trunc(e.salary / 1000) as "THOUSTANDS"
  from employees e;

14.Show all employees who have managers with a salary higher than $15,000. Show the following data: employee name, manager name, manager salary, and salary grade of the manager.

-- 14. 展示所有拥有年薪超过1.5万美元的经理的员工。显示如下数据:员工姓名、经理姓名、经理工资、经理工资等级。
-- e1是员工 e2是老板
select e1.