﻿ SQL Challenges-1: Number of customers, orders, and total order amount for each city - w3resource

# SQL Challenges-1: Number of customers, orders, and total order amount for each city

## SQL Challenges-1: Exercise-71 with Solution

From the following tables write a query in SQL to find the number of customer issued orders, number of orders they have issued along with the total order amount for each cities. Those cities should come in result set where four or more orders have been issued. Returns city name, number of customers, number of orders and total order amount.

Table: customer

Structure:

FieldTypeNullKeyDefaultExtra
cust_codevarchar(10)NOPRI
cust_namevarchar(25)YES
cust_cityvarchar(25)YES
cust_countryvarchar(25)YES

Data:

C00001MichealNew YorkUSA2
C00002BoltNew YorkUSA3
C00004WinstonBrisbanAustralia1
C00005SasikantMumbaiIndia1
C00007RamanathanChennaiIndia1
C00009RameshMumbaiIndia3
C00010CharlesHampshairUK3
C00011SundariyaChennaiIndia3
C00012StevenSan JoseUSA1
C00013HolmesLondonUK2
C00014RangarappaBangaloreIndia2
C00015StuartLondonUK1
C00016VenkatpatiBangaloreIndia2
C00017SrinivasBangaloreIndia2
C00018FlemingBrisbanAustralia2
C00019YearannaiduChennaiIndia1
C00020AlbertNew YorkUSA3
C00021JacksBrisbanAustralia1
C00022AvinashMumbaiIndia2
C00023KarlLondonUK0
C00024CookLondonUK2
C00025RavindranBangaloreIndia2

Table: orders

Structure:

FieldTypeNullKeyDefaultExtra
ord_numintNOPRI
ord_amountintYES
ord_datedateYES
cust_codevarchar(10)YESMUL

Data:

ord_numord_amountord_datecust_code
20010010002008-01-08C00015
20010130002008-07-15C00001
20010220002008-05-25C00012
20010315002008-05-15C00021
20010415002008-03-13C00006
20010525002008-07-18C00025
20010625002008-04-20C00005
20010745002008-08-30C00007
20010840002008-02-15C00008
20010935002008-07-30C00011
20011030002008-04-15C00019
20011110002008-07-10C00020
20011220002008-05-30C00016
20011340002008-06-10C00022
20011435002008-08-15C00002
2001165002008-07-13C00010
2001178002008-10-20C00014
2001185002008-07-20C00023
20011940002008-09-16C00007
2001205002008-07-20C00009
20012115002008-09-23C00008
20012225002008-09-16C00003
2001235002008-09-16C00022
2001245002008-06-20C00017
20012520002008-10-10C00018
2001265002008-06-24C00022
20012725002008-07-20C00015
20012835002008-07-20C00009
20012925002008-07-20C00024
20013025002008-07-30C00025
2001319002008-08-26C00012
20013312002008-06-29C00009
20013442002008-09-25C00004
20013520002008-09-16C00007

Sample Solution:

SQL Code(MySQL):

``````create table customer(
cust_code		varchar(10) not null unique,
cust_name		varchar(25),
cust_city		varchar(25),
cust_country	varchar(25),
);

insert into customer values('C00013','Holmes     ','London    ','UK       ', 2);
insert into customer values('C00001','Micheal    ','New York  ','USA      ', 2);
insert into customer values('C00020','Albert     ','New York  ','USA      ', 3);
insert into customer values('C00025','Ravindran  ','Bangalore ','India    ', 2);
insert into customer values('C00024','Cook       ','London    ','UK       ', 2);
insert into customer values('C00015','Stuart     ','London    ','UK       ', 1);
insert into customer values('C00002','Bolt       ','New York  ','USA      ', 3);
insert into customer values('C00018','Fleming    ','Brisban   ','Australia', 2);
insert into customer values('C00021','Jacks      ','Brisban   ','Australia', 1);
insert into customer values('C00019','Yearannaidu','Chennai   ','India    ', 1);
insert into customer values('C00005','Sasikant   ','Mumbai    ','India    ', 1);
insert into customer values('C00007','Ramanathan ','Chennai   ','India    ', 1);
insert into customer values('C00022','Avinash    ','Mumbai    ','India    ', 2);
insert into customer values('C00004','Winston    ','Brisban   ','Australia', 1);
insert into customer values('C00023','Karl       ','London    ','UK       ', 0);
insert into customer values('C00006','Shilton    ','Torento   ','Canada   ', 1);
insert into customer values('C00010','Charles    ','Hampshair ','UK       ', 3);
insert into customer values('C00017','Srinivas   ','Bangalore ','India    ', 2);
insert into customer values('C00012','Steven     ','San Jose  ','USA      ', 1);
insert into customer values('C00008','Karolina   ','Torento   ','Canada   ', 1);
insert into customer values('C00003','Martin     ','Torento   ','Canada   ', 2);
insert into customer values('C00009','Ramesh     ','Mumbai    ','India    ', 3);
insert into customer values('C00014','Rangarappa ','Bangalore ','India    ', 2);
insert into customer values('C00016','Venkatpati ','Bangalore ','India    ', 2);
insert into customer values('C00011','Sundariya  ','Chennai   ','India    ', 3);

create table orders(
ord_num		integer(10) not null unique,
ord_amount		integer,
ord_date	date,
cust_code	varchar(10),
foreign key(cust_code) references customer(cust_code)
);

insert into orders values(200114, 3500,'2008-08-15','C00002');
insert into orders values(200122, 2500,'2008-09-16','C00003');
insert into orders values(200118,  500,'2008-07-20','C00023');
insert into orders values(200119, 4000,'2008-09-16','C00007');
insert into orders values(200121, 1500,'2008-09-23','C00008');
insert into orders values(200130, 2500,'2008-07-30','C00025');
insert into orders values(200134, 4200,'2008-09-25','C00004');
insert into orders values(200108, 4000,'2008-02-15','C00008');
insert into orders values(200103, 1500,'2008-05-15','C00021');
insert into orders values(200105, 2500,'2008-07-18','C00025');
insert into orders values(200109, 3500,'2008-07-30','C00011');
insert into orders values(200101, 3000,'2008-07-15','C00001');
insert into orders values(200111, 1000,'2008-07-10','C00020');
insert into orders values(200104, 1500,'2008-03-13','C00006');
insert into orders values(200106, 2500,'2008-04-20','C00005');
insert into orders values(200125, 2000,'2008-10-10','C00018');
insert into orders values(200117,  800,'2008-10-20','C00014');
insert into orders values(200123,  500,'2008-09-16','C00022');
insert into orders values(200120,  500,'2008-07-20','C00009');
insert into orders values(200116,  500,'2008-07-13','C00010');
insert into orders values(200124,  500,'2008-06-20','C00017');
insert into orders values(200126,  500,'2008-06-24','C00022');
insert into orders values(200129, 2500,'2008-07-20','C00024');
insert into orders values(200127, 2500,'2008-07-20','C00015');
insert into orders values(200128, 3500,'2008-07-20','C00009');
insert into orders values(200135, 2000,'2008-09-16','C00007');
insert into orders values(200131,  900,'2008-08-26','C00012');
insert into orders values(200133, 1200,'2008-06-29','C00009');
insert into orders values(200100, 1000,'2008-01-08','C00015');
insert into orders values(200110, 3000,'2008-04-15','C00019');
insert into orders values(200107, 4500,'2008-08-30','C00007');
insert into orders values(200112, 2000,'2008-05-30','C00016');
insert into orders values(200113, 4000,'2008-06-10','C00022');
insert into orders values(200102, 2000,'2008-05-25','C00012');

SELECT cust_city, COUNT(o.ord_num) as "Number of orders",
COUNT(distinct c.cust_code) as "Number of Customer",
SUM(ord_amount) as "Total order Amountt"
FROM customer c LEFT JOIN orders o
ON c.cust_code = o.cust_code
GROUP BY cust_city
HAVING COUNT(o.ord_num) >= 4
```
```

Sample Output:

```cust_city |Number of orders|Number of Customer|Total order Amountt|
----------+----------------+------------------+-------------------+
Bangalore |               5|                 4|               8300|
Chennai   |               5|                 3|              17000|
London    |               4|                 4|               6500|
Mumbai    |               7|                 3|              12700|
Torento   |               4|                 3|               9500|
```

SQL Code Editor: