Member Avatar for 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..

Edited by Stjerne because: a
  • 2 Contributors
  • 3 Replies
  • 82 Views
  • 52 Minutes Discussion Span
  • Latest Post Latest Post by Dani
Member Avatar for 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....

Member Avatar for Stjerne

Never mind, managed to solve it!

Member Avatar for Dani

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.