Database Help (SQL)

[NoBrakes]

I hope this is the right form to ask this question...if not I apologize, I am a novice here. Well let me get to the problem (=.

I'm taking a database class and to be brief we have just a practice
database with 6 tables (Tb_Supplier, Tb_Consumer, Tb_Product,
Tb_Offers, Tb_Requests, and Tb_Transactions)
For extra credit one of our problems is as follows:
Write an SQL statement which returns ALL Supplier Names who Offer ALL
Products EXCEPT computers, cars, and tvs. Does anyone have any advice
how this might be accomplished? Here is our tables..and what I have tried/thought of so far.

I'll show you what I've "attempted so
far. But here are the created tables first, of course we later added
primary/foreign key constraints.

CREATE TABLE [dbo].[Tb_Consumer] (
[Con_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[Name] [char] (10) NOT NULL ,
[City] [char] (15) NULL
)
GO

CREATE TABLE [dbo].[Tb_Offers] (
[Supp_ID] [bigint] NOT NULL ,
[Prod_ID] [bigint] NOT NULL ,
[Quantity] [decimal](18, 0) NULL ,
[Price] [money] NULL
)
GO

CREATE TABLE [dbo].[Tb_Product] (
[Prod_ID] [bigint] IDENTITY (1, 1) NOT NULL,
[Name] [char] (10)NOT NULL ,
[MU] [char] (7) NULL
)
GO

CREATE TABLE [dbo].[Tb_Requests] (
[Con_ID] [bigint] NOT NULL ,
[Prod_ID] [bigint] NOT NULL ,
[Quantity] [decimal](18, 0) NULL ,
[Price] [money] NULL
)
GO

CREATE TABLE [dbo].[Tb_Supplier] (
[Supp_ID] [bigint] IDENTITY (1, 1) NOT NULL,
[Name] [char] (10) NOT NULL ,
[City] [char] (15) NULL
)
GO

CREATE TABLE [dbo].[Tb_Transactions] (
[Tran_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[Supp_ID] [bigint] NOT NULL ,
[Con_ID] [bigint] NOT NULL ,
[Prod_ID] [bigint] NOT NULL ,
[Quantity] [decimal](18, 0) NULL ,
[Price] [money] NULL
)
GO

We then added primary/foreign key constraints. For the Supplier,
Consumer, and Product tables...the primary key(s) are Supp_ID, Con_ID,
and Prod_Id respectively.
For the Offers Table, it is linked to the supplier and product tables
through its two foreign keys. (Supp_ID, and Prod_ID)
For the Requests Table, it is linked to the consumer and product
tables through its two foreign keys (Con_ID and Prod_ID)
And lastly, the Transactions table is linked to the Supplier,
Consumer, and Product tables through foreign keys (Supp_ID, Con_ID,
and Prod_ID)

The query I'm trying to solve for extra credit is to return ALL
supplier names, who offer ALL products, EXCEPT cars, computers, and
tvs.

Here is what I have tried/ my thoughts. I first tried breaking it
into parts and seeing if I could solve them. For instance, I wanted
to return all suppliers NOT offering computers, cars, or tvs. I
accomplished that with the following query.

SELECT Name
FROM Tb_Supplier
WHERE NOT EXISTS (SELECT *
FROM Tb_Offers, Tb_Product
WHERE Tb_Offers.Prod_ID=Tb_Product.Prod_ID
AND (Tb_Product.Name='computer'
OR Tb_Product.Name='car'
OR Tb_Product.Name='tv'))

(also wrote it using the NOT IN statement)

SELECT Name
FROM Tb_Supplier
WHERE Supp_ID NOT IN
(SELECT DISTINCT Supp_ID
FROM Tb_Offers, Tb_Product
WHERE Tb_Offers.Prod_ID=Tb_Product.Prod_ID
AND (Tb_Product.Name='computer'
OR Tb_Product.Name='car'
OR Tb_Product.Name='tv'))




Then I wrote the query to return the list of the suppliers who offer
all products.

SELECT Name
FROM Tb_Supplier S
WHERE NOT EXISTS(SELECT *
FROM Tb_Product P
WHERE NOT EXISTS (SELECT *
FROM Tb_Offers
WHERE S.Supp_ID=Tb_Offers.Supp_ID
AND Prod_ID=P.Prod_ID))


Now here is where I am stuck. I can't just simply combine the queries
with an "and" as follows:

SELECT Name
FROM Tb_Supplier S
WHERE NOT EXISTS(SELECT *
FROM Tb_Product P
WHERE NOT EXISTS (SELECT *
FROM Tb_Offers
WHERE S.Supp_ID=Tb_Offers.Supp_ID
AND Prod_ID=P.Prod_ID))
AND Supp_ID NOT IN
(SELECT DISTINCT Supp_ID
FROM Tb_Offers, Tb_Product
WHERE Tb_Offers.Prod_ID=Tb_Product.Prod_ID
AND (Tb_Product.Name='computer'
OR Tb_Product.Name='car'
OR Tb_Product.Name='tv'))

This is contradictory since I first select all suppliers offering ALL
products (meaning they offer everything INCLUDING computers, tvs, and
cars) while I leave out the suppliers who I DO want, who offer
everything EXCEPT computers, tvs, and cars. This query I came up with
always returns nothing.

I have been working on this solution and trying everything. I just
can't figure it out )=. Please help. I'm not just some kid looking
for "answers" on his homework, I really have worked on this. I could use some advice.

[OS-Wiz]

Hint: UNION