# oracle 查询上机实验

288人阅读 评论(0)

查询是否有hr用户

conn sys/oracle as sysdba;

desc dba_users；

select username,account_status from dba_users;

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,
as "SALARY" from employees;

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

7.
select distinct job_id,
'ST_MAN','B',
'IT_PROG','C',
'SA_REP','D',
'ST_CLERK','E',
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'
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
);


0
0

* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
个人资料
• 访问：235680次
• 积分：6434
• 等级：
• 排名：第3771名
• 原创：430篇
• 转载：1篇
• 译文：0篇
• 评论：17条
个人微信公众平台

来，聊一聊这扯淡的人生！

求打赏

意思一下就行了，哈！

友情链接
博客专栏
 算法侦探 文章：39篇 阅读：16421
 hibernate3小白入门 文章：17篇 阅读：8180
 Struts2小白入门 文章：17篇 阅读：9503
评论排行
最新评论