w3resource
SQL exercises

SQL Exercises: Display the details of a order i.e. order number, order date, amount of order, customer and salesman name and commission of the salesman for an order

SQL JOINS: Exercise-6 with Solution

Write a SQL statement to find the details of a order i.e. order number, order date, amount of order, which customer gives the order and which salesman works for that customer and how much commission he gets for an order.

Sample table: orders


Sample table: customer


Sample table: salesman


Sample Solution:

SELECT a.ord_no,a.ord_date,a.purch_amt,
b.cust_name AS "Customer Name", b.grade, 
c.name AS "Salesman", c.commission 
FROM orders a 
INNER JOIN customer b 
ON a.customer_id=b.customer_id 
INNER JOIN salesman c 
ON a.salesman_id=c.salesman_id;

Output of the Query:

 ord_no	ord_date	purch_amt	Customer Name	grade	Salesman	commission
70009	2012-09-10	270.65		Brad Guzan		Pit Alex	0.11
70002	2012-10-05	65.26		Nick Rimando	100	James Hoog	0.15
70004	2012-08-17	110.50		Geoff Cameron	100	Lauson Hen	0.12
70005	2012-07-27	2400.60		Brad Davis	200	James Hoog	0.15
70008	2012-09-10	5760.00		Nick Rimando	100	James Hoog	0.15
70010	2012-10-10	1983.43		Fabian Johnson	300	Mc Lyon		0.14
70003	2012-10-10	2480.40		Geoff Cameron	100	Lauson Hen	0.12
70011	2012-08-17	75.29		Jozy Altidor	200	Paul Adam	0.13
70013	2012-04-25	3045.60		Nick Rimando	100	James Hoog	0.15
70001	2012-10-05	150.50		Graham Zusi	200	Nail Knite	0.13
70007	2012-09-10	948.50		Graham Zusi	200	Nail Knite	0.13
70012	2012-06-27	250.45		Julian Green	300	Nail Knite	0.13

Explanation:

Syntax of details of a order

Pictorial presentation:

Result of details of a order
Result of details of a order

Query Visualization:

Duration:

Query visualization of Display the details of a order i.e. order number, order date, amount of order, customer and salesman name and commission of the salesman for an order - Duration

Rows:

Query visualization of Display the details of a order i.e. order number, order date, amount of order, customer and salesman name and commission of the salesman for an order - Rows

Cost:

Query visualization of Display the details of a order i.e. order number, order date, amount of order, customer and salesman name and commission of the salesman for an order - Cost

Practice Online


Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL statement to find the list of customers who appointed a salesman for their jobs who does not live in the same city where their customer lives, and gets a commission is above 12%.
Next: Write a SQL statement to make a join on the tables salesman, customer and orders in such a form that the same column of each table will appear once and only the relational rows will come.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming