准备数据
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-05-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-2017', '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')); insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime) values (17, 'p-123h', '雪碧', 2, 3, '王五', 50, 'd', 1, to_date('01-05-2017', 'dd-mm-yyyy')); insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime) values (18, 'p-111h', '冰红茶', 2, 3, '王五', 40, 'd', 1, to_date('18-05-2017', 'dd-mm-yyyy')); insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime) values (19, 'p-18-h', '可乐', 2, 3, '王五', 30, 'd', 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-qq46', '圆珠笔', 3, 5, '王五', 55, 'e', 1, to_date('14-09-2016', 'dd-mm-yyyy')); commit;
题目
-- 使用insert into任意添加三条数据(数据信息自定)加到这个表中
INSERT INTO product(ID,pcode,pname,inprice,outprice,toma,lastcou,ptype,mark,createtime) VALUES (21, 'f-12tt', '西瓜', 2.4, 4, '张三', 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 (22, 'f-15at', '苹果', 2.5, 4.2, '张三', 34, 'a', 1, to_date('26-06-2016', 'dd-mm-yyyy')); INSERT INTO product(ID,pcode,pname,inprice,outprice,toma,lastcou,ptype,mark,createtime) VALUES (23, 'f-34at', '菠萝', 3.5, 4.8, '张三', 12, 'a', 1, to_date('20-04-2016', 'dd-mm-yyyy')); --1. 删除'猪肉'的那条记录
DELETE FROM product WHERE pname='猪肉'; -- 2.将'扳手'和'钳子'的商品名称分别改为'16号扳手'和'小号钳子', 并将这两个商品的管理人员都改为'赵六'
UPDATE product p SET pname='16号扳手',p.toma='赵六' WHERE pname='扳手'; UPDATE product p SET pname='小号钳子', p.toma='赵六' WHERE pname='钳子'; -- 3.查询商品类型为'a'的所有商品, 并按照售价由大到小排序
SELECT * FROM product WHERE ptype='a' ORDER BY outprice DESC; -- 4.查询王五负责的c类商品
SELECT * FROM product WHERE ptype='c' AND toma='王五'; -- 5.查询剩余数量小于50的所有商品
SELECT * FROM product WHERE lastcou < 50; -- 6.查询剩余数量在60-80之间的所有商品
SELECT * FROM product WHERE lastcou BETWEEN 60 AND 80; -- 7.查询商品名称带'笔'的和带'鱼'的记录
SELECT * FROM product WHERE pname LIKE '%笔'; SELECT * FROM product WHERE pname LIKE '%鱼'; -- 8.查询商品编号中带'tt'的记录
SELECT * FROM product WHERE pcode LIKE '%tt'; -- 9.查询商品类型为a, d, c的所有商品
SELECT * FROM product WHERE ptype=ANY('a','b','c'); --方法二
SELECT * FROM product WHERE ptype IN('a','b','c'); -- 10.查询商品类型不是a, d, c的所有商品, 并且不是苹果
SELECT * FROM product WHERE ptype!=ALL('a','b','c') AND pname!='苹果'; -- 11.查询商品编号重复的记录, 并将他们删除
select p.* from product p where p.pcode in (select p.pcode from product p group by p.pcode having count(1) > 1); DELETE product WHERE pcode IN(select p.pcode from product p group by p.pcode having count(1) > 1); -- 12.查询整个表中每一类商品的剩余数量, 并按照剩余数由大到小排序(三种方法)
SELECT p.ptype, SUM(p.lastcou) FROM product p GROUP BY p.ptype ORDER BY 2 DESC
SELECT p.ptype, SUM(p.lastcou) FROM product p GROUP BY p.ptype ORDER BY SUM(p.lastcou) DESC
SELECT p.ptype, SUM(p.lastcou) 剩余数量 FROM product p GROUP BY p.ptype ORDER BY 剩余数量 DESC
-- 13.查询所有'e'类型商品, 并按照剩余库存数量排序
SELECT p.* FROM product p WHERE p.ptype='e' ORDER BY p.lastcou; -- 14.查询管理商品种类数量少于4的管理员名称和管理的商品种类数量
select p.toma,count(1) from product p group by p.toma having count(1) < 4; -- 15.查询所有的管理员名称和其手上所管理的所有商品种类数量
SELECT p.toma,COUNT(1) FROM product p GROUP BY p.toma; -- 16.查询所有的管理员名称和其手上所管理的所有'a'类商品种类数量, 数量为0的直接显示为0
SELECT p.toma,SUM(decode(p.ptype,'a',1,0))FROM product p GROUP BY p.toma ; -- 17.查询所有商品中剩余数量最少的一个
SELECT p.* FROM product p WHERE p.lastcou IN(SELECT MIN(p.lastcou) FROM product p ); -- 18.查询生产日期在2015-5-31之前的数据
SELECT p.* FROM product p WHERE p.createtime < TO_DATE('2015-5-31','yyyy-mm-dd'); -- 19.假设所有商品的有效期是一年(按照365天计算), 截止到2017-06-01为标准, 查询所有商品的信息, 并且加一列标注是否过期
SELECT p.*,CASE WHEN TO_date('2017-06-01','yyyy-mm-dd') - p.createtime >365 THEN '过期'ELSE '正常' end 是否过期 FROM product p ; -- 20.将所有过期的商品的有效标志改为0
UPDATE product p SET p.mark= 0 WHERE TO_date('2017-06-01','yyyy-mm-dd') - p.createtime >365; COMMIT; -- 21.查询每个管理员所管理的所有商品的平均进价价格和售价价格, 并四舍五入保留2位小数
SELECT p.toma,round(AVG(p.inprice),2),round(AVG(p.outprice),2) FROM product p GROUP BY p.toma; -- 22.查询假设每个管理员把所有商品售完后各自的利润总和(不计进价和售价为空的商品)
SELECT p.toma,SUM((p.outprice -p.inprice)*p.lastcou) 利润 FROM product p GROUP BY p.toma;