w3resource

SQL Challenges-1: Sales Person

SQL Challenges-1: Exercise-22 with Solution

From the following tables find those customers who did not make any order to the supplier 'DCX LTD'. Return customers name.

Input:

Table: customers

Structure:

FieldTypeNullKeyDefaultExtra
customer_idint(11)YES
customer_namevarchar(255)YES
customer_cityvarchar(255)YES
avg_profitint(11)YES

Data:

customer_idcustomer_namecustomer_cityavg_profit
101LiamNew York25000
102JoshAtlanta22000
103SeanNew York27000
104EvanToronto15000
105TobyDallas20000

Table: supplier

Structure:

FieldTypeNullKeyDefaultExtra
supplier_idint(11)YES
supplier_namevarchar(255)YES
supplier_cityvarchar(255)YES

Data:

supplier_idsupplier_namesupplier_city
501ABC INCDallas
502DCX LTDAtlanta
503PUC ENTNew York
504JCR INCToronto

Table: orders

Structure:

FieldTypeNullKeyDefaultExtra
order_idint(11)YES
customer_idint(11)YES
supplier_idint(11)YES
order_datedateYES
order_amountint(11)YES

Data:

order_idcustomer_idsupplier_idorder_dateorder_amount
4011035012012-03-084500
4021015032012-09-153650
4031025032012-06-274800
4041045022012-06-175600
4051045042012-06-226000
4061055022012-06-255600

Sample Solution:

SQL Code(MySQL):

DROP TABLE if exists customers;
CREATE TABLE customers (customer_id int, customer_name varchar(255), customer_city varchar(255), avg_profit int);
INSERT INTO customers  VALUES ('101', 'Liam','New York',25000);
INSERT INTO customers  VALUES ('102', 'Josh','Atlanta',22000);
INSERT INTO customers  VALUES ('103', 'Sean','New York',27000);
INSERT INTO customers  VALUES ('104', 'Evan','Toronto',15000);
INSERT INTO customers  VALUES ('105', 'Toby','Dallas',20000);

CREATE TABLE supplier (supplier_id int, supplier_name varchar(255), supplier_city varchar(255));
INSERT INTO supplier  VALUES ('501', 'ABC INC','Dallas');
INSERT INTO supplier  VALUES ('502', 'DCX LTD','Atlanta');
INSERT INTO supplier  VALUES ('503', 'PUC ENT','New York');
INSERT INTO supplier  VALUES ('504', 'JCR INC','Toronto');
	
CREATE TABLE orders (order_id int, customer_id int, supplier_id int, order_date Date, order_amount int);
INSERT INTO orders  VALUES (401, 103,501,'2012-03-08','4500');
INSERT INTO orders  VALUES (402, 101,503,'2012-09-15','3650');
INSERT INTO orders  VALUES (403, 102,503,'2012-06-27','4800');
INSERT INTO orders  VALUES (404, 104,502,'2012-06-17','5600');
INSERT INTO orders  VALUES (405, 104,504,'2012-06-22','6000');
INSERT INTO orders  VALUES (406, 105,502,'2012-06-25','5600');


SELECT * FROM customers;
SELECT * FROM supplier;
SELECT * FROM orders; 


SELECT cus.customer_name
FROM customers cus
WHERE cus.customer_id 
NOT IN (SELECT ord.customer_id
FROM orders ord
LEFT JOIN supplier sup 
ON ord.supplier_id = sup.supplier_id
WHERE sup.supplier_name = 'DCX LTD');

Sample Output:

customer_name|
-------------|
Liam         |
Josh         |
Sean         |

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Consecutive Availability of a doctor in a clinic.
Next: Highest Single marks.



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