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.
- 3 Answers
- 4965 Views
- Techno Solutions, I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
What is constraints and its types?
Explain different types of lock modes in sql server 2000?
What do you understand by triggers and mention the different types of it?
What are partitioned views and distributed partitioned views?
Suppose we have a table "MyTable" containing 10 rows, what query should be executed to update the odd rows "Salary" as 9000?
Why use “nolock” in sql server?
What are triggers? How do you invoke a trigger on demand?
What is merge replication?
What is the purpose of indexing?
Name the different type of indexes in sql?
How to drop an existing table with "drop table" statements in ms sql server?
Explain forward - only cursors?
If a user does not have permission to a table, but has permission to a view created on it, will he be able to view the data in table?
While using a cursor, how can you differentiate between a deleted row and a row that has been inserted with null data values?
How to insert multiple rows with one insert statement in ms sql server?
