商店、商品表

【代码】

CREATE TABLE A(
A# CHAR(10) PRIMARY KEY,
ANAME CHAR(10),
WQTY SMALLINT,
CITY CHAR(10));

CREATE TABLE B(
B# CHAR(10) PRIMARY KEY,
BNAME CHAR(10),
PRICE INT);

CREATE TABLE AB(
A# CHAR(10),
B# CHAR(10),
QTY INT,
PRIMARY KEY(A#,B#),
FOREIGN KEY(A#) REFERENCES A(A#),
FOREIGN KEY(B#) REFERENCES B(B#));

【插入】

INSERT
INTO A
VALUES('101','SHAOSHAN',15,'CHANGSHA'),
('204','QIANMEN',89,'BEIJING'),
('256','DONGFENG',501,'BEIJING'),
('345','TIEDAO',76,'CHANGSHA'),
('620','DIYIBAIHUO',413,'SHANGHAI');

INSERT
INTO B
VALUES
('1','MAOBI',21),
('2','YUMAOQIU',784),
('3','SHOUYINGJI',1325),
('4','BACKPACK',242);

INSERT
INTO AB
VALUES
('101','1',105),
('101','2',42),
('101','3',25),
('101','4',104),
('204','3',61),
('256','1',241),
('256','2',91),
('345','1',141),
('345','2',18),
('345','4',74),
('620','4',125);

【查询】找出至少供应了代号为'256'的商店所供应的全部商品的其他商品的商店名和所在城市

SELECT DISTINCT A#,ANAME,CITY
FROM A
WHERE A# <> '256' AND A# IN(
SELECT A#
FROM AB X
WHERE NOT EXISTS(
SELECT*
FROM AB Y
WHERE Y.A# = '256' AND NOT EXISTS(
SELECT* 
FROM AB Z
WHERE Z.A# = X.A# AND Z.B#=Y.B#) ) );