练习1.txt

来自「orale培训教材包括了所有的sql说明和实例」· 文本 代码 · 共 37 行

--从采购信息表plsql101_purchase中取出在一季度采购的产品

select product_name,quantity,salesperson from plsql101_purchase
where to_char(purchase_date,'mon') in ('1月','2月','3月');

insert into plsql101_purchase values('豆腐','张',to_date('2002/01/01','yyyy/mm/dd'),10);
insert into plsql101_purchase values('钢笔','张',to_date('2002/02/01','yyyy/mm/dd'),10);
insert into plsql101_purchase values('钢笔','关',to_date('2002/03/01','yyyy/mm/dd'),10);
insert into plsql101_purchase values('钢笔','关',to_date('2002/04/01','yyyy/mm/dd'),10);



select product_name,quantity,salesperson from plsql101_purchase
where to_char(purchase_date,'mon') in ('1月','2月','3月');
--未选定行
--为什么？to_char(purchase_date,'mon')的结果最后有一个空格'1月 '与'1月'不同。

--更改
select product_name,quantity,salesperson from plsql101_purchase
where to_char(purchase_date,'mon') in ('1月 ','2月 ','3月 ');

--有无更好的方法？使用sql  trim函数
select product_name,quantity,salesperson from plsql101_purchase
where trim(to_char(purchase_date,'mon')) in ('1月','2月','3月');


select product_name,quantity,salesperson from plsql101_purchase
where to_char(purchase_date,'mm') in ('01','02','03');



--祛除其它年份
insert into plsql101_purchase values('钢笔','关',to_date('2003/04/01','yyyy/mm/dd'),10);
--祛除其它年份确保为同一年的1季度
select product_name,quantity,salesperson from plsql101_purchase
where trim(to_char(purchase_date,'mon')) in ('1月','2月','3月') 
and to_char(purchase_date,'yyyy') = '2002';