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
From the following tables write a SQL query to find the details of an order. Return ord_no, ord_date, purch_amt, Customer Name, grade, Salesman, commission.
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:
Pictorial presentation:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: From the following tables write a SQL query to find those salespersons do not live in the same city where their customers live and received a commission from the company more than 12%. Return Customer Name, customer city, Salesman, salesman city, commission.
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.
Test your Programming skills with w3resource's quiz.
What is the difficulty level of this exercise?
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework