数据库练习

单表查询

查询所有职员的所有信息
select * from tbemp;
查询所有职员的姓名,电话,地址
select ename,etel,eaddr from tbemp;
查询所有女职员的详细信息
select * from tbemp where esex = '女';
查询年龄在24到26岁之间的职员的姓名,性别
select ename,esex from tbemp where eage >=24 and eage <=26;
select ename,esex from tbemp where eage between 24 and 26;
查询家住长沙的女职员的姓名,电话,地址
select * from tbemp where eaddr like '%长沙%' and esex = '女';
select * from tbemp where instr(eaddr,'长沙')>0 and esex = '女';
查询李云,孙一成,林笑的电话,地址
select eaddr,etel from tbemp where ename = '李云' or ename = '孙一成' or ename = '林笑';
select eaddr,etel from tbemp where ename in ('李云','孙一成','林笑');
查询郴洲和株洲的职员的姓名,性别,年龄
select ename,esex,eage from tbemp where eaddr like '%郴洲%' or eaddr like '%株洲%';
select ename,esex,eage from tbemp where instr(eaddr,'郴洲')>0 or instr(eaddr,'株洲')>0;
查询家住长沙,年龄在25到28岁之间的男职员的姓名
select ename from tbemp where eaddr like '%长沙%' and eage between 25 and 28 and esex ='男';
select ename from tbemp where instr(eaddr,'长沙')>0 and eage between 25 and 28 and esex ='男';
查询邮件地址为空的职员
select * from tbemp where eemail is null;

多表查询

所有职员,所有商品的销售情况,包括:职员姓名,商品类别,商品品牌,商品规格,销售数量,销售价值,销售时间,按销售时间从高到低排列
select e.ename,p.ptype,p.pmark,p.pspec,s.pqty,s.pamount,s.sdate from tbemp e left join tbsales s on e.eid = s.eid full outer join tbprod p on s.pid=p.pid order by s.sdate asc;
笔记本4月份的销售情况,包括:商品编号,商品品牌,商品规格,销售数量,销售价值,按销售数量从低到高排列
select p.ptype ,s.pid,p.pmark,p.pspec,s.pqty,s.pamount from tbsales s right join tbprod p on s.pid = p.pid where to_char(s.sdate,'mm')= 04 and p.ptype = '笔记本' order by s.pqty asc;
李云3月份的销售情况,包括:商品编号,销售数量,销售价值
select s.pid,s.pqty,s.pamount from tbemp e left join tbsales s on e.eid = s.eid where e.ename = '李云' and to_char(s.sdate,'mm')= 03;
长虹29英寸纯平艺术电视机的销售情况,包括:销售数量,销售价值,销售时间
select s.pqty,s.pamount,s.sdate from tbsales s right join tbprod p on s.pid = p.pid where p.pmark='长虹' and p.pspec like '29英寸纯平艺术' and p.ptype = '电视机';
长沙男职员4月份的销售情况,包括:职员姓名,商品类别,商品品牌,商品规格,销售数量,销售价值
select e.ename,p.ptype,p.pmark,p.pspec,s.pqty,s.pamount from tbemp e left join tbsales s on e.eid = s.eid full outer join tbprod p on s.pid = p.pid where instr(eaddr,'长沙')>0 and to_char(s.sdate,'mm')=4 and esex='男';

合计

总共有多少个职员？
select count(*)职员个数 from tbemp;
长虹电视机被销售了多少次?
select sum(pqty) 销售数量 from tbsales s right join tbprod p on s.pid = p.pid and p.pmark = '长虹'and p.ptype='电视机';
年龄最小的女职员的年龄
select min(eage) as 女职员最小年龄 from tbemp where esex='女';
笔记本单笔销售价值最高的单笔销售价值;
select max(s.pamount)最高单笔销售价值 from tbsales s right join tbprod p on s.pid = p.pid where p.ptype='笔记本';
熊猫电视机的平均销售价格

select avg(s.pamount) from tbsales s right join tbprod p on s.pid = p.pid where p.ptype ='电视机'and p.pmark='熊猫';
select avg(s.pamount)/avg(s.pqty) from tbsales s right join tbprod p on s.pid = p.pid where p.ptype ='电视机'and p.pmark='熊猫';
紫光笔记本4月份的销售总额
select sum(s.pamount)销售总额 from tbsales s right join tbprod p on s.pid = p.pid and p.pmark='紫光'and p.ptype ='笔记本' and to_char(s.sdate,'mm')=04;

分组与合计

按类型,品牌统计商品的销售总数量,销售总数量低于20的不统计
select p.ptype,p.pmark,sum(s.pqty)销售总数量 from tbsales s right join tbprod p on s.pid = p.pid group by p.ptype,p.pmark having sum(s.pqty)>=20;
按品牌,规格统计笔记本的平均销售价格,按销售价格从高到低的顺序显示
select p.pmark,p.pspec,avg(s.pamount/s.pqty) from tbsales s right join tbprod p on s.pid = p.pid where p.ptype= '笔记本' group by p.pmark,p.pspec order by avg(s.pamount/s.pqty) desc;
select p.pmark,p.pspec,avg(s.pamount) from tbsales s right join tbprod p on s.pid = p.pid where p.ptype= '笔记本' group by p.pmark,p.pspec order by avg(s.pamount/s.pqty) desc;
按姓名统计男职员3月份的销售总额,销售总额低于80000的不统计
select e.ename,sum(s.pamount)销售总额 from tbemp e left join tbsales s on e.eid = s.eid where e.esex = '男' and to_char(s.sdate,'mm')=03 group by e.ename having sum(s.pamount) >=80000;
找出销售总额最高的职员的姓名
select ename from (select e.ename,sum(s.pamount)a from tbemp e inner join tbsales s on e.eid = s.eid group by ename order by a desc ) where rownum =1;
按姓名统计每个职员一共完成了多少笔销售业务
select ename,count(s.pqty) from tbemp e left join tbsales s on e.eid = s.eid group by ename;
找出完成销售业务笔数最少的职员的姓名和他完成的业务数
select * from (select e.ename,count(s.pqty) c from tbemp e left join tbsales s on e.eid = s.eid group by e.ename order by c asc)where rownum = 1 ;
按性别统计职员的最大年龄和最小年龄
select esex,max(eage),min(eage) from tbEmp group by esex;