w3resource

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

SQL Challenges-1: Exercise-27 with Solution

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:

FieldTypeNullKeyDefaultExtra
item_codeint(11)NoPRI
item_namevarchar(255)YES

Data:

item_codeitem_name
10091juice
10092chocolate
10093cookies
10094cake

item_code is the primary key column for items table.

Table: orders

Structure:

FieldTypeNullKeyDefaultExtra
order_idint(11)YES
distributor_idint(11)YES
item_orderedint(11)YESMUL
item_quantityint(11)YES

Data:

order_iddistributor_iditem_ordereditem_quantity
150110091250
250210093100
350310091200
450210091150
550210092300
650410094200
750310093250
850310092250
950110094180
1050310094350

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.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8