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
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
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?