I have a website that allows customers to browse and place
orders for certain products.
I have 2 tables; Customers and Orders. The Customers table
holds the customer records and the Orders table
holds the orders placed by the customer. Both tables are
tied based on the field Cust_ID.
Example of the data is shown below:
Cust_ID Cust_Name Cust_ID Product Amount Order_Date
1001 John Tan 1001 P-5211 $120.00 2/13/2006
1002 Michael Wong 1001 K-1428 $88.90 1/11/2006
1003 Mary Cheong 1003 C-0923 $82.50 1/27/2006
1004 Ahmad Suffian 1003 K-1428 $88.90 2/2/2006
Write a single SQL statement that extracts all purchase
records with the following criteria:
1. Customer names starting with “M” only.
2. Orders placed within the current month only.
3. Amount does not exceed $100.00
The list must be sorted by order date with the latest order
showing on top.
Answer Posted / bobby
IN SQL SERVER
select * from (select
c.cust_ID,Cust_Name,product,Amount,order_Date from Customer
c join Orders o
on c.cust_Id=o.cust_ID)p where Cust_Name like 'M%'and
(datename("mm",order_Date)=datename("mm",getdate()))
and amount<100 order by order_Date desc
Is This Answer Correct ? | 1 Yes | 0 No |
Post New Answer View All Answers
what are the basic functions for master, msdb, model, tempdb and resource system databases? : sql server database administration
How to provide default values to stored procedure parameters?
What extended events?
How you can minimize the deadlock situation?
You have modified 100 store procedures and want to replicate these changes from development to prodution, and production can have users using the Server/DB, how would you replicate without causing issues?
Is it possible to update the views? If yes, how, if not, why?
What does select 1 mean?
How to attach adventureworkslt physical files to the server?
How to delete existing triggers using "drop trigger"?
What is nolock hint in sql server 2008
What is the maximum row of a size?
how to create “alternate row colour”?
What are the High-Availability solutions in SQL Server and differentiate them briefly?
Difference between aggregate functions of sql?
What language is sql server written in?