oracle 查询上机实验

原创 2016年06月01日 23:26:47
查询是否有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
);

版权声明:本文为博主原创文章,未经博主允许不得转载。

oracle上机实验内容

这是oracle实验的部分代码,我花了一中午做的。 第一次上机内容 实验目的:熟悉ORACLE11G的环境 实验内容:   第二次上机内容 实验目标:掌握oracle体系结构,掌握sqlplus的运行...

《数据库系统》上机题_实验四:T-SQL查询、存储过程、触发器、完整性

T-SQL查询、存储过程、触发器、完整性   第一部分:T-SQL程序设计 (1).如果3号课程的平均成绩在80分以上,则输出“3号课程成绩良好”,否则输出“3号成绩一般” if (select...

操作系统第一次上机实验 进程控制(终极版)

实验一进程控制 一、      实验目的 1.       理解进程控制块的结构。  2.       理解进程各种状态之间的转换过程。 3.       掌握进程创建与撤消的方法。 4. ...

Android上机实验:身高计算器的实现

一、实验名称 身高计算器的实现 二、实验目标 1.掌握布局及控件的使用,会搭建常见布局 2.掌握程序调试方法,实现对程序的调试 3.实现身高计算器 三、实验操作人员 班级:##级物联网工...

数据库系统教程(第二版何玉洁)课后数据库上机实验答案

数据库系统教程(第二版何玉洁)课后数据库上机实验答案 数据操作语句,索引和视图,触发器和存储过程...

C++第一次上机实验-1

一、问题及代码 问题:输入两个整数a和b,输出它们的和及差。 代码: /* * 文件名称: cone * 作 者: 李瑞祥 * 完成日期: 2016 年 3 月 10 日 * 版 本...

第五次C++上机实验---数组分离

一:问题及代码 /*  * 文件名称:数组分离  * 作    者:王洋   * 完成日期:   2016   年  5   月 10   日  * 版 本 号:v1.0  * 对任务及求解方法的...

林子雨—大数据技术原理与应用—上机实验五

题目:MapReduce编程初级实践 实验环境:       操作系统:ubuntu16.04       hadoop版本:1.2.1(伪分布式)       JDK版本:1.8       Ec...

第五次c++上机实验报告

一、问题及代码 /* *文件名称:aaa *作        者:李钊 *完成日期:2017年4月24日 *版  本  号:vc++6.0 *对任务及求解方法的描述部分: *输入描述:无...

C++第二次上机实验——选择结构:项目2,项目3

一、问题及代码 [cpp] view plain copy /* * 文件名称:Ex1-1.cpp * 作    者:聂雪云 * 完成日期:2017 年 3 月 13 ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:oracle 查询上机实验
举报原因:
原因补充:

(最多只允许输入30个字)