Write a single SQL statement that lists all suppliers supplying only hammers usi

Question

Write a single SQL statement that lists all suppliers supplying only hammers using the difference operation.

When finished, stop the spooling and exit the program.

Load SQL file that contains:

/* log in as SYSTEM and run this script */
SET ECHO ON
SET SQLBLANKLINES ON

REM The next command will fail on the first run
DROP USER iggy CASCADE;
CREATE USER iggy PROFILE DEFAULT
IDENTIFIED BY oracle DEFAULT TABLESPACE USERS
ACCOUNT UNLOCK;
GRANT CONNECT TO iggy;
GRANT RESOURCE TO iggy;

CREATE TABLE iggy.supplier (
supplierName VARCHAR(32) NOT NULL,
PRIMARY KEY (supplierName))
TABLESPACE USERS;

CREATE TABLE iggy.part (
partName VARCHAR(32) NOT NULL,
PRIMARY KEY (partName))
TABLESPACE USERS;

CREATE TABLE iggy.quote (
supplierName VARCHAR(32) NOT NULL,
partName VARCHAR(32) NOT NULL,
quote NUMBER(8,2) NOT NULL,
PRIMARY KEY (supplierName,partName),
FOREIGN KEY (supplierName) REFERENCES iggy.supplier,
FOREIGN KEY (partName) REFERENCES iggy.part)
TABLESPACE USERS;

/* ------------ Add Data to Tables -------------- */

INSERT INTO iggy.supplier
VALUES ('NEW YANKEE WORKSHOP, INC.');
INSERT INTO iggy.supplier
VALUES ('OLD YANKEE WORKSHOP, INC.');
INSERT INTO iggy.supplieR
VALUES ('TOOL TIME, INC.');

INSERT INTO iggy.part
VALUES ('HAMMER');
INSERT INTO iggy.part
VALUES ('NAIL');

INSERT INTO iggy.quote
VALUES ('NEW YANKEE WORKSHOP, INC.','HAMMER','1.89');
INSERT INTO iggy.quote
VALUES ('NEW YANKEE WORKSHOP, INC.','NAIL','0.19');
INSERT INTO iggy.quote
VALUES ('OLD YANKEE WORKSHOP, INC.','HAMMER','2.09');
INSERT INTO iggy.quote
VALUES ('TOOL TIME, INC.','HAMMER','1.99');
INSERT INTO iggy.quote
VALUES ('TOOL TIME, INC.','NAIL','0.20');

Explanation / Answer

SELECT iggy.supplierName,T.partName FROM iggy.quote inner join (
SELECT supplierName,partName,count(supplierName) FROM iggy.quote
GROUP BY partName HAVING count(supplierName)=1) T
on iggy.supplierName=T.supplierName
WHERE T.partName='Hamme'

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.