oracle关于查询的习题，oracle习题
/*
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 the salaries).
*/
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 departments
where department_name in (‘Administration’, ‘Executive’)))
group by job_id
order by fruquency desc;
/*
12.Show all employees who were hired in the first half of the month (before the 16th of the month).
*/
select last_name, hire_date
from employees
where to_char(hire_date, ‘dd’) <= 15;
/*
13.Show the names, salaries, and the number of dollars (in thousands) that all employees earn.
*/
select last_name, round(salary, 0), trunc(salary / 1000, 0) thousands
from employees;
/*
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.
*/
select e.last_name,m.last_name as manager,m.salary
from employees e,employees m
where e.manager_id=m.employee_id and m.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.
通过lag函数来实现
*/
select (nvl2((lag(t.department_id, 1, null)
over(partition by t.department_id order by e.employee_id)),
null,
t.department_id)) as department_id,
(nvl2((lag(t.department_id, 1, null)
over(partition by t.department_id order by e.employee_id)),
null,
t.department_name)) as department_name,
(nvl2((lag(t.department_id, 1, null)
over(partition by t.department_id order by e.employee_id)),
null,
t.employees)) as employees,
(nvl2((lag(t.department_id, 1, null)
over(partition by t.department_id order by e.employee_id)),
null,
t.avg_salary)) as avg_salary,
last_name,
job_id,
salary
from employees e,
(select d.department_id,
department_name,
count(e.department_id) as employees,
round(nvl(avg(salary), 0), 2) as avg_salary
from employees e, departments d
where e.department_id = d.department_id
group by d.department_id, d.department_name) t
where t.department_id = e.department_id
order by t.department_id, employee_id;
/*
16.Show the department number and the lowest salary of the department with the highest average salary.
*/
select e.department_id, min(salary)
from employees e,
(select *
from (select department_id, avg(salary) as avg_salary
from employees
group by department_id
order by avg_salary desc)
where rownum = 1) d
where e.department_id = d.department_id
group by e.department_id;
/*
17.Show the department numbers, names, and locations of the departments where no sales representatives work
*/
/*
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:
*/
select e.department_id, department_name, count(e.employee_id) as employees
from departments d, employees e
where e.department_id = d.department_id
group by e.department_id, department_name
having count(e.employee_id) < 3;
select *
from (select e.department_id,
department_name,
count(e.employee_id) as employees
from departments d, employees e
where e.department_id = d.department_id
group by e.department_id, department_name
order by employees desc)
where rownum = 1;
select *
from (select e.department_id,
department_name,
count(e.employee_id) as employees
from departments d, employees e
where e.department_id = d.department_id
group by e.department_id, department_name
order by employees)
where rownum = 1;
/*
19.Show the employee number, last name, salary, department number, and the average salary in their department for all employees.
*/
select employee_id,
last_name,
department_id,
round(avg(salary) over(partition by department_id),4) as avg_salary
from employees
order by employee_id;
/*
20.Show all employees who were hired on the day of the week on which the highest number of employees were hired.
*/
select last_name, dd as day
from employees,
(select dd
from (select to_char(hire_date, ‘day’) as dd, count(*) as cc
from employees
group by to_char(hire_date, ‘day’)
order by cc desc)
where rownum = 1)
where to_char(hire_date, ‘day’) = dd;
/*
21.Create an anniversary overview based on the hire date of the employees. Sort the anniversaries in ascending order.
*/
create view emp_hiredate as select last_name, to_char(hire_date, ‘month-dd’) as birthday
from employees
order by to_char(hire_date,’mm’),to_char(hire_date,’dd’) ;
/*
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.
*/
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.
*/
select nvl2(aa,lpad(aa,2,’0’),’no’)||’%raise’ as raise,employee_id, salary, nvl(aa/100,1) * salary as new_salary
from (select employee_id as em_id,
(case
when department_id in (10,50,110) then 5
when department_id = 60 then 10
when department_id in (20,80) then 15
else null
end
) aa
from employees),
employees
where employees.employee_id = em_id
order by aa;
/*
24.Alter the session to set the NLS_DATE_FORMAT to DD-MON-YYYY HH24:MI:SS.
*/
Alter session set NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24:MI:SS’
/*
25.a. Write queries to display the time zone offsets (TZ_OFFSET) for the following time zones.
Australia/Sydney
*/
SELECT TZ_OFFSET (‘Australia/Sydney’) from dual;
–Chile/Easter Island
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’;
/*
c. Display the SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP for this session.
*/
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’;
/*
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’;
/*
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.
*/
select last_name, extract(month from hire_date) as month_join, hire_date
from employees
where extract(month from hire_date) = 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
*/
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);
/*
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.
*/
select department_id, job_id, manager_id, max(salary), min(salary)
from employees
group by grouping sets((department_id, job_id),(job_id, manager_id));
/*
29.Write a query to display the top three earners in the EMPLOYEES table. Display their last names and salaries.
*/
select *
from (select last_name, salary from employees order by 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.
*/
select last_name,employee_id from employees e,departments d,locations l
where e.department_id = d.department_id
and 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:
*/
DELETE FROM job_history JH
WHERE employee_id = (SELECT employee_id
FROM employees E
WHERE JH.employee_id = E.employee_id
AND start_date =
(SELECT MIN(start_date)
FROM job_history JH
WHERE JH.employee_id = E.employee_id)
AND 3 > (SELECT COUNT(*)
FROM job_history JH
WHERE JH.employee_id = E.employee_id
GROUP BY employee_id
HAVING COUNT(*) >= 2));
/*
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.
*/
with
max_sal_clac as (select employees.job_id,job_title,max(salary) as job_salary from employees,jobs
where jobs.job_id = employees.job_id
group by employees.job_id,job_title),
max_sal_cmp as (select max(salary) as max_salary from employees)
select job_title,job_salary from max_sal_clac
where job_salary > (select * from max_sal_cmp)/2
/*
34. Write a SQL statement to display employee number, last name, start date, and salary, showing:
a. De Haan’s direct reports
*/
select employee_id, last_name, hire_date, salary
from employees
where manager_id =
(select employee_id from employees where last_name = ‘De Haan’);
/*
b. The organization tree under De Haan (employee number 102)
*/
select employee_id, last_name, hire_date, salary
from employees
start with manager_id = 102
connect by manager_id = prior employee_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.
*/
select employee_id,manager_id,last_name,level
from employees
where level>2
start with employee_id=102
connect by manager_id = prior employee_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.
*/
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;