Oracle练习大全,简单查询和多表查询

最新推荐文章于 2022-06-08 14:43:47 发布

初识初始

最新推荐文章于 2022-06-08 14:43:47 发布

阅读量1.7k

点赞数 2

分类专栏： Oracle 文章标签： oracle

本文链接：https://blog.csdn.net/qq_45252842/article/details/109392579

版权

Oracle 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1.现在已知有以下表

s_dept-部门名称表
id name 部门名字 region_id 部门地域ID

s_emp-员工表
id       last_name 员工名字    first_name    userid
start_date 日期   comments      manager_id 经理ID
title 职称      dept_id    部门ID      salary    工资    commission_pct

s_region 部门地域表
id name部门地域名字

可以执行以下SQL语句生成对应的表和数据

drop table s_dept;

CREATE TABLE s_dept(
   id	NUMBER(7) 	CONSTRAINT s_dept_id_nn NOT NULL,
   name	VARCHAR2(25)	CONSTRAINT s_dept_name_nn NOT NULL,
   region_id	NUMBER(7),
   CONSTRAINT s_dept_id_pk PRIMARY KEY (id),
   CONSTRAINT s_dept_name_region_id_uk UNIQUE (name, region_id)
);





INSERT INTO s_dept VALUES (10, 'Finance', 1);
INSERT INTO s_dept VALUES (31, 'Sales', 1);
INSERT INTO s_dept VALUES (32, 'Sales', 2);
INSERT INTO s_dept VALUES (33, 'Sales', 3);
INSERT INTO s_dept VALUES (34, 'Sales', 4);
INSERT INTO s_dept VALUES (35, 'Sales', 5);
INSERT INTO s_dept VALUES (41, 'Operations', 1);
INSERT INTO s_dept VALUES (42, 'Operations', 2);
INSERT INTO s_dept VALUES (43, 'Operations', 3);
INSERT INTO s_dept VALUES (44, 'Operations', 4);
INSERT INTO s_dept VALUES (45, 'Operations', 5);
INSERT INTO s_dept VALUES (50, 'Administration', 1);
COMMIT;


------------------------------------------

drop table s_emp;

CREATE TABLE s_emp(
	id	NUMBER(7)	CONSTRAINT s_emp_id_nn NOT NULL,
	last_name	VARCHAR2(25)	CONSTRAINT s_emp_last_name_nn NOT NULL,
	first_name	VARCHAR2(25),
	userid		VARCHAR2(8),
	start_date	DATE,
	comments	VARCHAR2(255),
	manager_id	NUMBER(7),
	title		VARCHAR2(25),
	dept_id		NUMBER(7),
	salary		NUMBER(11, 2),
	commission_pct	NUMBER(4, 2),
	CONSTRAINT s_emp_id_pk PRIMARY KEY (id),
	CONSTRAINT s_emp_userid_uk UNIQUE (userid),
	CONSTRAINT s_emp_commission_pct_ck
	CHECK (commission_pct IN (10, 12.5, 15, 17.5, 20)));






INSERT INTO s_emp VALUES (1, 'Velasquez', 'Carmen', 'cvelasqu','03-MAR-90', NULL, NULL, 'President',50, 2500, NULL);
INSERT INTO s_emp VALUES (2, 'Ngao', 'LaDoris', 'lngao','08-MAR-90', NULL, 1, 'VP, Operations',41, 1450, NULL);
INSERT INTO s_emp VALUES (3, 'Nagayama', 'Midori', 'mnagayam','17-JUN-91', NULL, 1, 'VP, Sales',31, 1400, NULL);
INSERT INTO s_emp VALUES (4, 'Quick-To-See', 'Mark', 'mquickto','07-APR-90', NULL, 1, 'VP, Finance',10, 1450, NULL);
INSERT INTO s_emp VALUES (5, 'Ropeburn', 'Audry', 'aropebur','04-MAR-90', NULL, 1, 'VP, Administration',50, 1550, NULL);
INSERT INTO s_emp VALUES (6, 'Urguhart', 'Molly', 'murguhar','18-JAN-91', NULL, 2, 'Warehouse Manager',41, 1200, NULL);
INSERT INTO s_emp VALUES (7, 'Menchu', 'Roberta', 'rmenchu','14-MAY-90', NULL, 2, 'Warehouse Manager',42, 1250, NULL);
INSERT INTO s_emp VALUES (8, 'Biri', 'Ben', 'bbiri','07-APR-90', NULL, 2, 'Warehouse Manager',43, 1100, NULL);
INSERT INTO s_emp VALUES (9, 'Catchpole', 'Antoinette', 'acatchpo','09-FEB-92', NULL, 2, 'Warehouse Manager',44, 1300, NULL);
INSERT INTO s_emp VALUES (10, 'Havel', 'Marta', 'mhavel','27-FEB-91', NULL, 2, 'Warehouse Manager',45, 1307, NULL);
INSERT INTO s_emp VALUES (11, 'Magee', 'Colin', 'cmagee','14-MAY-90', NULL, 3, 'Sales Representative',31, 1400, 10);
INSERT INTO s_emp VALUES (12, 'Giljum', 'Henry', 'hgiljum','18-JAN-92', NULL, 3, 'Sales Representative',32, 1490, 12.5);
INSERT INTO s_emp VALUES (13, 'Sedeghi', 'Yasmin', 'ysedeghi','18-FEB-91', NULL, 3, 'Sales Representative',33, 1515, 10);
INSERT INTO s_emp VALUES (14, 'Nguyen', 'Mai', 'mnguyen','22-JAN-92', NULL, 3, 'Sales Representative',34, 1525, 15);
INSERT INTO s_emp VALUES (15, 'Dumas', 'Andre', 'adumas','09-OCT-91', NULL, 3, 'Sales Representative',35, 1450, 17.5);
INSERT INTO s_emp VALUES (16, 'Maduro', 'Elena', 'emaduro','07-FEB-92', NULL, 6, 'Stock Clerk',41, 1400, NULL);
INSERT INTO s_emp VALUES (17, 'Smith', 'George', 'gsmith','08-MAR-90', NULL, 6, 'Stock Clerk',41, 940, NULL);
INSERT INTO s_emp VALUES (18, 'Nozaki', 'Akira', 'anozaki','09-FEB-91', NULL, 7, 'Stock Clerk',42, 1200, NULL);
INSERT INTO s_emp VALUES (19, 'Patel', 'Vikram', 'vpatel','06-AUG-91', NULL, 7, 'Stock Clerk',42, 795, NULL);
INSERT INTO s_emp VALUES (20, 'Newman', 'Chad', 'cnewman','21-JUL-91', NULL, 8, 'Stock Clerk',43, 750, NULL);
INSERT INTO s_emp VALUES (21, 'Markarian', 'Alexander', 'amarkari','26-MAY-91', NULL, 8, 'Stock Clerk',43, 850, NULL);
INSERT INTO s_emp VALUES (22, 'Chang', 'Eddie', 'echang','30-NOV-90', NULL, 9, 'Stock Clerk',44, 800, NULL);
INSERT INTO s_emp VALUES (23, 'Patel', 'Radha', 'rpatel','17-OCT-90', NULL, 9, 'Stock Clerk',34, 795, NULL);
INSERT INTO s_emp VALUES (24, 'Dancs', 'Bela', 'bdancs','17-MAR-91', NULL, 10, 'Stock Clerk',45, 860, NULL);
INSERT INTO s_emp VALUES (25, 'Schwartz', 'Sylvie', 'sschwart','09-MAY-91', NULL, 10, 'Stock Clerk',45, 1100, NULL);
COMMIT;


---------------------------------------------------

drop table s_region;

CREATE TABLE s_region(
	id	NUMBER(7)	CONSTRAINT s_region_id_nn NOT NULL,
	name	VARCHAR2(50)	CONSTRAINT s_region_name_nn NOT NULL,
	CONSTRAINT s_region_id_pk PRIMARY KEY (id),
	CONSTRAINT s_region_name_uk UNIQUE (name)
);



INSERT INTO s_region VALUES (1, 'North America');
INSERT INTO s_region VALUES (2, 'South America');
INSERT INTO s_region VALUES (3, 'Africa / Middle East');
INSERT INTO s_region VALUES (4, 'Asia');
INSERT INTO s_region VALUES (5, 'Europe');
COMMIT;

练习1

1)查询员工的工资和last_name，对工资进行升序排列
select id,last_name from s_emp
order by salary asc;

2)查询员工的工资和last_name，对last_name进行降序排列
select salary,last_name from s_emp
order by last_name desc;

3)查询员工的工资和last_name，先对salary进行降序排列，再对last_name进行升序排列
select salary,last_name from s_emp
order by salary desc,last_name asc;

4)请举例说明空值的排序规则
升序排列，默认情况下，null值排后⾯
降序排序，默认情况下，null值排前⾯

5)查询41号部门员工的信息？
select * from s_emp
where dept_id=41;

6)查询工资大于1000的员工的信息？
select * from s_emp
where salary > 1000;

7)查询工资大于1000小于1500员工的信息，使用两种方式来做？
select * from s_emp
where salary >1000 and salary <1500;

select * from s_emp
where salary between 1000 and 1500;

8）查询41或者42部门员工的信息，使用两种方式来做？
   select * from s_emp
   where dept_id=41 or dept_id=42;

   select * from s_emp
    where dept_id in(41,42);

9）查询last_name包含n的员工的信息？
select * from s_emp
where last_name like '%n%';

10）查询last_name包含n或者N的员工的信息？
select * from s_emp
where last_name like '%n%' or last_name like '%N%';

11)查询last_name第二个字母为g的员工的信息？
select * from s_emp
where last_name like '_g%';

12)查询last_name最后一个字母为o的员工的信息？
select * from s_emp
where last_name like '%o';

13)查询last_name以N开头的员工的信息？
select * from s_emp
where last_name like 'N%';

14)先插入这条数据
   insert into s_emp(id,last_name)
   values(999,'_briup');
   commit;

   然后查询以_开头的员工的信息
   select * from s_emp
   where last_name like '/_%' escape '/';

15)找出部门41中所有经理和部门43中的所有办事员的详细资料
   经理职称为:Warehouse Manager
   办事员的职称为:Stock Clerk
   select * from s_emp
   where (title='Warehouse Manager' and dept_id =41 )
   or (title='Stock Clerk' and dept_id=43);

16)找出部门41中所有经理、部门43中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料
   select * from s_emp
   where (title='Warehouse Manager' and dept_id =41 )
   or (title='Stock Clerk' and dept_id=43)
   or ((title!='Warehouse Manager' and title!='Stock Clerk')and salary>=2000);

17)显示不带有'S'的雇员姓名
select '姓名:' || last_name || '-' || first_name name from s_emp
where last_name not like '%S%' and first_name not like '%S%';

18)显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select '姓名:' || last_name || '-' || first_name name from s_emp
order by start_date asc;

19)显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而职位相同时按薪金升序
select '姓名:' || last_name || '-' || first_name name,title,salary from s_emp
order by title desc,salary asc;

20)查询12个月的年薪大于18000的员工的信息
select * from s_emp
where salary*12>18000;

21)查询13个月的年薪在10000到14000之间的员工信息
select * from s_emp
where salary*13 between 10000 and 14000;

22)查询41号部门工资大于1400的员工信息
select * from s_emp
where dept_id=41 and salary>1400;

23）查询除41,42号部门12个月的年薪大于17000员工的信息
select * from s_emp
where (dept_id!=41 and dept_id!=42) and salary*12>17000;

练习二

1)显示"hello world"字符串大写的形式
select upper('hello world') from dual;

2)显示"HELLO WORLD"字符串小写的形式
select lower('HELLO WORLD') from dual;

3)显示"hello world"符串的首字母大写，其他小写的形式
select initcap('hello world') from dual;

4）将hello字符串后拼接world字符串
select hello || world from dual;
select concat('hello','world') from dual;

5)求hello world字符串的第三个字符到第八个字符的子字符串？
select substr('hello world',3,6) from dual;

6) 求hello world字符串的长度
select length('hello world') from dual;

7)查询员工的全名和工资，并且全名以大写的方式显示，并且first_name的长度大于6，最后工资降序排序
全名：first_name.last_name
select upper(first_name || '.'|| last_name) from s_emp where length(first_name)>6 order by salary desc;

8)执行下面的sql语句，看看执行后结果，分析round函数的含义？
正数：精确到第n位，
0：精确到个位
负数：精确到第(n+1)位，第n位是四舍五入的哪一位
select round(45.67) from dual;     46
select round(45.67,1) from dual;   45.7
select round(45.67,2) from dual;   45.67
select round(45.67,-1) from dual; 50
select round(45.67,-2) from dual; 0
select round(55.67,-2) from dual; 100
   round 表示四舍五入,后面有两个参数,第二个参数不写时表示精确到个位数
   第二个参数是正数时表示精确到小数点后几位,是负数时表示精确到小数点前几位

9)执行下面的sql语句，看看执行后结果，分析trunc函数的含义？
select trunc(45.67) from dual;     45
select trunc(45.67,1) from dual;   45.6
select trunc(45.67,2) from dual;   45.67
select trunc(45.67,-1) from dual; 40
select trunc(45.67,-2) from dual; 0
select trunc(55.67,-2) from dual; 0
   trunc表示截取,后面有两个参数,第二个参数不写时表示截取到个位数
   第二个参数是正数时表示截取到小数点后几位,是负数时表示截取到小数点前几位

10)执行下面的sql语句，看看执行后结果，分析mod函数的含义？
select mod(1500,400) from dual; 300
mod表示取余数,前除以后取余数;

11)显示当前时间，查询当前时间是这年的第几天？是这个星期的第几天？是这个月的第几天？
select to_char(sysdate , 'ddd') ,to_char(sysdate , 'd'),to_char(sysdate , 'dd')from dual;
select to_char(sysdate,'d dd ddd') from dual;

12)显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序
select last_name,start_date from s_emp order by to_char(start_date,'mm') asc;
select to_char(start_date,'yyyy') year ,to_char(start_date,'mm') month from s_emp order by month asc;

13)找出在（任何年份的）2月受聘的所有雇员
select id,last_name,start_date from s_emp where to_char(start_date,'mm') = 2;

14)显示所有雇员的姓名以及满10年服务年限的日期
select last_name,add_months(start_date,12*10) from s_emp;
select last_name,start_date from s_emp where months_between(sysdate,start_date)>120;

15)对于每个雇员，显示其加入公司的天数
select id, last_name,trunc(sysdate - start_date,0) as "加入天数:" from s_emp ;

16)显示所有雇员的姓名的前三个字符
select substr(last_name,1,3) from s_emp;

17)显示正好为6个字符的雇员姓名
select last_name from s_emp where length(last_name)=6;

18)显示只有首字母大写的所有雇员的姓名
   select last_name from s_emp where last_name = initcap(last_name) ;
   //select initcap(last_name) from s_emp;
19)以年、月和日显示所有雇员的服务年限
年：to_number(to_char(sysdate,'yyyy'))-
   to_number(to_char(start_date,'yyyy'))
月：months_between(sysdate,start_date)
日：sysdate-start_dat
   select last_name,(to_number(to_char(sysdate,'yyyy'))-trunc(to_number(to_char(start_date,'yyyy')))) ||'年' ||
   mod(trunc(months_between(sysdate,start_date)),12) || '月'
   || to_char(round(sysdate-(add_months(start_date,(months_between(sysdate,start_date)))))) || '日' from s_emp;

   select last_name,to_char(trunc(months_between(sysdate,start_date)/12))||'年'||
   to_char(trunc(mod(months_between(sysdate,start_date),12)))||'月'||
   to_char(round(sysdate-(add_months(start_date,(months_between(sysdate,start_date))))))||'日' time
from s_emp;

20)找出早于23年之前受雇的雇员
   select id,last_name from s_emp where (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(start_date,'yyyy'))) > 23;

21)找出各月最后一天受雇的所有雇员
select id,last_name from s_emp where last_day(start_date) = start_date;

22)以这种2020/07/22格式来显示入职时间
select to_char(start_date,'yyyy/mm/dd') from s_emp;

练习三

1)查询所有员工的ID，名字和所在部门的名称
   select e.id,last_name,d.region_id from s_emp e,s_dept d
   where dept_id=d.id(+);

   select e.id,last_name,d.region_id from s_emp e left join s_dept d
   on dept_id=d.id;

2)查询员工的姓名和部门所在地区的名称
   select last_name,r.name
   from s_emp e ,s_dept d,s_region r
   where e.dept_id=d.id(+) and region_id=r.id(+);

3）查询部门名称包含sa的员工姓名和薪水
   select d.name,e.last_name,e.salary
   from s_emp e,s_dept d
   where dept_id = d.id and d.name like '%Sa%';

4）查询欧洲销售部门的薪水在1000到2000的员工信息
select * from s_emp e ,s_dept d,s_region r
where dept_id=d.id and region_id=r.id and r.name='Europe' and salary between 1000 and 2000;

5）查询部门名称是5位，该部门员工的薪水不等于1500，并按员工的薪水降序排序
   select e.id,d.name,salary
   from s_emp e,s_dept d
   where dept_id=d.id and length(d.name)=5 and salary !=1500
   order by salary desc;

6）创建下面这张表并插入数据

create table s_gender(
        id number(5) primary key,
       minSal number(7),
       maxSal number(7),
       name varchar2(20));
insert into s_gender values(1,0,1000,'蓝领');
insert into s_gender values(2,1000,1500,'白领');
insert into s_gender values(3,1500,2500,'金领');
commit;
然后查询所有员工月薪涨500后的工资等级？
   select e.id,last_name,salary+500,g.name
   from s_emp e,s_gender g
   where (salary+500) between g.minSal and g.maxSal
   order by salary;

7）查询所有员工所在部门的名称，但是需要把所有部门给查询出来.
   select d.name
   from s_emp e,s_dept d
   where dept_id(+)=d.id;

   select d.name
   from s_emp e right join s_dept d
   on dept_id=d.id;

8）查询员工所在部门的名称，包括没有部门号的员工
   select last_name,d.name
   from s_emp e,s_dept d
   where dept_id=d.id(+);

   select last_name,d.name
   from s_emp e left join s_dept d
   on dept_id=d.id;

9）查询员工所在部门的信息，没有部门号的员工,所有的部门都查询出来
   select last_name,d.name
   from s_emp e full join s_dept d
   on dept_id=d.id;

10）查询所有员工的经理的last_name,salary,包括没有经理的员工。
   select e1.id,e1.last_name,e2.id,e2.last_name,e2.salary
   from s_emp e1,s_emp e2
   where e1.manager_id = e2.id(+);

   select e1.id,e1.last_name,e2.id,e2.last_name,e2.salary
   from s_emp e1 left join s_emp e2
   on e1. manager_id = e2.id;

11）部门表中前7条记录？
select * from s_dept where rownum<=7;

12）部门表中第3条到第5条数据？
   select * from s_dept where rownum<=5
   minus
   select * from s_dept where rownum<3;

12)查询部门所在的地区名？
select r.name from s_dept s,s_region r where region_id = r.id;

练习四

1)查询所有员工的平均工资，最高工资最低工资，工资总和，还有有多少个员工?
select avg(salary),max(salary),sum(salary),count(id)
from s_emp;

2)查询每个部门的平均工资,对平均工资降序排序,平均工资大于1400
   select avg(salary) from s_emp,s_dept
   where s_emp.dept_id = s_dept.id
   having avg(salary)>1400
   group by s_dept.id
   order by avg(salary) desc;

3）查看各个部门的最高工资
   select max(salary) from s_emp , s_dept
   where dept_id = s_dept.id
   group by s_dept.id;

4)查看各个部门的员工数
select count(last_name) from s_emp
group by dept_id;

5)查询各个部门各个职称的平均薪水和最大薪水,并且平均薪水大于2000的部门id。
   select avg(salary),max(salary),d.id
   from s_emp s,s_dept d
   where dept_id=d.id
   group by dept_id,d.id
   having avg(salary)>2000;

6)查询title中不包含vp字符串的每个职位的平均薪水，并对平均薪水进行降序排列，
并且每个职位的总薪水大于5000。
   select avg(salary) from s_emp s,s_dept d
   where dept_id=d.id and title not like '%VP%'
   group by title
   having sum(salary)>5000
   order by avg(salary) desc;

7)查看每个区域部门数
   select count(r.name)
   from s_dept d,s_region r
   where d.region_id = r.id
   group by r.id;

8)查询南美地区的部门数？
   select count(d.name)
   from s_dept d,s_region r
   where r.name='South America' and d.region_id = r.id
   group by r.id;

9)查询南美地区工资大于1400的员工的信息？
   select e.id,e.last_name,e.salary
   from s_emp e,s_dept d,s_region r
   where dept_id = d.id and d.region_id = r.id
       and r.name='South America' and salary>1400;

练习五

1）查看和Ngao所在部门的区域id一样的所有员工id和名字
select id,last_name from s_emp where dept_id
= (select dept_id from s_emp where last_name = 'Ngao');

2）查询每一个部门的员工小于该部门的平均工资的员工的信息？

   select e.id,e.last_name,e.dept_id,e.salary from s_emp e,(select avg(salary) avg,
   dept_id from s_emp group by dept_id) e1
   where e.dept_id in(e1.dept_id) and salary<e1.avg
   order by dept_id,salary;

   select e.id,e.last_name,e.dept_id,e.salary from s_emp e,(select avg(salary) avg,
   dept_id from s_emp group by dept_id) e1
   where e.dept_id =e1.dept_id and salary<e1.avg;

3)查看职位和名字为Chang的员工一样的所有员工id和名字
   select id,last_name,title from s_emp where title
   =(select title from s_emp where last_name = 'Chang');

4)查看员工工资小于平均工资的所有员工的id和名字
   select id,last_name,salary from s_emp where salary
   <(select avg(salary) from s_emp) order by salary;

5)查看部门id和名字为Chang的部门相同或者区域ID为2的部门信息
   select id,name,region_id from s_dept
   where id=(select dept_id from s_emp where last_name = 'Chang')
   or region_id = 2;

6)查询员工所在区域为2的部门的员工的信息？
select id,last_name,dept_id from s_emp
where dept_id in(select id from s_dept where region_id =2);

7)查看部门平均工资大于32号部门平均工资的部门id
select avg(salary),dept_id from s_emp group by dept_id
having avg(salary)>(select avg(salary) from s_emp where dept_id=32);

8)查询工资大于Smith所在部门平均工资的员工的信息
   select id,last_name,salary from s_emp where
   salary>(select avg(salary) from s_emp where dept_id
   =(select dept_id from s_emp where last_name='Smith'))
   order by salary;

9)查看薪资高于Chang员工经理的经理薪资的员工信息
   select id,last_name,salary from s_emp where salary>(
   select salary from s_emp where id
   =(select manager_id from s_emp where last_name='Chang'))
   order by salary;
10)查看和Patel在同一个部门的员工的信息
   select id,last_name,dept_id from s_emp
   where dept_id in(select dept_id from s_emp where last_name='Patel');

初识初始

关注

2
点赞
踩
9

收藏

觉得还不错? 一键收藏
2
评论
Oracle练习大全,简单查询和多表查询

1.现在已知有以下表s_dept-部门名称表id name 部门名字 region_id 部门地域IDs_emp-员工表id last_name 员工名字 first_name userid start_date 日期 comments manager_id 经理IDtitle 职称 dept_id 部门ID salary 工资 commission_pcts_region 部门...
复制链接

扫一扫