﻿ SQL Challenges-1: Distributor who purchased all types of item from the company - 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
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 (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:

﻿

## 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