w3resource

SQL Challenges-1: Customers without any Order

SQL Challenges-1: Exercise-6 with Solution

From the following tables, write a SQL query to find those customers who never ordered anything. Return customer name.

Input:

Table: customers

Structure:

FieldTypeNullKeyDefaultExtra
customer_idint(11)YES
customer_namevarchar(255)YES

Data:

customer_idcustomer_name
101Liam
102Josh
103Sean
104Evan
105Toby

Table: orders

structure:

order_idcustomer_idorder_dateorder_amount
4011032012-03-084500
4021012012-09-153650
4031022012-06-274800

Sample Solution:

SQL Code(MySQL):

CREATE TABLE IF NOT EXISTS customers (customer_id int, customer_name varchar(255));
TRUNCATE TABLE customers;
INSERT INTO customers (customer_id, customer_name) VALUES ('101', 'Liam');
INSERT INTO customers (customer_id, customer_name) VALUES ('102', 'Josh');
INSERT INTO customers (customer_id, customer_name) VALUES ('103', 'Sean');
INSERT INTO customers (customer_id, customer_name) VALUES ('104', 'Evan');
INSERT INTO customers (customer_id, customer_name) VALUES ('105', 'Toby');	
CREATE TABLE IF NOT EXISTS orders (order_id int, customer_id int, order_date Date, order_amount int);
TRUNCATE TABLE orders;
INSERT INTO orders (order_id, customer_id,order_date,order_amount) VALUES ('401', '103','2012-03-08','4500');
INSERT INTO orders (order_id, customer_id,order_date,order_amount) VALUES ('402', '101','2012-09-15','3650');
INSERT INTO orders (order_id, customer_id,order_date,order_amount) VALUES ('403', '102','2012-06-27','4800');
SELECT * FROM customers;
SELECT * FROM orders;

SELECT customer_name as customers
FROM customers
WHERE customer_id NOT IN
(
SELECT customer_id FROM orders
);

Sample Output:

Customers|
---------|
Evan     |
Toby     |

Solution-1:

SELECT customer_name AS customers
FROM customers cus
LEFT JOIN orders ord
ON cus.customer_id = ord.customer_id
WHERE ord.customer_id IS NULL;

Solution-2:

SELECT customer_name AS Customers 
FROM customers WHERE 0 = 
(
SELECT COUNT(*) FROM orders 
WHERE customers.customer_id = orders.customer_id
);

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Duplicate Emails.
Next: Remove Duplicate Emails.



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