1 / 13

Sales Ledger and Stock Control ER Problem O.

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

brinly
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. [Team 7] Chase Younger ▪ Kristin Hamilton ▪ Santiago Paiz Sales Ledger and Stock ControlER Problem O.

  2. Breakdown of one of our lab9 reports showing effect of removing or modifying certain statements from query (ORDER BY, GROUP BY, SUM())

  3. Relevant tables

  4. “What is the biggest supplier – quantity” 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

  5. ▪ ORDER BY (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;

  6. ▪ ORDER BY (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

  7. ▪ ORDER BY (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;

  8. ▪ GROUP BY 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;

  9. ▪ GROUP BY(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.

  10. ▪ SUM() 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;

  11. ▪ GROUP BY, SUM() 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;

  12. Example using MySQL 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;

  13. end of Team 7 presentation

More Related