SQL Exercises: Find all customers with orders on October 5, 2012
Orders on Specific Date
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
customer_id | cust_name | city | grade | salesman_id
-------------+----------------+------------+-------+-------------
3002 | Nick Rimando | New York | 100 | 5001
3007 | Brad Davis | New York | 200 | 5001
3005 | Graham Zusi | California | 200 | 5002
3008 | Julian Green | London | 300 | 5002
3004 | Fabian Johnson | Paris | 300 | 5006
3009 | Geoff Cameron | Berlin | 100 | 5003
3003 | Jozy Altidor | Moscow | 200 | 5007
3001 | Brad Guzan | London | | 5005
Sample table: orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
Sample Solution:
-- This query selects all columns ('*') from the 'customer' table (aliased as 'a') and the 'orders' table (aliased as 'b').
-- It retrieves data where the 'customer_id' column in the 'a' table (customer) matches the 'customer_id' column in the 'b' table (orders),
-- and the 'ord_date' column in the 'b' table is equal to '2012-10-05'.
SELECT *
-- Specifies the tables from which to retrieve the data (in this case, 'customer' aliased as 'a' and 'orders' aliased as 'b').
FROM customer a, orders b
-- Specifies the conditions for joining the tables and filtering the data.
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
Code Explanation:
The said query in SQL that performs a join between two tables: 'customer' and 'orders'. It returns all columns (*) from both tables where the customer_id column in table 'customer' matches the customer_id column in table 'orders' and the ord_date in table 'orders' is equal to '2012-10-05'.
Relational Algebra Expression:
Relational Algebra Tree:
Explanation:
For more Practice: Solve these Related Problems:
- Write a SQL query to retrieve all order and customer details for orders placed on October 5, 2012, ensuring that each customer appears only once.
- Write a SQL query to list order numbers, customer names, and purchase amounts for orders on October 5, 2012, ordering the results by customer name.
- Write a SQL query to display customer and order details for orders on October 5, 2012, filtering out orders where the purchase amount is below 100.
- Write a SQL query to fetch customer information along with their order details for orders on October 5, 2012, grouping by customer and computing the total purchase amount per customer.
- Write a SQL query to list orders and corresponding customer details for October 5, 2012, including only those customers whose city names are longer than six characters.
Contribute your code and comments through Disqus.
Previous SQL Exercise: Find salesman commission details of given customer.
Next SQL Exercise: SQL Exercises, Practice, Solution - JOINS.
What is the difficulty level of this exercise?