Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

SQL Subquery Exercises: Find all orders attributed to a salesman in New York

SQL SUBQUERY: Exercise-5 with Solution

5. From the following tables, write a SQL query to find all the orders generated in New York city. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

Sample table: Salesman


Sample table: Orders


Sample Solution: 

SELECT *
FROM orders
WHERE salesman_id IN
    (SELECT salesman_id 
     FROM salesman 
     WHERE city ='New York');

Output of the Query:

ord_no	purch_amt	ord_date	customer_id	salesman_id
70002	65.26		2012-10-05	3002		5001
70005	2400.60		2012-07-27	3007		5001
70008	5760.00		2012-09-10	3002		5001
70013	3045.60		2012-04-25	3002		5001

Explanation:

SQL Subqueries: Find all orders attributed to a salesman in New York.

Practice Online


Inventory database model

Query Visualization:

Duration:

Query visualization of Find all orders attributed to a salesman in New York - Duration

Rows:

Query visualization of Find all orders attributed to a salesman in New York - Rows

Cost:

Query visualization of Find all orders attributed to a salesman in New York - Cost

Contribute your code and comments through Disqus.

Previous: From the following tables, write a SQL query to find the order values greater than the average order value of 10th October 2012. Return ord_no, purch_amt, ord_date, customer_id, salesman_id.
Next: From the following tables, write a SQL query to find the commission of the salespeople work in Paris City. Return commission.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



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