SQL Challenges-1: Distributor who purchased all types of item from the company

Last update on December 20 2024 11:39:25 (UTC/GMT +8 hours)

From the following table write a SQL query to find those distributors who purchased all types of item from the company. Return distributors ids.

Input:

Table: items

Structure:

Data:

item_code is the primary key column for items table.

Table: orders

Structure:

Data:

item_ordered is a foreign key to items table.

Sample Solution:

SQL Code(MySQL):

CREATE TABLE items (item_code int not null unique, item_name varchar(255));
INSERT INTO items VALUES (10091,'juice');
INSERT INTO items VALUES (10092,'chocolate');
INSERT INTO items VALUES (10093,'cookies');
INSERT INTO items VALUES (10094,'cake');

CREATE TABLE orders (order_id int, distributor_id int, item_ordered int, item_quantity int,
foreign key(item_ordered) references items(item_code));
INSERT INTO orders VALUES (1,501,10091,250);
INSERT INTO orders VALUES (2,502,10093,100);
INSERT INTO orders VALUES (3,503,10091,200);
INSERT INTO orders VALUES (4,502,10091,150);
INSERT INTO orders VALUES (5,502,10092,300);
INSERT INTO orders VALUES (6,504,10094,200);
INSERT INTO orders VALUES (7,503,10093,250);
INSERT INTO orders VALUES (8,503,10092,250);
INSERT INTO orders VALUES (9,501,10094,180);
INSERT INTO orders VALUES (10,503,10094,350);

SELECT distributor_id
FROM (
    SELECT t.distributor_id, COUNT(*) AS item_count
    FROM (
        SELECT DISTINCT distributor_id, item_ordered
        FROM orders
        WHERE item_ordered IN (SELECT item_code FROM items)
        ORDER BY distributor_id
    ) AS t
    GROUP BY t.distributor_id
) AS u
WHERE u.item_count = (SELECT COUNT(item_code) FROM items);

Sample Output:

distributor_id|
--------------|
           503|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Find those salespersons whose commission is less than ten thousand.
Next: Actors and Directors who jointly worked three or more movies.