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

In MySQL, you can add queries into queries. Subqueries are nothing more than a query within a query. Usually, the subquery is used in the WHERE clause. But there are other possible ways to use subqueries.

Query Q011. This MySQL query displays information about products from the m_product table, whose codes are also in the m_income table:

SELECT *
FROM m_product
WHERE id IN (SELECT product_id FROM m_income);

Query Q012. Example of a MySQL query to display information about products from the table m_product, whose codes are not in the table m_outcome:

SELECT *
FROM m_product
WHERE id NOT IN (SELECT product_id FROM m_outcome);

Query Q013. A unique list of id-s and product names is displayed, the id-s of which are in the table m_income, but which are not in table the m_outcome:

SELECT DISTINCT product_id, title
FROM m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
WHERE product_id NOT IN (SELECT product_id FROM m_outcome);

Query Q014. The unique list of categories starting with the letter Ш is displayed from the m_category table.

SELECT DISTINCT title
FROM m_product
WHERE title LIKE 'Ш%';

Query Q015. An example of performing arithmetic operations on fields in a query and renaming fields in a query (alias). In this example, for each commodity expense entry, the cost (= quantity * price) and profit are calculated, assuming that the profit is 7 percent of the sales price:

SELECT dt, product_id, amount, price, amount*price AS outcome_sum, 
amount*price/100*7 AS profit
FROM m_outcome;

Query Q016. By analyzing and simplifying arithmetic operations, you can increase the speed of query execution:

SELECT dt, product_id, amount, price, amount*price AS outcome_sum, 
amount*price*0.07 AS profit
FROM m_outcome;

Query Q017. Using the INNER JOIN statement, you can combine the data of several tables. In the following example, depending on the value of ctgry_id, for each entry in the m_income table, the name of the category from the m_category table to which the item belongs is mapped:

SELECT c.title, b.title, dt, amount, price, amount*price AS income_sum
FROM m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id 
INNER JOIN m_category AS c ON b.ctgry_id=c.id
ORDER BY c.title, b.title;

Query Q018. Such functions as SUM - sum, COUNT - quantity, AVG - arithmetic mean, MAX - maximum value, MIN - minimum value are called aggregate functions. They take multiple values, and after they are processed, a single value is returned. An example of counting the sum of the product of the amount and price fields using the aggregate function SUM:

SELECT SUM(amount*price) AS Total_Sum
FROM m_income;

Query Q019. Example of using multiple aggregate functions:

SELECT Sum(amount) AS Amount_Sum, AVG(amount) AS Amount_AVG, 
MAX(amount) AS Amount_Max, Min(amount) AS Amount_Min, 
Count(*) AS Total_Number
FROM m_income;

Query Q020. In this MySQL query example, the sum of all products with id 1 credited in June 2011 was calculated:

SELECT Sum(amount*price) AS income_sum
FROM m_income
WHERE product_id=1 AND dt BETWEEN "2011-6-1" AND "2011-6-30";