SQL Exercises: Display the list for the salesmen who works either for one or more customer or not yet join under any of the customers who placed either one or more orders or no order to their supplier
SQL JOINS: Exercise-13 with Solution
From the following tables write a SQL query to list all salespersons along with customer name, city, grade, order number, date, and amount. Condition for selecting list of salesmen : 1. Salesmen who works for one or more customer or, 2. Salesmen who not yet join under any customer, Condition for selecting list of customer : 3. placed one or more orders, or 4. no order placed to their salesman.
Sample table: customer
Sample table: salesman
Sample table: orders
Sample Solution:
SELECT a.cust_name,a.city,a.grade,
b.name AS "Salesman",
c.ord_no, c.ord_date, c.purch_amt
FROM customer a
RIGHT OUTER JOIN salesman b
ON b.salesman_id=a.salesman_id
RIGHT OUTER JOIN orders c
ON c.customer_id=a.customer_id;
Output of the Query:
cust_name city grade Salesman ord_no ord_date purch_amt Brad Guzan London Pit Alex 70009 2012-09-10 270.65 Nick Rimando New York 100 James Hoog 70002 2012-10-05 65.26 Geoff Cameron Berlin 100 Lauson Hen 70004 2012-08-17 110.50 Brad Davis New York 200 James Hoog 70005 2012-07-27 2400.60 Nick Rimando New York 100 James Hoog 70008 2012-09-10 5760.00 Fabian Johnson Paris 300 Mc Lyon 70010 2012-10-10 1983.43 Geoff Cameron Berlin 100 Lauson Hen 70003 2012-10-10 2480.40 Jozy Altidor Moscow 200 Paul Adam 70011 2012-08-17 75.29 Nick Rimando New York 100 James Hoog 70013 2012-04-25 3045.60 Graham Zusi California 200 Nail Knite 70001 2012-10-05 150.50 Graham Zusi California 200 Nail Knite 70007 2012-09-10 948.50 Julian Green London 300 Nail Knite 70012 2012-06-27 250.45
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: Write a SQL statement to make a list in ascending order for the salesmen who works either for one or more customer or not yet join under any of the customers.
Next: Write a SQL statement to make a list for the salesmen who either work for one or more customers or yet to join any of the customer. The customer, may have placed, either one or more orders on or above order amount 2000 and must have a grade, or he may not have placed any order to the associated supplier.
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