Can Somebody help me solve this?

[DaCD]

PERSON
OID_PERSON

PREFIX
FIRSTNAME
LASTNAME
SUFFIX
GENDER




ADDRESS
OID_ADDRESS

OID_PERSON (FK)
ADDRESS1
ADDRESS2
CITY
STATE
ZIP
PLUS4


TRANSACTIONS
OID_TRANSACTION

OID_PERSON (FK)
TRANSACTION_DATE
TRANSACTION_AMOUNT
TRANSACTION_TYPE





With SQL, how can you return FirstName, LastName and Address components for records with the LastName of “Jackson” and the zip of 75063?


With SQL, how can you identify the Person with the largest total amount of money spent and return their FirstName, LastName, Address components, and the total amount of all their transactions?


With SQL, how can you identify the Person with the most purchase transactions and return their FirstName, LastName, Address components, and the number of transactions they have made?

 

[niall29]

This should work for your first question:

Select

p.FirstName,
p.LastName,
a.Address1,
a.Address2,
a.city,
a.zip,
a.plus4

From
Person p left join Address a on (p.OID_Person = a.OID_Person)

where
p.LastName = 'Jackson' and a.Zip = '75063'

 

[niall29]

As for the 2nd question Start it nearly the same only this time use Tranactions t
and in the select statement do a sum(t.TRANSACTION_AMOUNT)total
You also could do a order by total Desc:
which will show everyone but put the highest to the top

3rd Question
Is again like before only this time use count(t.OID_PERSON)