SQL语句练习

a)按基本工资由大到小显示所有职工的职工号和实际工资
Select e.empno,i.base+i.subsidy+i.supply+i.comm-i.deduct-i.tax 实际工资 from emp e left join income i on e.empno=i.empno order by i.base desc;
b)按实际工资由大到小显示所有职工的职工号和实际工资
Select e.empno,i.base+i.subsidy+i.supply+i.comm-i.deduct-i.tax 实际工资 from emp e left join income i on e.empno=i.empno order by 实际工资;
c)按部门升序显示职工的职工号和实际工资
Select e.empno,i.base+i.subsidy+i.supply+i.comm-i.deduct-i.tax 实际工资 from emp e left join income i on e.empno=i.empno order by e.deptno;
d)求出所有职工的基本工资的和
Select sum(base) from income group by month;
e)求出所有职工的实际工资的和
Select sum(base+i.subsidy+i.supply+i.comm-i.deduct-i.tax) from income group by month;
f)求出5-10号没有被扣除工资的员工的基本工资和
Select sum(base) from income where (extract(month from date) between 5 and 10) and deduct=0;
g)求出所有职工基本工资的平均工资
Select avg(base) from income;
h)求出所有职工实际工资的平均工资
Select avg(base+i.subsidy+i.supply+i.comm-i.deduct-i.tax) from income;
i)统计出女工的数目
Select count() from emp where sex=’女’;
j)统计出天津的女工个数
Select count() from emp where sex=’女’ and native=’天津市’;
k)统计出本月被扣除工资的职工数和扣除的总金额
Select count(),sum(deduct) from income where deduct<>0;
l)统计出实际工资大于3000的职工数
Select count() from income
where base+i.subsidy+i.supply+i.comm-i.deduct-i.tax>3000;
m)统计出职工的最大工龄
Select max(age) from emp;
n)统计出女职工的最小年龄
Select min(age) from emp;
o)统计出天津女党员的最大年龄
Select max(age) from emp where native=’天津市’ and sex=’女’ and community=’是’;
p)统计出各部门最高的工龄
Select deptno,max(age) from emp group by deptno;
q)统计出最大的男工和女工的年龄
Select max(age) from emp group by sex;
r)统计出各部门的员工数
Select count() from emp group by deptno;
s)列出各部门的女工数
Select count() from emp where sex=’女’ group by deptno;
t)列出各部门的男工数和女工数
Select count() from emp group by deptno,sex;
u)列出员工数超过3人的部门号和员工数
Select deptno,count() from emp group by deptno having count()>3;
v)列出女工数超过1人的部门号和女工数
Select deptno,count() from emp where sex=’女’ and count(*)>1;
2.多表连接查询：
a)显示出所有职工的姓名和部门名
Select e.name,d.name from emp e left join dept d on e.deptno=d.deptno;
b)按部门升序显示出所有职工的姓名和部门名
Select e.name,d.name from emp e left join dept d on e.deptno=d.deptno order by d.name;
c)显示出所有职工的姓名、基本工资和入职时间
Select e.name,i.base,i.hiredate from emp e left join income i on e.empno=i.empno;
d)显示出所有职工的姓名、年龄、部门和实际工资
Select e.name,e.age,d.name,i.base+i.subsidy+i.supply+i.comm-i.deduct-i.tax 实际工资 from emp e left join dept d on e.deptn0=d.deptno
Left join income i on e.empno=i.empno;
e)显示出所有被扣工资的员工姓名和被扣金额
Select e.name,i.deduct from emp e left join income i on e.empno=i.empno
Where i.deduct=0;
f)按部门升序显示职工的姓名、所在部门和实际工资
Select e.name,d.name,i.base+i.subsidy+i.supply+i.comm-i.deduct-i.tax 实际工资 from emp e left join dept d on e.deptno=d.deptno left join income i on e.empno=i.empno order by d.name;
g)按部门名升序、实际工资降序显示部门名称、职工号和实际工资
Select d.name,e.empno,i.base+i.subsidy+i.supply+i.comm-i.deduct-i.tax 实际工资 from emp e left join dept d on e.deptno=d.deptno left join income i on e.empno=i.empno order by d.name,实际工资 desc;
h)统计出天津籍员工的最高实际工资
Select max(i.base+i.subsidy+i.supply+i.comm-i.deduct-i.tax) from emp e left join income i on e.empno=i.empno where e.native=’天津市’;
3.子查询
a)显示出工龄最大的职工姓名和工龄
Select name,extract(year from sysdate)-extract(year from hiredate) 工龄
From emp
Where extract(year from sysdate)-extract(year from hiredate)=
( Select min(extract(year from sysdate)-extract(year from hiredate)) from emp)
b)显示出最小的女职工的姓名和年龄
Select name,extract(year from sysdate)-extract(year from birth) 年龄
From emp
Where extract(year from sysdate)-extract(year from birth)=
( Select min(extract(year from sysdate)-extract(year from birth)) from emp)
c)统计出实际工资最高的北京籍员工的姓名和实际工资
Select e.name,i.base+i.subsidy+i.supply+i.comm-i.deduct-i.tax 实际工资
from emp e left join income i
on e.empno=i.empno
where (i.base+i.subsidy+i.supply+i.comm-i.deduct-i.tax) =
(select max(i.base+i.subsidy+i.supply+i.comm-i.deduct-i.tax)
from emp e left join income i
on e.empno=i.empno
Where e.native=’北京市’
);
d)显示出年龄最大的天津女党员
Select * from emp
Where sysdate-birth=
(select max(sysdate-birth) empno from emp
where native=’天津市’ and community=’是’)
e)显示出基本工资比天津市最高工资高的所有职工
Select * from emp e left join income i on e.empno=i.empno
Where i.base>
(Select max(i.base) from emp e left join income i on e.empno=i.empno
Where e.native=’天津市’);
f)所有非天津籍员工的补助增加100元作为思乡补助

g)删除所有上海籍员工的基本信息（注意删除的次序）
h)删除入职时间1990年以前的员工信息

10 创建一个名称为myproc的存储过程，该存储过程的功能是按部门名升序、实际工资降序显示部门名称、职工号和实际工资
11 创建一个名称为myind的创建索引,对职工的所在部门进行部门

用SELECT语句为"成绩"表各科成绩做一个A,B,C的等级评分
select （case when 成绩>=90 then ‘A’ when 成绩>=80 and 成绩<90 then ‘B’ esle ‘C’ end) 评级from table

limit

select * from tablename limit i,n
#i:查询结果索引值
#n:查询结果返回数量.
#默认i为0

查找入职员工时间排名倒数第三的员工所有信息

select * from employees order by hire_date desc limit 2,1;