Microsoft comparison Access SQL and ANSI SQL Microsoft Access SQL is primarily ANSI-89 (Level 1) some ANSI SQL features are not used in Microsoft Access SQL Microsoft Access SQL uses reserved words and features not supported by ANSI SQL Access 2000 (MS Jet 4.0) includes extensions that bring the language closer to ANSI SQL-92 standard - mode available only when using MS OLE DB Provider for Jet
SELECT Command Syntax (Microsoft Access) SELECT [ predicate ] ( * | table.* | [ table.] field_1 [, [ table.] field_2 [,...]]) FROM expression [,...] Clause FROM SELECT list Fields FROM expression Expression is an expression that defines one or more tables from which data is retrieved. This expression can be the name of a single table, the name of a saved query, or the result of an INNER JOIN, LEFT JOIN, or RIGHT JOIN operation. INNER JOIN,LEFT JOIN RIGHT JOIN.
Arguments of the SELECT statement A predicate (ALL, DISTINCT, DISTINCTROW or TOP) is one of the selection conditions. Predicates are used to limit the number of records returned. If they are missing, the default predicate is ALL (selects all records that match the conditions specified in the SQL statement). SQL DISTINCT statements - excludes records that contain duplicate values in the selected fields. DISTINCTROW - Omits data based on entire duplicate records rather than individual duplicate fields. The DISTINCTROW predicate is ignored if the query contains only one table or all fields of all tables. TOP n - Returns the specified number of records that are at the beginning or end of the range described using the ORDER BY clause. SELECT ]] FROM table of the 5 most numerous departments: SELECT TOP 5 employee.[Department code], Count(employee.full name) AS number_of_employees FROM employee GROUP BY employee.[department code] ORDER BY Count(employee.full name) DESC;
Arguments of the SELECT statement Table - the name of the table from which records should be selected. WITH OWNERACCESS OPTION - used in a multi-user environment with a secure working group, to grant the user working with the request permissions that match those of the request owner. field_1, field_2 - names of the fields from which data should be selected. If you include multiple fields, they will be retrieved in the order specified. Alias_1, Alias_2 - names that will become column headers instead of the original column names in the table. Expression - the names of one or more tables that contain the data being selected. External Database - the name of the database that contains the tables specified by the expression argument, if they are not in the current database. SELECT employee.full name, [salary]*0.5 AS Bonus FROM employee; SELECT Avg(employee.salary) AS Average_salary FROM employee;
Joint processing of several tables SELECT employee.Name, subject.[short name of the subject] FROM employee INNER JOIN (subject INNER JOIN [ownership of objects] ON subject.[subject code] = [ownership of objects].[discipline code]) ON employee.Code = [item ownership].[employee code];
Between...And expression 1 BETWEEN expression 2 AND expression 3 (in Microsoft Access SQL, expression 2 can be greater than expression 3, but not in ANSI SQL). SELECT employee.full name, employee.salary FROM employee WHERE (((employee.salary) Between 1000 And 2000)); Query: SELECT employee.full name, employee.salary FROM employee WHERE (((employee.salary) Between 2000 And 1000)); does not generate an error and gives the same answer SELECT employee.full name, employee.salary FROM employee WHERE (((employee.salary) 15000)); SELECT employee.full name, employee.salary FROM employee WHERE (((employee.salary) Not Between 1000 And 2000));
Pattern Characters Various pattern characters are used with the Like predicate. MS Access SQL ANSI SQL pattern character one character? _ (underscore) group of characters * % single sign, included in character_list [character_list] missing single character not included in character_list [! character_list] missing The last two options are Access only. Access 2000 in ANSI SQL-92 mode allows the use of ANSI wildcards. You cannot mix characters in one request SELECT employee.full name FROM employee WHERE (((employee.full name) Like "D*"));
Clause GROUP BY SELECT list of Fields FROM table WHERE Selection condition where grouped Fields are names of fields (up to 10) that are used to group records. The order of the field names in the Grouped Fields argument determines the level of grouping for each of those fields. Use the WHERE clause to exclude records from grouping, and the HAVING clause to apply a filter to records after grouping. HAVING When using the GROUP BY clause, all fields in the field list of the SELECT statement must either be included in the GROUP BY clause or used as arguments to the aggregate SQL functions. Departments with more than 5 employees: SELECT co-workers[Department code], Count(co-workers' full name) AS [Number of employees] FROM co-workers GROUP BY co-workers[department code] HAVING (((Count(co-workers' full name))>5 ));
5));">
Nested query. Predicate Exists. SELECT employee company, Count(employee employee) AS number_certificate_employee FROM employee WHERE (((Exists (SELECT DISTINCT employee company, employee employee FROM employee INNER JOIN instance ON employee employee = employee employee))False)) GROUP BY co-working company; False)) GROUP BY co-owned company;"> False)) GROUP BY co-owned company;"> False)) GROUP BY co-owned company;" title="Nested query. Predicate Exists. SELECT co-owned company, Count(collaborator) AS number_certificate_collaborator FROM collaborator WHERE (((Exists (SELECT DISTINCT collaborator, collaborator FROM collaborator INNER JOIN instance ON collaborator = instance collaborator))False)) GROUP BY collaborator company;"> title="Nested query. Predicate Exists. SELECT employee company, Count(employee employee) AS number_certificate_employee FROM employee WHERE (((Exists (SELECT DISTINCT employee company, employee employee FROM employee INNER JOIN instance ON employee employee = employee employee))False)) GROUP BY co-working company;"> !}
Creating a subquery using the QBE Query Builder If you are using a subquery to define conditions for a field, enter a SELECT statement in the Selection Condition row cell in that field's column. The SELECT statement must be enclosed in parentheses.
Deleting entries. SQL query (generated) DELETE DISTINCTROW student.full name FROM student WHERE (((student.full name)="Burlak G. N."));
DELETE Statement A delete request deletes entire records, not just the contents of the specified fields. To delete data for a specific field, create a record update query that replaces the existing values with Null values. A delete request without specified search conditions will delete all records from the table. Unlike the DROP command, the table structure and all properties are preserved
Deleting entries. SQL query Query: DELETE * FROM student WHERE student.Full name="Burlak G. N."; gives a similar result.
35
41
The MS Access DBMS application is a full-fledged assistant for creating and maintaining databases enclosed in tables and arrays. If the database is too large, it is quite difficult to quickly find the necessary values.
This is why Access has a feature called queries. Let's look at what it is, how it works, and what features it has.
Creating Queries in Microsoft Access
To figure out how to create queries in Access, you need to know the basics of working with a DBMS.
There are two ways to perform this procedure:
- Query builder.
- Query Wizard.
The first method makes it possible to create any of all available queries in manual mode, but with the slight caveat that the user has experience with an Access application. He must also understand at least its basic tasks. As for the second method, it needs to be considered in more detail.
Easy way for beginners
A knowledgeable person, with a few clicks of the mouse, selects those components that the user will need to complete the request, and then quickly creates a registry in accordance with the collected key values. If this is the first acquaintance with the DBMS, and the user has no idea how to create queries in Access, then the Wizard program is selected.
In this mode, you can familiarize yourself with and understand the following types requests:
- Simple.
- Cross.
- Records without subordinates.
- Duplicate entries.
This choice is made already at the first stage of working with the Master. And in the future, following clear instructions, even a novice user can easily create a request. Let's get acquainted with its varieties.
Simple request
This spreadsheet tool collects the required data from user-specified fields. The name alone shows that this is the most popular type of request for beginners. Its convenience lies in the fact that this procedure opens in a new tab. Therefore, the answer to the question of how to create a query in Access 2010 becomes obvious after opening the first Wizard menu.
Cross request
This type of sampling is more complex. To figure out how to create a cross-query in Access using the “Wizard” in this mode, you need to click on this function in the first window.
A table will appear on the screen in which you can select up to three columns located in the original.
One of the remaining unselected fields can be used as query table headers. At the third stage of the procedure (intersection), another value is selected with a variability function (average value, sum, first, last).
The photo shows that the cross-request has been created and that the necessary actions have been taken according to the specified parameters.
Duplicate entries
As the name implies, the main purpose of this request– selection of all identical rows in the table according to the specified parameters. It looks like this:
In addition, you can select additional fields to match several lines at once.
To select duplicate records, you need to expand the list of queries and create there new folder. Next, in the “New Query” window, select the line “Search for duplicate records.” Next you need to follow the instructions of the Master.
Records without subordinates
This is the last type of query available in the Master - Records without Slaves mode.
In this case, only those values are selected that are not involved in any field of tables and queries, but which have already been created.
This type is relevant only in cases where there are several databases.
All four of these query types provide a basic starting point for working with complex elements, but make it easy to understand how to create a query in an Access database.
Query functions in MS Access
Let's figure out why you need to perform the steps described above. The purpose of all simple and complex queries in the Access DBMS is as follows:
- Collecting the necessary data in tables, then viewing, editing, adding new values.
- Excellent source material for preparing all kinds of reporting forms.
- Carrying out mathematical and statistical counting procedures on entire data arrays with the results displayed on the screen (average value, sum, deviation, totals).
Sample request
This type of database work is complex because it requires the participation of multiple tables.
It is necessary that all tables have common key fields. Otherwise, the operation will not be possible.
Let's review how to create a selection query in Access. First you need to create a simple query with the selection of the required fields. Here you can edit the data to bring it into the desired form. By the way, changes made will be transferred to the source tables, so this point must be taken into account.
In the designer window that opens, the “Add tables” window is filled in. Here you need to add those tables or queries from which you need to extract the original values.
After adding, you can start filling out the request conditions. For this we need the "Field" string. In it you need to select those values from the tables that will be displayed during the request.
To complete the operation, you need to click on the “Run” button.
Request with parameters
This is another type of complex procedure that will require the user to have certain database skills. One of the main areas of this action is preparing for the creation of reports with large-scale data, as well as obtaining summary results. How to create queries in Access 2007 using the designer will be discussed below.
You need to start this data sampling procedure by creating a simple query to select the required fields. Next, through the Designer mode, you must fill in the “Selection Condition” field and, based on the entered value, the selection will be carried out.
Thus, to the question of how to create a query with a parameter in Access, the answer is simple - enter the initial parameters for the selection. To work with the Designer, you must use the Query Wizard. There, primary data for filtering is created, which serves as the basis for further work.
Advanced Cross Query
We continue to complicate the situation. Even more difficult to understand is information about how to create queries in Access if there are multiple tables of data. Cross-request has already been discussed above as one of the options for working with the Master. However, you can create a similar request in Design mode.
To do this, click “Query Builder” - “Cross”.
A menu for adding source tables opens, as well as the ability to fill in selected fields. The only things you should pay attention to are the “group operation” and “cross tabulation” items. They must be filled out correctly, otherwise the procedure will not be completed correctly.
Cross queries are the easiest way to search and retrieve information from multiple data sources, plus the ability to generate charts and graphs.
Moreover, when using this procedure, the search is completed faster, even with several development options.
Of course, there are also pitfalls that can interfere with your work. For example, when creating a query to sort a database by column values, the system generates an error. That is, only sorting by standard items is available - “ascending and descending”.
To summarize, it must be said that the user must decide how to create queries in Access - using the Wizard or the Designer. Although, for most people who use the MS Access DBMS, the first option is more suitable. After all, the Wizard will do all the work himself, leaving only a few mouse clicks for the user to select the request conditions.
To use advanced settings, professional-level database experience is clearly required. If large databases are involved in the work, it is best to contact specialists in order to avoid disruption of the DBMS and possible data loss.
There is one point that is available only to programmers. Since the main language of the DBMS is SQL, the required query can be written in the form program code. To work in this mode, just click on the line of the already created request, and in the opened context menu select "SQL Mode".
In the simplest case, the query implements the selection from one table of the required fields, records that meet the specified selection conditions, and viewing the results of the query.
Constructing selection queries with selection conditions
Let's look at selection queries in Access using the example of retrieving information from the PRODUCT table of the Supply of Goods database.
Problem 1. Suppose you need to select a number of characteristics of a product by its name.
- To create a query in the database window, select the ribbon tab - Creation(Create) and in the group Requests(Queries) click the button Query Builder(Query Design). An empty selection request window will open in design mode - RequestN(QueryN) and dialog box Adding a table(Show Table) (Fig. 4.2).
- In the window Adding a table(Show Table) select the PRODUCT table and click the button Add(Add). The selected table will be displayed in the query data schema area. Close the window Adding a table(Show Table) by pressing the button Close(Close).
As a result of the actions performed in the query designer window (Fig. 4.1), a query data schema will appear in the top panel, which includes the tables selected for this query. IN in this case one table PRODUCT. The table is represented by a list of fields. The first row in the list of table fields, marked with an asterisk (*), denotes the entire set of table fields. The bottom panel is a request form that must be filled out.
In addition, it appears on the ribbon and is automatically activated new inset(Query Tools | Design) (Figure 4.3 shows part of this tab), on which the type of query created is highlighted in color - Sample(Select). Therefore, by default, a select query is always created. The commands on this tab provide tools for performing the necessary actions when creating a request. This tab opens when you create a new query or edit an existing one in Design view.
- To remove any table from the query data schema, place the mouse cursor on it and press a key. To add - click the button Show table(Show Table) in group Query setup(Query Setup) tab Working with requests | Constructor(Query Tools | Design) or run the command Add table(Show Table) in the context menu called on the request data diagram.
- In the designer window (Fig. 4.4), sequentially drag the fields NAME_IT, PRICE, AVAILABILITY_IT from the list of fields of the PRODUCT table into the columns of the request form into the row Field(Field).
- To include the desired fields from the table in the corresponding query columns, you can use the following techniques:
- in the first line of the request form Field(Field) click the mouse to make the list button appear and select the desired field from the list. The list contains the fields of the tables represented in the query data schema;
- double-click the table field name in the query data schema;
- To include all table fields, you can drag or double-click the * (asterisk) symbol in the list of table fields in the query data schema.
- If you accidentally dragged an unnecessary field onto the request form, delete it. To do this, move the cursor to the column label area at the top, where it will appear as a black arrow pointing down, and click the mouse button. The column will be highlighted. Press a key or run a command Remove Columns(Delete Columns) in group Query setup(Query Setup).
- In line Output on display(Show) check the fields, otherwise they will not be included in the query table.
- Write on the line Selection conditions(Criteria) name of the product, as shown in the request form in Fig. 4.4. Since the expression in the selection condition does not contain an operator, the operator = is used by default. The text value used in the expression is entered in double quotes, which are added automatically.
- Run the query by clicking the Run button or the View button in the Results group. A query window will appear on the screen in table mode with an entry from the PRODUCT table that meets the specified selection conditions.
COMMENT
The query window in table view is similar to the database table view window. Through some query tables, changes can be made to the data in the base table underlying the query. A query viewed in Datasheet view, unlike an Access 2010 database table, does not have a column Click to add(Click to Add), designed to change the structure of the table. In this mode, on the ribbon tab home(Home) the same buttons are available as when opening a database table.
- If you made an inaccuracy when entering a complex product name, the product will not be found in the table. Using the wildcard operators - asterisk (*) and question mark (?) (ANSI-89 standard, used for default queries) or percent sign (%) and underscore (_) (ANSI-92, recommended as the standard for SQL Server), simplifies the search for the required strings and avoids many mistakes. Enter Corpus* or Corpus% instead of the full product name. Complete the request. If in the product name field one value begins with the word “Case”, the result of the request will be the same as in the previous case. After executing the request, the entered expression will be completed operator Like"Frame*". This operator allows wildcard characters to be used when searching in text fields.
- If you need to find multiple products, use the In operator. It allows you to check for equality with any value from the list, which is specified in parentheses. Write In in the selection conditions line (“MiniTower case”; “HDD Maxtor 20GB”; “FDD 3.5″). The query table will display three rows. The In statement does not allow wildcard characters.
- Save your request by clicking on the tab File(File) and running the command Save(Save). In the window Preservation(Save As) enter the request name Example1. Note that the query name should not coincide not only with the names of existing queries, but also with the names of tables in the database.
- Close the current request using the context menu command Close(Close) or by clicking the query window button Close(Close).
- Execute a saved query by highlighting the query in the navigation pane and selecting the command from the context menu Open(Open).
- To edit a request, select it in the navigation area and execute the command in the context menu Constructor(Design View).
Task 2. Suppose you need to select goods whose price is no more than 1000 rubles, and VAT is no more than 10%, and also select goods whose price is more than 2500 rubles. The result must contain the name of the product (NAIM_TOV), its price (PRICE) and VAT (RATE_VAT).
- Create a new query in design mode, add the PRODUCT table. In the designer window (Fig. 4.5), sequentially drag the fields NAME_IT, PRICE, RATE_VAT from the list of fields of the PRODUCT table to the request form.
- Write it down Selection conditions(Criteria), as shown in the request form in Fig. 4.5. A logical AND operation is performed between conditions written on the same line. A logical OR operation is performed between conditions written on different lines.
- Complete the request, click the button Execute(Run) in the group results(Results). A query window will appear on the screen in table mode with records from the PRODUCT table that meet the specified selection conditions.
- Save the request by executing the appropriate command in the request context menu, which is called when you place the cursor on the request header. Give it a name Example2.
Problem 3. Suppose we need to select all invoices for a given period. The result must contain the invoice number (NOM_NAK), warehouse code (CODE_SC), date of shipment (DATE_SHIP) and the total cost of the shipped goods (SUMMA_INKL).
- Create a new query in Design view, add the INVOICE table. In the designer window, sequentially drag all the necessary fields from the list of fields of the INVOICE table to the request form.
- For the DATE_OTGR field in the line Selection conditions(Criteria) write Between #01/11/2008# And #03/31/2008#. The Between operator specifies a date interval (ANSI-92 uses single quotes ' instead of the # sign). Additionally, this operator allows you to specify an interval for a numeric value.
To reinforce this, watch the video tutorial:
You can create databases, tables, forms and other reports in MS Access. This article will help the user to run SQL queries in MS Access. You can run the same queries that are used in SQL to retrieve data from a database. This article is intended for users who have just started learning MS Access and want to run SQL queries in MS Access. The only condition that is necessary before you begin is that you have access to the database used in the organization.
Steps
What you will need
- The user must have access to the organization's database
- The user can contact technology support before running queries via MS Access
Article information
This page has been viewed 4443 times.
Was this article helpful?
Access DBMS
Microsoft Access is a relational type DBMS, in which all the tools and capabilities typical for modern systems database management. A relational database makes it easier to find, analyze, maintain, and protect data because it is stored in one place. Access translated from English means "access". MS Access is one of the most powerful, flexible and easy to use DBMS. You can create most applications in it without writing a single line of program, but if you need to create something very complex, then MS Access provides a powerful programming language - Visual Basic Application.
The popularity of the Microsoft Access DBMS is due to the following reasons:
Accessibility and clarity allow Access to be one of the best systems quickly create database management applications;
Possibility of using OLE technology;
Integration with Microsoft package Office;
Full support for Web technologies;
Visual technology allows you to constantly see the results of your actions and correct them;
Availability of a large set of “masters” for developing objects.
The main types of objects that the program works with are: table, query, form, report, page, macro, module.
A table is an object that is used to store data. Each table includes information about an object of a certain type. The table contains fields (columns) that store various types of data, and records (rows). Each table must have a primary key defined (one field that has a unique value for each record, or multiple fields whose combined value is unique for each record), which is a unique identifier for each record in the table.
To increase the speed of data access, individual table fields (or a set of them) can be declared indexes. An index is a tool that speeds up searching and sorting in a table by using key values to ensure the uniqueness of table rows. The table's primary key is automatically indexed. It is not allowed to create indexes on fields with some data types.
A query is an object that allows the user to retrieve desired data from one or more tables. Queries can also create new tables using data from one or more tables that already exist. The most common type of query is a fetch query. A select query selects data from one or more tables based on specified conditions, and then displays them in the desired order.
A form is an object designed primarily to enter data, display it on the screen, or control the operation of an application.
A report is an object designed to create a document that can subsequently be printed or included in a document in another application.
visual development programming base
Page - Used to access data in the current Access database.
A macro is an object that is a structured description of one or more actions that Access should perform in response to a specific event.
A module is an object containing Microsoft Visual Basic programs that allow you to break a process into smaller actions and detect errors that could not be found using macros.
The DBMS is launched from Start - Programs - Microsoft Access. Execute the command File - New.
The interface for working with database objects is unified. For each of them, standard operating modes are provided: Create (creating an object structure); Constructor (changing the structure of objects); Open (View, Run - designed to work with database objects).
SQL Query Language
SQL (Structured Query Language) since 1986. is standard language relational databases. In particular, it is used in Access and Excel applications.
SQL is an information-logical language designed to describe stored data, retrieve stored data and modify data. Initially, SQL was the main way a user worked with a database and was a small set of commands (operators) that allowed creating tables, adding new records to tables, retrieving records from tables, deleting records and changing table structures. As SQL has become more complex, it has become a more application-oriented programming language, and users are now able to use visual query builders.
The SQL language is a collection of operators:
Data Definition Language (DDL) operators;
data manipulation operators (Data Manipulation Language, DML);
data access definition operators (Data Control Language, DCL);
Transaction Control Language (TCL) operators.
Queries in MS Access are saved and implemented using the SQL language. Although most queries can be created graphically (pattern queries), they are stored as SQL statements. In some cases (for example, in subqueries), you can only use SQL.
SQL is a non-procedural language. It simply declares what needs to be done, and the execution is left to the DBMS (database management system).
SQL uses three-valued logic. Along with the traditional Boolean values TRUE and FALSE, NULL (UNKNOWN or NO DATA) is used.
Operations are performed on entire sets of data, rather than on individual elements, as in other programming languages.
A SQL query consists of statements. Each instruction can contain several sentences.
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 the WHERE clause. But there are other ways to use subqueries.
Query Q011. Information about products from the m_product table is displayed, the codes of which are also in the m_income table:
SELECT *
FROM m_product
WHERE id IN (SELECT product_id FROM m_income);
Query 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 displays a unique list of product codes and names that are 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);
Query Q014. A unique list of categories whose names begin with the letter M is displayed from the m_category table:
SELECT DISTINCT title
FROM m_product
WHERE title LIKE "M*";
Query 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 expense entry, assuming profit is 7 percent of sales:
Price, amount*price AS outcome_sum,
amount*price/100*7 AS profit
FROM m_outcome;
Query Q016. Having analyzed and simplified arithmetic operations, you can increase the speed of query execution:
outcome_sum*0.07 AS profit
FROM m_outcome;
Request Q017. You can use the INNER JOIN statement to join 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. Such functions as SUM - sum, COUNT - quantity, AVG - arithmetic average value, MAX - maximum value, MIN - minimum value are called aggregate functions. They accept many values and after processing them return a single value. An example of calculating the sum of the product of the amount and price fields using aggregate function SUM:
SELECT SUM(amount*price) AS Total_Sum
FROM m_income;
Query Q019. An example of using several 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 amount of all goods with code 1, capitalized in June 2011, is calculated:
SELECT
FROM m_income
WHERE product_id=1 AND dt BETWEEN #6/1/2011# AND #6/30/2011#;.
Query Q021. The following SQL query calculates the amount of sales of items with code 4 or 6:
SELECT
FROM m_outcome
WHERE product_id=4 OR product_id=6;
Query Q022. It is calculated how much goods with code 4 or 6 were sold on June 12, 2011:
SELECT Sum(amount*price) AS outcome_sum
FROM m_outcome
WHERE (product_id=4 OR product_id=6) AND dt=#6/12/2011#;
Query Q023. The task is this. Calculate the total amount of goods in the “Bakery Products” category that were capitalized.
To solve this problem, you need to operate with three tables: m_income, m_product and m_category, because:
- the quantity and price of capitalized 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 title category is stored in the m_category table.
To solve this problem we will use the following algorithm:
- determining the category code "Bakery products" from the m_category table using a subquery;
- connecting the m_income and m_product tables to determine the category of each purchased product;
- calculation of the receipt amount (= quantity*price) for goods whose category code is equal to the code defined by the above subquery.
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="Bakery products"); !}
Query Q024. We will solve the problem of calculating the total amount of capitalized goods in the “Bakery Products” category using the following algorithm:
- match each entry in the m_income table, depending on the value of its product_id, from the m_category table, with the name of the category;
- select records for which the category is “Bakery Products”;
- calculate the amount of receipt = quantity*price.
SELECT Sum(amount*price) AS income_sum
FROM (m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id)
WHERE c.title="Bakery products"; !}
Query Q025. This example calculates how many items of goods were consumed:
SELECT COUNT(product_id) AS product_cnt
FROM (SELECT DISTINCT product_id FROM m_outcome) AS t;
Query Q026. The GROUP BY statement is used to group records. Typically, records are grouped by the value of one or more fields, and some 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 containing the names of the goods and the amount for which they were 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;
Query Q027. Sales report by category. That is, a table is generated that contains the names of product categories, the total amount for which products of these categories were sold, and the average sales amount. The ROUND function is used to round the average value to the nearest hundredth (the second digit 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;
Query Q028. The total and average number of its receipts is calculated for each product and displays information about products whose total receipts are 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;
Query Q029. This query calculates for each product the amount and average of its receipts made in the second quarter of 2011. If the total amount of the product receipt is at least 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;
Query Q030. In some cases, you need to match each record of some table with each record of another table; which is called the Cartesian product. The table resulting from such a connection is called Descartes' table. For example, if some table A has 100 records and table B has 15 records, then their Cartesian table will consist of 100*15=150 records. The following query joins each record in the m_income table with each record in the m_outcome table:
SELECT *FROM m_income, m_outcome;
Query 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
Query 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,
SUM(amount*price) AS outcome_sum
GROUP BY supplier_id, product_id;
Query Q033. In this example, the two queries above (q031 and q032) are used as subqueries. The results of these queries using the LEFT JOIN method are combined into one report. The following query displays a report on the quantity and amount of products received and sold for each supplier. Please note that if some product has already been received, but has not yet been sold, then the outcome_sum cell for this entry will be empty. It should also be noted 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);
Query Q034. In this example, the two queries above (q031 and q032) are used as subqueries. The results of these queries using the RIGTH JOIN method are combined into one report. The following query displays a report on the amount of payments of each client according to the payment systems he used and the amount of investments he made. The following query displays a report on the quantity and amount of products received and sold for each supplier. Please note that if some product has already been sold, but has not yet arrived, then the income_sum cell for this entry will be empty. The presence of such empty cells is an indicator of an error in sales accounting, since before a sale it is first necessary for the corresponding product to arrive:
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);
Query Q035. A report is displayed showing the amount of income and expenses by product. 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 income is calculated according to the m_income table and the amount of its expenses according to the m_outcome table:
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;
Query Q036. The EXISTS function returns value 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 contains no elements. The following query displays the product codes that are contained in both the m_income and m_outcome tables:
SELECT DISTINCT product_id
FROM m_income AS a
WHERE EXISTS(SELECT product_id FROM m_outcome AS b
Query Q037. Product codes that are contained in both the m_income and m_outcome tables are displayed:
SELECT DISTINCT product_id
FROM m_income AS a
WHERE product_id IN (SELECT product_id FROM m_outcome)
Query Q038. Product codes are displayed that are contained 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);
Query Q039. A list of products with the maximum sales amount is displayed. The algorithm is as follows. For each product, the amount of its sales is calculated. Then, the maximum of these amounts is determined. Then, for each product, the sum of its sales is again calculated, and the code and the sales sum of goods whose sales sum is equal to the maximum are displayed:
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));
Query 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 item delivery 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<500,"малая","большая") AS mark
FROM m_income;
SQL Query Q041. In the case where the IIF operator is used several times, it is more convenient to replace it with the SWITCH operator. The SWITCH operator (multiple selection 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,"малая",amount>=500,"large") AS mark
FROM m_income;
Query Q042. <300 не выполняется, то проверяется является ли количество товаров в партии меньше 500. Если размер партии меньше 500, то она считается «средней». В противном случае партия считается «большой»:
SELECT dt, product_id, amount,
IIF(amount<300,"малая",
IIF(amount<1000,"средняя","большая")) AS mark
FROM m_income;
SQL Query Q043. In the next request, if the quantity of goods in the received batch is less than 300, then the batch is considered “small”. Otherwise, that is, if the amount condition<300 не выполняется, то проверяется является ли количество товаров в партии меньше 500. Если размер партии меньше 500, то она считается «средней». В противном случае партия считается «большой»:
SELECT dt, product_id, amount,
SWITCH(amount<300,"малая",
amount<1000,"средняя",
amount>=1000,"large") 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 or more). Next, the total amount is calculated for each group:
SELECT Category, SUM(outcome_sum) AS Ctgry_Total
FROM (SELECT amount*price AS outcome_sum,
IIf(amount*price<150,"малая",
IIf(amount*price<300,"средняя","большая")) AS Category
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 obtain 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) adds 1 month to the date from the dt field and displays 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 date of receipt of the goods (table m_income) to the current date is 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 quantity 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. Column month_last1 calculates the absolute number of months, column month_last2 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 is displayed on the quantity and amount of goods purchased for 2011:
SELECT kvartal, SUM(outcome_sum) AS Total
FROM (SELECT amount*price AS outcome_sum, month(dt) AS m,
SWITCH(m<4,1,m<7,2,m<10,3,m>=10.4) AS kvartal
FROM m_income WHERE year(dt)=2011) AS t
GROUP BY quarter;
Query Q051. The following query helps to find out whether users were able to enter into the system information about the consumption of goods in an amount greater than the amount of goods received:
SELECT product_id, SUM(in_sum) AS income_sum, SUM(out_sum) AS outcome_sum Query Q052. The numbering of rows returned by a 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 this needs to be done in the SQL query itself. So, the following query will number the rows of the m_income table according to the ascending order of the ID field values: SELECT COUNT(*) as N, b.id, b.product_id, b.amount, b.price Query Q053. The top five products among the products by sales amount are displayed. The first five records are printed using the TOP instruction: SELECT TOP 5, product_id, sum(amount*price) AS summa Query Q054. The top five products among products by sales amount are displayed, and the rows are numbered as a result: SELECT COUNT(*) AS N, b.product_id, b.summa 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, The Access DBMS uses two types of queries: QBE – query by sample and SQL(Structured Query Language) - structured query language. A sample request is generated by filling out a special request form in the “Query Builder” window. SQL – queries are created by programmers from a sequence of SQL – instructions. SQL is generated, as a rule, by programmers on a query form, which is opened by the “Query Builder” command on the “Creation” tab and “SQL Mode” is selected from the View menu. The SQL language is designed to work with data, i.e. for creating, modifying and managing data in relational databases. It should be noted that there are several SQL query modes (ANSI-89 SQL and ANSI-92 SQL mode queries) that comply with the ANSI-89 SQL and ANSI-92 SQL standards. The instructions contain a description of the data set in SQL. SQL statements consist of clauses (SELECT, FROM, WHERE, etc.). Offers in SQL language consist of terms (operators or commands, identifiers, constants, etc.). The statement begins with a statement (one of the commands SELECT, CREATE, INSERT, UPDATE, DELETE, etc.) and ends with a semicolon. Basic SQL statements: SELECT, FROM and WHERE. For example, the SQL statement: The SELECT clause contains operator SELECT and identifier"Students.Student Code". Here, the full name of the "StudentCode" field is preceded by the name of the "Students" table of the database. SELECT - specifies the field that contains the required data. The FROM clause consists of the FROM clause and the identifier "Students". FROM - defines a table that contains the fields specified in the SELECT clause. It should be noted that when creating a query in SQL, you must take into account its syntax. Despite the fact that the syntax of the SQL language is based on the syntax of the English language, the syntax of the SQL language versions may differ for different DBMSs. There are several types of queries: selecting, updating, adding and deleting records, cross-query, creating and deleting tables, joining tables, etc. The most common is a sample request. Selection queries are used to select the information contained in tables that the user needs. They are created only for related tables. To view SQL query queries in the Access 2003 or 2007 DBMS, you need to execute the SQL View/Mode command in the active window for designing a sample query (Fig. 1). Let's get an SQL statement (SELECT) to sample data from the Access 2003 database according to the student performance criterion "Grade=5" (Fig. 2). As follows from the SELECT statement (Fig. 1), it describes a data set in SQL language: SELECT - defines field names preceded by the names of the tables that contain the data; FROM - defines tables and their relationships through the key fields of the tables (the INNER JOIN ... ON construction is used for this), on the basis of which the data is selected; WHREME - defines the conditions for selecting fields; ORDER BY - determines how to sort in ascending order (by default, ascending order is performed) the values of the "Last Name" field of the "Students" table. As follows from the instructions for retrieving data from the database, the SQL language describes what needs to be obtained from the database, while execution is entrusted to the DBMS, since the SQL language does not have its own means of controlling program execution.
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)
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;
FROM m_outcome
GROUP BY product_id
ORDER BY sum(amount*price) DESC;
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(*)<=5
ORDER BY COUNT(*);
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;
SELECT Students.StudentCode
FROM Students;
consists of the clause "SELECT Students.StudentCode" and the clause "FROM Students".
Rice. 1.
Rice. 2.