﻿ SQL Challenges-1: Orders for companies not listed with distributor - w3resource

# SQL Challenges-1: Find the order id and the item name for all companies who are not registered with the distributor

## SQL Challenges-1: Exercise-50 with Solution

From the following table write a SQL query to find the order id and the item name for all companies who are not registered with the distributor. Return the result table in any order.

Input:

Table: company_info

Structure:

FieldTypeNullKeyDefaultExtra
company_id int(11)NOPRI
company_namevarchar(25)YES

Data:

company_id company_name
5001Intel
5002Kingston
5003Dell
5004Sony
5005Iball
5006Canon

Table: orders

Structure:

FieldTypeNullKeyDefaultExtra
order_idint(11)YES
item_namevarchar(25)YES
company_idint(11)YES

Data:

order_iditem_namecompany_id
101mother board5001
102RAM5002
103printer5006
104keyboard5005
105mouse6051
106speaker6009
107web cam5005
108hard disk5002
109monitor5003
110scanner7023

Sample Solution:

SQL Code(MySQL):

``````CREATE TABLE company_info (company_id int not null unique, company_name varchar(25));
INSERT INTO company_info VALUES(5001,'Intel');
INSERT INTO company_info VALUES(5002,'Kingston');
INSERT INTO company_info VALUES(5003,'Dell');
INSERT INTO company_info VALUES(5004,'Sony');
INSERT INTO company_info VALUES(5005,'Iball');
INSERT INTO company_info VALUES(5006,'Canon');

CREATE TABLE orders (order_id int, item_name varchar(25), company_id int);

INSERT INTO orders VALUES(101	,'mother board	',5001);
INSERT INTO orders VALUES(102	,'RAM			',5002);
INSERT INTO orders VALUES(103	,'printer		',5006);
INSERT INTO orders VALUES(104	,'keyboard		',5005);
INSERT INTO orders VALUES(105	,'mouse',6051);
INSERT INTO orders VALUES(106	,'speaker',6009);
INSERT INTO orders VALUES(107	,'web cam		',5005);
INSERT INTO orders VALUES(108	,'hard disk		',5002);
INSERT INTO orders VALUES(109	,'monitor		',5003);
INSERT INTO orders VALUES(110	,'scanner',7023);

SELECT orders.order_id, orders.item_name
FROM orders
Left Join company_info
on orders.company_id = company_info.company_id
WHERE company_info.company_name IS NULL;
```
```

Sample Output:

```order_id|item_name|
--------|---------|
105|mouse    |
106|speaker  |
110|scanner  |
```

SQL Code Editor: