Select * From product ;
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (22, 'f-46', '圆珠笔', 3, 5, '王五', 55, 'e', 1,to_date('14-09-2016', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (21, 'f-32', '桃子', 2.4, 3, '张三', 65, 'a', 1,to_date('09-05-2017', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (20, 'f-nh3', '咖啡', 3.8, 5, '李四', 67, 'd', 1,to_date('18-09-2015', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (23, 'f-jh45', '虾', 6.4, 8, '王五', 78, 'b', 1,to_date('09-06-2016', 'dd-mm-yyyy'));
--删除'猪肉'的那条记录
Delete product t Where t.pname='猪肉';
--将'扳手'和'钳子'的商品名称分别改为'16号扳手'和'小号钳子', 并将这两个商品的管理人员都改为'赵六'
Update product t Set t.pname='16号扳手', t.toma='赵六' Where t.id=9;
Update product t Set t.pname='小号钳子',t.toma='赵六' Where t.id=10;
--查询商品类型为'a'的所有商品，并按照售价由大到小排列
Select * From product p Where p.ptype='a' Order By p.outprice Desc
-- 查询王五负责的c类商品
Select t.pname From product t Where t.toma='王五'And t.ptype='c' ;
-- 查询剩余数量小于50的所有商品
-- 查询剩余数量在60-80之间的所有商品
Select * From product t Where lastcou<50;
Select * From product t Where t.lastcou Between 60 And 80;
-- 查询商品名称带'笔'的和带'鱼'的记录
-- 查询商品编号中带'tt'的记录
Select * From product t Where t.pname Like '%笔%' Or t.pname Like '%鱼%';
Select * From product t Where t.pcode Like '%tt%';
-- 查询商品类型为a, d, c的所有商品
Select * From product t Where t.ptype='a'Or t.ptype='b' Or t.ptype='c';
--查询商品编号重复的记录，并将它们删除
DELETE * FROM product p WHERE p.pcode=(SELECT p.pcode,COUNT (*) FROM product p GROUP BY p.pcode HAVING COUNT(2)) FROM product p;
-- 查询整个表中每一类商品的剩余数量, 并按照剩余数由大到小排序
Select * From (Select t.ptype,Count(t.lastcou) cou From product t Group By t.ptype) e Order By e.cou Asc;
Select p.ptype,Sum(p.)
-- 查询所有'e'类型商品, 并按照剩余库存数量排序
Select * From (Select * From product t Where t.ptype='e') e Order By e.lastcou Asc;
-- 查询管理商品数少于4的管理员名称和管理的商品数量
Select * From (Select t.toma,Count(t.pname) cou From product t Group By t.toma) e Where e.cou<=4;
-- 查询所有的管理员名称和其手上所管理的所有商品数量
Select t.toma,Sum(t.lastcou) From product t Group By t.toma;
-- 查询所有的管理员名称和其手上所管理的所有'a'类商品名称的数量
Select e.ptype,sum(e.lastcou) From product e Where e.ptype='a' Group By e.ptype;
Select p.toma,Count(p.pname) From (Select e.toma,e.pname From product e Where e.ptype='a') p Group By p.toma;
-- 查询所有商品中剩余数量最少的一个
Select * From product p Where p.lastcou=(Select Min(lastcou) From product)
-- 查询生产日期在2015/5/31之前的数据
Select * From product e Where e.createtime < to_date('2015/05/31','yyyy/mm/dd')
-- 假设所有商品的有效期是一年, 以当前2017-06-01为标准, 查询所有商品的信息, 并且加一列标注是否过期
alter Table product Modify(SFGQ char(10))
Select t.* gq,Case When to_date('2017-06-01','yyyy-mm-dd')-t.createtime>365 Then '过期' Else '未过期' end from product t
-- 将所有过期的商品的有效标志改为0
Update product p Set p.sfgq=0 Where to_date.
-- 查询所有商品中还有一个月就过期的商品(一个月按照30天计算)
SELECT * FROM product p WHERE p.createtime+30>TO_DATE ('2017-06-01','yyyy-mm-dd')
-- 查询每个管理员所管理的商品的平均进价价格和售价价格
Select t.toma,Avg(nvl(t.inprice,0)),Avg(nvl(t.outprice,0))From product t Group By t.toma
-- 查询每个管理员手上的商品的利润的平均值
Select t.toma,Avg(nvl(t.outprice,0)-nvl(t.inprice,0))From product t Group By t.toma
-- 查询假设每个管理员把所有商品售完后各自的利润总和
Select t.toma,Sum(t.lastcou*(nvl(t.outprice,0)-nvl(t.inprice,0))) lirun From product t Group By t.toma
-- 查询出利润总和最高的管理员的名称, 以及利润
With rr As
( Select t.toma toma,Sum(t.lastcou*(nvl(t.outprice,0)-nvl(t.inprice,0)))lirun From product t Group By t.toma)
Select Max(rr.lirun) From rr
Commit;