数据库高级查询题目集

一、销售信息管理系统实现要求

--员工信息表
create table tbEmp (
       eid int primary key,
       ename varchar2(6),
       esex  char(2)  default '男' 
             constraints CK_tbEmp_esex check(esex='男' or esex='女'),
       eage  int  
             constraints CK_tbEmp_eage check(eage>=18 or eage<=50),
       eaddr varchar2(20) default '地址不详'
);
--添加非空约束
alter table tbEmp modify eid not null;
alter table tbEmp modify ename not null;
alter table tbEmp modify eage not null;
--添加唯一约束
alter table tbEmp add constraints CK_tbEmp_ename unique(ename);
--员工编号从10000自增
create sequence seq_eid start with 10000 increment by 1;


--产品信息表
create table tbProd(
       pid int primary key,
       ptype varchar2(10),
       pmark varchar2(20),
       pspec varchar2(20)
);
--添加非空约束
alter table tbProd modify  ptype not null;
--产品编号自增
create sequence seq_pid start with 1 increment by 1;


--销售信息表
create table tbSales(
       srno int primary key,
       eid int,
       pid int,
       pqty int
               constraints CK_tbSales_pqty check(pqty>0),
       pamount int
               constraints CK_tbSales_pamount check(pamount>0),
       sdate date
);


--srno 自增
create sequence seq_srno start with 10100 increment by 1;
--添加eid外键约束
alter table tbSales add constraints FK_tbSales_eid foreign key(eid) references tbEmp(eid);
--添加pid外键约束
alter table tbSales add constraints FK_tbSales_pid foreign key(pid) references tbProd(pid);
--添加非空约束
alter table tbSales modify pqty not null;
alter table tbSales modify pamount not null;


drop sequence seq_eid;
drop sequence seq_pid;
drop sequence seq_srno;


select * from  tbEmp;
select * from  tbProd;
select * from  tbSales;


drop table tbEmp;
drop table tbProd;
drop table tbSales;


--建关系


--插入数据
insert into tbEmp values(seq_eid.nextval,'张三','男',21,'湖南长沙');
insert into tbEmp values(seq_eid.nextval,'李四',default,22,'湖南衡阳');
insert into tbEmp values(seq_eid.nextval,'吴昊','男',20,'广东广州');
insert into tbEmp values(seq_eid.nextval,'小小','女',20,'湖南邵阳');
insert into tbEmp values(seq_eid.nextval,'乐乐','女',23,'广西桂林');
insert into tbEmp values(seq_eid.nextval,'潇潇','女',24,'湖南耒阳');
insert into tbEmp values(seq_eid.nextval,'注定','女',20,'湖南');




insert into tbProd values(seq_pid.nextval,'手机','oppo','R7');
insert into tbProd values(seq_pid.nextval,'手机','iphone','plus6');
insert into tbProd values(seq_pid.nextval,'笔记本','惠普','G560');
insert into tbProd values(seq_pid.nextval,'笔记本','戴尔','G360');
insert into tbProd values(seq_pid.nextval,'电视机','长虹','37寸');
insert into tbProd values(seq_pid.nextval,'电视机','熊猫','40寸');




insert into tbSales values(seq_srno.nextval,10000,1,100,200000,to_date('2015-1','yyyy-mm' ));
insert into tbSales values(seq_srno.nextval,10001,2,50,250000,to_date('2015-2','yyyy-mm'));
insert into tbSales values(seq_srno.nextval,10002,3,50,150000,to_date('2015-3','yyyy-mm'));
insert into tbSales values(seq_srno.nextval,10003,4,30,90000,to_date('2015-4', 'yyyy-mm'));
insert into tbSales values(seq_srno.nextval,10004,5,30,120000,to_date('2015-5', 'yyyy-mm'));
insert into tbSales values(seq_srno.nextval,10005,6,20,80000,to_date('2015-6','yyyy-mm'));
insert into tbSales values(seq_srno.nextval,10005,6,20,2500000,to_date('2015-6','yyyy-mm'));
insert into tbSales values(seq_srno.nextval,10001,2,50,2500000,to_date('2015-2','yyyy-mm'));




/*=======查询数据练习=========*/ 
--1.查询三表的数据--
select * from  tbEmp;
select * from  tbProd;
select * from tbSales;


--2.查询男员工名单-- 
select ename from tbEmp where esex='男';


---多表查询(不需要查询总销售量和总销售价值,只需显示商品的销售情况即可)
--1. 所有职员,所有商品的销售情况,包括:职员姓名,商品类别,商品品牌,商品规格,销售数量,销售价值,销售时间,按销售时间从高到低排列
select ename,ptype,pmark,pspec,pqty,pamount,sdate from tbEmp e,tbProd p,tbSales s where e.eid=s.eid and p.pid=s.pid order by sdate asc;




--2. 手机2月份的销售情况,包括:商品编号,商品品牌,商品规格,销售数量,销售价值,按销售数量从低到高排列
select p.pid,ptype,pmark,pspec,pqty,pamount,sdate from tbProd p,tbSales s 
where p.pid=s.pid and extract(MONTH from sdate)=2 order by pqty asc;


--3. 李四2月份的销售情况,包括:商品编号,销售数量,销售价值
select ename,pid,pqty,pamount,sdate from tbEmp e,tbSales s where e.eid=s.eid and ename='李四' 
and extract(MONTH from sdate)=2;




--4. 长虹29英寸艺术电视机的销售情况,包括:销售数量,销售价值,销售时间
select pqty,pamount,sdate,pmark,pspec from tbProd p,tbSales s where p.pid=s.pid and pmark='长虹'and pspec='37寸';


--5. 长沙男职员4月份的销售情况,包括:职员姓名,商品类别,商品品牌,商品规格,销售数量,销售价值
select ename,esex,eaddr,ptype,pmark,pspec,pqty,pamount,sdate from tbEmp e,tbProd p,tbSales s
where e.eid=s.eid and p.pid=s.pid and esex='男' and eaddr='湖南长沙';
--统计
--1. 总共有多少个职员？
select count(ename) from tbEmp;


--2. 长虹电视机被销售了多少次?
select count(pid) from  tbSales where pid=5;


--3. 年龄最小的女职员的年龄(年龄最小的女职员的姓名,年龄)   
select ename,eage,esex from tbEmp where eage=(select min(eage)from tbEmp) and esex='女';


--4. 笔记本单笔销售价值最高的单笔销售价值
select ptype,pqty,pamount from tbProd p,tbSales s where p.pid=s.pid and ptype='笔记本' 
and pamount=(select max(s.pamount) from tbSales s where s.pid=3 or s.pid=4);


--5. 熊猫电视机的每次销售的平均销售价值(熊猫电视机的平均销售价格)
select 
--6. 戴尔笔记本4月份的销售总额
select ptype,pmark,pamount,sdate from tbProd p,tbSales s where p.pid=s.pid and ptype='笔记本' and
pmark='戴尔' and extract(MONTH from sdate)=4;


--分组与合计
--1. 按类型,品牌统计商品的销售总数量,销售总数量低于5的不统计
select ptype,pmark,pqty from tbProd p,tbSales s where p.pid=s.pid and pqty>=5;


--2. 按品牌,规格统计笔记本的平均销售价格,按销售价格从高到低的顺序显示
select avg(pqty) from (select p.ptype,p.pmark,p.pspec,s.pqty from tbProd p,tbSales s
 where p.pid=s.pid and p.ptype='笔记本');
 
--3. 按姓名统计男职员3月份的销售总额,销售总额低于10000的不统计
select ename,sdate,total from tbEmp e,tbSales s,(select sum(s.pamount) total from tbSales s) a
 where e. eid=s.eid and extract(MONTH from sdate)=2 and total>10000;
 
 
--4. 找出销售总额最高的职员的姓名
select * from(select ename,s.pamount from tbEmp e,tbSales s where e.eid=s.eid order 
by s.pamount desc)where rownum=1;


select ename,s.pamount from tbEmp e,tbSales s where s.pamount in(select max(pamount)from tbSales) and e.eid=s.eid;
--5. 按姓名统计每个职员一共完成了多少笔销售业务
select ename,count(ename)from(select e.ename,e.eid from tbEmp e,tbSales s where e.eid=s.eid) group by ename;


--6. 找出完成销售业务笔数最多的职员的姓名和他完成的业务数
select ename,count(ename)from(select e.ename,e.eid from tbEmp e,tbSales s where e.eid=s.eid) 
group by ename order by count(ename) desc;


--7. 按性别统计职员的最大年龄和最小年龄
select max(eage),min(eage)  from tbEmp ;




--(8) 创建登录账号和数据库用户
-- 假定该数据库的用户是销售总监和员工，销售总监负责查看销售信息和管理员工信息，员工负责录入销售信息和产品信息，所以他们的权限分别是:
 -- 销售总监: 对员工表tbemp具备增、删、改、查的权限，对销售表tbsales和产品表tbprod只有查看的权限，不能修改
 -- 员工: 对tbsales和tbprod具备增、删、改、查的权限，对tbemp表只有查看的权限，不能修改
--a. 请创建sql server登录账号：销售总监账号 salesmanager, 员工账号 emp, 默认访问salesdb数据库
--b. 创建销售总监和员工对应的数据库用户，并授予相应权限
create user xszj identified by a;
create user emplyee identified by a;
grant create session to xszj;
grant create session to emplyee;
grant alter,select on scott.tbEmp to xszj;
grant select on scott.tbSales to xszj;
grant select on scott.tbProd to xszj;


grant alter,select on scott.tbProd to emplyee;
grant select on scott.tbSales to emplyee;


select * from scott.tbEmp;
select * from scott.tbSales;
select * from scott.tbProd;

二、商品销售数据库系统设计

--商品销售数据库
create table Article(
       商品号 varchar2(4) primary key,
       商品名 varchar2(16),
       单价   number(8,2),
       库存量 int
);


create table Customer(
       顾客号 varchar2(4) primary key,
       顾客名 varchar2(8),
       性别   varchar2(2) default '男'
              constraints CK_Customer_性别 check (性别='男' or 性别='女'),
       年龄  int
              constraints CK_Customer_年龄 check (年龄>10 and 年龄<100)
);


create table OrderIterm(
       顾客号  varchar2(4) ,
       商品号  varchar2(16),
       数量   int,
       购买价  number(8,2),
       日期  varchar2(20)
);
--添加商品号外键约束
alter table  OrderIterm add constraints FK_OrderItem_商品号 foreign key(商品号) references Article(商品号);
alter table OrderIterm add constraints FK_OrderItem_顾客号 foreign key(顾客号) references Customer(顾客号);


insert into Article values ('S001','计算机',5000,10);
insert into Article values ('S002','打印机',1000,12);
insert into Article values ('S003','洗衣机',800,10);
insert into Article values (