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?


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..

Edited by Stjerne: a

4 Years
Discussion Span
Last Post by Dani

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....

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.