Oracle sql 复习题目总结

一解：

select p.promo_name,s.clerk_name,sum(s.sale_amount) top_sale
from promotions p inner join sales s on s.sale_date between p.start_date and p.end_date
group by p.promo_name,p.start_date,p.end_date
having sum(s.sale_amount) > all(
       select sum(s2.sale_amount) from sales s2
       where s.clerk_name <> s2.clerk_name and s2.sale_date between p.start_date and p.end_date
       group by s2.clerk_name
);

二解：

SELECT s1.clerk_name,p.promo_name,p.end_date,SUM(s1.sale_amount)
FROM sales s1 INNER JOIN promotions p ON s1.sale_date BETWEEN p.start_date AND p.end_date
group by s1.clerk_name,p.end_date
HAVING SUM(s1.sale_amount)>= ALL(
        SELECT SUM(s2.sale_amount) FROM sales s2
        WHERE s2.sale_date BETWEEN p.start_date AND p.end_date
        GROUP BY s2.clerk_name
);

sql 题目五

财务咨询顾问的收入统计问题
金太阳财务咨询服务公司，聘请了一些财务顾问提供财务操作咨询服务，这些顾问都以兼职形式为公司服务，公司将按小时为顾问工作计算薪酬。具体表信息如下：

a. 顾问信息表 (consultant)

create table consultant(
    cst_id int primary key,cst_name varchar2(30) not null
);
insert into consultant values(1,'david');
insert into consultant values(2,'henry');
insert into consultant values(3,'mary');
insert into consultant values(4,'kent');

b. 顾问费率表 (billings)

不同级别的顾问，在不同的时期，每小时的收费是不同的，公司会根据情况上调或者下调顾问的小时佣金待遇。

create table billings(
    cst_id int not null,bill_date date not null,bill_rate int not null,constraint PK_BILLING PRIMARY KEY(cst_id,bill_date),constraint FK_BILLING_EMP FOREIGN KEY (cst_id) references consultant(cst_id)
);
insert into billings values(1,to_date('2010-1-1',50);
insert into billings values(2,60);
insert into billings values(3,70);
insert into billings values(4,40);
insert into billings values(1,to_date('2011-1-1',60);
insert into billings values(4,45);

c. 顾问工作情况记录表 (hoursworked)

create table hoursworked(
     list_id int primary key,cst_id int not null,work_date date not null,bill_hrs  decimal(5,2) not null,constraint FK_HW_EMP FOREIGN KEY (cst_id) references consultant(cst_id)
   );
insert into hoursworked values(1,to_date('2010-7-1',3);
insert into hoursworked values(2,to_date('2010-8-1',5);
insert into hoursworked values(3,2);
insert into hoursworked values(4,to_date('2011-7-1',4);
insert into hoursworked values(5,3,to_date('2011-8-1',3.5);
insert into hoursworked values(6,4,to_date('2010-9-1',10);
insert into hoursworked values(7,6);

题目

我们编写一个SQL查询语句，显示顾问的名字以及其总的顾问费用。

求解

select c.cst_id,e.cst_name,sum(d.bill_rate*c.bill_hrs) total_fee from (
     select a.cst_id,a.work_date,max(b.bill_date) bill_date,a.bill_hrs
     from billings b inner join hoursworked a
     on a.cst_id=b.cst_id and a.work_date>=b.bill_date
     group by a.cst_id,a.bill_hrs
    ) c,billings d,consultant e
where c.cst_id=d.cst_id and c.bill_date=d.bill_date and c.cst_id=e.cst_id
group by c.cst_id,e.cst_name
order by c.cst_id;

sql 题目六

ABC在线销售公司业务系统

表结构

1、表名：t_category (商品类别表)
字段(字段名/类型/长度)：
类别编号 category_id INT
类别名称 category_name VARCHAR2(30)

2、表名：t_goods (商品表)
字段(字段名/类型/长度)：
商品编号 goods_no CHAR(3)
商品名称 goods_name VARCHAR2(30)
商品价格 goods_price number(7,2)
所属类别 goods_category INT
点击次数 goods_click_num INT

3、 表名: t_saleinfo (销售信息表)
字段(字段名/类型/长度)：
销售流水号 sid INT
商品编号 goods_no CHAR(3)
销售日期 sale_date date
销售数量 quantity INT
销售金额 amount number(10,2)

题目

1、查询酒类商品的总点击量
2、查询各个类别所属商品的总点击量,并按降序排列
3、查询所有类别中最热门的品种(点击量最高),并按点击量降顺序排列
4、查询茅台的销售情况,按日期升序排列

格式如下:
商品编号 商品名称 销售日期 销售数量 销售金额 累计数量 累计金额
1 茅台 2011-12-1 10 7000 10 7000
1 茅台 2011-12-2 15 10500 25 17500

建表

create table t_category(
   category_id int primary key,category_name varchar2(30)
);

create table t_goods(
   goods_no char(3) primary key,goods_name varchar2(30) not null,goods_price number(7,goods_category int not null,goods_click_num int default 0,constraint FK_GOODS_CATEGORY FOREIGN KEY (goods_category) references t_category(category_id)
);

create table t_saleinfo(
   sid int primary key,goods_no char(3) not null,sale_date date  not null,quantity int not null,amount number(10,constraint FK_SALEINFO_GOODS FOREIGN KEY (goods_no) references t_goods(goods_no)
);

-- 增加类别数据
insert into t_category values(1,'酒类');
insert into t_category values(2,'服装');
insert into t_category values(3,'书籍');

-- 商品数据
insert into t_goods values('G01','贵州茅台',550.56,128);
insert into t_goods values('G02','福建老酒',5.43,24);
insert into t_goods values('G03','泸州老窖',90.56,67);
insert into t_goods values('G04','剑南春',80.56,88);
insert into t_goods values('G05','七匹狼夹克',350.56,348);
insert into t_goods values('G06','七匹狼衬衫',105.43,908);
insert into t_goods values('G07','七匹狼男长裤',130.50,167);
insert into t_goods values('G08','七匹狼领带',280.00,388);
insert into t_goods values('G09','J2EE开发',50.50,236);
insert into t_goods values('G10','STRUTS应用',24.50,654);
insert into t_goods values('G11','ORACLE 11G',100.50,145);
insert into t_goods values('G12','dotnet技术',80.00,988);

-- 销售数据
insert into t_saleinfo values(1,'G01',to_date('2008-1-1','yyyy-MM-dd'),50,50*550.56);
insert into t_saleinfo values(2,to_date('2008-1-2',25,25*550.56);
insert into t_saleinfo values(3,to_date('2008-1-3',31,31*550.56);
insert into t_saleinfo values(4,to_date('2008-1-4',43,43*550.56);
insert into t_saleinfo values(5,to_date('2008-1-5',55,55*550.56);
insert into t_saleinfo values(6,to_date('2008-1-6',102,102*550.56);
insert into t_saleinfo values(7,'G11',82,82*100.5);
insert into t_saleinfo values(8,to_date('2008-1-7',202,202*100.5);

求解

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!