oracle数据库方面的知识到今天已经整理了12篇。当然，这不是终点，这只是一个开始，希望我写的文章可以帮助更多初学数据库的童鞋快速上手，如果你觉得文章对你有帮助，那么恭喜你已经入门了，数据库里面的知识有很多，多到让你可以从入门到放弃。那么你可以通过该篇文章快速入门oracle中关于查询的各种姿势：

oracle课程大纲：

　   

　　

　　

　　

　　

　　

　　

　　

　　

　　

　　

　　

 

如果你想拿一些数据库的习题练习，下面的例子或许是一个不错的选择！

--题目

--1、新员工王小明，员工编号是11，性别是男，年龄30，岗位编号是5，岗位是测试工程师，部门编号是3，

--部门名称是测试部，薪水6000(基本工资2800，奖金3200);

select * from salary; --薪水表select * from employ; --员工表select * from dept; --部门表select * from station; --岗位表

1题答案

--薪水表insert into salary(salaryid,employid,basesalary,bonussalary) values(11,11,2800,3200);commit;--员工表insert into employ(ename,employid,sex,age,stationid,deptid) values('王小明',11,'男',30,5,3);commit;--部门表insert into dept(deptid,deptname) values(3,'测试部');commit;--岗位表insert into station values(5,'测试工程师');commit;

--2、王小明试用期过了，表现非常好，公司决定给他基本工资调薪10%，奖金调15%;

select * from salary; --薪水表select * from employ; --员工表select * from dept; --部门表select * from station; --岗位表update salaryset basesalary = basesalary + basesalary * 0.1,bonussalary = bonussalary + bonussalary * 0.15where salaryid = 11;commit;

--3、查询测试部门最高薪水，最低薪水，平均薪水，显示最高薪水，最低薪水，平均薪水;

select * from salary; --薪水表select * from employ; --员工表select * from dept; --部门表select * from station; --岗位表select t2.deptname 部门,max(t3.basesalary + t3.bonussalary) 最高薪,min(t3.basesalary + t3.bonussalary) 最低薪,avg(t3.basesalary + t3.bonussalary) 平均薪资from employ t1, dept t2, salary t3where t1.employid = t3.employidand t2.deptid = t1.deptidand t2.deptid = 3group by t2.deptname;

--4、查询所有部门的最高薪水，最低水，平均薪水，显示部门，最高薪水，最低薪水，平均薪水,并按部门名升序排序;

select * from salary; --薪水表select * from employ; --员工表select * from dept; --部门表select * from station; --岗位表 select t3.deptid 部门名称,t3.deptname 部门名字,max(t2.basesalary+t2.bonussalary) 最高薪,min(t2.basesalary+t2.bonussalary) 最低薪,avg(t2.basesalary+t2.bonussalary) 平均薪资from employ t1, salary t2, dept t3 where t1.employid=t2.employid and t1.deptid=t3.deptid group by t3.deptid,t3.deptname order by t3.deptname asc;

--5、统计测试部门有多少员工，显示员工数;

select * from salary; --薪水表select * from employ; --员工表select * from dept; --部门表select * from station; --岗位表select t2.deptname 部门名字, count(t1.employid) 人数from employ t1, dept t2where t1.deptid = t2.deptidand t2.deptid = group by t2.deptname;

--6、统计所有部门员工数，并按部门进行升序排序，显示部门，员工数;

select * from salary; --薪水表select * from employ; --员工表select * from dept; --部门表select * from station; --岗位表 select t2.deptname 部门名字, count(t1.employid) 员工总数from employ t1, dept t2where t1.deptid = t2.deptidgroup by t2.deptnameorder by t2.deptname asc;

--7、查询所有姓王的所有员工信息;

select * from salary; --薪水表select * from employ; --员工表 select * from dept; --部门表select * from station; --岗位表select *from employ t1, salary t2, dept t3, station t4where t1.employid = t2.employidand t1.deptid = t3.deptidand t1.stationid = t4.stationidand t1.ename like '%王%'order by t1.employid;

--8、按部门，性别统计平均薪水;

select t2.deptname 部门名字,t1.sex 性别,round(avg(t3.basesalary + t3.bonussalary)) 平均薪水from employ t1, dept t2, salary t3where t1.deptid = t2.deptidand t1.employid = t3.employidgroup by t2.deptname, t1.sex;

--9、查询30到40岁的平均薪水;

select t1.ename 姓名, avg(t2.basesalary + t2.bonussalary) 平均薪资from employ t1, salary t2where t1.employid = t2.employidand t1.age between 30 and 40group by t1.ename;

--10、查询测试部薪水最高的员工，显示员工姓名；

select * from salary; --薪水表select * from employ; --员工表 select * from dept; --部门表select * from station; --岗位表select t1.ename 员工姓名,t2.deptname 部门名字,max(t3.basesalary + t3.bonussalary) 薪水from employ t1, dept t2, salary t3where t1.deptid = t2.deptidand t1.employid = t3.employidand t2.deptid = 3group by t2.deptname, t1.ename;

--11、删除王小明的所有信息

select * from salary; --薪水表select * from employ; --员工表 select * from dept; --部门表select * from station; --岗位表delete from salary where salaryid=11;delete from employ where ename='王小明';delete from dept where deptid=3;delete from station where stationid=5;