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

One SQL query can be invested in another. A subquery is nothing but 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. Information about products from the m_product table is displayed, which codes are also in the m_income table:

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

Query Q012. The list of products from the m_product table is displayed, the codes of which are not in the m_outcome table:

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

Query Q013. This SQL query displays a unique list of codes and product names, the codes of which are in the m_income table, but which are not in the m_outcome table:

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 M 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 amount of the expense is calculated = quantity * price and profit, assuming that the profit is 7 percent of the sales amount:

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

Query Q016. In MS SQL Server, you can use the = sign instead of AS. Only in this case it is first necessary to write the name of the alias. Analyzing and simplifying arithmetic operations, you can increase the speed of query execution:

SELECT dt, product_id, amount, price, outcome_sum=amount*price, 
profit=amount*price*0.07
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 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-06-01' AND '2011-06-30';