drop table PRODUCT cascade constraints;
create table PRODUCT
(
id NUMBER not null, --主键
pcode VARCHAR2(20) not null, --商品编号
pname VARCHAR2(20), --商品名称
inprice NUMBER(7,2), --进价
outprice NUMBER(7,2), --售价
toma VARCHAR2(20), --管理员名称
lastcou NUMBER, --剩余库存数量
ptype VARCHAR2(5), --商品类型
mark NUMBER, --有效标志
createtime DATE --生产日期
);
alter table PRODUCT
ADD CONSTRAINT pk_product primary key (ID);
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (1, 'f-qq45', '香蕉', 2.8, 4, '张三', 12, 'a', 1, to_date('05-06-2017', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (2, 'f-56tt', '苹果', 3.4, 5, '张三', 33, 'a', 1, to_date('16-06-2016', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (3, 'f-332', '梨', 2.7, 4, '张三', 76, 'a', 1, to_date('23-09-2016', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (4, 'f-20', '猪肉', 14, 16, '张三', 56, 'b', 1, to_date('31-12-2013', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (5, 'f-qq46', '圆珠笔', 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 (6, 'f-57tt', '樱桃', 8, null, '王五', 12, 'a', 1, to_date('22-05-2006', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (7, 'f-292', '三文鱼', 5, 8, '王五', 23, 'b', 1, to_date('31-07-2001', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (8, 'f-604', '螺丝刀', 12, 15, '王五', 72, 'c', 1, to_date('24-02-2013', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (9, 'f-qq47', '扳手', 14, 19, '王五', 90, 'c', 1, to_date('14-05-2015', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (10, 'f-58tt', '钳子', 10, 13, '张三', 97, 'c', 1, to_date('28-03-2012', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (11, 'f-916', '矿泉水', 2.5, 3, '李四', 44, 'd', 1, to_date('21-07-2012', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (12, 'f-1228', '杏仁露', 4, 5, '李四', 38, 'd', 1, to_date('22-02-2013', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (13, 'f-59tt', '钢笔', null, 43, '李四', 22, 'e', 1, to_date('30-09-2013', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (14, 'f-1540', '铅笔', .5, .8, '赵六', 72, 'e', 1, to_date('14-05-2018', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (15, 'f-1852', '大马哈鱼', 1.5, 2, '赵六', 76, 'b', 1, to_date('17-05-2012', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (16, 'f-2164', '鸡蛋', 2.7, 4, '赵六', 56, 'b', 1, to_date('14-09-2010', 'dd-mm-yyyy'));
commit;
-- 使用insert into任意添加三条数据(数据信息自定)加到这个表中
-- 删除'猪肉'的那条记录
-- 将扳手和钳子的商品名称分别改为'16号扳手'和'小号钳子', 并将这两个商品的管理人员都改为'赵六'
-- 查询商品类型为'a'的所有商品
-- 查询王五负责的c类商品
-- 查询剩余数量小于50的所有商品
-- 查询剩余数量在60-80之间的
-- 查询商品名称带'笔'的和带'鱼'的记录
-- 查询商品编号中带'tt'的记录
-- 查询商品类型为a, d, c的所有商品
-- 查询所有'e'类型商品, 并按照剩余库存数量排序
-- 查询管理商品数少于4的管理员名称和管理的商品数量
-- 查询所有的管理员名称和其手上所管理的所有商品数量
-- 查询所有的管理员名称和其手上所管理的所有'a'类商品名称的数量
-- 查询所有商品中剩余数量最少的一个
-- 查询生产日期在2015/5/31之前的数据
-- 假设所有商品的有效期是一年, 以当前系统时间为标准, 查询所有商品的信息, 并且加一列标注是否过期
-- 查询每个管理员所管理的商品的平均进价价格和售价价格
-- 查询每个管理员手上的商品的利润的平均值
-- 查询假设每个管理员把所有商品售完后各自的利润总和
答案：
--SELECT * FROM product
/*INSERT INTO product
VALUES(50,'qqwweerr','豆腐',2.5, 3, '张三', 30, 'b', 1, SYSDATE);
COMMIT;*/
/*DELETE product p WHERE p.pname='猪肉';
COMMIT;*/
/*UPDATE product p SET p.pname='16号扳手', p.toma='赵六'
WHERE p.pname='扳手';
UPDATE product p SET p.pname='小号钳子', p.toma='赵六'
WHERE p.pname='钳子';
COMMIT;*/
--SELECT * FROM product p WHERE p.ptype='a';
--SELECT * FROM product p WHERE p.ptype='c' AND p.toma='王五';
--SELECT * FROM product p WHERE p.lastcou<50;
--SELECT * FROM product p WHERE p.lastcou <80 AND p.lastcou > 60;
--SELECT * FROM product p WHERE p.lastcou BETWEEN 60 AND 80;
/*SELECT * FROM product p
WHERE p.pname LIKE '%笔%' OR p.pname LIKE '%鱼%';*/
--SELECT * FROM product p WHERE p.pcode LIKE '%tt%';
/*SELECT * FROM product p WHERE p.ptype IN ('a','c','d');
SELECT * FROM product p WHERE p.ptype = 'a'
OR p.ptype = 'c'
OR p.ptype = 'd'*/
--SELECT * FROM product p
--WHERE p.ptype='e' ORDER BY p.lastcou ASC或者desc
/*SELECT p.toma, COUNT(p.pname) 管理的商品数
FROM product p GROUP BY p.toma
HAVING COUNT(p.pname) < 4*/
/*SELECT p.toma, COUNT(p.pname) 管理的商品数
FROM product p GROUP BY p.toma*/
/*SELECT p.toma, SUM(DECODE(p.ptype, 'a', 1, 0))
FROM product p GROUP BY p.toma
SELECT p.toma, COUNT(p.ptype) FROM product p
WHERE p.ptype='a' GROUP BY p.toma*/
/*SELECT * FROM product p
WHERE p.lastcou<=ALL(SELECT lastcou FROM product)*/
/*SELECT * FROM product p
WHERE p.lastcou=(SELECT MIN(lastcou) FROM product)*/
/*SELECT * FROM product p
WHERE p.createtime < to_date('20150531','yyyymmdd')*/
/*SELECT p.pname, CASE WHEN (SYSDATE-p.createtime) > 365
THEN '过期' ELSE '正常' END 是否过期
FROM product p*/
/*SELECT p.toma, AVG(p.inprice), round(AVG(p.outprice), 2)
FROM product p
GROUP BY p.toma*/
/*SELECT p.toma, AVG(p.outprice-p.inprice)
FROM product p GROUP BY p.toma*/
SELECT p.toma, SUM((p.outprice-p.inprice)* p.lastcou) 利润总和
FROM product p GROUP BY p.toma
