10.
select a.order_no,
b.cus_name,c.emp_name,a.tot_amt,
a.order_date,a.ship_date,
a.invoice_no,a.o_static from
sales a,customer b,employee_hope c
where a.cus_id=b.cus_id(+)
and a.sale_id=c.emp_no(+)
and a.order_date>=date'1996-10-1'
and a.order_date<date'1996-11-1';select a.order_no,
(select cus_name from customer where cus_id=a.cus_id)cus_name,
(select emp_name from employee_hope where emp_no=a.sale_id)emp_name,
a.tot_amt,a.order_date,a.ship_date,a.invoice_no,a.o_static
from sales a
where a.order_date>=date'1996-10-1'
and a.order_date<date'1996-11-1';
11.
select addr,count(1)
from customer a
group by addr;
12.
select b.addr,sum(a.tot_amt)
from sales a,customer b
where a.cus_id=b.cus_id
group by b.addr;
13.
select a.emp_name,a.title,
trunc(months_between(sysdate,birthday)/12) age
from employee_hope a
where a.title<>'职员'
order by birthday desc;
14.
select distinct a.order_no,b.sup_name
from sale_item a,supply b
where a.sup_id=b.sup_id
and a.unit_price>2000
and a.order_date>=date'1996-8-1';
40.
select b.sup_name,sum(a.qty*a.unit_price)
from sale_item a,supply b
where a.sup_id=b.sup_id
group by b.sup_id,b.sup_name都很简单啊,这些题不错
楼主自己练习一下比较好
不会的再问吧
select a.order_no,
b.cus_name,c.emp_name,a.tot_amt,
a.order_date,a.ship_date,
a.invoice_no,a.o_static from
sales a,customer b,employee_hope c
where a.cus_id=b.cus_id(+)
and a.sale_id=c.emp_no(+)
and a.order_date>=date'1996-10-1'
and a.order_date<date'1996-11-1';select a.order_no,
(select cus_name from customer where cus_id=a.cus_id)cus_name,
(select emp_name from employee_hope where emp_no=a.sale_id)emp_name,
a.tot_amt,a.order_date,a.ship_date,a.invoice_no,a.o_static
from sales a
where a.order_date>=date'1996-10-1'
and a.order_date<date'1996-11-1';
11.
select addr,count(1)
from customer a
group by addr;
12.
select b.addr,sum(a.tot_amt)
from sales a,customer b
where a.cus_id=b.cus_id
group by b.addr;
13.
select a.emp_name,a.title,
trunc(months_between(sysdate,birthday)/12) age
from employee_hope a
where a.title<>'职员'
order by birthday desc;
14.
select distinct a.order_no,b.sup_name
from sale_item a,supply b
where a.sup_id=b.sup_id
and a.unit_price>2000
and a.order_date>=date'1996-8-1';
40.
select b.sup_name,sum(a.qty*a.unit_price)
from sale_item a,supply b
where a.sup_id=b.sup_id
group by b.sup_id,b.sup_name都很简单啊,这些题不错
楼主自己练习一下比较好
不会的再问吧
相当于to_date()函数吗
wildwave 麻烦稍加解释下
相当于to_date()函数吗
wildwave 麻烦稍加解释下
a.order_date>=to_date('1996-10-01','YYYY-MM-DD')
先谢谢了!@@
看看对不对。
select t,sum(m) from
(
select to_char(order_date,'yyyy-mm') t,sum(qty*unit_price) m
from sale_item group by order_date
) group by t
起先可以网上查资料等等
还有一些很变态的sql语句,其实开发中用到的并不是太多。
from sales a
group by to_char(a.order_date,'yyyy-mm');这个可能根据sale_item来统计也行,不过通过订单统计应该比较好