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

Last update on December 20 2024 11:59:00 (UTC/GMT +8 hours)

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:

Data:

Table: orders

Structure:

Data:

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.