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.
Answers were Sorted based on User's Feedback
Answer / santhi kandasamy
In oracle.,
Select * from customers, orders where
CUSTOMERS.CUST_ID=ORDERS.CUST_ID AND
Cust_name like 'M%' AND
TO_CHAR(orderdate,'mm,yyyy')= TO_CHAR(sysdate,'mm,yyyy') AND
SUBSTR(amount,2)<=100.00
ORDER BY ORDERDATE DESC;
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / 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 |
Answer / santhi kandasamy
In oracle.,
Select * from customers, orders where
CUSTOMERS.CUST_ID=ORDERS.CUST_ID AND
Cust_name like 'M%' AND
TO_CHAR(orderdate,'mm,yyyy')= TO_CHAR(sysdate,'mm,yyyy') AND
SUBSTR(amount,2)<=100.00;
Is This Answer Correct ? | 0 Yes | 0 No |
Delete duplicate rows without using rowid.
What is update_statistics command?
What is create statement?
How to add additional conditions in SQL?
Rate yourself in .NET and SQL ?
1 Answers Cognizant, HCL, SunGard,
Explain the use of containers in ssis and also their types?
What is coalesce and check constraint in sql server?
Explain the purpose of indexes?
What is use of attributehierarchyenabled? : sql server analysis services, ssas
How to perform key word search in tables?
which database is best to use in oracle and sql server? explain reasons?
What is the meaning of resultset type_scroll_insensitive?