Oracle基础试题与答案！
表结构:
create table tbEmp --职员表
(
eID number(7) primarykey, --职员编号
eName varchar(20) not null, --职员姓名
eSex varchar(2) not null --职员性别
check(esex in('男','女')),
eAge smallint not nullcheck(eage>=18), --职员年龄
eAddr varchar2(50) not null, --职员地址
eTel varchar2(30) not null, --职员电话
eEmail varchar2(30) --职员邮箱
)
创建序列
create sequence tbEmp_seq
minvalue 1
maxvalue 9999999
start with 1
increment by 1
cache 100;
基础数据
insert into tbemp (eid,ename,esex,eage,eaddr,etel)
values(tbEmp_seq.Nextval,'陈飞飞','男',22,'广东省广州市棠德小区6栋808号','020-3434123');
insert into tbemp(eid,ename,esex,eage,eaddr,etel)
values(tbEmp_seq.Nextval,'杜娟娟','女',23,'广东省广州市上社村1栋2号','020-34345233');
insert into tbemp (eid,ename,esex,eage,eaddr,etel)
values(tbEmp_seq.Nextval,'刘一刀','男',25,'广东省广州市城市广场606号','020-12344321');
insert into tbemp(eid,ename,esex,eage,eaddr,etel)
values(tbEmp_seq.Nextval,'张小杰','男',27,'广东省深圳4221工厂','0755-9876543');
insert into tbemp (eid,ename,esex,eage,eaddr,etel)
values(tbEmp_seq.Nextval,'阿房女','女',23,'河北小桥市青色路87号','0689-5656564');
select * from tbemp;
商品信息表
create table tbProd
(
pID number(7) primary key, --商品编号
pType varchar(20) notnull, --商品类型
pMark varchar(20) not null, --商品品牌
pSpec varchar(20) --商品规格
)
创建序列
create sequence tbProd_seq
minvalue 1
maxvalue 9999999
start with 1
increment by 1
cache 100;
基础数据
insert into tbprodvalues(tbProd_seq.Nextval,'电视机','创维','48英寸等离子电视机');
insert into tbprodvalues(tbProd_seq.Nextval,'电视机','创维','56英寸数字电视机');
insert into tbprodvalues(tbProd_seq.Nextval,'电视机','创维','32英寸背投');
insert into tbprodvalues(tbProd_seq.Nextval,'电视机','熊猫','48英寸等离子电视机');
insert into tbprodvalues(tbProd_seq.Nextval,'电视机','熊猫','56英寸数字电视机');
insert into tbprod values(tbProd_seq.Nextval,'电视机','TCL','32英寸背投');
insert into tbprodvalues(tbProd_seq.Nextval,'笔记本','宏基','奔腾双核 T6666');
insert into tbprodvalues(tbProd_seq.Nextval,'笔记本','宏基','酷睿2双核 E7500 ');
insert into tbprodvalues(tbProd_seq.Nextval,'笔记本','联想','奔腾双核 T6666');
insert into tbprodvalues(tbProd_seq.Nextval,'笔记本','联想','酷睿2双核 E7500 ');
销售情况表
create table tbSales
(
srNO number(7) primarykey, --记录编号
eID number(7) not nullreferences tbemp, --职员编号
pID number(7) not nullreferences tbprod, --商品编号
pQty number(7) not null check(pqty>0), --销售数量
pAmount number(7) notnull check(pamount>0), --销售价值
sDate date --销售时间
)
创建序列
create sequence tbSales_seq
minvalue 1
maxvalue 9999999
start with 1
increment by 1
cache 100;
基础数据
insert into tbsalesvalues(tbSales_seq.Nextval,1,1,10,21000,to_date('2010-3-12','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,1,2,5,20000,to_date('2010-3-12','yyyy-mm-dd'));
insert into tbsales values(tbSales_seq.Nextval,1,4,12,23500,to_date('2010-3-14','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,1,5,4,16500,to_date('2010-3-14','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,2,3,3,31000,to_date('2010-3-11','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,2,6,4,40000,to_date('2010-3-13','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,3,7,5,40000,to_date('2010-3-13','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,3,8,3,36000,to_date('2010-3-14','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,4,9,6,41500,to_date('2010-3-12','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,4,10,5,50000,to_date('2010-3-14','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,1,1,10,21000,to_date('2010-4-12','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,1,2,5,20000,to_date('2010-4-12','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,1,4,12,23500,to_date('2010-4-14','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,1,5,4,16500,to_date('2010-4-14','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,2,3,3,31000,to_date('2010-4-11','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,2,6,4,40000,to_date('2010-4-13','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,3,7,5,40000,to_date('2010-4-13','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,3,8,3,36000,to_date('2010-4-14','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,4,9,6,41500,to_date('2010-4-12','yyyy-mm-dd'));
insert into tbsalesvalues(tbSales_seq.Nextval,4,10,5,50000,to_date('2010-4-14','yyyy-mm-dd'));
二.单表查询 (每题2分,共10分)
1. 查询所有女职员的详细信息
Select * from tbemp where tbemp .esex=’女’;
2. 查询年龄在24到26岁之间的职员的姓名,性别
Selectename 姓名,esex 姓名 from tbemp where eage<=26 andeage>=24;
Select ename 姓名,esex 姓名 from tbempwhere eage between 24 and 26;
3. 查询家住广州的女职员的姓名,电话,地址
Select ename 姓名,etel 电话,eaddr 地址 from tbemp where eaddr like ‘%广州%’;
4. 查询陈飞飞,阿房女,张小杰的电话,地址
Select etel 电话,eaddr 地址 from tbemp where ename in (‘陈飞飞’,’ 阿房女’,’ 张小杰’);
Select etel 电话,eaddr 地址 from tbemp where ename=‘陈飞飞’ or ename=’ 阿房女’ or ename= ‘张小杰’;
5. 查询广州和深圳的职员的姓名,性别,年龄
Select ename 姓名,esex 性别 , eage 年龄 from tbemp where eaddr like ‘%广州%’ or
eaddr like ‘%深圳%’;
三.多表查询 (每题4分，共20分)
1. 所有职员,所有商品的销售情况,包括:职员姓名,商品类别,商品品牌,商品规格,销售数量,销售价值,销售时间,按销售时间从高到低排列
SelectE.ENAME,P.PTYPE,P.PMARK,P.PSPEC,S.PQTY,S.PAMOUNT,P.SDATE
from tbemp e, tbprod p,tbsales s
where E.EID=S.EID AND S.PID=P.PID
ORDER BY S.SDATE DESC;
2. 笔记本4月份的销售情况,包括:商品编号,商品品牌,商品规格,销售数量,销售价值,按销售数量从低到高排列
SELECT P.PID,P.PMARK,P.SPEC,S.PQTY,S.PAMOUNT
FROM TBPROD P, TBSALES S
WHERE S.PID=P.PID AND P.PTYPE=’笔记本’ AND S.SDATE LIKE ‘%-4%’
ORDER BY S.PQTY;
----判断日期类型的月份，可用函数extract
----WHERE EXTRACT(MONTH FROM S.SDATE ) = ’4’
3. 陈飞飞3月份的销售情况,包括:商品编号,销售数量,销售价值
SELECT P.PID,S.PQTY,S.PAMOUNT
FROM TBPROD P, TBSALES S
WHERE S.PID=P.PID AND
S.EID IN (SELECT E.EID FROM TBEMP E WHERE E.ENAME=’陈飞飞’)
AND EXTRACT(MONTH FROM S.SDATE)=’3’;
4. 创维56英寸数字电视机 的销售情况,包括:销售数量,销售价值,销售时间
SELECT S.PQTY,S.PAMOUNT,S.SDATE FROM TBSALES S , TBPROD P
WHERE S.PID=P.PID
AND P.PMRK=’ 创维’
AND P.PSPEC=’ 56英寸数字电视机’;
5. 广州男职员4月份的销售情况,包括:职员姓名,商品类别,商品品牌,商品规格,销售数量,销售价值
SELECT E.ENAME,P.PTYPE, P.PMARK, P.PSPEC, S.PQTY, S.PAMOUNT
FROM TBEMP E,TBSALES S,TBPROD P
WHERE E.EID=S.EID AND S.PID=P.PID
AND E.EADDR LIKE ‘%广州%’
AND E.ESEX=’ 男’
AND EXTRACT(MONTH FROM S.SDATE)=’4’;
四.合计 (每题5分,共 30分)
1. 总共有多少个职员？
SELECT COUNT(EID) FROM TBEMP;
2. 创维电视机被销售了多少次?//总共有几条销售记录
SELECT COUNT(S.SRNO) FROM TBSALES S, TBPROD P
WHERE S.PID=P.PID
AND P.PTYPE=’ 电视机’
AND P.PMARK=’ 创维’ ;
3. 年龄最小的女职员的年龄
SELECT MIN(EAGE) FROM TBEMP
WHERE ESEX=’ 女’;
4. 笔记本单笔销售价值最高的单笔销售价值
SELECT MAX(S.PAMOUNT) FROM TBSALESS,TBPROD P
WHERE S.PID=P.PID
AND P.PTYPE=’ 笔记本’;
5. TCL电视机的平均销售价格
---平均销售价格=总的销售价格/总的销售数量
SELECT SUM(S.PAMOUNT)/SUM(S.PQTY) FROM TBSALES S, TBPROD P
WHERE S.PID=P.PID
AND P.PTYPE=’ 电视机’
AND P.PMARK=’ TCL’;
6. 联想笔记本4月份的销售总额
SELECT SUM(S.PAMOUNT) FROM TBSALES S, TBPROD P
WHERE S.PID=P.PID
AND P.PTYPE=’ 笔记本’
AND P.PMARK=’ 联想’
AND EXTRACT(MONTH FROM S.SDATE)=’4’;
五.分组与合计 (每题 5分，共计 35分)
1. 按类型,品牌统计商品的销售总数量,销售总数量低于20的不统计
SELECT SUM(S.PQTY) FROM TBSALES S,TBPROD P
WHERE S.PID=P.PID
GROUP BY P.PTYPE,P.PMARK
HAVING SUM(S.PQTY)>=20;
2. 按品牌,规格统计笔记本的平均销售价格,按销售价格从高到低的顺序显示
SELECT SUM(S.PAMOUNT)/SUM(S.PQTY) FROM TBSALES S, TBPROD P
WHERE S.PID=P.PID
AND P.PTYPE=’ 笔记本’
GROUP BY P.PMARK , P.PSPEC
ORDER BY SUM(S.PAMOUNT)/SUM(S.PQTY) DESC;
3. 按姓名统计男职员3月份的销售总额,销售总额低于80000的不统计
SELECT SUM(S.PAMOUNT) FROM TBEMP E,TBSALES S
WHERE E.EID=S.EID
AND E.ESEX=’ 男’
AND EXTRACT(MONTH FROM S.SDATE)=’3’
GROUP BY E.ENAME
HAVING SUM(S.PAMOUNT)>=80000;
4. 找出销售总额最高的职员的姓名
SELECT G.NAME FROM (
SELECT E.ENAME NAME, SUM(S.PAMOUNT) TOTAL
FROM TBEMP E, TBSALES S
WHERE E.EID=S.EID
GROUP BY E.ENAME)G
WHERE G.TOTAL IN
(SELECT MAX(H.TOTAL) FROM
(SELECT E.ENAME NAME, SUM(S.PAMOUNT) TOTAL
FROMTBEMP E, TBSALES S
WHERE E.EID=S.EID
GROUP BY E.ENAME)H
);
----方法2
SELECT E.ENAME FROM TBEMP E, TBSALES S
WHERE E.EID=S.EID
GROUP BY E.ENAME
HAVING SUM(S.PAMOUNT)>=ALL(
SELECT SUM(S.PAMOUNT) FROM TBSALES S
GROUP BY E.EID
);
5. 按姓名统计每个职员一共完成了多少笔销售业务
SELECT COUNT( S.SRNO) FROM TBEMP E, TBSALES S
WHERE E.EID=S.EID(+)
GROUP BY E.ENAME;
6. 找出完成销售业务笔数最少的职员的姓名和他完成的业务数
SELECT E.ENAME, COUNT( S.SRNO) FROM TBEMP E, TBSALES S
WHERE E.EID=S.EID(+)
GROUP BY E.ENAME
HAVING COUNT(S.SRNO)<=ALL(
SELECT COUNT( S.SRNO) FROM TBEMP E, TBSALES S
WHERE E.EID=S.EID(+)
GROUP BY E.ENAME
) ;
7. 按性别统计职员的最大年龄和最小年龄
SELECT MAX(EAGE), MIN(EAGE) FROM TBEMP
GROUP BY ESEX ;