SQL Queries

[Stjerne]

Hello,

I'm pretty new to SQL and I have only worked with one-two table(s) at time. I got an assignment now against multiple tables, and the queries it wants me to solve is really hard. I was just wondering if you could help me to solve and understand these questions?

Tables:

Employee(Empno, FirstName, LastName, Address, Zip, Date of Birth, Job, Salary)
Category(Catno, Name)
Customer(Custno, FirstName, LastName, Addresse, Zip, Sex)
Salary(Salary, Salary at Year)
Order(OrderNo, OrderDate, Custno, Empno, SentDate, PaidDate)
OrderLine(OrderNo, Itemno, PricePerUnit, Total)
ZipPlace(Zip, Place)
PriceHistory(Itemno, Date, OldPrice)
Item(Itemno, Name, Prce, Catno, Total, Section)

The first one I'm having a hard time with is:

1. Find last name, first name and amount on the customer who bought the most total.

Could anyone tell me how I to solve this? Explain how..

[Stjerne]

This is what I did:

SELECT C.Lastname, C.Firstname, MAX(OL.PricePerUnit*OL.Total) AS Sum FROM Customer AS C, Orderline AS OL, Order AS O WHERE C.Custno = O.Custno AND O.OrderNo = OL.OrderNo;

My output is wrong. Sum should be 116271, I get 75000... I get the wrong names too.. The tables got over 6000 rows, so I can't count it by myself xD Also when I only write MAX(OL.PricePerUnit * OL.Total), I get 75000....

[Stjerne]

Never mind, managed to solve it!

[Dani]

Can you let us know what you did so it can help other people as well?