Examples can be used for a workshop on MySQL queries.

Query Q041. If the IF statement is used multiple times, it's more convenient to replace it with the CASE statement. The CASE operator (multiple choice operator) is used to evaluate a logical expression and perform an action depending on the result. In the following example, the delivered batch is considered to be "small" («малой») if the quantity of the goods in the batch is less than 500. Otherwise, that is, if the quantity of the goods is greater than or equal to 500, the batch is considered "big" («большой»):

SELECT dt, product_id, amount, 
CASE WHEN amount<500 THEN "малая" ELSE "большая" END AS mark
FROM m_income; 

Query Q042. In the next MySQL query, if the quantity of the goods in the incoming batch is less than 300, then the batch is considered "small". Otherwise, that is, if the condition amount & lt; 300 is not satisfied, then it is checked whether the quantity of goods in the batch is less than 500. If the lot size is less than 500, then it is considered "average". Otherwise, the lot is considered "big":

SELECT dt, product_id, amount, 
IF(amount<300,"малая",
IF(amount<1000,"средняя","большая")) AS mark
FROM m_income;

Query Q043. In the next MySQL query, if the quantity of the goods in the incoming batch is less than 300, then the batch is considered "small". Otherwise, that is, if the condition amount & lt; 300 is not satisfied, then it is checked whether the quantity of goods in the batch is less than 500. If the lot size is less than 500, then it is considered "average". Otherwise, the lot is considered "big":

SELECT dt, product_id, amount, 
CASE 
WHEN amount<300 THEN "малая"
WHEN amount<1000 THEN "средняя"
ELSE "большая" 
END AS mark
FROM m_income;

Query Q044. In this MySQL query example, sales are divided into three groups: small (up to 150), medium (from150 to 300), large (300 or more). Next, for each group, the total is calculated:

SELECT Category, SUM(outcome_sum) AS Ctgry_Total
FROM (SELECT amount*price AS outcome_sum,
IF(amount*price<150,"малая",
IF(amount*price<300,"средняя","большая")) AS Category 
FROM m_outcome)  AS t
GROUP BY Category;

Query Q045. DateAdd is used to add days, months, or years to a given date and get a new date. The following query:

1) to the date from the dt field adds 30 days and displays a new date in the dt_plus_30d field;
2) the date from the dt field adds 1 month and displays a new date in the dt_plus_1m field:

SELECT dt, DATE_ADD(dt, INTERVAL 30 DAY) AS dt_plus_30d, 
DATE_ADD(dt, INTERVAL 1 MONTH) AS dt_plus_1m
FROM m_income;

Query Q046. The DateDiff function is used to calculate the difference between two dates in days. The following query calculates the difference between the date in the dt field and the current date in days:

SELECT dt, DATEDIFF(NOW(),dt) AS last_day
FROM m_income;

Query Q047. Calculate the number of days from the day the goods arrive (table m_income) to the current date using the DateDiff function and match the expiration date (m_product table):

SELECT a.id, product_id, dt, lifedays, DATEDIFF(NOW(),dt) AS last_day
FROM m_income AS a INNER JOIN m_product AS b 
ON a.product_id=b.id;

Query Q048. The number of days from the date of receipt of the goods to the current date is calculated, then it is checked whether this expiration date exceeds:

SELECT a.id, product_id, dt, lifedays, DATEDIFF(NOW(),dt) AS last_days, 
IF(DATEDIFF(NOW(),dt)>lifedays,"Да","Нет") AS date_expire
FROM m_income a INNER JOIN m_product b
ON a.product_id=b.id;

Query Q049. Calculates the number of months from the date of receipt of goods to the current date:

SELECT 
PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(YEAR_MONTH FROM dt)) AS months 
FROM m_income;

Query Q050. Quarterly report on the number and amount of goods credited for 2011:

SELECT kvartal, SUM(outcome_sum) AS Total
FROM (SELECT amount*price AS outcome_sum, month(dt) AS m, 
CASE WHEN month(dt)<4 THEN 1 
WHEN month(dt)<7 THEN 2 
WHEN month(dt)<10 THEN 3 
ELSE 4 END AS kvartal 
FROM m_income WHERE year(dt)=2011)  AS t
GROUP BY kvartal