Examples can be used for a workshop on MySQL queries.

Query Q051. The following MySQL query helps to determine whether users were able to enter information about the expense of goods worth more than the sum of the goods:

SELECT product_id, SUM(in_sum) AS income_sum, SUM(out_sum) AS outcome_sum
FROM (SELECT product_id, amount*price as in_sum, 0 as out_sum
from m_income
UNION ALL
SELECT product_id, 0 as in_sum, amount*price as out_sum
from m_outcome)  AS t
GROUP BY product_id
HAVING SUM(in_sum)<SUM(out_sum);

Query Q052. The numbering of the rows returned by the query is implemented differently. Can be renumbered using programming languages. For example, PHP. However, sometimes this must be done in the SQL query itself. So, the following MySQL query enumerates rows of the m_income table in accordance with the order of increasing ID field values:

SELECT COUNT(*) as N, b.id, b.product_id, b.amount, b.price
FROM m_income a INNER JOIN m_income b ON a.id <= b.id
GROUP BY b.id, b.product_id, b.amount, b.price;

Query Q053. This query MySQL displays the top five products among the total sales. To sample the first five records, use the LIMIT statement:

SELECT product_id, sum(amount*price) AS summa
FROM m_outcome
GROUP BY product_id
ORDER BY sum(amount*price) DESC
LIMIT 0, 5;

Query Q054. The top five products are listed based on the sales amount, and numbers the lines as a result:

SELECT COUNT(*) AS N, b.product_id, b.summa
FROM 
(SELECT product_id, sum(amount*price) AS summa, 
sum(amount*price)*10000000+product_id AS id 
FROM m_outcome GROUP BY product_id)  AS a 
INNER JOIN 
(SELECT product_id, sum(amount*price) AS summa, 
sum(amount*price)*10000000+product_id AS id 
FROM m_outcome GROUP BY product_id)  AS b 
ON a.id>=b.id
GROUP BY b.product_id, b.summa
HAVING COUNT(*)<=5
ORDER BY COUNT(*);