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:
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
item_code | int(11) | No | PRI | ||
item_name | varchar(255) | YES |
Data:
item_code | item_name |
---|---|
10091 | juice |
10092 | chocolate |
10093 | cookies |
10094 | cake |
item_code is the primary key column for items table.
Table: orders
Structure:
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
order_id | int(11) | YES | |||
distributor_id | int(11) | YES | |||
item_ordered | int(11) | YES | MUL | ||
item_quantity | int(11) | YES |
Data:
order_id | distributor_id | item_ordered | item_quantity |
---|---|---|---|
1 | 501 | 10091 | 250 |
2 | 502 | 10093 | 100 |
3 | 503 | 10091 | 200 |
4 | 502 | 10091 | 150 |
5 | 502 | 10092 | 300 |
6 | 504 | 10094 | 200 |
7 | 503 | 10093 | 250 |
8 | 503 | 10092 | 250 |
9 | 501 | 10094 | 180 |
10 | 503 | 10094 | 350 |
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
MySQL select 10 random rows from 600K rows fast:
SELECT name FROM random AS r1 JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM random)) AS id) AS r2 WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1
Ref: https://bit.ly/3GdCTM3
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook