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 Query on Multiple Tables Exercises: Find all customers who placed orders on October 5, 2012

SQL Query on multiple tables : Exercise-8 with Solution

From the following table, write a SQL query to find those customers who placed orders on October 5, 2012. Return customer_id, cust_name, city, grade, salesman_id, ord_no, purch_amt, ord_date, customer_id and salesman_id.

Sample table: Customer


Sample table: Orders


Sample Solution: 

SELECT *
FROM customer a,orders  b 
WHERE a.customer_id=b.customer_id 
AND b.ord_date='2012-10-05';

Output of the Query:

customer_id	cust_name	city		grade	salesman_id	ord_no	purch_amt	ord_date	customer_id	salesman_id
3002		Nick Rimando	New York	100	5001		70002	65.26		2012-10-05	3002		5001
3005		Graham Zusi	California	200	5002		70001	150.50		2012-10-05	3005		5002

Relational Algebra Expression:

Relational Algebra Expression: Find all customers with orders on October 5, 2012.

Relational Algebra Tree:

Relational Algebra Tree: Find all customers with orders on October 5, 2012.

Explanation:

SQL Subqueries: Find all customers with orders on October 5, 2012.
Inventory database model

Contribute your code and comments through Disqus.

Previous: From the following tables, write a SQL query to find those orders executed by the salesperson, ordered by the customer whose grade is greater than or equal to 200. Compute purch_amt*commission as "Commission". Return customer name, commission as "Commission%" and Commission.
Next: SQL Exercises, Practice, Solution - JOINS.

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