1、查询id为90的部门的所有员工
select * from employees where department_id =90;
2、查询last_name为Whalen的员工
select * from employees where last_name = 'Whalen';
3、查询入职时间为7-6月-1994
select * from employees where hire_date='17-JUN-2003';
4、在employees表中来查询所有的部门编号 不允许重复出现
select distinct department_id from employees;
5、工资在2500到3500之间的雇员信息 包含边界值
select *from employees where salary between 2500 and 3500;
select * from employees where salary >= 2500 and salary <= 3500;
6、查询雇员编号为 115 116 117 的雇员信息
select * from employees where employee_id = 115 or employee_id=116 or employee_id=117;
select * from employees where employee_id in(115,116,117);
7、查询雇员名以字母A开头的雇员信息 需要结合% 、_两个通配符使用
select * from employees where first_name like 'A%';
8、查询雇员名中的第二个字母为a的雇员信息
select * from employees where first_name like '_a%';
9、查询雇员名字中包含字母a的雇员信息
select * from employees where first_name like '%A%';等同与
select * from employees where first_name like 'A%';
10、IS NULL 查询没有奖金的雇员信息
select * from employees where commission_pct is null;
--IS NULL 查询有奖金的雇员信息
select * from employees where commission_pct is not null;
11、查询所有的雇员信息 根据first_name 进行降序排序 ,对于值为字符串的字段的排序 是根据字典顺序
select * from employees order by first_name ;
12、查询所有的100部门的员工信息 并根据入职时间进行升序显示
select * from employees where department_id =100 order by hire_Date;
13、 计算所有的雇员的年薪(yearSalary)包含奖金 并按照年薪进行排序
select first_name ,last_name ,salary ,salary * (1 + NVL(commission_pct,0))*12 YEARSALARY
from employees
ORDER BY YEARSALARY;
14、查询所有的雇员信息 并按照入职时间排序 如果入职时间相同 则按照薪资从高到底排序
select * from employees order by hire_date desc ,salary asc;
15、将字符串转换为小写
select LOWER('ORACLE SQL') from dual;
16、将字符串转换为大写
select UPPER('oracle sql') from dual;
17、将每个单词的首字母大写
select initcap('oracle sql') from dual;
18、查询 lastname 为De Haan 的雇员信息
select * from employees where last_name= initcap('De Haan');
19、substr arg1:要截取字符串 arg2:截取的开始位置 arg3:截取的长度
select substr('helloworld',2,5) from dual;
20、查找某一个字符或字符串在字符串中出现的位置 如果是一个字符串 则是首字母的位置,如果查找的字符 或字符串不存在 则返回0
select instr('helloworld','wor') from dual;
21、LPAD RPAD 判断字符串的长度是=是否满足给定的长度 如果不足 则在左边或右边使用给定的字符来补充
SELECT LPAD('helloworld',15,'*') from dual;
SELECT RPAD('helloworld',15,'*') from dual;
22、TRIM 表示去除字符串两端的空格
select length('helloworld ')from dual;
select length(Trim('helloworld ')) from dual;
23、REPLACE 替换
select replace('hello world','l','L')from dual;
24、ROUND: 四舍五入
SELECT ROUND(45.9362,0),ROUND(45.9362,2),ROUND(145.9362,-2) FROM dual;
25、TRUNC: 截断
SELECT TRUNC(45.9362,0),TRUNC(45.9362,2),TRUNC(145.9362,-2) FROM dual;
26、MOD: 求余
SELECT MOD(1560,200) FROM dual;
27、查看系统当前日期
select sysdate from dual;
28、查询employees表中每个员工入职的工龄(年)
select first_name,last_name, round((sysdate-hire_date)/365) from employees;
29、查询employees表中每个员工入职的月数
select first_name,last_name,months_between(sysdate,hire_date) from employees;
30、add_months
select add_months(sysdate,3)from dual;
31、next_day 下一个星期几(右第二个参数指定)的日期
select next_day(sysdate,'FRIDAY') from dual;
32、当前月的最后一天的日期
select last_day(sysdate) from dual;
33、12小时制
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss')from dual;
34、24小时制
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;
35、查询入职时间为1994年之前的员工 将条件中的日期转换为date再和hiredate进行比较
select first_name,last_name,hire_date from employees where to_char(hire_date,'yyyy-mm-dd') < '2004-01-01';
36、查询所有雇员的薪资 并以$符开头 整数部分三位使用千位符进行分隔 小数部分使用00表示
select first_name,last_name,to_char(salary,'L999,999,999,999,999.00')from employees;
37、NVL2 (expr1, expr2, expr3) : expr1不为NULL,返回expr2;为NULL,返回expr3
select nvl2(null,null,100)from dual;
38、查询员工的奖金率,若为空,返回0.01,若不为空,返回实际奖金率+0.015
select commission_pct,nvl2(commission_pct,commission_pct+0.015,0.01) from employees;
39、判断雇员的first_name和last_name的长度 如果长度相同则返回null 不同则返回first——name的长度
select length(first_name) rexp1,length(last_name) rexp2 ,nullif(length(first_name),length(last_name)) from employees;
40、 COALESCE 返回第一个非空的值 如果三个都为空 则返回null
select COALESCE(null,null,null) from dual;
41、查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数
select first_name,last_name,salary,department_id,
case department_id when 10 then salary * 1.1
when 20 then salary * 1.2
when 30 then salary * 1.3
else salary
end
from employees
where department_id in(10,20,30);
42、查询所有的职位 将职位名称全部翻译成中文
select job_id,job_title,
decode(job_title,'President','pre','经理',
'Finance Manager','财务总监',
'Sales Manager','销售总监',
'Programmer' ,'程序员',
'other'
)
from jobs;
43、根据雇员薪资 计算该雇员的个人所得税的税率,个人所得税的起征点为2000,
2000-3000 税率为0.09
3000-5000 税率为0.20
5000-7000 税率为0.30
7000-9000 税率为0.40
9000-12000 税率为0.42
12000-14000 税率为0.44
1400以上税率为0.45
select first_name,last_name,salary,
decode(trunc(salary/2000,0),1,0.09,2,0.20,3,0.30,4,0.40,5,0.42,6,0.44,0.45)
tax_rate
from employees;
44、查询雇员中没有领导的雇员信息,如果该雇员没有经理,则将manager_id 修改为’No Manager’,在此处 manager_id为number类型, 因此, 在使用nvl函数时, 必须先转换为字符串
select first_name,last_name,manager_id+100,
nvl(to_char(manager_id),'no manager')||100--隐式转换 将此处的100转换为字符串
from employees;
45、oracle的等值连接
select first_name,last_name,employees.department_id,department_name
from employees,departments
where employees.department_id = departments.department_id;
46、查询出公司员工的 last_name, department_name, city ,country,region_name
select e.last_name,d.department_name,l.city,c.country_name,r.region_name
from employees e ,departments d,locations l,countries c ,regions r
where e.department_id = d.department_id and d.location_id = l.location_id and l.country_id = c.country_id and c.region_id = r.region_id;
47、根据雇员的薪资来查询雇员的基本信息及职位名称 使用scott用户的表
select e.ename,e.job,e.sal ,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal;
48、左外连接
select first_name,last_name,dept.department_id,department_name
from employees emp ,departments dept
where emp.department_id = dept.department_id(+);
49、当两张表查询时 对于两张表中都存在的字段 则必须指明所查询的字段来自于那张表 使用表名.字段名来指定,在等值连接中 如果一张表中存在null值 而另一张表中不存在 则不会显示该条记录
select first_name,last_name,dept.department_id,department_name
from employees emp ,departments dept
where emp.department_id = dept.department_id(+);
50、自连接
查询所有雇员的信息及其经理的姓名
此时数据来自一张表 但是我们可以将一张表看作两张表
都是employees表 一张看作雇员表 另一张看作经理表
select * from employees;
select e1.employee_id empid ,e1.last_name empname, e1.manager_id mid ,e2.employee_id manager_id, e2.last_name manage_name
from employees e1,employees e2
where e1.manager_id = e2.employee_id(+);
51、自然连接: NATURAL JOIN 子句,会以两个表中具有相同名字的列为条件创建等值连接
SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations ;
52、查询所有的雇员及部门信息,使用 USING 子句创建连接
select * from employees join departments using(department_id);
53、使用ON 子句创建连接
select * from employees e join departments d on(e.department_id = d.department_id);
54、使用 ON 子句创建多表连接
select * from employees e
join departments d on (e.department_id = d.department_id)
join locations l on (d.location_id = l.location_id);
55、左外连接
select * from employees e
left outer join departments d
on (e.department_id = d.department_id);
56、分组函数作用于一组数据,并对一组数据返回一个值。查询所有雇员中的最高工资
select max(salary) from employees;
57、计算所有雇员的平均工资 工资总和 最高工资 最低工资
select avg(salary),sum(salary),max(salary),min(salary)from employees;
58、查询入职时间最早和最晚的雇员信息
select * from employees where hire_date = (select min(hire_date) from employees);
59、查询所有雇员的平均奖金率
select avg(commission_pct) from employees;
60、查询所有雇员的平均奖金率
select avg(commission_pct) from employees;--avg只针对有奖金率的员工进行均值的计算
select avg(nvl(commission_pct,0)) from employees;--avg是针对所有员工
61、在雇员表中统计部门的数量
select count(distinct department_id) from employees;
62、查询每个部门的雇员的平均工资
select department_id, avg(salary)from employees
group by department_id;
63、查询每个部门入职时间最早入职时间
select department_id,min(hire_date) m
from employees
group by department_id ;
64、查询所有部门最工资大于10000的部门信息
select department_id,max(salary)
from employees
group by department_id
having max(salary) > 10000;
65、查询所有部门最工资大于10000的部门信息
select department_id,max(salary)
from employees
group by department_id
having max(salary) > 10000;
66、显示各部门平均工资的最大值(嵌套组函数)
select max(avg(salary))
from employees
group by department_id;
67、查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
select manager_id,min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary) >= 6000;
68、查询所有部门的名字,location_id,员工数量和工资平均值
select department_name,location_id,count(employee_id),avg(salary)
from employees e , departments d
where e.department_id(+) = d.department_id
group by department_name,location_id
69、查询公司在2001-2005年之间,每年雇用的人数
select count(*) total,
count(decode(to_char(hire_date,'yyyy'),'2001',1,null)) "2001",
count(decode(to_char(hire_date,'yyyy'),'2002',1,null)) "2002",
count(decode(to_char(hire_date,'yyyy'),'2003',1,null)) "2003",
count(decode(to_char(hire_date,'yyyy'),'2004',1,null)) "2004",
count(decode(to_char(hire_date,'yyyy'),'2005',1,null)) "2005"
from employees
where to_char(hire_date,'yyyy') in('2001','2002','2003','2004','2005');
70、谁的工资比 Abel 高
select * from employees where salary >(
select salary from employees where last_name = 'Abel');
71、查询 工资低于平均工资的雇员信息
select * from employees
where salary <
(select avg(salary) from employees);
72、返回job_id与141号员工相同,salary比143号员工多的员工 姓名,job_id 和工资
select first_name,last_name,job_id,salary
from employees
where job_id = (select job_id from employees where employee_id =141)
and salary > (select salary from employees where employee_id =143)
73、ANY 小于子查询中的任意一个
题目:返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
select employee_id,last_name,job_id,salary
from employees
where salary < any(
select salary from employees where job_id ='IT_PROG')
74、ALL 小于所有的 那就是比最小的还小
select employee_id,last_name,job_id,salary
from employees
where salary < ALL(
select salary from employees where job_id ='IT_PROG')
75、rownum 自动生成 与数据无关 在每次的查询中总是从1开始:显示前20行记录
select rownum ,employee_id,first_name ,last_name from employees where rownum <= 20 ;
76、显示11 - 20 条记录 现在每页显示10记 录 显示第二页 11 -20
select * from (
select rownum r ,employee_id,first_name ,last_name from employees where rownum <= 20 ) temp
where temp.r >10;
77、为表中所有的字段插入对应的值
insert into student(stu_id,stu_name,stu_age,stu_sex)
values(1,'zhangsan',23,'男');
78、为表中所有的字段都插入值此时可以省略字段列表
insert into student
values(3,'wangwu',20,'man');
79、在插入的过程中可以使用&符号来绑定一个参数该参数的值 是由用户输入的,对于字符串类型的数据 需要加单引号
insert into student (stu_id,stu_name,stu_age,stu_sex)
values(&stu_id,'&stu_name',&stu_age,'&stu_sex');
80、从其他表中拷贝数据该操作需要表存在
create table emp
(
employee_id NUMBER(6) not null,
first_name VARCHAR2(20),
last_name VARCHAR2(25),
email VARCHAR2(25),
phone_number VARCHAR2(20),
hire_date DATE,
job_id VARCHAR2(10),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
manager_id NUMBER(6),
department_id NUMBER(4)
)
insert into emp
select * from employees;
81、查询当前所用户所使用的字符集
select userenv('language') from dual;
82、查询服务端的字符集
select * from v$nls_parameters;
83、更新数据,必须为更新操作设置条件如果没有设置条件将会使表中所有的记录都被更新
update student
set stu_sex = '女';
update student
set stu_sex = '男'
84、调整与employee_id 为200的员工job_id相同的员工的department_id。为employee_id为100的员工的department_id。
update employees
set department_id =(select department_id from employees where employee_id=100 )
where employee_id=200;
85、删除student表中的记录 删除记录 也必须条件删除条件 否则就会删除表中所有的记录
select * from student;
delete from student where stu_id = 2;
86、从student表中删除class表中班级名称中含2字符的部门id
delete from student
where cls_id = (select cls_id from class where cls_name like '%2%');
87、从student表中删除class表中班级名称中含2字符的部门id
delete student
where cls_id = (select cls_id from class where cls_name like '%2%');
88、创建表空间 data_test 表空间名称 datafile 指定数据文件的存储路径 data_1.dbf文件名称 size表空间的默认大小
create tablespace data_test datafile 'C:\app\Adminstrator\oradata\orcl\data_1.dbf' size 20M;
89、创建用户zb 并指定密码为zb 默认表空间为data_test
create user zb identified by zb default tablespace data_test;
90、为用户创建临时表空间
create temporary tablespace user_temp
tempfile 'C:\app\Adminstrator\oradata\orcl\user_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
91、创建一张user表
create table users(
id number(18),
user_name varchar2(50),
password varchar2(18)
)
--在命令窗口中查看表结构
desc users;
92、使用 ALTER TABLE 语句追加, 修改, 或删除列的语法给现有的表增加一个新列
alter table emp
add (test varchar2(100))
--给现有的表修改一个列
alter table emp
modify (test number(10));
-- 删除表中的某一个存在的列
alter table emp
drop column test;
--重命名一个列
alter table emp
rename column SALARY to sal;
93、在创建表的同时 为表中的列条件非空约束
create table STUDENT
(
stu_id NUMBER not null,
stu_name VARCHAR2(50) CONSTRAINT stu_name_null not null,
stu_age NUMBER,
stu_sex VARCHAR2(10),
cls_id NUMBER
)
94、在添加外键约束的时候 对于所关联的表中的字段(class表中的cls_id) 该字段必须时主键列或者有唯一约束
create table STUDENT
(
stu_id NUMBER not null,
stu_name VARCHAR2(50) not null,
stu_age NUMBER,
stu_sex VARCHAR2(10),
cls_id NUMBER,
CONSTRAINT stu_cls_fk FOREIGN KEY(cls_id)
REFERENCES class(cls_id)--外键约束
);
95、检查约束
create table STUDENT
(
stu_id NUMBER not null,
stu_name VARCHAR2(50) not null,
stu_age NUMBER,
stu_sex VARCHAR2(10) ,
cls_id NUMBER,
CONSTRAINT stu_cls_fk FOREIGN KEY(cls_id)
REFERENCES class(cls_id) on delete set null,--外键约束 同时设置删除策略为级联置空
CONSTRAINT stu_sex_ck check (stu_sex in('男','女')),--检查约束
constraint stu_age_ck check(stu_age >18 and stu_age < 50)--设置年龄的范围为18-50岁
);
96、创建一个试图 该视图包含 雇员编号 姓名 job_id 部门名称 部门所在城市名称
**create view v_edl
as
select employee_id ,first_name ,last_name ,job_id, department_name,city
from emp e, departments d, locations l
where e.department_id = d.department_id and d.location_id = l.location_id;.
--查询试图
select * from v_edl;**
97、修改序列 步长改为10 在修改序列的时候 start with不能修改
alter SEQUENCE student_stutid_seq
INCREMENT BY 10
-- START WITH 100
MAXVALUE 9999999
NOCACHE
NOCYCLE;
98、给Student表插入数据
declare
V_id student.stu_id%type ;
V_name student.stu_name%type;
V_age student.stu_age%type ;
V_sex student.stu_sex%type ;
begin
insert into student(stu_id,stu_name,stu_age,stu_sex)
values(&V_id,'&V_name',&V_age,'&V_sex');
end;
99、根据用户输入的用户编号,确定用户的工资等级
declare
V_empno emp.employee_id%type := &V_empno;
V_sal emp.salary %Type;
begin
select salary into V_sal from emp where employee_id=V_empno;
if V_sal >= 10000 then
dbms_output.put_line('一级');
elsif V_sal >= 8000 and V_sal <10000 then
dbms_output.put_line('二级');
elsif V_sal >=5000 and V_sal <8000 then
dbms_output.put_line('三级');
else
dbms_output.put_line('四级');
end if;
end;
100、NULL 语句
declare
i number :=10;
begin
for i in reverse 1..10 loop
if i = 5 then
null;--什么都不做 类似于Java中;
else
dbms_output.put_line(i);
end if;
end loop;
end;