SQL server 2. Create a query that will show how much money has paid out to the o
ID: 3604103 • Letter: S
Question
SQL server
2. Create a query that will show how much money has paid out to the owners of the sold items and what the profit was on that item. As a reminder, the owner of New 2 You retains 25% of the purchase price of any item as part of her cost. Include in your query the first and last name of the owner as one field, the amount paid to the owner and the profit. Sort your output by the owner's last name. Round your numbers to 2 decimal places and only print out 2 decimal places. My output looked like the following:
Owner Name Pay Out Profit
------------------------------- -------- --------
Lamson Andrew 63.75 21.25
Jacob Blake 7.88 2.63
Miguel Cabrera 157.88 52.63
Jason Cook 5.44 1.81
Anna Dillinger 69.56 23.19
Prince Fielder 93.75 31.25
Jackie Griffen 36.00 12.00
Thomas Griffen 11.25 3.75
Fanthon James 11.25 3.75
LeMay Joe 108.75 36.25
Paul Jones 60.00 20.00
Jason Kingston 37.46 12.49
Ginter Lisa 18.75 6.25
Venske Mark 75.00 25.00
Smith Megan 5.25 1.75
Wilson Melisa 19.49 6.50
Roberta Menchu 11.25 3.75
Bateman Michael 61.88 20.63
LeMay Mike 9.00 3.00
Veerasamy Raja 11.63 3.88
Raja Rakesh 15.75 5.25
Raja Ram 9.38 3.13
Amy Reeves 2.24 0.75
April Roberts 108.75 36.25
Samantha Ross 43.13 14.38
Bob Scott 35.96 11.99
Martina Shelton 1.13 0.38
Jacksonn Susan 3.75 1.25
Mike Walker 11.25 3.75
Sue Wilson 15.00 5.00
SQL file: https://drive.google.com/open?id=0BwasM-ovulfxcF9VX2RFbmUyU2M
ERD PDF: https://drive.google.com/open?id=0BwasM-ovulfxeUgtclNvb3FSMG8
Explanation / Answer
Hi,
Below is the query-
select lastame+' '+firstname as 'Owner Name', round((oi.price-0.25*oi.price),2) as Payout, round((oi.price-i.max_price),2) as profit from people p join item i
on p.peopleid=i.ownerid
join order_item oi
on i.itemid=oi.itemid
order by lastname
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.