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



I have a website that allows customers to browse and place orders for certain products. I have 2 ..

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

I have a website that allows customers to browse and place orders for certain products. I have 2 ..

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

I have a website that allows customers to browse and place orders for certain products. I have 2 ..

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

Post New Answer

More SQL Server Interview Questions

Write the Syntax for Cursors.

2 Answers   CarrizalSoft Technologies, HP,


What are the operating modes in which database mirroring runs?

0 Answers  


What are the basic functions for master, msdb, model, tempdb databases?

0 Answers  


2) Consider a Table name A which has below records ID --- 5 5 5 5 5 Consider another table B which has below records ID -- 5 5 5 5 5 5 5 5 How many rows will be returned by each of the below queries a) select * from A inner join B on A.id = b.ID b) select * from A left join B on A.id = b.ID c) select * from A right join B on A.id = b.ID

2 Answers   Synechron, TCS,


What objects does the fn_my_permissions function reports on? : sql server security

0 Answers  






How to connect of datebase with sql express.?

0 Answers   MCN Solutions,


How can we Use Linked Server? Uses of Linked server

1 Answers   IBM,


What are indexes? When do you need to create Indexes?

4 Answers   CarrizalSoft Technologies, HP,


How to insert a new row into a table with "insert into" statements in ms sql server?

0 Answers  


How to count groups returned with the group by clause in ms sql server?

0 Answers  


what is the importence of the trigger in sqlserver2000?

1 Answers   ATI,


how to control the amount of free space in your index pages? : Sql server database administration

0 Answers  


Categories