Monday, February 11, 2019

SQL question challenge (Customer with no orders)

SQL Challenge
Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything.

Table: Customer.

+----+-------+
| Id | Name  |   
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Using the above tables as example, return the following:

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

DDL SCRIPTS



create table customer (id int,name varchar(100))
insert into customer values(1,'Joe');
insert into customer values(2,'Henry');
insert into customer values(3,'Sam');
insert into customer values(4,'Max');

create table orders(id int,customer_id int);
insert into orders values(1,3);
insert into orders values(3,1);

 
solution

select cust.name from customer cust
 left join orders ord on cust.id=ord.id
 where ord.id is null;

No comments:

Post a Comment