Examples can be used for a workshop (practical work) on MySQL queries.

Query Q021. The following query, MySQL calculates how much you have sold for products that have id 4 or 6:

SELECT Sum(amount*price) as outcome_sum
FROM m_outcome
WHERE product_id=4 OR product_id=6;

Query Q022. In this example, the MySQL query calculates how much was sold on June 12, 2011 for items that have code 4 or 6:

SELECT Sum(amount*price) AS outcome_sum
FROM m_outcome
WHERE (product_id=4 OR product_id=6) AND dt="2011-6-12";

Query Q023. The task is as follows. Calculate how much the total amount was credited to the goods category "Хлебобулочные изделия"(Bakery).

To solve this problem, you need to operate with three tables: m_income, m_product and m_category, because:
- the quantity and price of the goods posted are stored in the m_income table;
- the category code of each product is stored in the m_product table;
- the title category name is stored in the m_category table.

To solve this problem, we use the following algorithm:
- the definition of the category code "Bakery products" from the table m_category by means of a subquery;
- joining the m_income and m_product tables to determine the category of each goods received;
- calculation of the amount of the arrival (= quantity * price) for goods, the category code of which is equal to the code defined by the above subquery.

So:

SELECT Sum(amount*price) AS income_sum
FROM m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id
WHERE ctgry_id = (SELECT id FROM m_category WHERE title='Хлебобулочные изделия');

Query Q024. The task of calculating the total amount of the accrued goods of the category "Bakery products" is solved by the following algorithm:

- each record of the m_income table, depending on the value of its product_id, from the m_category table, Name of the category;
- select records for which the category is equal to "Bakery products";
- calculate the amount of the parish = quantity * price.

So:

SELECT Sum(amount*price) AS income_sum
FROM m_product AS a 
INNER JOIN m_income AS b ON a.id=b.product_id
INNER JOIN m_category AS c ON a.ctgry_id=c.id
WHERE c.title='Хлебобулочные изделия';

Query Q025. This example calculates how many items have been spent:

SELECT COUNT(DISTINCT product_id) AS product_cnt
FROM m_outcome  AS t;

Query Q026. The GROUP BY clause is used to group records. Usually, records are grouped by the value of one or more fields, and an aggregate operation is applied to each group. For example, the following query collects a report on the sale of goods. That is, a table is generated in which there will be the names of the goods and the amount they are sold to:

SELECT title, SUM(amount*price) AS outcome_sum
FROM m_product AS a INNER JOIN m_outcome AS b 
ON a.id=b.product_id
GROUP BY title;

Query Q027. A sales report is displayed by category. That is, a table is generated in which there will be the names of the categories of goods, the total amount for which the goods of these categories are sold, and the average amount of sales. The ROUND function is used to round off the average value to the hundredth part (the second character after the integer and fractional part separator):

SELECT c.title, SUM(amount*price) AS outcome_sum, 
ROUND(AVG(amount*price),2) AS outcome_sum_avg
FROM m_product AS a 
INNER JOIN m_outcome AS b ON a.id=b.product_id
INNER JOIN m_category AS c ON a.ctgry_id=c.id
GROUP BY c.title;

Query Q028. Calculates the total and average number of receipts for each product and displays information about products with a total receipt of at least 500:

SELECT product_id, SUM(amount) AS amount_sum, 
ROUND(AVG(amount),2) AS amount_avg
FROM m_income
GROUP BY product_id
HAVING Sum(amount)>=500;

Query Q029. For each product, the amount and average value of its revenues in the second quarter of 2011 are calculated. If the total amount of goods arrival is not less than 1000, then the information about this product is displayed:

SELECT title, SUM(amount*price) AS income_sum
FROM m_income a INNER JOIN m_product b ON a.product_id=b.id
WHERE dt BETWEEN "2011-4-1" AND "2011-6-30"
GROUP BY title
HAVING SUM(amount*price)>=1000;

Query Q030. In some cases, you need to map each record of a table to each entry in another table; Which is called the Cartesian product. The table formed as a result of such a connection is called the Descartes table. For example, if some table A has 100 entries and table B has 15 entries, then their Descartes table will consist of 100 * 15 = 1500 records. The following query connects each record of the m_income table with each record of the m_outcome table:

SELECT *
FROM m_income, m_outcome;