Write a single SQL statement that lists all suppliers supplying ONLY hammers WIT

Question

Write a single SQL statement that lists all suppliers supplying ONLY hammers WITHOUT using the difference operation. (A join or subquery would be appropriate.)

The output should only be showing OLD YANKEE WORKSHOP, INC. as this supplier only has hammer without nails

Data provided below

SQL> DESCRIBE iggy.part; Name Null? Type PARTNAME NOT NULL VARCHAR2 (32) SQL> SELECT* FROM iggy.part; PARTNAME HAMMER NAIL SQL> DESCRIBE iggy.supplier; Name Null? Type NOT NULL VARCHAR2(32) SUPPLIERNAME SQL> SELECT FROM iggy. supplier; SUPPLIERNAME NEW YANKEE WORKSHOP, INC OLD YANKEE WORKSHOP, INC TOOL TIME, INC SQL> DESCRIBE iggy.quote; Name Null? Type NOT NULL VARCHAR2 (32) NOT NULL VARCHAR2 (32) NOT NULL NUMBER (8,2) SUPPLIERNAME PARTNAME QUOTE SQL> SELECT* FROM iggy.quote; SUPPLIERNAME PARTNAME QUOTE NEW YANKEE WORKSHOP, INC NEW YANKEE WORKSHOP, INC OLD YANKEE WORKSHOP, INC TOOL TIME, INC TOOL TIME, INC HAMMER NAIL HAMMER HAMMER NAIL 1.89 .19 2.09 1.99

Explanation / Answer

Join

Select iggy.supplier.suppliername from iggy.supplier inner join iggy.quote on iggy.supplier.suppliername = iggy.quote.suppliername group by iggy.supplier.suppliername having partname = 'HAMMER' and partname != 'NAIL' ;

The tables supplier and quote are joined on common attribute suppliername and then where clause is used to extract suppliers who supply partname HAMMER.

Subquery

Select * from iggy.supplier where suppliername !=( Select suppliername from iggy.quote.suppliername where partname = != 'HAMMER' );

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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