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:


Contribute your code and comments through Disqus.

Previous: List the items sold out within a specific period.
Next: Salesman who makes Largest Number of transactions.



Follow us on Facebook and Twitter for latest update.