--1. select spbh,spname,jj,lsj from t-sp --2. select spname,jj,lsj,lsj-jj as ce from t-sp --3. alter table t-spjc add xsshl decimal(14,2) --4. update t-sp set lsj=jj*1.15 --5. delect from t-sp a where not exists(select 1 from t-spjc where spid=a.spid --6. select * from t-sp a where exists(select 1 from t-spjc group by spid having count(*)>1 --7. select left(spid,3) as 类号,avg(jj),avg(lsj) from t-sp group by left(spid,3) --8. delete from t-spjc a where exists(select 1 from t-sp where spid=a.spid)
--1. SELECT spbh,spname,Kcsl,Jj,Lsj FROM T-sp S LEFT JOIN T-Spjc SP ON S.spid=SP.spid --2. SELECT Jj,Lsj,Jj-Lsj FROM T-sp --3. ALTER TABLE T-Spjc ADD xsshl Decimal(14,2) --4. UPDATE T-sp SET Lsj=Jj+15 --5. DELETE FROM Jj-Lsj WHERE Kcsl=0 --6. SELECT * FROM T-sp WHERE spbh IN( SELECT spbh FROM T-sp GROUP BY spbh having count(spbh)>1) --7. SELECT LEFT(spbh,3),SUM(Jj),SUM(Lsj) FROM T-sp GROUP BY LEFT(spbh,3) --8. DELETE T FROM T-Spjc T WHERE NOT EXISTS(SELECT 1 FROM T-sp WHERE T.spid=spid)
create table T-sp ( spid varchar(20) primary key,--商品id spbh varchar(15),--商品编号 spname varchar(50),--商品名称 Jj decimal(14,3),--进价 Lsj decimal(14,3),--零售价 )create table T-Spjc ( spid varchar(20) primary key,--商品ID Kcsl decimal(14,2)--库存量 )--1.列出所有商品的商品编号、品名、库存数量、进价金额、零售金额 select a.spbh,a.spname,b.Kcsl,a.Jj,a.Lsj from T-sp a,T-Spjc b where a.spid=b.spid --2.试求出所有商品的进价金额、零售金额和零售金额与进价金额之间的差额。 select Jj,Lsj,Lsj-Jj from T-sp --3. 给表T-Spjc增加一个字段xsshl,字段属性为:Decimal(14,2)。 alter table T-Spjc add xsshl decimal(14,2) --4、将所有商品的零售价定义为:在进价的基础上加15个点 update T-sp set Lsj=Lsj+Lsj*0.15 --5、删除所有库存数量为零的商品信息。 delete from T-sp a where not exists(select 1 from T-Spic where spid=a.spid) --6、试列出所有商品编号重复的商品 。 select * from T-sp where spbh in(select spbh from T-sp group by spbh having count(spbh)>1) --7、如果商品编号的前三位表示商品分类,请列出每类商品的进价金额和零售金额 select left(spbh,3),Jj,Lsj from T-sp --8、请从T-Spjc表中删除在T-sp表中不存在的商品信息 delete from T-Spjc a where not exists(select 1 from T-sp where spid=a.spid)
select spbh,spname,jj,lsj from t-sp
--2.
select spname,jj,lsj,lsj-jj as ce from t-sp
--3.
alter table t-spjc add xsshl decimal(14,2)
--4.
update t-sp set lsj=jj*1.15
--5.
delect from t-sp a where not exists(select 1 from t-spjc where spid=a.spid
--6.
select * from t-sp a where exists(select 1 from t-spjc group by spid having count(*)>1
--7.
select left(spid,3) as 类号,avg(jj),avg(lsj) from t-sp group by left(spid,3)
--8.
delete from t-spjc a where exists(select 1 from t-sp where spid=a.spid)
SELECT spbh,spname,Kcsl,Jj,Lsj FROM T-sp S LEFT JOIN T-Spjc SP ON S.spid=SP.spid
--2.
SELECT Jj,Lsj,Jj-Lsj FROM T-sp
--3.
ALTER TABLE T-Spjc ADD xsshl Decimal(14,2)
--4.
UPDATE T-sp SET Lsj=Jj+15
--5.
DELETE FROM Jj-Lsj WHERE Kcsl=0
--6.
SELECT * FROM T-sp WHERE spbh IN(
SELECT spbh FROM T-sp GROUP BY spbh having count(spbh)>1)
--7.
SELECT LEFT(spbh,3),SUM(Jj),SUM(Lsj) FROM T-sp GROUP BY LEFT(spbh,3)
--8.
DELETE T FROM T-Spjc T WHERE NOT EXISTS(SELECT 1 FROM T-sp WHERE T.spid=spid)
create table T-sp
(
spid varchar(20) primary key,--商品id
spbh varchar(15),--商品编号
spname varchar(50),--商品名称
Jj decimal(14,3),--进价
Lsj decimal(14,3),--零售价
)create table T-Spjc
(
spid varchar(20) primary key,--商品ID
Kcsl decimal(14,2)--库存量
)--1.列出所有商品的商品编号、品名、库存数量、进价金额、零售金额
select a.spbh,a.spname,b.Kcsl,a.Jj,a.Lsj from T-sp a,T-Spjc b where a.spid=b.spid
--2.试求出所有商品的进价金额、零售金额和零售金额与进价金额之间的差额。
select Jj,Lsj,Lsj-Jj from T-sp
--3. 给表T-Spjc增加一个字段xsshl,字段属性为:Decimal(14,2)。
alter table T-Spjc add xsshl decimal(14,2)
--4、将所有商品的零售价定义为:在进价的基础上加15个点
update T-sp set Lsj=Lsj+Lsj*0.15
--5、删除所有库存数量为零的商品信息。
delete from T-sp a where not exists(select 1 from T-Spic where spid=a.spid)
--6、试列出所有商品编号重复的商品 。
select * from T-sp where spbh in(select spbh from T-sp group by spbh having count(spbh)>1)
--7、如果商品编号的前三位表示商品分类,请列出每类商品的进价金额和零售金额
select left(spbh,3),Jj,Lsj from T-sp
--8、请从T-Spjc表中删除在T-sp表中不存在的商品信息
delete from T-Spjc a where not exists(select 1 from T-sp where spid=a.spid)