query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

shawn4069
Starting Member

1 Post

Posted - 2007-04-19 : 18:07:28

Can someone verify if these are correct?

CUSTOMER(Customer_ID, Customer_Name, Customer_address, City, State, Postal_code)

EMPLOYEE (Employee_ID, Employee_Name, Employee_Phone)

ORDER(Order_ID, Order_date, Customer_ID, Employee_ID)

ORDER_LINE(Order_ID, Product_ID, Order_Quanitity)

PRODUCT(Product_ID, Product_Name, Material, Unit_Price)

1)List each item ordered for Order#1001(Order_ID=1001), it's unit price and total price of each item ordered.

select p.product_id, p.unit_price, p.unit_price from product where ol.order_id='1001';

2)List the customer(Customer_ID, Customer_name, order_quantitiy) who has purchased the largest quantity of any product offered.

select c.customer_id, c.customer_name, ol.order_quantity from customer c, order_line ol, order o where c.customer_id=(select o.customer_id from Order OP , order_line OLP where ol.order_quanitity=max(olp.order_quantity ));

3)Delete orders placed by customer Betty Wise in January 2000

delete from order_id OID where OID.customer_id IN(select c.customer_id from customer where OID.order_date<'January 2000'>

4)List the states with more than one customer.

select state, count(state) from customer GROUP BY state HAVING count(state)>1;

5)List all the products (Product_Name) and the number of times each product has been ordered.

select p.product_name, count(product_id) from Product P, Orderline OL where P.product_id= OL.product_id;

6)List the product ID, product name, quantity of products ordered, and total cost for each order. Only include orders that have more than five products ordered. Sort the result by descending total cost.

Need help with this one...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-19 : 20:30:47

Have you try to run your query at all ?

quote:

1) List each item ordered for Order#1001(Order_ID=1001), it's unit price and total price of each item ordered.

select p.product_id, p.unit_price, p.unit_price from product where ol.order_id='1001';

few mistake here.
1. you prefix the column name with 'p' which clearly it is an alias. But you did named any table the alias p
you should have named it like : select p.product_id, . . . from product p
2. the query reference ol.order_id. table ol is not used in this query at all
You need to inner join product table to order_line table.
something like this

FROM product p INNER JOIN order_line ol ON p.product_id = ol.product_id

For the rest, please try to run the query and SQL Server will inform you if there are any syntax error etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources