sql query in ms access. Introduction

SQL - Lesson 4. Selecting data - SELECT statement

So, in our forum database there are three tables: users (users), topics (topics) and posts (messages). And we want to see what data they contain. To do this, there is an operator in SQL SELECT. The syntax for using it is as follows:

SELECT select_what FROM select_from;

Instead of "what_to_select", we must specify either the name of the column whose values we want to see, or the names of several columns separated by commas, or the asterisk character (*), which means the selection of all columns of the table. Instead of "from_choose" you should specify the name of the table.

Let's first look at all the columns from the users table:

SELECT * FROM users;

That's all our data that we entered into this table. But suppose we only want to look at the id_user column (for example, in the last lesson, we needed to know what id_users are in the users table to populate the topics table). To do this, we will specify the name of this column in the query:

SELECT id_user FROM users;

Well, if we want to see, for example, the names and e-mails of our users, then we will list the columns of interest separated by commas:

SELECT name, email FROM users;

Similarly, you can see what data our other tables contain. Let's see what topics we have:

SELECT * FROM topics;

Now we have only 4 topics, and if there are 100 of them? I would like them to be displayed, for example, alphabetically. There is a keyword for this in SQL. ORDER BY followed by the name of the column by which sorting will occur. The syntax is the following:

SELECT column_name FROM table_name ORDER BY sort_column_name;

The default sorting is ascending, but this can be changed by adding the keyword DESC

Now our data is sorted in descending order.

You can sort by several columns at once. For example, the following query will sort the data by the topic_name column, and if there are several identical rows in this column, then the id_author column will be sorted in descending order:

Compare the result with the result of the previous query.

Very often we do not need all the information from the table. For example, we want to know which topics were created by the user sveta (id=4). There is a keyword for this in SQL. WHERE, the syntax for such a request is as follows:

For our example, the condition is the user ID, i.e. we only want rows that have 4 in the id_author column (user ID sveta):

Or we want to know who created the "bicycles" theme:

Of course, it would be more convenient to display the author's name instead of the author's id, but the names are stored in another table. In later lessons, we will learn how to select data from multiple tables. In the meantime, let's learn what conditions can be specified using the WHERE keyword.

Operator	Description
= (equal)	Selected values equal to the specified Example: SELECT * FROM topics WHERE id_author=4; Result:
> (more)	Values greater than the specified are selected Example: SELECT * FROM topics WHERE id_author>2; Result:
< (меньше)	Values less than specified are selected Example: SELECT * FROM topics WHERE id_author Result:
>= (greater than or equal to)	Values greater than or equal to the specified value are selected. Example: SELECT * FROM topics WHERE id_author>=2; Result:
<= (меньше или равно)	Values less than or equal to the specified value are selected. Example: SELECT * FROM topics WHERE id_author Result:
!= (not equal)	Values not equal to the specified are selected Example: SELECT * FROM topics WHERE id_author!=1; Result:
IS NOT NULL	Rows that have values in the specified field are selected Example: SELECT * FROM topics WHERE id_author IS NOT NULL; Result:
IS NULL	Rows that do not have a value in the specified field are selected Example: SELECT * FROM topics WHERE id_author IS NULL; Result: Empty set - no such strings.
BETWEEN (between)	Values between the specified values are selected. Example: SELECT * FROM topics WHERE id_author BETWEEN 1 AND 3; Result:
IN (value contained)	The values corresponding to the specified Example: SELECT * FROM topics WHERE id_author IN (1, 4); Result:
NOT IN (value not contained)	Selected values other than those specified Example: SELECT * FROM topics WHERE id_author NOT IN (1, 4); Result:
LIKE (match)	Sample values are selected Example: SELECT * FROM topics WHERE topic_name LIKE "vel%"; Result: Possible metacharacters of the LIKE operator will be discussed below.
NOT LIKE	Values that do not match the sample are selected Example: SELECT * FROM topics WHERE topic_name NOT LIKE "vel%"; Result:

LIKE operator metacharacters

Metacharacter searches can only be performed in text fields.

The most common metacharacter is % . It means any characters. For example, if we want to find words that start with the letters "vel", then we will write LIKE "vel%", and if we want to find words that contain the characters "club", then we will write LIKE "%club%". For example:

Another commonly used metacharacter is _ . Unlike %, which denotes few or no characters, underscore denotes exactly one character. For example:

Pay attention to the space between the metacharacter and "fish", if you skip it, the request will not work, because metacharacter _ stands for exactly one character, and a space is also a character.

It's enough for today. In the next lesson, we will learn how to query two or more tables. In the meantime, try to make your own queries against the posts table (messages).

This lesson is about SQL queries to the database on VBA Access. We will look at how VBA queries INSERT, UPDATE, DELETE to the database are carried out, and we will also learn how to get a specific value from a SELECT query.

Those who program VBA Access and while working with a SQL server database, they often face such a simple and necessary task as sending an SQL query to the database, be it INSERT, UPDATE or a simple SQL SELECT query. And since we are novice programmers, we should also be able to do this, so today we will do just that.

We have already touched on the topic of obtaining data from a SQL server, where we wrote code in VBA to obtain this data, for example, in an article about Uploading data to a text file from MSSql 2008 or also touched a little in the material Uploading data from Access to a Word and Excel template. but one way or another there we considered it superficially, and today I propose to talk about it in a little more detail.

Note! All examples below are discussed using an Access 2003 ADP project and an MSSql 2008 database.

Initial data for examples

Let's say we have a table test_table that will contain the numbers and names of the months in a year (queries are made using management studio)

CREATE TABLE .( NOT NULL, (50) NULL) ON GO

As I said, we will use an ADP project configured to work with MS SQL 2008, in which I created a test form and added a start button with a caption "Run", which we will need to test our code, i.e. we will write all the code in the event handler " Button press».

Database queries INSERT, UPDATE, DELETE in VBA

In order not to drag it out for a long time, let's get started, let's say we need to add a row to our test table ( code is commented)/

Private Sub start_Click() "Declare a variable to store the query string Dim sql_query As String "Write the query we need into it sql_query = "INSERT INTO test_table (id, name_mon) VALUES ("6", "June")" "Execute it with DoCmd. RunSQL sql_query End Sub

In this case, the query is executed using the current database connection settings. We can check if the data has been added or not.

As you can see, the data has been inserted.

In order to delete one line, we write the following code.

Private Sub start_Click() "Declare a variable to hold the query string Dim sql_query As String "Write a delete query into it sql_query = "DELETE test_table WHERE id = 6" "Execute it DoCmd.RunSQL sql_query End Sub

If we check, we will see that the desired line has been deleted.

To update the data, we write an update query to the sql_query variable, I hope the meaning is clear.

SELECT query to database in VBA

Here things are a little more interesting than with other SQL constructs.

First, let's say we need to get all the data from the table, and, for example, we will process it and display it in a message, and you, of course, can use it for other purposes, for this we write the following code

Private Sub start_Click() "Declaring variables "For a record set from the database Dim RS As ADODB.Recordset "Query string Dim sql_query As String "String for displaying total data in the message Dim str As String "Creating a new object for records set RS = New ADODB .Recordset "Query string sql_query = "SELECT id, name_mon FROM test_table" "Execute query using current project connection settings to display a message str = str & RS.Fields("id") & "-" & RS.Fields("name_mon") & vbnewline "go to next record RS.MoveNext Wend "Output message msgbox str End Sub

Here we are already using VBA Access Loops to loop through all the values in our recordset.

But, quite often it is necessary to get not all values from a set of records, but only one, for example, the name of the month by its code. And for this, using a loop is somehow expensive, so we can simply write a query that will return only one value and refer to it, for example, we will get the name of the month by code 5

Private Sub start_Click() "Declaring variables "For a record set from the database Dim RS As ADODB.Recordset "Query string Dim sql_query As String "String for displaying the final value Dim str As String "Creating a new object for records set RS = New ADODB.Recordset "Query string sql_query = "SELECT name_mon FROM test_table WHERE id = 5" "Execute query using current project connection settings RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Get our value str = RS.Fields(0) msgbox str end sub

For universality, here we have already addressed not by the name of the cell, but by its index, i.e. 0, which is the very first value in Recordset, in the end we got the value "May".

As you can see, everything is quite simple. If you often need to get a specific value from the database ( as in the last example), then I recommend that you output all the code in a separate function (How to write a function in VBA Access 2003) with one input parameter, for example, the month code ( considering our example) and simply, where it is necessary to display this value, call the function we need with the necessary parameter, and that's it, we will significantly reduce the VBA code and improve the perception of our program.

That's all for today. Good luck!

Description of the educational project "Shop"

Table link scheme

Description of tables

m_category - product categories

m_income - receipt of goods

m_outcome - consumption of goods

m_product - directory, product description

m_supplier - directory; supplier information

m_unit - directory; units

To practically test the examples given in this tutorial, you need to have the following software available:

Microsoft Access 2003 or later.

SQL query in MS Access. Start

To see the contents of a table, double-click on the table name in the left pane:

To switch to the table field editing mode, select Design mode on the top panel:

To display the result of an SQL query, double click on the query name in the left pane:

To switch to the SQL query editing mode, select the SQL mode on the top panel:

SQL query. Examples in MS Access. SELECT: 1-10

In an SQL query, the SELECT statement is used to select from database tables.

SQL query Q001. An example SQL query to get only the required fields in the desired sequence:

SELECT dt, product_id, amount

FROM m_income;

SQL query Q002. In this SQL query example, the asterisk character (*) is used to display all the columns of the m_product table, in other words, to get all the fields of the m_product relation:

SELECT *
FROM m_product;

RequestSQLQ003. The DISTINCT statement is used to eliminate duplicate records and get many unique records:

SELECT DISTINCT product_id

FROM m_income;

SQL query Q004. The ORDER BY statement is used to sort (order) records by the values of a particular field. The field name follows the ORDER BY clause:

SELECT *
FROM m_income

ORDER BY price;

SQL query Q005. The ASC statement is used in addition to the ORDER BY statement and is used to define an ascending sort. The DESC statement is used in addition to the ORDER BY statement and is used to define a descending sort. In the case when neither ASC nor DESC are specified, the presence of ASC (default) is assumed:

SELECT *
FROM m_income

ORDER BY dt DESC , price;

SQL query Q006. To select the necessary records from the table, various logical expressions are used that express the selection condition. The boolean expression comes after the WHERE clause. An example of getting from the m_income table all records for which the amount value is greater than 200:

SELECT *
FROM m_income

WHERE amount>200;

SQL query Q007. To express complex conditions, the logical operations AND (conjunction), OR (disjunction) and NOT (logical negation) are used. An example of getting from the m_outcome table all records for which the amount value is 20 and the price value is greater than or equal to 10:

price

FROM m_outcome
WHERE amount=20 AND price>=10;

SQL query Q008. To join data from two or more tables, use the INNER JOIN, LEFT JOIN, RIGHT JOIN statements. The following example retrieves the dt, product_id, amount, price fields from the m_income table and the title field from the m_product table. The record of the m_income table is connected to the record of the m_product table when the value of m_income.product_id is equal to the value of m_product.id:

ON m_income.product_id=m_product.id;

SQL query Q009. There are two things you need to pay attention to in this SQL query: 1) the search text is enclosed in single quotes ("); 2) the date is in the #Month/Day/Year# format, which is correct for MS Access. In other systems, the date format may be different.An example of displaying information about the receipt of milk on June 12, 2011. Pay attention to the date format #6/12/2011#:

SELECT dt, product_id, title, amount, price

FROM m_income INNER JOIN m_product

WHERE title="Milk" And dt=#6/12/2011#; !}

SQL query Q010. The BETWEEN instruction is used to test whether a range of values belongs to it. An example SQL query displaying information about goods received between June 1st and June 30th, 2011:

SELECT *
FROM m_income INNER JOIN m_product

ON m_income.product_id=m_product.id
WHERE dt BETWEEN #6/1/2011# And #6/30/2011#;

SQL query. Examples in MS Access. SELECT: 11-20

One SQL query can be nested within another. A subquery is nothing more than a query within a query. Typically, a subquery is used in a WHERE clause. But there are other ways to use subqueries.

Request Q011. Displays information about products from the m_product table, the codes of which are also in the m_income table:

SELECT *
FROM m_product

WHERE id IN (SELECT product_id FROM m_income);

Request Q012. A 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);

Request Q013. This SQL query returns a unique list of codes and product names that have codes in the m_income table but 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);

Request Q014. A unique list of categories is displayed from the m_category table, the names of which begin with the letter M:

SELECT DISTINCT title

FROM m_product
WHERE title LIKE "M*";

Request Q015. An example of performing arithmetic operations on fields in a query and renaming fields in a query (alias). This example calculates expense = quantity*price and profit for each item consumption record, assuming profit is 7 percent of sales:

amount*price/100*7 AS profit
FROM m_outcome;

Request 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,

outcome_sum*0.07 AS profit
FROM m_outcome;

Request Q017. Using the INNER JOIN statement, you can combine data from multiple tables. In the following example, depending on the value of ctgry_id, each entry in the m_income table is matched with the name of the category from the m_category table to which the product belongs:

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;

Request Q018. Functions such as SUM - sum, COUNT - quantity, AVG - arithmetic mean, MAX - maximum value, MIN - minimum value are called aggregate functions. They take multiple values and return a single value when processed. An example of calculating the sum of the product of the fields amount and price using the SUM aggregate function:

SELECT SUM(amount*price) AS Total_Sum

FROM m_income;

Request Q019. An 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;

Request Q020. In this example, the sum of all items with code 1 received in June 2011 is calculated:

SELECT Sum(amount*price) AS income_sum

FROM m_income
WHERE product_id=1 AND dt BETWEEN #6/1/2011# AND #6/30/2011#;.

Request Q021. The following SQL query calculates how much the goods with code 4 or 6 were sold for:

SELECT Sum(amount*price) as outcome_sum

FROM m_outcome
WHERE product_id=4 OR product_id=6;

Request Q022. It is calculated for what amount was sold on June 12, 2011 of goods with code 4 or 6:

SELECT Sum(amount*price) AS outcome_sum

FROM m_outcome
WHERE (product_id=4 OR product_id=6) AND dt=#6/12/2011#;

Request Q023. The task is this. Calculate the total amount for which the goods of the category "Baked products" were credited.

To solve this problem, you need to operate on three tables: m_income, m_product and m_category, because:

- the quantity and price of credited goods are stored in the m_income table;
- the category code of each product is stored in the m_product table;
- the name of the category title is stored in the m_category table.

To solve this problem, we use the following algorithm:

- determination of the category code "Baked 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 credited product;
- calculation of the receipt amount (= quantity * price) for goods, the category code of which is equal to the code defined by the above subquery.
SELECT
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="Baked goods"); !}

Request Q024. The problem of calculating the total amount of credited goods of the category "Baked products" will be 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, match the name of the category;
- select records for which the category is equal to "Baked products";
- calculate the amount of income = quantity * price.

FROM (m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id)

WHERE c.title="Baked goods"; !}

Request Q025. This example calculates how many items were consumed:

SELECT COUNT(product_id) AS product_cnt

FROM (SELECT DISTINCT product_id FROM m_outcome) AS t;

Request Q026. The GROUP BY clause is used to group records. Typically, 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 generates a report on the sale of goods. That is, a table is generated that will contain the names of the goods and the amount for which they are sold:

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;

Request Q027. Sales report by category. That is, a table is generated that will contain the names of product categories, 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 the mean value to the nearest hundredth (the second decimal place after the decimal 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;

Request Q028. For each product, the total and average number of its receipts is calculated and displays information about the goods, the total number of receipts of which is 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;

Request Q029. This query computes for each item the sum and average of its receipts made in the second quarter of 2011. If the total amount of receipt of goods is not less than 1000, then 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 #4/1/2011# AND #6/30/2011#
GROUP BY title
HAVING SUM(amount*price)>=1000;

Request Q030. In some cases it is necessary to match each record of some table with each record of another table; what is called a Cartesian product. The table resulting from such a join is called a Descartes table. For example, if some table A has 100 entries and table B has 15 entries, then their Cartesian table will consist of 100*15=150 entries. The following query joins each entry in the m_income table with each entry in the m_outcome table:
FROM m_income, m_outcome;

Request Q031. An example of grouping records by two fields. The following SQL query calculates for each supplier the amount and quantity of goods received from him:

SUM(amount*price) AS income_sum

Request Q032. An example of grouping records by two fields. The following query calculates, for each supplier, the amount and quantity of their products sold by us:

SELECT supplier_id, product_id, SUM(amount) AS amount_sum,

GROUP BY supplier_id, product_id;

Request Q033. In this example, the two queries above (q031 and q032) are used as subqueries. The results of these queries are merged into one report using the LEFT JOIN method. The following query displays a report on the number and amount of products received and sold for each supplier. You should pay attention to the fact that if some product has already arrived, but has not yet been sold, then the outcome_sum cell for this record will be empty. that this query is only an example of using relatively complex queries as a subquery. The performance of this SQL query with a large amount of data is questionable:

SELECT *
FROM

SUM(amount*price) AS income_sum

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

Request Q034. In this example, the two queries above (q031 and q032) are used as subqueries. The results of these queries are combined into one report using the RIGTH JOIN method. The following query prints out a report on the amount of each customer's payments for the payment systems they used and the amount of investments they made. The following query displays a report on the number and amount of products received and sold for each supplier. Note that if a product has already been sold but not yet received, then the income_sum cell for this entry will be empty. The presence of such empty cells is an indicator of an error in accounting for sales, since before the sale, it is first necessary that the corresponding product arrives:

SELECT *
FROM

(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS income_sum
FROM m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS a
RIGHT JOIN
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS outcome_sum
FROM m_outcome AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS b
ON (a.supplier_id=b.supplier_id) AND (a.product_id=b.product_id);

Request Q035. A report on the amount of income and expenses by product is displayed. To do this, a list of products is created according to the m_income and m_outcome tables, then for each product from this list, the sum of its receipts according to the m_income table and the sum of its expenses according to the m_outcome table are calculated:

SELECT product_id, SUM(in_amount) AS income_amount,

SUM(out_amount) AS outcome_amount
FROM
(SELECT product_id, amount AS in_amount, 0 AS out_amount
FROM m_income
UNION ALL
SELECT product_id, 0 AS in_amount, amount AS out_amount
FROM m_outcome) AS t
GROUP BY product_id;

Request Q036. The EXISTS function returns TRUE if the set passed to it contains elements. The EXISTS function returns FALSE if the set passed to it is empty, that is, it has no elements. The following query returns the product codes that are contained in both the m_income table and the m_outcome table:

SELECT DISTINCT product_id

FROM m_income AS a
WHERE EXISTS(SELECT product_id FROM m_outcome AS b

Request Q037. Product codes are displayed that are contained in both the m_income table and the m_outcome table:

SELECT DISTINCT product_id

FROM m_income AS a
WHERE product_id IN (SELECT product_id FROM m_outcome)

Request Q038. Product codes are displayed that are contained as in the m_income table, but are not contained in the m_outcome table:

SELECT DISTINCT product_id

FROM m_income AS a
WHERE NOT EXISTS(SELECT product_id FROM m_outcome AS b
WHERE b.product_id=a.product_id);

Request Q039. A list of products with the highest sales amount is displayed. The algorithm is this. For each product, the sum of its sales is calculated. Then, the maximum of these sums is determined. Then, for each product, the sum of its sales is again calculated, and the code and the sum of sales of goods are displayed, the sum of sales of which is equal to the maximum:

SELECT product_id, SUM(amount*price) AS amount_sum

FROM m_outcome
GROUP BY product_id
HAVING SUM(amount*price) = (SELECT MAX(s_amount)
FROM (SELECT SUM(amount*price) AS s_amount FROM m_outcome GROUP BY product_id));

Request Q040. The reserved word IIF (conditional operator) is used to evaluate a logical expression and perform an action depending on the result (TRUE or FALSE). In the following example, the delivery of an item is considered "small" if the quantity is less than 500. Otherwise, that is, the receipt quantity is greater than or equal to 500, the delivery is considered "large":

SELECT dt, product_id, amount,

IIF(amount FROM m_income;

SQL query Q041. In the case where the IIF statement is used more than once, it is more convenient to replace it with the SWITCH statement. The SWITCH 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 lot is considered "small" if the quantity of goods in the lot is less than 500. Otherwise, that is, if the quantity of goods is greater than or equal to 500, the lot is considered "large":

SELECT dt, product_id, amount,

SWITCH(amount =500,"big") AS mark
FROM m_income;

Request Q042. In the next query, if the quantity of goods in the incoming batch is less than 300, then the batch is considered "small". Otherwise, i.e. if the condition amount SELECT dt, product_id, amount,
IIF(amount IIF(amount FROM m_income;

SQL query Q043. In the next query, if the quantity of goods in the incoming batch is less than 300, then the batch is considered "small". Otherwise, i.e. if the condition amount SELECT dt, product_id, amount,
SWITCH(amount amount amount>=1000,"big") AS mark
FROM m_income;

SQL query Q044. In the following query, sales are divided into three groups: small (up to 150), medium (from 150 to 300), large (300 and more). Next, for each group, the total amount is calculated:

SELECT Category, SUM(outcome_sum) AS Ctgry_Total

FROM (SELECT amount*price AS outcome_sum,
IIf(amount*price IIf(amount*price FROM m_outcome) AS t
GROUP BY Category;

SQL query Q045. The DateAdd function is used to add days, months, or years to a given date and get a new date. Next request:
1) adds 30 days to the date from the dt field and displays the new date in the dt_plus_30d field;
2) add 1 month to the date from the dt field and display the new date in the dt_plus_1m field:

SELECT dt, dateadd("d",30,dt) AS dt_plus_30d, dateadd("m",1,dt) AS dt_plus_1m

FROM m_income;

SQL query Q046. The DateDiff function is designed to calculate the difference between two dates in different units (days, months, or years). The following query calculates the difference between the date in the dt field and the current date in days , months and years:

SELECT dt, DateDiff("d",dt,Date()) AS last_day,

DateDiff("m",dt,Date()) AS last_months,
DateDiff("yyyy",dt,Date()) AS last_years
FROM m_income;

SQL query Q047. The number of days from the day the goods were received (table m_income) to the current date are calculated using the DateDiff function and the expiration date is compared (table m_product):

DateDiff("d",dt,Date()) AS last_days
FROM m_income AS a INNER JOIN m_product AS b
ON a.product_id=b.id;

SQL 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 number exceeds the expiration date:

SELECT a.id, product_id, dt, lifedays,

DateDiff("d",dt,Date()) AS last_days, IIf(last_days>lifedays,"Yes","No") AS date_expire
FROM m_income a INNER JOIN m_product b
ON a.product_id=b.id;

SQL query Q049. The number of months from the date of receipt of the goods to the current date is calculated. The month_last1 column calculates the absolute number of months, the month_last2 column calculates the number of full months:

SELECT dt, DateDiff("m",dt,Date()) AS month_last1,

DateDiff("m",dt,Date())-iif(day(dt)>day(date()),1,0) AS month_last2
FROM m_income;

SQL query Q050. A quarterly report on the quantity and amount of goods received for 2011 is displayed:

SELECT kvartal, SUM(outcome_sum) AS Total

FROM (SELECT amount*price AS outcome_sum, month(dt) AS m,
SWITCH(m =10.4) AS kvartal
FROM m_income WHERE year(dt)=2011) AS t
GROUP BY block;

Request Q051. The following query helps to find out if users managed to enter into the system information about the consumption of goods for an amount greater than the amount of receipt 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)
Request Q052. The numbering of the lines returned by the query is implemented in different ways. For example, you can renumber the lines of a report prepared in MS Access using MS Access itself. You can also renumber using programming languages, for example, VBA or PHP. However, sometimes it needs to be done in the SQL query itself. So, the following query will number the rows of the m_income table in accordance with the ascending order of the values of the ID field:

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 GROUP BY b.id, b.product_id, b.amount, b.price;

Request Q053. The top five among the products by the amount of sales are displayed. The output of the first five records is carried out 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;

Request Q054. The top five among the products by the amount of sales are displayed, and the lines are numbered as a result:

SELECT COUNT(*) AS N, b.product_id, b.summa

FROM

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(*)ORDER BY COUNT(*);

Request 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.idGROUP BY b.id) t;

SQL query. Examples in MS Access. UPDATE: 1-10

Request U001. The following SQL change query increases the prices of items with code 3 in the m_income table by 10%:

UPDATE m_income SET price = price*1.1

WHERE product_id=3;

Request U002. The following SQL update query increases the quantity of all products in the m_income table by 22 units whose names begin with the word "Butter":

UPDATE m_income SET amount = amount+22

WHERE product_id IN (SELECT id FROM m_product WHERE title LIKE "Oil*");

Request U003. The following SQL change query in the m_outcome table reduces the prices of all goods produced by OOO Sladkoe by 2 percent:

UPDATE m_outcome SET price = price*0.98

WHERE product_id IN
(SELECT a.id FROM m_product a INNER JOIN m_supplier b
ON a.supplier_id=b.id WHERE b.title="OOO"Сладкое"");. !}

Lab #1

SQL: RETRIEVE DATA - commandSELECT

Goal of the work:

Familiarize yourself with SQL statements
learn how to create simple SQL queries in Access using the SELECT command;

· use of operators IN, BETWEEN, LIKE, IS NULL.

Exercise№1. Create a query to select in SQL mode all the values of the fields FIRST NAME and LAST NAME from the STUDENTS table.

SELECT FIRST NAME, LAST NAME

FROM STUDENTS;

Exercise№2 . Create a select query in SQL mode for all columns of the STUDENTS table.

SELECT *

FROM STUDENTS;

Task number 3. Create a query to select in SQL mode the names of the cities where students live, information about which is in the PERSONAL DATA table.

SELECT DISTINCT CITY

FROM [PERSONAL DATA];

Task number 4. Create a select query in SQL mode that selects the names of all students with the last name Ivanov, whose information is in the STUDENTS table.

SELECT SURNAME, NAME

FROM STUDENTS

WHERE SURNAME="Ivanov";

Task number 5. Create a query for a selection in SQL mode to obtain the names and surnames of students studying in the UIT-22 group on a budget-funded form of education.

SELECT SURNAME, NAME

FROM STUDENTS

WHERE GROUP="HIT-22" AND BUDGET=true;

Task number 6. Create a query in SQL mode. for a sample from the EXAM PASSING table, information about students with grades of only 4 and 5.

SELECT *

FROM[CHANGEEXAMS]

WHEREGRADEIN (4.5);

Task number 7. Create a zanpoc and SQL mode for a sample of information about students who have an exam grade of 3 in the subject of IOSU.

SELECT *

FROM[CHANGEEXAMS]

WHEREITEM=" ISSU" AndGRADENot In (4.5);

Task number 8. Create a query in SQL mode to select records for items whose hours are between 100 and 130.

SELECT *

FROMITEMS

WHEREWATCHBETWEEN 100 AND 130;

Task number 9. Create a query in SQL mode to select from the STUDENTS table information about students whose last names begin, for example, with the letter "C".

SELECT *

FROMSTUDENTS

WHERESURNAMELIKE"WITH*";

Conclusion: During the laboratory work, we got acquainted with SQL statements, learned how to create simple SQL queries in Access using the SELECT command using the IN, BETWEEN, LIKE operators.

SQL query examples can be used to learn and practice writing SQL queries in MS Access.

One SQL query can be nested within another. A subquery is nothing more than a query within a query. Typically, a subquery is used in a WHERE clause. But there are other ways to use subqueries.

Request Q011. Displays information about products from the m_product table, the codes of which are also in the m_income table:

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

Request Q012. A 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);

Request Q013. This SQL query returns a unique list of codes and product names that have codes in the m_income table but 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);

Request Q014. A unique list of categories is displayed from the m_category table, the names of which begin with the letter M:

SELECT DISTINCT title
FROM m_product
WHERE title LIKE "M*";

amount*price/100*7 AS profit
FROM m_outcome;

Request 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,
outcome_sum*0.07 AS profit
FROM m_outcome;

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;