Examples of SQL queries can be used to study and practice writing SQL queries in MS Access.

Query Q051. The following query helps you determine whether users have been 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 in different ways. For example, you can renumber the rows of a report prepared in MS Access by means of MS Access itself. Renumbering can also be done using programming languages, for example, VBA or PHP. However, sometimes this must be done in the SQL query itself. So, the following query numbers the 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. The top five products are listed by the amount of sales. The output of the first five records is done using the TOP instruction:

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

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, 
summa*10000000+product_id AS id 
FROM m_outcome GROUP BY product_id)  AS a 
INNER JOIN 
(SELECT product_id, sum(amount*price) AS summa, 
summa*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(*);

Query Q055. The following SQL query shows the use of the mathematical functions COS, SIN, TAN, SQRT, ^ and ABS in MS Access SQL:

SELECT (select count(*) from m_income) as N, 3.1415926 as pi, k,  
2*pi*(k-1)/N as x, COS(x) as COS_, SIN(x) as SIN_, TAN(x) as TAN_, 
SQR(x) as SQRT_, x^3 as 'x^3', ABS(x) as ABS_
FROM (SELECT COUNT(*) AS k
FROM m_income AS a INNER JOIN m_income AS b ON a.id<=b.id
GROUP BY b.id) t;