本文中，将会包含部分我做过的Orcale习题，以供参考，希望能有热心的小伙伴，积极讨论，欢迎指出错误，或更好的解决方法~
一、Orcale 经典习题-40题
数据库的结构和数据都在我上传的资源中，可以进行免费下载。（本来想免费的，结果设置不了免费，至少1分，就设置成了1分 0.0）
题目&我做的答案
--1.查询雇佣时间在1997年之后的员工信息
Select
*
From
employees e
Where
1997 < extract(year from e.hire_date) ;
--2.查询有提成的员工信息（last name, job, salary, and commission），并按工资降序排列。
Select
last_name, job_id, salary, e.commission_pct
From
employees e
Order by
3 desc;
--3.Show the employees that have no commission with a 10% raise in their salary
--(round off the salaries).
-- 向没有佣金的员工展示他们的工资增加10％（四舍五入的薪水）。
Select
employee_id, first_name, last_name, salary,
round(nvl2(commission_pct, salary, salary * 1.10), 0) new_salary
From
employees e
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.
--显示所有员工的姓氏以及他们被雇用的年数和完成月数。
With
Q_mon As (Select employee_id, extract(month from sysdate)-extract(month from hire_date) mon_diff From employees)
Select
last_name,
case when mon_diff >= 0 then (extract(year from sysdate) - extract(year from hire_date)) else
(extract(year from sysdate) - extract(year from hire_date) - 1) end work_year,
case when mon_diff >= 0 then mon_diff else (mon_diff + 12) end work_month
From
employees e, Q_mon
Where
e.employee_id = Q_mon.employee_id;
--5. Show those employees that have a name starting with J, K, L, or M
--显示名称以J，K，L或M开头的员工
Select * From employees Where first_name like 'J%'
union all
Select * From employees Where first_name like 'K%'
union all
Select * From employees Where first_name like 'L%'
union all
Select * From employees Where first_name like 'M%';
--Select * From employees Where regexp_like(first_name,'^[J|K|L|M].*');
--6.Show all employees, and indicate with “Yes” or “No” whether they receive a commission.
--显示所有员工，并在“是”或“否”时表明他们是否收到佣金。
Select
employee_id, first_name, last_name,
case when commission_pct is null then 'NO' else 'YES' end whether
From
employees e;
--7.Show the department names, locations, names, job titles,
--and salaries of employees who work in location 1800.
--在1800位置显示工作的员工的部门名称，地点，姓名，职位和工资。
Select
d.department_name, d.location_id, e.first_name, e.last_name, e.job_id, e.salary
From
employees e, departments d
Where
e.department_id = d.department_id and d.location_id = 1800;
--8.How many employees have a name that ends with an n? Create two possible solutions.
--有多少员工的名字以n结尾？ 创建两个可能的解决方
Select
count(last_name)
From
employees
Where
last_name like '%n';
Select
sum(decode(substr(last_name, -1), 'n', 1, 0)) sum_number
From
employees;
Select
count(last_name)
From
employees
Where
regexp_like(last_name,'.*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, d.department_name, d.location_id, count(employee_id)
From
departments d left join employees e on d.department_id = e.department_id
Group by
d.department_id, d.department_name, d.location_id
Order by
1 ;
--10.Which jobs are found in departments 10 and 20?
--在部门10和20中找到哪些工作？
Select
job_id
From
employees e
Where
e.department_id = 10
Union
Select
job_id
From
employees e
Where
e.department_id = 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(employee_id)
From
employees e left join departments d on e.department_id = d.department_id
Where
upper(d.department_name) in (upper('Administration'), upper('Executive'))
Group by
job_id
Order by
2 desc;
--12.Show all employees who were hired in the first half of the month (before the 16th of the month).
--显示在本月上半月（本月16日之前）被雇用的所有员工。
Select employee_id, last_name, hire_date
From employees e
Where extract(day from hire_date) < 16;
--13. Show the names, salaries, and the number of dollars (in thousands) that all employees earn.
--显示所有员工的姓名，赚取的工资和美元数（以千计）。
Select
first_name, last_name, to_char(salary, 'L9,999,000,00')
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.
--显示所有经理薪水高于15,000美元的员工。 显示以下数据：员工姓名，经理姓名，经理薪水和工资等级。
Select
e.first_name ||' '||e.last_name employee_name,
e1.first_name ||' '||e1.last_name manager_name,
e1.salary, (case when e1.salary < 5000 then 'A'
when e1.salary < 10000 then 'B'
when e1.salary < 15000 then 'C'
when e1.salary < 20000 then 'D'
else 'E' end) salary_level
From
employees e left join employees e1 on e1.employee_id = e.manager_id
Where
e1.salary > 15000 ;
--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.
--显示所有部门的部门编号，姓名，员工人数和平均工资，以及每个部门工作人员的姓名，工资和工作。
Select
d.department_id , department_name,
/*(select count(*) From employees e
Where e.department_id = d.department_id Group by e.department_id) sum_number,
(Select Round( avg(salary), 2) From employees e1
Where e1.department_id = d.department_id Group by e1.department_id) avg_salary,*/
count(*) over (partition by e2.department_id) sum_number,
Round( avg(salary) over (partition by e2.department_id), 2 ) avg_salary,
last_name, salary, job_id
From
departments d left join employees e2 on d.department_id = e2.department_id;
Select
nvl2(lag(d.department_id, 1, null) over (partition by e2.department_id order by null),null, d.department_id),
nvl2(lag(d.department_name, 1, null) over (partition by d.department_name order by null),null, d.department_name),
-- nvl2(lag(), 1, null)
-- over (partition by (count(*) over (partition by e2.department_id)) order by null),null, d.department_id),
d.department_id , department_name,
count(*) over (partition by e2.department_id) sum_number,
Round( avg(salary) over (partition by e2.department_id), 2 ) avg_salary,
last_name, salary, job_id
From
departments d left join employees e2 on d.department_id = e2.department_id ;
select
decode ( lag(t1.A ,1,null) over (partition by t1.A order by t1.A,t1.B,t1.C,t1.D),null,t1.A,null) A1,
decode ( lag(t1.B ,1,null) over (partition by t1.A order by t1.A,t1.B,t1.C,t1.D),null,t1.B,null) A2,
decode ( lag(t1.C ,1,null) over (partition by t1.A order by t1.A,t1.B,t1.C,t1.D),null,t1.C,null) A3,
decode ( lag(t1.D ,1,null) over (partition by t1.A order by t1.A,t1.B,t1.C,t1.D),null,t1.D,null) A4,
t1.E,t1.F,t1.G
from
(select distinct d.department_id A ,
d.department_name B,
count(e.employee_id) over (partition by e.department_id ) as C,
-- nvl2(avg(e.salary) over (partition by e.department_id ),to_char(),'no average') as D,
nvl (to_char(avg(e.salary) over (partition by e.department_id ),'999,999,999.99'),'no average') as D,
e.last_name as E,
e.salary as F ,
e.job_id as G
from departments d,employees e
where d.department_id=e.department_id(+)) t1;
--使用 lag 进行置空
--16.Show the department number and the lowest salary of the department with the highest average salary.
--显示平均工资最高的部门编号和最低工资。
With
Q1 as (Select department_id, avg(salary) avg_salary From employees Group by department_id)
Select
distinct e.department_id, min(salary) over(partition by e.department_id) min_salary
From
employees e left join Q1 on e.department_id = Q1.department_id
Where
avg_salary >= (Select max(Q1.avg_salary) From Q1);
--17.Show the department numbers, names,
--and locations of the departments where no sales representatives work.
--显示没有销售代表工作的部门的部门编号，名称和位置。
Select
distinct d.department_id, d.department_name, d.location_id, d.manager_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), 'title_null') <> upper('Sales Representative')
Order by
1;
--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. 显示部门名称、编号、工作在每个部门的员工数量，分别有以下三个要求
--a小于三个员工 b员工数最多 c员工数最少
With
Q1 AS (Select d.department_id, count(employee_id) sum_number
From departments d left join employees e on d.department_id = e.department_id
Group by d.department_id)
Select
department_name, d.department_id, sum_number
From
departments d left join Q1
on d.department_id = q1.department_id
Where
--q1.sum_number < 3
--q1.sum_number >= (Select max(sum_number) From Q1 )
q1.sum_number <= (Select min(sum_number) From Q1);
--需要 departments 与 employees 做 左外链接 ！！！
--19. Show the employee number, last name, salary, department number,
--and the average salary in their department for all employees.
--显示所有员工的员工编号，姓氏，工资，部门编号以及部门的平均工资。
Select
e.employee_id, first_name || ' ' ||last_name employee_name, e.department_id,
Round(avg(e.salary)over(partition by e.department_id) , 2) depart_salary_avg
From
employees e;
--20.Show all employees who were hired on the day of the week
--on which the highest number of employees were hired.
--显示在雇用最多雇员人数的当天雇用的所有员工。
With
Q1 as (Select to_char(hire_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN') hire_day, count(employee_id) hire_sum
From employees e1
Group by to_char(hire_date, 'DAY','NLS_date_language=american')
Order by 2 Desc)
Select
first_name, last_name, to_char(hire_date, 'DAY','NLS_date_language=american') hire_day
From
employees e1
Where
to_char(hire_date, 'DAY','NLS_date_language=american') in
(Select hire_day From Q1 Where Q1.hire_sum >= (Select max(hire_sum) From Q1)) ;
--21.Create an anniversary overview based on the hire date of the employees.
--Sort the anniversaries in ascending order.
--根据员工的雇用日期创建周年纪念概览。 按升序排列周年纪念日。
Select
first_name, last_name,to_char(hire_date, 'mm-dd') birth_hire
From
employees e
Order by
3 ASC;
Select localtimestamp From dual;
--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.
--找到1990年上半年填补的工作和1991年同期填补的工作。
Select
job_id
From
employees
Where
extract(year from hire_date) = 1990
and extract(month from hire_date) <= 6
Intersect
Select
job_id
From
employees
Where
extract(year from hire_date) = 1991
and extract(month from hire_date) <= 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. 显示工资涨幅，员工编号，原来的工资和增加的工资：
--部门号10、50、110的有5%的涨幅，部门号为60的涨10%的工资
--部门号为20和80涨幅为15%，部门为90的不涨工资
Select
e1.department_id, salary_by, employee_id, salary, salary * (1 + salary_by)
From
employees e1 left join
(Select department_id,
decode(department_id, 10,0.05, 50,0.05, 100,0.05, 60,0.10, 20,0.15, 80,0.15, 0 )salary_by
From employees
Group by department_id) s on e1.department_id = s.department_id
Order by
2 desc;
--24.Alter the session to set the NLS_DATE_FORMAT to DD-MON-YYYY HH24:MI:SS.
Select * from nls_session_parameters Where parameter = 'NLS_DATE_FORMAT'; --'DD-MON-RR'
Select sysdate From dual;
Alter session Set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
--25the following time zones.
--a. Australia/Sydney Chile/Easter Island
SELECT TZ_OFFSET ('Australia/Sydney') from dual;
SELECT TZ_OFFSET ('Chile/EasterIsland') from dual;
--b. Alter the session to set the TIME_ZONE parameter value to the time zone offset of Australia/Sydney.
--alter session set time_zone = '+10:00';
Alter session set time_zone = 'Australia/Sydney';
Select SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP From dual;
--c. Display the SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP for this session.
--Note: The output might be different based on the date when the command is executed.
SELECT SYSDATE,CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;
--d. Alter the session to set the TIME_ZONE parameter value to the time zone offset of Chile/Easter Island.
--alter session set time_zone = '-6:00';
Alter session set time_zone = 'Chile/EasterIsland';
Select SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP From dual;
--e. Display the SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP for this session.
Select SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP From dual;
--f. Alter the session to set the NLS_DATE_FORMAT to DD-MON-YYYY.
Alter session Set NLS_DATE_FORMAT = 'DD-MON-YYYY';
Select * from nls_session_parameters;
Select * from nls_database_parameters;
Select * from nls_instance_parameters;
--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.
--写一个查询来显示姓氏，加入日期和雇用日期？ 无论加入年份如何，在1月份加入的员工。
With
Q1 as (Select
count(*) sum_number, to_char(e.hire_date, 'yyyy-mm') year_mon
From
employees e Group by to_char(e.hire_date, 'yyyy-mm'))
Select
last_name,
sum_number,
hire_date
From
employees e left join Q1
on to_char(e.hire_date, 'yyyy-mm') = Q1.year_mon
Where
1 = extract(month from hire_date);
--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
--编写查询以显示部门ID更高的部门的以下内容？ 比80：
--部门内每项工作的总薪水
--总薪水
--各部门所在城市的总薪资
--每个工作的总薪水，不论部门如何
--不论城市如何，每个部门的总薪水
--各部门所在城市的总薪资
--各部门的总薪资，不论职称和城市
Select
city, department_name, job_id, sum(salary)
From
employees e, departments d, locations l
Where
l.location_id = d.location_id and d.department_id = e.department_id
and d.department_id > 80
Group by
cube(city, department_name, job_id)
Order by
1, 2, 3;
--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.
--编写查询以显示以下分组：
--部门ID，职务ID
--工作ID，经理ID
-- 查询应计算每个组的最高和最低工资。
Select
department_id, job_id, manager_id, max(salary), min(salary)
From
employees e
Group by
--rollup(department_id, manager_id, job_id)
grouping sets((department_id, job_id),(job_id, manager_id))
Order by
1,2,3;
--29.Write a query to display the top three earners in the EMPLOYEES table.
-- Display their last names and salaries.
--编写查询以显示EMPLOYEES表中的前三个收入者。 显示他们的姓氏和工资。
Select
last_name,salary
From
(Select rownum r1 , last_name, salary From employees Order by 3 desc) Q
Where
r1 < 4;
--使用分析函数
--30.Write a query to display the employee ID and last names of the employees
--who work in the state of California.
--编写查询以显示在加利福尼亚州工作的员工的员工ID和姓氏。
Select
employee_id, last_name
From
employees e, departments d, locations l
Where
e.department_id = d.department_id and d.location_id = l.location_id
and upper(l.state_province) = upper('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:
--编写查询以通过查找员工的MIN（START_DATE）的JOB_HISTORY表来删除员工的最旧JOB_HISTORY行。
--仅删除已更改至少两个作业的员工的记录。 如果您的查询正确执行，您将获得反馈：
Delete From
job_history j
--Select j.* From job_history j
Where
/* j.employee_id = (Select Q1.employee_id
From (Select employee_id, count(employee_id) change_time
From job_history j1
Group by employee_id
Having count(employee_id) > 1) Q1
Where Q1.employee_id = j.employee_id)
and */
j.start_date = (Select min(j2.start_date)
From job_history j2
Where j2.employee_id = j.employee_id
having count(*) >= 2);
rollback;
--32.Roll back the transaction.
Select job_id, job_title, min_salary, max_salary
From Jobs
Order by 4 desc;
Insert into jobs(job_id, job_title, min_salary, max_salary)
Values('My_job', 'My_title', 15000.00, 30000.00);
Savepoint insert_jobs;
Update jobs
Set min_salary = 20000.00, max_salary = 50000.00
Where job_id = 'My_job';
Savepoint update_jobs;
rollback to savepoint insert_jobs;
Update jobs
Set min_salary = 45000.00, max_salary = 60000.00
Where job_id = 'My_job';
Savepoint update_jobs1;
rollback to savepoint update_jobs;
Insert into jobs(job_id, job_title, min_salary, max_salary)
Values('My_job1', 'My_title1', 15000.00, 30000.00);
Savepoint insert_jobs1;
rollback to savepoint update_jobs;
rollback to savepoint update_jobs1;
rollback ;
commit;
--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.
--编写查询以显示最高工资高于整个公司最高工资一半的工作的工作ID。
--使用WITH子句编写此查询。 将查询命名为MAX_SAL_CALC。
With
Job_salary_max As (Select job_id, max(salary) job_max From employees Group by job_id),
All_salary_max As (Select max(salary) * 0.50 Half_all From employees)
Select
job_title, job_max
From
Job_salary_max,All_salary_max,jobs
Where
Job_salary_max.job_id = jobs.job_id and Job_salary_max.job_max > All_salary_max.Half_all;
--34.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)
--编写一个SQL语句来显示员工编号，姓氏，开始日期和工资：
--De Haan 的领导， --手下的员工
Select
employee_id, first_name, last_name, hire_date, salary
From
employees e
Start with
upper(last_name) = upper('De Haan')
Connect by
--employee_id = prior manager_id;
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.
--编写分层查询以显示员工De Haan（员工编号102）以下两级员工的员工编号，
--经理编号和员工姓氏。还显示员工的级别。
Select *
From (
Select
employee_id, first_name, last_name, manager_id, level employee_levles
From
employees e
Start with
upper(last_name) = upper('De Haan')
Connect by
prior employee_id = manager_id
)
Where
employee_levles > 2;
--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.
--生成分层报告以显示员工编号，经理编号，LEVEL伪列和员工姓氏。
--对于EMPLOYEES表中的每一行，您应该打印一个树结构，显示员工，员工的经理，
--然后是经理的经理，等等。 对NAME列使用缩进。
Select
employee_id, manager_id, level, Lpad(last_name, length(last_name)-1+level, '_' ) level_name
From
employees e
Start with
employee_id = 100
Connect by
prior employee_id = manager_id;
select employee_id, manager_id,
level, lpad(last_name, length(last_name) + level - 1, '_')
from employees e
start with employee_id in
(select employee_id
from employees
start with manager_id is null
connect by manager_id = prior employee_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. 显示员工编号、入职时间、薪水、上司的编号，要求员工编号大于等于200
--如果薪水小于5000，将员工编号和薪水插入到SPECIAL_SAL表里
--将员工编号、入职时间、薪水插入到SAL_HISTORY表
--将员工编号、上司编号、薪水插入到MGR_HISTORY表
Create table
special_sal(employee_id number primary key, salary number)
Create table
sal_history(employee_id number primary key, hire_date date not null, salary number)
Create table
mgr_history(employee_id number primary key, manager_id number, salary number)
Insert all
When salary < 5000 then into special_sal values(employee_id, salary)
When 1 = 1 then into sal_history values(employee_id, hire_date, salary)
When 1 = 1 then into mgr_history values(employee_id, manager_id, salary)
Select
employee_id, hire_date, salary, manager_id
From
employees
Where
employee_id >= 200;
Select * From special_sal;
Select * From sal_history;
Select * From mgr_history;
rollback;
--38.Query the SPECIAL_SAL, SAL_HISTORY and the MGR_HISTORY tables to view the inserted records.
--查询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
Create table
locations_named_index (Deptno number(4) constraint locations_pk_idx primary key,
Dname varchar2(30) );
select
t.*,i.index_type
from
user_ind_columns t,user_indexes i
where
t.index_name = i.index_name and t.table_name= Upper('locations_named_index');
drop table locations_named_index;
--40.Query the USER_INDEXES table to display the INDEX_NAME for the LOCATIONS_NAMED_INDEX table.
--
Select
index_name
From
USER_INDEXES
Where
tabele_name = 'LOCATIONS_NAMED_INDEX';
扫一扫
1675
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
