oracle 查询上机实验

查询是否有hr用户

conn sys/oracle as sysdba;

desc dba_users；

select username,account_status from dba_users;

第一章 基本的sql语句
4.
select distinct job_id from employees;
5.
select last_name || ', ' || job_id as "JOB"
from employees;
6.
select
employee_id || ',' ||
first_name || ',' ||
last_name || ',' ||
email || ',' ||
phone_number || ',' ||
hire_date || ',' ||
job_id || ',' ||
salary || ',' ||
commission_pct || ',' ||
manager_id || ',' ||
department_id  as "the_output"
from employees;


第二章 限制和排序数据
1
select last_name,salary
from employees where
salary>12000;

2.
select last_name,department_id 
from employees where
employee_id = 176;

3.
select last_name,salary
from employees
where salary not between 5000 and 12000;

4.**
select last_name,job_id,hire_date from employees where
hire_date between to_date('2/20/1998','MM/DD/YYYY')
and to_date('5/1/1998','MM/DD/YYYY')
order by hire_date;

5.*
select last_name,department_id
from employees
where department_id in(20,50)
order by 1;

6.
select last_name,hire_date
from employees
where to_char(hire_date,'YYYY')=1994;

7.*
select last_name,job_id
from employees
where manager_id is null;

8.*
select last_name,salary,commission_pct
from employees
where commission_pct is not null
order by salary desc,commission_pct desc;


9.*
select e.last_name,j.job_title,e.salary
from jobs j,employees e
where e.job_id=j.job_id and
j.job_title in('Sales Representative','Stock Clerk')
and e.salary not in(2500,3500,7000);

10
select last_name,salary,commission_pct
from employees
where commission_pct=0.2;


第三章 单行函数
1.
select sysdate as "Date" from dual;

2.
select employee_id,last_name,salary,salary*1.15 "New Salary"
from employees;

3.
select initcap(last_name) last_name,length(last_name) name_length
from employees
where upper(substr(last_name,1,1)) in ('J','A','M')
order by 1;

4.
select 
last_name || ' earns ' || salary || ' monthly but wants ' || 3*salary as "Dream Salaries"
from employees;

5.
select last_name,
lpad(substr(to_char(salary),1,15),15,'$') 
as "SALARY" from employees;

6.
select last_name,
nvl(to_char(commission_pct),'NO Commission')
as "COMM" from employees; 

7.
select distinct job_id,
decode(job_id, 'AD_PRES','A',
		'ST_MAN','B',
		'IT_PROG','C',
		'SA_REP','D',
		'ST_CLERK','E',
		'0') Grade
from employees;

8.
select distinct job_id,
case job_id when 'AD_PRES' then 'A'
	    when 'ST_MAN' then 'B'
	    when 'IT_PROG' then 'C'
	    when 'SA_REP' then 'D'
	    when 'ST_CLERK' then 'E'
	    else '0'
    	    end Grade
from employees;


第五章 使用分组函数聚集数据
1.
select max(salary) "Maximum",min(salary) "Minimum",
sum(salary) "Sum",round(avg(salary),0) "Average"
from employees;

2.
select job_id,
max(salary) "Maximum",min(salary) "Minimum",
sum(salary) "Sum", round(avg(salary),0)  "Average"
from employees
group by job_id;

3.
select job_id, count(*) "Count"
from employees
group by job_id;

4.
select count(distinct manager_id) "Manager Count"
from employees;

5.
select max(salary)-min(salary) "DIFFERENCE"
from employees;

6.
select e.manager_id,min(e.salary) "salary"
from employees e
group by e.manager_id
having min(e.salary)<=6000
order by 2;

7.
select d.department_name "Name",d.location_id "Location",
count(*) "Number of People",round(avg(salary),2) "Salary"
from employees e,departments d
where e.department_id=d.department_id
group by d.department_name,d.location_id;

8.
select 行列转换
count(*) total,
sum(decode(to_char(hire_date,'YYYY'),1995,1,0))"1995",
sum(decode(to_char(hire_date,'YYYY'),1996,1,0))"1996",
sum(decode(to_char(hire_date,'YYYY'),1997,1,0))"1997",
sum(decode(to_char(hire_date,'YYYY'),1998,1,0))"1998"
from employees;

9. 行列转换
select job_id "Job",
sum(decode(department_id,20,salary,0)) "dept_20",
sum(decode(department_id,50,salary,0)) "dept_50",
sum(decode(department_id,80,salary,0)) "dept_80",
sum(decode(department_id,90,salary,0)) "dept_90"
from employees
group by job_id;

第六章 多表查询
1.
select e.last_name,d.department_id,d.department_name
from employees e,departments d
where e.department_id=d.department_id;

2.
select distinct j.*
from employees e,jobs j
where j.job_id=e.job_id 
and e.department_id=80;


3.
select
e.last_name,d.department_name,d.location_id,
l.city
from employees e,departments d,locations l
where e.department_id=d.department_id
and d.location_id=l.location_id
and e.commission_pct is not null;

4.
select e.last_name,d.department_name
from employees e,departments d
where e.department_id=d.department_id
and lower(e.last_name) like '%a%';


5.
select
e.last_name,e.job_id,d.department_id,d.department_name
from employees e,departments d,locations l
where l.city='Toronto'
and e.department_id=d.department_id
and d.location_id=l.location_id;

6.
select
e.last_name "Employee",e.employee_id "Emp#",
m.last_name "Manager",m.manager_id "Mgr#"
from employees e,employees m
where e.manager_id=m.employee_id;

8.
select e.last_name,e.hire_date
from employees e,employees d
where d.hire_date<e.hire_date
and d.last_name='Davies'
;

9.
select e.last_name "Employee",e.hire_date "Emp_Hired",
m.last_name "Manager",m.hire_date "Mgr_Hired"
from employees e,employees m
where e.hire_date<m.hire_date
and e.manager_id=m.employee_id;


第七章 子查询
1.
select last_name,hire_date
from employees
where department_id=(select department_id from employees
where last_name='Zlotkey')
and last_name<>'Zlotkey';

2.
select employee_id,last_name
from employees
where salary>(select avg(salary) from employees);

3.
select employee_id,last_name
from employees
where department_id in(
select department_id
from employees
where last_name like '%u%'
);

4.
select last_name,department_id,job_id
from employees
where department_id in
(
   select department_id
   from departments
   where location_id=1700
);

5.
select last_name,salary
from employees 
where manager_id in(
select employee_id
from employees 
where last_name='King'
);

6.
select department_id,last_name,job_id
from employees
where department_id in
(
	select department_id
	from departments
	where department_name='Executive'
);

7.
select employee_id,last_name,salary 
from employees
where department_id in
(
	select department_id
	from employees
	where last_name like '%u%'
) and salary>(
	select avg(salary)
	from employees
);