
SQL Exercises: Make a report with customer name, city, order no. order date, purchase amount for only those customers on the list who must have a grade and placed one or more orders or which order(s) have been placed by the customer who is neither in the list not have a grade
SQL JOINS: Exercise-16 with Solution
Write a SQL statement to make a report with customer name, city, order no. order date, purchase amount for only those customers on the list who must have a grade and placed one or more orders or which order(s) have been placed by the customer who is neither in the list not have a grade.
Sample table: customer
Sample table: orders
Sample Solution:
SELECT a.cust_name,a.city, b.ord_no,
b.ord_date,b.purch_amt AS "Order Amount"
FROM customer a
FULL OUTER JOIN orders b
ON a.customer_id=b.customer_id
WHERE a.grade IS NOT NULL;
Output of the Query:
cust_name city ord_no ord_date Order Amount Brad Guzan London 70009 2012-09-10 270.65 Nick Rimando New York 70002 2012-10-05 65.26 Geoff Cameron Berlin 70004 2012-08-17 110.50 Brad Davis New York 70005 2012-07-27 2400.60 Nick Rimando New York 70008 2012-09-10 5760.00 Fabian Johnson Paris 70010 2012-10-10 1983.43 Geoff Cameron Berlin 70003 2012-10-10 2480.40 Jozy Altidor Moscow 70011 2012-08-17 75.29 Nick Rimando New York 70013 2012-04-25 3045.60 Graham Zusi California 70001 2012-10-05 150.50 Graham Zusi California 70007 2012-09-10 948.50 Julian Green London 70012 2012-06-27 250.45
Explanation:

Pictorial presentation:

Query Visualization:
Duration:

Rows:

Cost:

N.B.: In certain instances not null is removed in table structure, so results may vary.
Practice Online
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a SQL statement to make a report with customer name, city, order no. order date, purchase amount for those customers from the existing list who placed one or more orders or which order(s) have been placed by the customer who is not on the list
Next: Write a SQL statement to make a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa.
What is the difficulty level of this exercise?
New Content: Composer: Dependency manager for PHP, R Programming