1,105,534 Community Members

SQL Queries

Member Avatar
Stjerne
Junior Poster in Training
53 posts since Sep 2011
Reputation Points: 14 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Stjerne
Junior Poster in Training
53 posts since Sep 2011
Reputation Points: 14 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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
Stjerne
Junior Poster in Training
53 posts since Sep 2011
Reputation Points: 14 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Never mind, managed to solve it!

Member Avatar
Dani
The Queen of DaniWeb
20,569 posts since Feb 2002
Reputation Points: 1,356 [?]
Q&As Helped to Solve: 931 [?]
Skill Endorsements: 204 [?]
Administrator
Featured
Sponsor
 
0
 

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

Question Answered as of 2 Years Ago by Dani
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article