前言
做题首先要有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.last_name, e2.last_name as "Manager", e2.salary, 'E' as GRA
from employees e1, employees e2
where e1.manager_id = e2.employee_id(+)
and e2.salary > 15000
order by e2.salary Desc;
15.Show the department number, name, number of employees, and average salary of all departments, together with the names, salaries, and jobs of the employees working in each department.
-- 15. 显示各部门的部门编号、姓名、员工人数、平均工资，以及各部门员工的姓名、工资、岗位。
-- 此题比较复杂，空白部分是最难处理的，先给出一个简单的没有空白的解答
select t1.department_id,
t1.department_name,
t1.EMPLOYEES,
t1.AVG_SAL,
t2.last_name,
t2.salary,
t2.job_id
from (select dep.department_id,
dep.department_name,
count(emp.employee_id) as EMPLOYEES,
round(avg(nvl(emp.salary, 0)), 2) as AVG_SAL
from departments dep, employees emp
where dep.department_id = emp.department_id(+)
group by dep.department_id, dep.department_name
order by dep.department_id) t1,
employees t2
where t1.department_id = t2.department_id(+)
order by t1.department_id;
-- 正确解答如下
select t4.department_id,
t4.department_name,
t4.employees,
t4.avg_sal,
t3.last_name,
t3.salary,
t3.job_id
from employees t3,
(select t2.department_id,
t2.department_name,
t1.top,
t1.employees,
t1.avg_sal
from (select e.department_id,
min(e.employee_id) top,
count(1) employees,
round(avg(nvl(e.salary, 0))) avg_sal
from employees e
group by e.department_id) t1,
departments t2
where t1.department_id = t2.department_id(+)) t4
where t3.employee_id = t4.top(+)
order by t3.department_id;
16.Show the department number and the lowest salary of the department with the highest average salary.
-- 16. 显示平均工资最高部门的部门编号和最低工资。
select *
from (select e.department_id, min(e.salary)
from employees e
group by e.department_id
order by avg(nvl(e.salary, 0)) Desc)
where rownum = 1;
17.Show the department numbers, names, and locations of the departments where no sales representatives work.
-- 17. 显示没有 Sales Representative 的部门的编号、名称和位置
Select distinct d.department_id,
d.department_name,
d.manager_id,
d.location_id
from departments d
left join employees e
on d.manager_id = e.manager_id
left join jobs j
on e.job_id = j.job_id
Where NVL2(j.job_title, upper(j.job_title), 'nodata') <>
upper('Sales Representative')
Order by department_id;
18.Show the department number, department name, and the number of employees working in each
department that:
- a. Includes fewer than 3 employees:
- b. Has the highest number of employees:
- c. Has the lowest number of employees:
-- 18. 显示部门编号、部门名称和在每个部门工作的员工人数:
-- 18.a 包括少于3名员工
select dep.department_id, dep.department_name, count(emp.employee_id)
from departments dep, employees emp
where dep.department_id = emp.department_id(+)
group by dep.department_id, dep.department_name
having count(emp.employee_id) < 3
order by dep.department_id;
-- 18.b 拥有最多的员工
select *
from (select dep.department_id,
dep.department_name,
count(emp.employee_id)
from departments dep, employees emp
where dep.department_id = emp.department_id(+)
group by dep.department_id, dep.department_name
order by count(emp.employee_id) desc)
where rownum = 1;
-- 18.c 拥有最少的员工
select *
from (select dep.department_id,
dep.department_name,
count(emp.employee_id)
from departments dep, employees emp
where dep.department_id = emp.department_id(+)
group by dep.department_id, dep.department_name
order by count(emp.employee_id) asc)
where rownum = 1;
19.Show the employee number, last name, salary, department number, and the average salary in their department for all employees.
-- 19. 显示员工编号、姓氏、工资、部门编号以及所在部门所有员工的平均工资。
select t2.employee_id, t2.last_name, t1.*
from (select e.department_id, round(avg(nvl(e.salary, 0)), 4) as AVG
from employees e
group by e.department_id) t1,
employees t2
where t1.department_id(+) = t2.department_id
order by t2.employee_id;
20.Show all employees who were hired on the day of the week on which the highest number of employees were hired.
-- 20. 显示所有员工在雇佣人数最多的那一天被雇佣。
select t2.last_name, t1.DAY
from (select t.DAY
from (select to_char(e.hire_date,
'DAY',
'NLS_DATE_LANGUAGE = ENGLISH') as DAY
from employees e
group by to_char(e.hire_date,
'DAY',
'NLS_DATE_LANGUAGE = ENGLISH')
order by count(1) desc) t
where rownum = 1) t1,
employees t2
where t1.DAY = to_char(t2.hire_date, 'DAY', 'NLS_DATE_LANGUAGE = ENGLISH');
21.Create an anniversary overview based on the hire date of the employees. Sort the anniversaries
in ascending order.
-- 21. 根据员工的雇佣日期创建周年概述。按升序排列周年纪念。
select e.last_name,
trim(to_char(e.hire_date, 'month', 'NLS_DATE_LANGUAGE = ENGLISH')) ||
to_char(e.hire_date, ' dd', 'NLS_DATE_LANGUAGE = ENGLISH') as BIRTHDAY
from employees e
order by to_number(to_char(e.hire_date, 'mm')) asc,
to_number(to_char(e.hire_date, 'dd')) asc;
22.Find the job that was filled in the first half of 1990 and the same job that was filled during the same period in 1991.
-- 22. 请找出在1990年上半年已填补的职位和在1991年同一期间已填补的职位中两者相同的职位。
(select e.job_id
from employees e
where to_char(e.hire_date, 'YYYY') = '2007'
and to_number(to_char(e.hire_date, 'mm')) <= 6)
INTERSECT (select e.job_id
from employees e
where to_char(e.hire_date, 'YYYY') = '2008'
and to_number(to_char(e.hire_date, 'mm')) <= 6);
23.Write a compound query to produce a list of employees showing raise percentages, employee IDs, and old salary and new salary increase. Employees in departments 10, 50, and 110 are given a 5% raise, employees in department 60 are given a 10% raise, employees in departments 20 and 80 are given a 15% raise, and employees in department 90 are not given a raise.
-- 23. 编写一个复合查询来生成一个员工列表，其中显示加薪百分比、员工id以及旧工资和新工资的增加。
-- 10、50和110部门的员工加薪5%，60部门的员工加薪10%，20和80部门的员工加薪15%，90部门的员工不加薪。
(
select '05% raise' as RAISE,
e.employee_id,
e.salary,
e.salary * 0.05 as NEW_SALARY,
e.department_id
from employees e
where e.department_id in (10, 50, 110))
UNION ALL (select '10% raise' as RAISE,
e.employee_id,
e.salary,
e.salary * 0.1 as NEW_SALARY,
e.department_id
from employees e
where e.department_id in (60))
UNION ALL (select '15% raise' as RAISE,
e.employee_id,
e.salary,
e.salary * 0.15 as NEW_SALARY,
e.department_id
from employees e
where e.department_id in (20, 80))
UNION ALL (select 'no raise' as RAISE,
e.employee_id,
e.salary,
e.salary * 0 as NEW_SALARY,
e.department_id
from employees e
where e.department_id in (90));
24.Alter the session to set the NLS_DATE_FORMAT to DD-MON-YYYY HH24:MI:SS.
-- 24. 修改会话，将NLS_DATE_FORMAT设置为DD-MON-YYYY HH24:MI:SS。
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;
25:
- a. Write queries to display the time zone offsets (TZ_OFFSET) for the following time zones.
- – Australia/Sydney
- – Chile/Easter Island
- b. Alter the session to set the TIME_ZONE parameter value to the time zone offset of Australia/Sydney.
- c. Display the SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP for this session.
- d. Alter the session to set the TIME_ZONE parameter value to the time zone offset of Chile/Easter Island.
- e. Display the SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP for this session.
- f. Alter the session to set the NLS_DATE_FORMAT to DD-MON-YYYY.
-- 25.a 编写查询来显示以下时区的时区偏移量(TZ_OFFSET)。
-- Australia/Sydney
select TZ_OFFSET('Australia/Sydney') as tz_offs from dual;
-- Chile/EasterIsland
select TZ_OFFSET('Chile/EasterIsland') as tz_offs from dual;
-- 25.b 更改会话，将TIME_ZONE参数值设置为Australia/Sydney的时区偏移量。
alter session set time_zone = '+10:00';
-- 25.c 显示此会话的SYSDATE、CURRENT_DATE、CURRENT_TIMESTAMP和LOCALTIMESTAMP。
select SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP from dual;
-- 25.d 修改会话，将TIME_ZONE参数值设置为Chile/Easter Island的时区偏移量。
alter session set time_zone = '-6:00';
-- 25.e 显示此会话的SYSDATE、CURRENT_DATE、CURRENT_TIMESTAMP和LOCALTIMESTAMP。
select SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP from dual;
-- 25.f 修改会话，将NLS_DATE_FORMAT设置为 DD-MON-YYYY
alter session set nls_date_format = 'DD-MON-YYYY';
26.Write a query to display the last names, month of the date of join, and hire date of those employees who have joined in the month of January, irrespective of the year of join.
-- 26. 编写查询以显示在1月份加入的雇员的姓氏、加入日期的月份和雇佣日期，而不考虑加入的年份。
select e.last_name,
extract(month from hire_date),
to_char(e.hire_date, 'dd-MON-YYYY' ， 'NLS_DATE_LANGUAGE = ENGLISH') as hire_date
from employees e
where to_number(to_char(e.hire_date, 'MM')) = 1;
27.Write a query to display the following for those departments whose department ID is greater than 80:
– The total salary for every job within a department
– The total salary
– The total salary for those cities in which the departments are located
– The total salary for every job, irrespective of the department
– The total salary for every department irrespective of the city
– The total salary of the cities in which the departments are located
– Total salary for the departments, irrespective of job titles and cities
-- 27. 编写一个查询，为部门ID大于80的部门显示以下内容:
--部门内每一份工作的总工资
--薪酬总额
--部门所在城市的工资总额
--个工作的总工资，不论部门
--每个部门的工资总额，不论城市
--各部门所在城市的工资总额
--各部门的工资总额，不分职位和城市
--group by departments employees locations
select city, department_name as dname, e.job_id as job, sum(salary)
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id
and e.department_id > 80
group by cube(city, department_name, e.job_id)
order by city asc;
28.Write a query to display the following groupings:
– Department ID, Job ID
– Job ID, Manager ID
The query should calculate the maximum and minimum salaries for each of these groups.
-- 28. 编写一个查询来显示以下分组
--部门ID，工作ID
--工作ID，经理ID
--查询应该计算每个组的最高和最低工资。
select e.department_id,
e.job_id,
e.manager_id,
max(e.salary),
min(e.salary)
from employees e
group by grouping sets((e.department_id, e.job_id),(e.job_id, e.manager_id))
order by e.department_id;
29.Write a query to display the top three earners in the EMPLOYEES table. Display their last names and salaries.
-- 29. 编写一个查询来显示EMPLOYEES表中前三位收入者。显示他们的姓氏和薪水。
select *
from (select e.last_name, e.salary from employees e order by e.salary desc)
where rownum <= 3;
30.Write a query to display the employee ID and last names of the employees who work in the state of California.
Hint: Use scalar subqueries.
-- 30. 编写一个查询来显示在California工作的员工的ID和姓氏。提示:使用标量子查询。
select e.employee_id, e.last_name, e.department_id
from employees e
where e.department_id =
(select d. department_id
from departments d, locations l
where d.location_id = l.location_id(+)
and l.state_province = 'California');
31.Write a query to delete the oldest JOB_HISTORY row of an employee by looking up the JOB_HISTORY table for the MIN(START_DATE) for the employee. Delete the records of only those employees who have changed at least two jobs. If your query executes correctly, you will get the feedback:
Hint: Use a correlated DELETE command.
-- 31. 通过在JOB_HISTORY表中查找该员工的最小值(START_DATE)，编写一个查询来删除该员工最老的JOB_HISTORY行。
--只删除那些更改了至少两个作业的员工的记录。如果您的查询执行正确，您将得到反馈:
delete from job_history jh
where (jh.employee_id, jh.start_date) in
(select jh.employee_id, min(jh.start_date)
from job_history jh
group by jh.employee_id
having count(1) >= 2);
32.Roll back the transaction.
-- 32. 回滚事务。
ROLLBACK WORK;
33.Write a query to display the job IDs of those jobs whose maximum salary is above half the maximum salary in the whole company. Use the WITH clause to write this query. Name the query MAX_SAL_CALC.
-- 33. 编写一个查询来显示那些工资超过整个公司最高工资一半的工作的工作id。使用WITH子句来编写这个查询。将查询命名为 MAX_SAL_CALC 。
with MAX_SAL_CALC as
(select e.job_id, e.salary
from employees e
where e.salary > (select max(salary) * 0.50 as money from employees))
select j.job_title, MAX_SAL_CALC.salary
from jobs j, MAX_SAL_CALC
where j.job_id = MAX_SAL_CALC.job_id
order by 2 desc;
- Write a SQL statement to display employee number, last name, start date, and salary, showing:
- a. De Haan’s direct reports
- b.The organization tree under De Haan (employee number 102)
-- 34. 编写一个SQL语句来显示员工编号、姓氏、开始日期和薪水，显示:
-- 34.a De Haan的直接报告
select e2.employee_id, e2.last_name, e2.hire_date, e2.salary
from employees e2
where e2.employee_id =
(select e.employee_id from employees e where e.last_name = 'De Haan') + 1;
-- 34.b De Haan下的组织树(员工编号102)
select e.employee_id, e.last_name, hire_date, e.salary
from employees e
start with manager_id = 102
connect by prior employee_id = manager_id;
35.Write a hierarchical query to display the employee number, manager number, and employee last name for all employees who are two levels below employee De Haan (employee number 102). Also display the level of the employee.
-- 35. 编写一个层次查询来显示比员工De Haan低两级(员工编号102)的所有员工的员工编号、经理编号和员工姓。还要显示员工的等级。
select e.employee_id, e.manager_id, e.last_name, level
from employees e
where level > 2
start with employee_id = 102
connect by prior employee_id = manager_id;
36.Produce a hierarchical report to display the employee number, manager number, the LEVEL pseudocolumn, and employee last name. For every row in the EMPLOYEES table, you should print a tree structure showing the employee, the employee’s manager, then the manager’s manager, and so on. Use indentations for the NAME column.
-- 36. 生成一个分层报告，以显示员工编号、管理人员编号、级别伪列和员工的姓。
--对于EMPLOYEES表中的每一行，应该打印一个树结构，显示员工、员工的经理、经理的经理，等等。对NAME列使用缩进。
select e.employee_id,
e.manager_id,
level,
lpad(e.last_name, length(e.last_name) + level - 1, '_')
from employees e
start with employee_id in
(select emp.employee_id
from employees emp
start with emp.manager_id is null
connect by prior emp.employee_id = emp.manager_id)
connect by e.employee_id = prior e.manager_id;
37.Write a query to do the following:
– Retrieve the details of the employee ID, hire date, salary, and manager ID of those
employees whose employee ID is more than or equal to 200 from the EMPLOYEES
table.
– If the salary is less than $5,000, insert the details of employee ID and salary into the
SPECIAL_SAL table.
– Insert the details of employee ID, hire date, and salary into the SAL_HISTORY table.
– Insert the details of employee ID, manager ID, and salary into the MGR_HISTORY table.
-- 37. 写一个查询来做以下事情:
--从employees表中检索雇员ID大于或等于200的员工的雇员ID、雇佣日期、工资和经理ID的详细信息。
--如果薪水小于$5,000，则在 SPECIAL_SAL 表中插入雇员ID和薪水的详细信息。
--在 SAL_HISTORY 表中插入雇员ID、雇佣日期和工资的详细信息。
--将员工ID、经理ID和工资的详细信息插入到 MGR_HISTORY 表中。
--先删除
drop table SPECIAL_SAL;
drop table SAL_HISTORY;
drop table MGR_HISTORY;
--创建3个表
create table SPECIAL_SAL(EMPLOYEE_ID number(6), SALARY number(8, 2));
create table SAL_HISTORY(EMPLOYEE_ID number(6),
HIRE_DATE date,
SALARY number(8, 2));
create table MGR_HISTORY(EMPLOYEE_ID number(6),
MANAGER_ID number(6),
SALARY number(8, 2));
-- 插入数据
insert all when salary < 5000 then into SPECIAL_SAL
(EMPLOYEE_ID, SALARY)
values
(employee_id, salary) when 1 = 1 then into SAL_HISTORY
(EMPLOYEE_ID, HIRE_DATE, SALARY)
values
(employee_id, hire_date, manager_id) when 1 = 1 then into MGR_HISTORY
(EMPLOYEE_ID, MANAGER_ID, SALARY)
values
(employee_id, manager_id, salary)
select e.employee_id, e.hire_date, e.salary, e.manager_id
from employees e
where e.employee_id >= 200;
38.Query the SPECIAL_SAL, SAL_HISTORY and the MGR_HISTORY tables to view the inserted records.
-- 38. 查询SPECIAL_SAL、SAL_HISTORY和MGR_HISTORY表以查看插入的记录。
select * from special_sal;
select * from sal_history;
select * from mgr_history;
39 Create the LOCATIONS_NAMED_INDEX table based on the following table instance chart. Name the index for the PRIMARY KEY column as LOCATIONS_PK_IDX.
-- 39. 根据下面的表实例图创建 LOCATIONS_NAMED_INDEX 表。
--将主键列的索引命名为 LOCATIONS_PK_IDX 。
drop table LOCATIONS_NAMED_INDEX;
create table LOCATIONS_NAMED_INDEX(Deptno number(4) constraint
LOCATIONS_PK_IDX primary key,
Dname varchar2(30));
40.Query the USER_INDEXES table to display the INDEX_NAME for the LOCATIONS_NAMED_INDEX table.
-- 40. 查询 USER_INDEXES 表以显示 LOCATIONS_NAMED_INDEX 表的 INDEX_NAME 。
Select index_name
From USER_INDEXES
Where table_name = 'LOCATIONS_NAMED_INDEX';
至此结束，楼主写完了最后一个sql，松了一口气，夜已深，早点睡吧~
1290
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
