数据更新

代码仓库： https://github.com/SKPrimin/HomeWork/tree/main/SQLSever/数据更新

1插入一个供应商‘S6’的信息，具体参数自己设定；

INSERT 
INTO S168(SNO,SNAME,STAT,CITY)
VALUES('S6','亳州制造厂','A','亳州')

2插入一个零件‘P7’的信息，具体参数自己设定；

INSERT 
INTO P168(PNO,PNAME,COLOR,WT)
VALUES('P7','螺丝刀','红','30')

3插入一个项目’J8’的信息，具体参数自己设定；

INSERT 
INTO J168(JNO,JNAME,CITY)
VALUES('J8','亳州机场','亳州').

4插入‘S6’供应项目’J8’ 零件P7的信息，供应量为500；

INSERT 
INTO SPJ168(SNO,PNO,JNO,QTY)
VALUES('S6','P7','J8','500')

5计算各个供应商供应各种零件的平均供货量，并将结果存放在数据库中（先建表）*；

CREATE TABLE Savg_QTY
(SNO CHAR(10),
PNO CHAR(10),
avg_grade INT)

INSERT
INTO Savg_QTY
	SELECT SNO,PNO,AVG(QTY)
	FROM SPJ168
	GROUP BY SNO,PNO 

6将所有供应工程“J2”零件“P3”的供应商改为S1；

UPDATE SPJ168
SET SNO = 'S1'
WHERE JNO = 'J2' AND PNO = 'P3'

7修改供应量大于100的供应详情，将供应量增加100；

UPDATE SPJ168
SET QTY = QTY + 100 
WHERE QTY > 100

8将J1项目的最大供应量降为为原来的一半；

UPDATE SPJ168 
SET QTY = QTY / 2 
WHERE
	QTY >= ALL ( SELECT QTY FROM SPJ168 WHERE JNO = 'J001' )

9删除S6供应商信息（注意参照完整性）；

DELETE 
FROM S168
WHERE SNO = 'S6'

10删除零件‘P7’的信息；

DELETE 
FROM P168
WHERE PNO = 'P7'

11删除项目’J8’的信息；

DELETE 
FROM J168
WHERE JNO = 'J8'

12删除项目‘J7’的所有供应信息；

DELETE 
FROM SPJ168
WHERE JNO = 'J007'

13删除 “天津安贝儿”供应商的所有供应信息；*

DELETE 
FROM
	SPJ168 
WHERE
	SNO IN ( SELECT SNO FROM S168 WHERE SNAME = '天津安贝儿' )

14删除使用了“北京”供应商供应的“螺丝刀”零件的供应信息和工程项目信息；

DELETE 
FROM
	SPJ168 
WHERE
	SNO = ( SELECT SNO FROM S168 WHERE SPJ168.SNO = S168.SNO AND CITY = '北京') AND PNO = ( SELECT PNO FROM P168 WHERE SPJ168.PNO = P168.PNO AND PNAME = '螺丝刀')