1 / 13

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

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

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

## PowerPoint Slideshow about ' Sales Ledger and Stock Control ER Problem O.' - brinly

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

Chase Younger ▪ Kristin Hamilton ▪ Santiago Paiz

### Sales Ledger and Stock ControlER 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 removing or modifying certain statements from query

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

▪ 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() 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 removing or modifying certain statements from queryMySQL user-created variables

SELECT SUM(quantityOrdered*sale_price)

INTO @salesTotal

FROM productOrders;

SELECT SUM(quantity*stock_price)

FROM Restock;

SELECT FLOOR(@[email protected]) AS productSalesProfit;

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