Team 7 chase younger kristin hamilton santiago paiz
Download
1 / 13

Sales Ledger and Stock Control ER Problem O. - PowerPoint PPT Presentation


  • 105 Views
  • Uploaded on
  • Presentation posted in: General

[Team 7] Chase Younger ▪ Kristin Hamilton ▪ Santiago Paiz. Sales Ledger and Stock Control ER Problem O. Breakdown of one of our lab9 reports showing effect of removing or modifying certain statements from query. (ORDER BY, GROUP BY, SUM()). Relevant tables.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha

Download Presentation

Sales Ledger and Stock Control ER Problem O.

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Team 7 chase younger kristin hamilton santiago paiz

[Team 7]

Chase Younger ▪ Kristin Hamilton ▪ Santiago Paiz

Sales Ledger and Stock ControlER Problem O.


Sales ledger and stock control er problem o
Breakdown of one of our lab9 reports showing effect of removing or modifying certain statements from query

(ORDER BY, GROUP BY, SUM())


Relevant tables
Relevant tables removing or modifying certain statements from query


What is the biggest supplier quantity
“What is the biggest supplier – quantity” removing or modifying certain statements from query

SELECT R.supplier, S.name, SUM(R.quantity) totalQty

FROM Restock R, Suppliers S

WHERE R.supplier = S.suppID

GROUP BY R.supplier

ORDER BY totalQtydesc;

largest

totalQty

*

smallest

totalQty


Order by 1 of 3
▪ ORDER BY removing or modifying certain statements from query(1 of 3)

SELECT R.supplier, S.name, SUM(R.quantity) totalQty

FROM Restock R, Suppliers S

WHERE R.supplier = S.suppID

GROUP BY R.supplier

ORDER BY totalQtydesc;


Order by 2 of 3
▪ ORDER BY removing or modifying certain statements from query(2 of 3)

SELECT R.supplier, S.name, SUM(R.quantity) totalQty

FROM Restock R, Suppliers S

WHERE R.supplier = S.suppID

GROUP BY R.supplier

ORDER BY totalQtydesc;

smallest

totalQty

largest

totalQty


Order by 3 of 3
▪ ORDER BY removing or modifying certain statements from query(3 of 3)

SELECT R.supplier, S.name, SUM(R.quantity) totalQty

FROM Restock R, Suppliers S

WHERE R.supplier = S.suppID

GROUP BY R.supplier

ORDER BY S.name;


Group by
▪ GROUP BY removing or modifying certain statements from query

SELECT R.supplier, S.name, SUM(R.quantity) totalQty

FROM Restock R, Suppliers S

WHERE R.supplier = S.suppID

GROUP BY R.supplier

ORDER BY totalQtydesc;


Group by contd
▪ GROUP BY removing or modifying certain statements from query(contd)

SELECT R.supplier, S.name, SUM(R.quantity) totalQty

FROM Restock R, Suppliers S

WHERE R.supplier = S.suppID

GROUP BY R.supplier

ORDER BY totalQtydesc;

= 500 + 380 + 350 + 315 + 50 + 20 + 3.


Sales ledger and stock control er problem o
▪ SUM() removing or modifying certain statements from query

SELECT R.supplier, S.name, SUM(R.quantity) totalQty

FROM Restock R, Suppliers S

WHERE R.supplier = S.suppID

GROUP BY R.supplier

ORDER BY R.quantitydesc;


Group by sum
▪ GROUP BY, SUM() removing or modifying certain statements from query

SELECT R.supplier, S.name, SUM(R.quantity) totalQty

FROM Restock R, Suppliers S

WHERE R.supplier = S.suppID

GROUP BY R.supplier

ORDER BY R.quantitydesc;


Example using mysql user created variables
Example using removing or modifying certain statements from queryMySQL user-created variables

SELECT SUM(quantityOrdered*sale_price)

INTO @salesTotal

FROM productOrders;

SELECT SUM(quantity*stock_price)

INTO@restockTotal

FROM Restock;

SELECT FLOOR(@salesTotal-@restockTotal) AS productSalesProfit;


End of team 7 presentation
end of Team 7 presentation removing or modifying certain statements from query


ad
  • Login