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 is the difference between the 2 operating modes of database mirroring?
What is a partition function in sql server?
What is the order in which the sql query is executed?
Tell me about pre-defined functions of sql?
What are the steps you can take to avoid “deadlocks”?
Explain the disadvantages/limitation of the cursor?
what are candidate key, alternate key and composite key? : Sql server database administration
What is proper subset of candidate key?
What’s the distinction between dropping a info and taking a info offline?
What is normalization of database?
Is resultset an interface?
How many null values we can have in a unique key field in sql server?
What is a schema in ms sql server 2005?
What is a table called, if it has neither cluster nor non-cluster index? What is it used for?
What are the difficulties faced in cube development? : sql server analysis services, ssas