Structured Query Language (SQL) is standard means for working with databases. It can be used both for interactive work with the database and included in programming languages. In the Access SQL DBMS you can:
· create, modify or delete tables in the database Access data;
· create or delete keys and indexes for tables in the Access database;
· insert, delete or modify table records;
· receive summary information about data in tables (number of records, amounts, averages, minimums, maximum values and etc.);
· search and retrieve data from one or more tables upon request.
Let's look at the basic SQL operators for searching and retrieving information from a database. To do this, use the SELECT statement
SELECT list of field names 1
FROM list of table names
SELECT, FROM, WHERE, ORDER BY, GROUP BY - keywords;
list of field names 1 - a set of field names that are selected from one or more tables specified in a list of table names, calculated fields, aggregate functions (for example, count, sum, min, max, avg etc.). To select all fields from a list of tables, instead of listing their names, you can use the “*” symbol. If the table field name contains a space, i.e. consists of several words, then this name must be enclosed in square brackets;
list of table names- a set of table names from which information is selected.
Field and table names are case-insensitive and are specified in lists separated by commas.
Examples:
SELECT * FROM Book- selecting all fields of all books from the table “ Book" bibliography databases.
SELECT Title, [Year of publication] FROM Book- selection of header fields ( Name) and year of publication ( The year of publishing) all books from the table " Book". The publication year field name is indicated in square brackets (with a space).
Table “Publisher” Table “Book” Table “Author”
Rice. 2.1. Structure and schema of the bibliography database
To search for information that satisfies a certain condition, The SELECT statement uses a WHERE clause, which has the following syntax:
SELECT list of field names FROM list of table names WHERE condition,
Where condition defines the criterion for searching information.
The condition uses field names, comparison operations (<, <=, =, >, >=, <>) and special comparison operations IN, LIKE, BETWEEN. These operations can be combined using logical operations (And, Or) and set complex information search conditions.
Examples:
SELECT Title, [Year of publication] FROM Book
WHERE [The year of publishing] > 1991 determines the selection of book titles whose year of publication is later than 1991.
WHERE Last name = ”Ivanov” defines the selection of fields for the first and last names of authors from the table " Author", whose surnames coincide with the surname Ivanov. Here the symbol “ is used to specify text constants.
Operation IN compares the contents of the field with a list of specified values that define the information search criterion.
Examples:
SELECT Title, [Year of publication] FROM Book
WHERE [The year of publishing] IN (1995, 1996) determines the choice of titles and year of publication of books published in 1995 or 1996.
SELECT Last name, first name, city FROM Author
WHERE City IN (“Omsk”, “Moscow”, “Kyiv”) determines the choice of authors living in the cities of Omsk, Moscow or Kyiv.
Operation LIKE compares the contents of the field with the sample value. String constants, wildcard characters, and character range lists are used to write a pattern.
The pattern characters are: *, ?, #. The symbol * matches a string of characters, ? - matches one character, # - matches one digit.
Examples:
P* - possible search results for hand, Rose.
CI? - possible search results cue, kit.
12345# - possible search results 123455, 123457.
The range list is enclosed in square brackets, with the first and last characters of the range separated by a hyphen (-). The range is specified in ascending order.
Examples:
SELECT Full Name FROM Author
WHERE Surname LIKE "AND*" determines the choice of authors whose last names begin with the letter I.
SELECT Name FROM Book
WHERE Name LIKE “ *database*" AND [Year of publication] = 1996 determines the selection of book titles published in 1996 and containing the phrase “database” in the title.
Operation BETWEEN checks whether a field value belongs to a range of values and is an inclusive value (records containing a field with a value equal to the boundary of the range are selected). Value boundaries are combined using the AND operation.
Examples:
SELECT Title, [Year of publication] FROM Book
WHERE [The year of publishing] Between #01.01.1994# And #30.06.1994# determines the selection of books published in the first half of 1994. Here the # symbol is used to specify date type constants.
SELECT [Name of publisher], address, telephone FROM Publishing house
WHERE [Publish name] Between "Naw" And "Fin" determines the selection of information about publishing companies, the initial letters of whose names are located in alphabetical order, are in the range between "Nau" and "Fin".
To determine the order, which presents search results for records, uses the section ORDER BY, whose syntax is as follows:
SELECT list of field names FROM list of table names ORDER BY field name 1 [A.S.C.| DESC][, field name 2[A.S.C.| DESC]]
Where field name1, field name2 – fields (from the list of field names) by which the ordering is performed. Option A.S.C. sets the sort order in ascending order (this order is set by default), DESC sets the sort order in descending order. Ordering can be done by multiple fields (for example, by last name and then by first name)
Example:
SELECT Name FROM Book
WHERE Name LIKE “ *database*" AND [Year of publication] = 1996
ORDER BY Name identifies a selection of book titles published in 1996 that contain the phrase "database" in the title, and arranges the titles alphabetically.
To group records section is used GROUP BY, whose syntax is as follows:
SELECT list of field names FROM list of table names GROUP BY field name 1 [, field name 2],
Where field name1, field name2 – fields (from the list of field names) by which records are grouped. The order of the fields in this sentence determines the levels of their grouping. Grouping can be done for expressions (for example, calculated fields), then in the list of fields this expression must be assigned some name. For aggregated functions ( count, sum, min, max, avg etc.) grouping is required.
Example:
SELECT Last name, [Year of publication], Sum (Price) AS[ Price]
GROUPBY Last name, [Year of publication]
ORDER BY Last name, [Year of publication] determines the total price ( Price) books of the year of publication for each author.
When working with multiple tables, each of the considered selection conditions can be applied to any of the tables. IN general case when forming a query using several tables, the WHERE clause indicates the relationship between the tables. The query syntax for multiple tables is as follows:
SELECT list of field names FROM list of table names WHERE list of connections,
Where list of connections defines how the tables in the list of table names are related to each other. For example, table_name1.field_name1 = table_name2.field_name2.
Examples:
SELECT Book. Title, Publisher. [Publish name]
FROM Book, Publishing house
WHERE Book. [Publisher code]= Publisher. [Publisher Code]And
Publishing house. [Publish name]=”Science”
This query determines the selection of book titles ( Name) from the table " Book"and names of publishers ( Publishing house name) from the table of publishers ( Publishing house). The WHERE clause does the following:
· defines the relationship between the tables (the title of the book and the publisher number are selected " Code published" from table " Book", by publisher number " Publisher code" in the table of publishers " Publishing house" the name of the publisher is located);
· sets a condition for selecting books published only by the Nauka publishing house.
The SQL language does not have the functions of a full-fledged development language, but is focused on data access, so it is included in program development tools. In this case it is called embedded SQL. The SQL language standard is supported by modern implementations of the following programming languages: PL/1, Ada, C, COBOL, Fortran, MUMPS and Pascal.
In specialized application development systems of the client-server type, the programming environment is, in addition, usually supplemented with communication tools (establishing and disconnecting connections with database servers, detecting and processing errors occurring in the network, etc.), tools for developing user interfaces, design tools and debugging
There are two main methods for using embedded SQL: static and dynamic.
At static language use (static SQL) The program text contains calls to SQL language functions, which are rigidly included in the executable module after compilation. Changes in called functions can be at the level of individual call parameters using programming language variables.
At dynamic language use (dynamic SQL) it is assumed that calls to SQL functions are dynamically constructed and interpretation of these calls, for example, accessing data from a remote database, during program execution. Dynamic method usually used in cases where the application does not know the type of SQL call in advance and it is constructed in dialogue with the user.
The main purpose of the SQL language (as well as other languages for working with databases) is to prepare and execute queries. As a result of sampling data from one or more tables, a set of records can be obtained, called presentation.
Performance is essentially a table formed as a result of executing a query. We can say that it is a type of stored query. You can build multiple views using the same tables. The view itself is described by specifying the view identifier and the request that must be made to obtain it.
For the convenience of working with views, the concept of a cursor was introduced into the SQL language. Cursor is a kind of pointer used to move through sets of records when processing them.
The description and use of a cursor in SQL is as follows. In the descriptive part of the program, linking is performed variable type cursor (CURSOR) with an SQL statement (usually a SELECT statement). In the executing part of the program, the cursor is opened (OPEN<имя курсора», перемещение курсора по записям (FETCI-1 <имя курсора>...), followed by appropriate processing, and finally closing the cursor (CLOSE<имя курсора>).
In relational DBMSs, to perform operations on relations, two groups of languages are used, which have as their mathematical basis theoretical query languages proposed by E. Codd:
Relational algebra;
Relational calculus.
In relational algebra The operands and results of all actions are relations. Relational algebra languages are procedural because the relation resulting from a query on a relational database is evaluated by executing a sequence of relational operators applied to the relations. Operators consist of operands, which are relations, and relational operations.
Codd relational algebra operations can be divided into two groups: basic set-theoretic and special relational. The first group of operations includes the classical operations of set theory: union, difference, intersection and product. The second group represents the development of ordinary set-theoretic operations towards real problems of data manipulation; it includes the operations: projection, selection, division and connection.
Calculus languages are non-procedural (descriptive or declarative) and allow queries to be expressed using a first-order predicate (function statements) that tuples or relational domains must satisfy. A database query executed using such a language contains only information about the desired result. These languages are characterized by the presence of sets of rules for writing queries. In particular, SQL belongs to the languages of this group.
Features of application SQL queries
An SQL query is a query created using an SQL statement. Examples of SQL queries are join queries, server queries, control queries, and subordinate queries.
A join query is a query that combines fields (columns) from one or more tables or queries into a single field or column in the result set of records. For example, six salespeople provide management with an inventory of their inventory every month. By creating a join query, you can combine these inventories into a result set of records, and then develop a create table query based on the join query.
A request to the server transmits SQL commands via ODBC to the server, for example, Microsoft SQL Server. Server queries allow you to directly work with tables on the server instead of joining them. The result of a request to the server can be loading records or changing data.
A control query creates or modifies database objects, such as Access or SQL Server tables.
A subquery consists of an SQL SELECT statement inside another select or change query. These instructions are entered in the “Field” line of the request form to define a new field or in the “Selection Condition” line to define the field selection condition. Subqueries are used to do the following:
Checking in a subquery for the existence of some results using the reserved words EXISTS or NO EXISTS;
Searches the main query for any values that are equal to, greater than, or less than the values returned in the subquery (using the ANY, IN, or ALL reserved words);
Creating subqueries within subqueries (nested subqueries).
The SQL language in Access can be used to develop screen forms, reports, as well as to create macros and VBA programs.
Relationship between QBE and SQL languages
In Access, there is a close relationship between the QBE and SQL languages. Query tables (forms) in the QBE language, filled out by the user, are converted into SQL expressions before immediate execution. That is, the SQL language is an internal standard for executing queries. This mechanism has the advantage of allowing the Access system to unify the preparation of queries for execution on local and remote computers. In the latter case, the SQL message is actually transmitted to the computer - the request server.
In Access, a query can be in one of three modes (states): Design, SQL, and Table. Design mode is used to develop a new query from scratch (without using Wizards or other tools) or to change the layout of an existing query. SQL mode is used to enter or view SQL statements. Table mode is used to work with query results.
SQL in Forms and Reports
The main sources of records in displays and reports are tables and queries. In the second case, the request can be a ready-made database query or one created when developing a form or report.
SQL in macros
Macro commands are part of macros that are used to automate the execution of frequently repeated actions in working with a database. A macro is one or more macro commands with arguments.
Macros are called from the database window or automatically when certain events occur. The event that triggers a macro can be, for example, the click of a button in the form area or the opening of a database window. Along with performing certain actions on database objects, macros can call other macros, Visual Basic programs, and external applications.
Of the many macro commands, two macro commands are directly related to SQL: Run SQL Query (Run SQL) and Open Query (OpenQuery)
Macro command Run SQL Query runs an Access change or control query using the appropriate SQL statement. This macro makes it possible to perform actions in a macro without first saving queries. You can also execute saved queries using the macro.
Change requests are SQL statements that implement the following functions: adding (INSERT INTO), deleting (DELETE), creating a table (SELECT...INTO) and updating (UPDATE)
Control requests are SQL statements that perform the following functions: create a table (CREATE TABLE), modify a table (ALTER TABLE), drop a table (DROP TABLE), create an index (CREATE INDEX), and drop an index (DROP INDEX)
The only and required argument of the macro command Running a SQL Query is a SQL statement. Macro argument as SQL text - instructions are entered manually in the macro input window or copied from the SQL window, which is often more convenient.
Macro command OpenRequest allows you to open a select or cross-query (in table, design, and preview modes), run a query to change or enter data.
The macro specifies three arguments: request name, mode, and data mode. The first argument specifies the name of the request to open and is required. The second argument specifies the query opening mode (Table, Design, and View). The third argument describes the mode of data entry into the request ("Add", "Change" and "Read Only")
SQL in VBA programs
VBA, like macros, is designed to automate repetitive operations on Access database objects.
In Access, there are the following ways to run VBA programs:
Including a program in the event processing procedure;
Calling a function in an expression;
Calling a Sub procedure in another procedure or in a debugging window;
Executing the macro command RunCode (RunCode) in a macro.
Functions are used in expressions that define calculated fields in forms, reports, or queries. Expressions are used to specify conditions in queries and filters, as well as in macros, VBA statements and methods, and SQL statements. A Sub procedure can include public VBA subroutines that are called from other procedures.
Let's look at running a database query using SQL statements in a Visual Basic for Applications program.
The request selects records from the database that satisfy certain conditions (selection request), or issues instructions to perform specified actions with records that satisfy certain conditions (change request).
There are the following ways to execute queries:
Calling the Execute method (to execute SQL queries for changes);
Creation and execution of a special QueryDef object;
Using an SQL statement as an argument to the OpenRecordset method;
Execute the OpenRecordset method on an existing QueryDef object;
Calling RunSQL and OpenQuery methods.
Execute method used if you need to make a change in the database that does not return records. For example, operations of inserting or deleting records.
QueryDef object represents a saved query definition in the database. It can be thought of as a compiled SQL statement.
OpenRecordset method used to open an object of type Recordset for performing subsequent operations on it.
RunSQL method executes a macro Running a SQL Query in VBA program
OpenQuery method executes the OpenQuery macro command in a VBA program. With it, you can open a query in Datasheet, Design, or View mode. This sets one of the following modes for working with data: adding, changing or reading only.
The choice of query execution option is determined by the programmer, taking into account the characteristics of the problem being solved.
SQL language is used to retrieve data from the database. SQL is a programming language that closely resembles English but is intended for database management programs. SQL is used in every query in Access.
Understanding how SQL works helps you create more accurate queries and makes it easier to correct queries that return incorrect results.
This is an article from a series of articles about the SQL language for Access. It describes the basics of using SQL to retrieve data and provides examples of SQL syntax.
In this article
What is SQL?
SQL is a programming language designed to work with sets of facts and the relationships between them. Relational database management programs such as Microsoft Office Access use SQL to manipulate data. Unlike many programming languages, SQL is readable and understandable even for beginners. Like many programming languages, SQL is an international standard recognized by standards committees such as ISO and ANSI.
Data sets are described in SQL to help answer questions. When using SQL, you must use the correct syntax. Syntax is a set of rules that allow the elements of a language to be combined correctly. The SQL syntax is based on the syntax in English and shares many elements with Visual Basic for Applications (VBA) language syntax.
For example, a simple SQL statement that retrieves a list of last names for contacts named Mary might look like this:
SELECT Last_Name
FROM Contacts
WHERE First_Name = "Mary";
Note: The SQL language is used not only to perform operations on data, but also to create and change the structure of database objects, such as tables. The part of SQL that is used to create and modify database objects is called DDL. DDL is not covered in this article. For more information, see Create or modify tables or indexes using a data definition query.
SELECT statements
The SELECT statement is used to describe a set of data in SQL. It contains a complete description of the set of data that needs to be retrieved from the database, including the following:
tables that contain data;
connections between data from different sources;
fields or calculations based on which data is selected;
selection conditions that must be met by the data included in the query result;
necessity and method of sorting.
SQL statements
An SQL statement is made up of several parts called clauses. Each clause in an SQL statement has a purpose. Some offers are required. The table below shows the most commonly used SQL statements.
SQL statement | Description | Mandatory |
|---|---|---|
|
Defines the fields that contain the required data. |
||
|
Defines tables that contain the fields specified in the SELECT clause. |
||
|
Defines the field selection conditions that all records included in the results must meet. |
||
|
Determines the sort order of the results. |
||
|
In an SQL statement that contains aggregation functions, specifies the fields for which a summary value is not calculated in the SELECT clause. |
Only if such fields are present |
|
|
An SQL statement that contains aggregation functions defines the conditions that apply to the fields for which a summary value is calculated in the SELECT clause. |
SQL terms
Each SQL sentence consists of terms that can be compared to parts of speech. The table below shows the types of SQL terms.
SQL term | Comparable part of speech | Definition | Example |
|---|---|---|---|
|
identifier |
noun |
A name used to identify a database object, such as a field name. |
Clients.[Phone Number] |
|
operator |
verb or adverb |
A keyword that represents or modifies an action. |
|
|
constant |
noun |
A value that does not change, such as a number or NULL. |
|
|
expression |
adjective |
A combination of identifiers, operators, constants, and functions designed to calculate a single value. |
>= Products.[Price] |
Basic SQL Clauses: SELECT, FROM, and WHERE
The general format of SQL statements is:
SELECT field_1
FROM table_1
WHERE criterion_1
;
Notes:
Access does not respect line breaks in SQL statements. Despite this, it is recommended to start each sentence on a new line so that the SQL statement is easy to read both for the person who wrote it and for everyone else.
Every SELECT statement ends with a semicolon (;). The semicolon can appear either at the end of the last sentence or on a separate line at the end of the SQL statement.
Example in Access
The example below shows what an SQL statement for a simple select query might look like in Access.
1. SELECT clause
2. FROM clause
3. WHERE clause
Let's look at the example sentence by sentence to understand how SQL syntax works.
SELECT clause
SELECT,Company
This is a SELECT clause. It contains a (SELECT) statement followed by two identifiers ("[Address Email]" and "Company").
If the identifier contains spaces or special characters (for example, "Email Address"), it must be enclosed in rectangular brackets.
The SELECT clause does not require you to specify the tables that contain the fields, and you cannot specify selection conditions that must be met by the data included in the results.
In a SELECT statement, the SELECT clause always comes before the FROM clause.
FROM clause
FROM Contacts
This is a FROM clause. It contains a (FROM) statement followed by an identifier (Contacts).
The FROM clause does not specify the fields to select.
WHERE clause
WHERE City="Seattle"
This is the WHERE clause. It contains a (WHERE) statement followed by the expression (City="Rostov").
There are many things you can do with SELECT, FROM, and WHERE clauses. For more information about using these offers, see the following articles:
Sorting results: ORDER BY
As in Microsoft Excel, in Access you can sort the results of a query in a table. By using the ORDER BY clause, you can also specify how the results are sorted when the query is executed. If an ORDER BY clause is used, it must appear at the end of the SQL statement.
The ORDER BY clause contains a list of fields to sort, in the same order in which the sort will be applied.
For example, suppose you first want to sort the results by the Company field in descending order, and then, if there are records with the same Company field value, sort them by the Email Address field in ascending order. The ORDER BY clause would look like this:
ORDER BY Company DESC,
Note: By default, Access sorts values in ascending order (A to Z, smallest to largest). To sort the values in descending order instead, you must specify keyword DESC.
For more information about the ORDER BY clause, see the ORDER BY clause article.
Working with summary data: GROUP BY and HAVING clauses
Sometimes you need to work with summary data, such as total sales for the month or the most expensive items in stock. To do this, the SELECT clause applies an aggregate function to the field. For example, if you were to run a query to get the number of email addresses for each company, the SELECT clause might look like this:
The ability to use a particular aggregate function depends on the type of data in the field and the desired expression. For more information about available aggregate functions, see SQL Statistical Functions.
Specifying fields that are not used in an aggregate function: GROUP BY clause
When using aggregate functions, you usually need to create a GROUP BY clause. The GROUP BY clause specifies all fields to which the aggregate function does not apply. If aggregate functions apply to all fields in the query, you do not need to create a GROUP BY clause.
The GROUP BY clause must immediately follow the WHERE or FROM clause if there is no WHERE clause. The GROUP BY clause lists the fields in the same order as the SELECT clause.
Let's continue the previous example. In the SELECT clause, if the aggregate function applies only to the [Email Address] field, then the GROUP BY clause would look like this:
GROUP BY Company
For more information about the GROUP BY clause, see the GROUP BY clause article.
Restricting aggregated values using grouping conditions: the HAVING clause
If you need to specify conditions to limit the results, but the field to which you want to apply them is used in an aggregate function, you cannot use a WHERE clause. The HAVING clause should be used instead. The HAVING clause works the same as the WHERE clause, but is used for aggregated data.
For example, suppose that the AVG function (which calculates the average) is applied to the first field in the SELECT clause:
SELECT COUNT(), Company
If you want to limit query results based on the value of the COUNT function, you cannot apply a selection condition to this field in the WHERE clause. Instead, the condition should be placed in the HAVING clause. For example, if you want your query to return rows only if a company has multiple email addresses, you can use the following HAVING clause:
HAVING COUNT()>1
Note: A query can include both a WHERE clause and a HAVING clause, with selection conditions for fields that are not used in statistical functions specified in the WHERE clause, and conditions for fields that are used in statistical functions in the HAVING clause.
For more information about the HAVING clause, see the HAVING clause article.
Combining query results: UNION operator
The UNION operator is used to simultaneously view all the data returned by multiple similar select queries as a combined set.
The UNION operator allows you to combine two SELECT statements into one. The SELECT statements being merged must have the same number and order of output fields with the same or compatible data types. When a query is executed, the data from each set of matching fields is combined into a single output field, so the query output has as many fields as each individual SELECT statement.
Note: In queries to join numeric and text types data are compatible.
Using the UNION operator, you can specify whether duplicate rows, if any, should be included in the query results. To do this, use the ALL keyword.
A query to combine two SELECT statements has the following basic syntax:
SELECT field_1
FROM table_1
UNION
SELECT field_a
FROM table_a
;
For example, suppose you have two tables called "Products" and "Services". Both tables contain fields with the name of the product or service, price and warranty information, as well as a field that indicates the exclusivity of the product or service offered. Although the "Products" and "Services" tables provide different types guarantees, the basic information is the same (whether certain products or services are provided with a quality guarantee). You can use the following join query to join four fields from two tables:
SELECT name, price, warranty_available, exclusive_offer
FROM Products
UNION ALL
SELECT name, price, guarantee_available, exclusive_offer
FROM Services
;
For more information about combining SELECT statements using the UNION operator, see
SQL is one of the most common programming languages for creating and managing a database, as well as for carrying out various actions with the data itself.
As practice shows, it is quite easy to learn and makes maximum use of standard English vocabulary. Like any other programming language, SQL has its own logic and syntax, a set of basic commands and rules for their use.
Classification of SQL commands
All standard ones can be considered based on their purpose. The following sets can be taken as the basis for informal classification:
Commands for building queries.
Commands of built-in procedures and functions.
Commands for triggers and system tables.
Sets of combinations for working with date and string variables.
Commands for working with data and tables.
This classification can be continued indefinitely, but the main sets of SQL commands will be built precisely on the basis of these types.
Considering the classification of language, one cannot fail to mention that it is universal, as evidenced by the scope of its use. This programming language and its variants are used not only in the standard environment, but also in other programs that, one way or another, you have used.
The scope of use of SQL can be considered from the point of view of office software, namely MicrosoftAccess. This language, or rather its version - MySQL, allows you to administer databases on the Internet. Even the Oracle development environment uses SQL commands at the core of its queries.
Using SQL in MicrosoftAccess
One of the most simple examples The language used for database programming is considered to be the Microsoft Office software package. Studying this software product is provided for in the school computer science course, and in the eleventh grade the MicrosoftAccess database management system is discussed.
It is by learning this application that students become familiar with the database development language and gain a basic understanding of everything involved. Access SQL commands are quite primitive, of course, if you consider them at a professional level. Executing such commands is very simple, and they are created in a custom code editor.
Let's look at a specific example:
SELECT Pe_SurName
WHERE Pe_Name = "Mary";
Based on the syntax of the command, you can understand that it will return the user's last name, in in this case a woman named Mary, which is stored in a Contacts database table.
Although the use of SQL in Access is limited, sometimes such simple queries can greatly simplify the task at hand.
Using SQL Commands in Oracle
Oracle is probably the only one serious competitor Microsoft SQL Server. It is this development and management environment that constantly leads to the improvement of the functions of the software product Microsoft, since competition is the engine of progress. Despite constant competition, Oracle's SQL teams replicate SQL. It is worth noting that although Oracle is considered an almost complete copy of SQL, the logic of this system and the language as a whole is considered simpler.
The Oracle system, when using a certain set of commands, does not have such a complex structure. Considering the capabilities of these database development environments, Oracle does not have a complex nested query structure.
This difference allows you to speed up work with data many times over, but, on the contrary, leads to irrational use of memory, in some individual cases. Oracle's structure is mainly built on temporary tables and their use. As an example: SQL commands in this system are constructed by analogy with the standards of the SQL language itself, although they differ slightly from it.
SELECTCONCAT(CONCAT(CONCAT('Employee', sname), CONCAT(SUBSTR(fname, 0, 1), SUBSTR(otch, 0, 1)), CONCAT('accepted', acceptdate)) FROM employees WHERE acceptdate > to_date ('01.01.80′,'dd.mm.yyyy');
This query will return data about employees who were hired in a certain period of time. Although the query structure is different, the execution of SQL commands in these systems is similar, except for minor details.
Using SQL on the Internet
With the advent world wide web, that is, the Internet, the scope of use of the SQL language is expanding. As you know, a lot of information is stored on the network, but it is not randomly located, but placed on sites and servers according to certain criteria.
Databases are directly responsible for storing information on the Internet, as in other places, and websites are management systems. As a rule, sites and their program code organized by different languages programming, but databases are based on one of the varieties of SQL, namely the database creation language, oriented towards MySQL web interfaces.
The syntax and basic set of commands of this language completely copy the familiar SQL, but with some of its own additions, which make it different from Microsoft tSQL Server.
SQL commands are completely similar not only in syntax, but also in the standard set of function words. The only difference is in the calling and structuring of the request. For example, you can consider a request to create a new table; this is the first thing children are taught in computer science schools:
$link = mysqli_connect("localhost", "root", "", "tester");
if (!$link) die("Error");
$query = "create table users(
login VARCHAR(20),
password VARCHAR(20)
if (mysqli_query($link, $query)) echo "The table has been created.";
elseecho "Table not created: ".mysqli_error();
mysqli_close($link);
As a result of executing such a request, you can get new table“Users”, which will have two fields: login and password.
The syntax has been modified for the Web, but is based on MicrosoftSQLServer commands.
Building MicrosoftSQLServer Queries
Selecting from tables of a specific data set is one of the main tasks of SQL. For such operations there is provision select command in SQL. This is what will be discussed below.
The rules for constructing a command are very simple, and the select command itself in SQL is constructed as follows. For example, there is a table that contains data about an employee, which, for example, has the name Person. Let's set the task that from the table you need to select data about employees whose date of birth is in the interval from the first of January to the first of March of the current year inclusive. For such a sample it is necessary to perform SQL command, in which there will be not only a standard design, but also a selection condition:
Select * from Person
Where P_BerthDay >= ‘01/01/2016’ and P_BerthDay<= ‘03/01/2016’
Executing such a command will return all data about employees whose birthday is in the period that you specified. Sometimes the task may be to display only the last name, first name and patronymic of an employee. To do this, you need to construct the request a little differently, for example, this way:
SelectP_Name - name
P_SurName - surname
P_Patronimic - patronymic
Where P_BerthDay >= ‘01/01/2016’ and P_BerthDay<= ‘03/01/2016’
However, this is just a choice of something. It, in essence, does not affect anything, but only provides information. But if you decide to take the SQL language seriously, you will have to learn how to make changes to databases, since building them without this is simply impossible. How this is done will be discussed below.
Basic SQL Commands for Changing Data
The language syntax is built not only for executing queries, but also for manipulating data. The main task of a database programmer is to write scripts for selections and reports, but sometimes it is necessary to make changes to tables. The list of SQL commands for such actions is small and consists of three main commands:
Insert (trans. Insert).
Update (trans. Update).
Delete (trans. Delete).
The purpose of these commands is easy to determine; all you need to do is translate their name. These commands are easy to use and have a simple structure, but it is worth mentioning that some of them, if used incorrectly, can cause irreparable harm to the database.
As a rule, before using such MSSQL commands you need to think through it and take into account all the possible consequences of their execution.
Having learned these commands, you can fully start working with database tables, thereby modifying it and introducing some new variables or deleting old ones.
Insert command
To insert data into a table, the safest command is Insert. Incorrectly inserted data can always be deleted and re-entered into the database.
The Insert command is intended for inserting new data into a table and allows you to add both a complete set and selectively.
For example, consider the insert command into the previously described Person table. In order to enter data into a table, you need to run an SQL command that will allow you to insert all the data into the table or fill it selectively.
Insert into person
Select ‘Grigoriev’,’Vitaly’,’Petrovich’,’01/01/1988’
Commands of this type automatically fill all table cells with the specified data. There are situations when an employee does not have a middle name, say, he came to work from Germany as an exchange student. In this case, you need to execute a data insertion command, which will enter into the table only what is needed. The syntax of such a command will be as follows:
Insertintoperson(P_Name, P_SurName ,P_BerthDay)
Values ('David', 'Hook', '02/11/1986')
Such a command will fill only the specified cells, and all others will have the value null.
Command to change data
To change the data of both the entire row and some cells, use the Update SQL command. You need to execute such a command only with a certain condition, namely, indicate exactly which line by number you need to make changes to.
The Update SQL command has a simple syntax. For correct use, you must indicate which data should be changed in which column and in which record. Next, create a script and execute it. Let's look at an example. We need to change the date of birth of David Hooke, who is listed as number 5 in the employee table.
Set P_BerthDay = '02/10/1986' where P_ID = 5
The condition (in this script) will not allow you to change the date of birth in all table records, but will update only the necessary ones.
It is this command that programmers use most often, since it allows you to change the data in the table without causing significant damage to all the information.
Commands for using built-in procedures and functions
Using the SQL language, you can not only build queries, but also create built-in mechanisms for working with data. As a rule, there are times when you need to use a selection that was written earlier in the body of one request.
If you judge logically, then you need to copy the text of the selection and paste it into the right place, but you can get by with a simpler solution. Let's consider an example when a button for printing a report, say in Excel, is displayed on the working interface. This operation will be performed as needed. Built-in stored procedures are used for such purposes. The commands in this case are enclosed in a procedure and called using the SQLExec command.
Let's assume that a procedure has been created to display the date of birth of employees from the previously described Person table. In this case, there is no need to write the entire request. To obtain the necessary information, simply execute the Exec [procedure name] command and pass the parameters necessary for sampling. As an example, we can consider the mechanism for creating a procedure of this nature:
CREATEPROCEDUREPrintPerson
@DBsmalldatetime
@DE smalldatetime
SELECT * from Person
FROM HumanResources.vEmployeeDepartmentHistory
WHERE P_BerthDay >= @DB and P_BerthDay<= @DE
ANDEndDateISNULL;
This procedure will return all information about employees whose birthday will be in a given time period.
Organization of data integrity. Triggers
Some MS SQL commands, one might even say constructs, allow you not only to organize manipulations with data, but also to ensure their integrity. For such purposes, the language contains system constructs that are created by the programmer himself. These are so-called triggers that can provide data control.
In this case, standard SQL query commands are used to organize the checking of conditions. In triggers, you can create a lot of conditions and restrictions for working with data, which will help control not only access to information, but also prohibit deleting, changing or inserting data.
There are no restrictions on the types of SQL commands that can be used in a trigger. Let's look at an example.
If we describe the mechanism for creating a trigger, the types of SQL commands here are the same as when creating a procedure. The algorithm itself will be described below.
First of all, we need to describe the service command for creating triggers:
We indicate for which data operation (in our case it is a data modification operation).
The next step is to specify the tables and variables:
declare @ID int. @Date smalldatetime @nID int. @nDatesmalldatetime
DEclare cursor C1 for select P_ID, P_BerthDay from Inserted
DEclare cursor C2 for select P_ID, P_BerthDay from deleted
We set the data selection steps. Afterwards, in the body of the cursors we write the condition and the reaction to it:
if @ID = @nID and @nDate = "01/01/2016"
sMasseges "The operation cannot be performed. The date is not suitable"
It is worth mentioning that a trigger can not only be created, but also disabled temporarily. This manipulation can only be carried out by a programmer by executing the SQL SERVER commands:
altertablePERSONdisabletriggerall - to disable all triggers created for this table, and, accordingly, altertablePERSONenabletriggerall - to enable them.
These basic SQL commands are the most commonly used, but their combinations can be varied. SQL is a very flexible programming language and gives the developer maximum possibilities.
Conclusion
From all of the above, we can draw the only conclusion: knowledge of the SQL language is simply necessary for those who are going to seriously engage in programming. It underlies all operations performed both on the Internet and in home databases. That is why a future programmer must know many commands of this language, since only with their help can one, so to speak, communicate with a computer.
Of course, there are disadvantages, as in everything in this world, but they are so insignificant that they simply pale in comparison with the advantages. Among all languages SQL programming almost one of a kind, because it is universal, and knowledge of writing scripts and codes underlies almost all sites.
The main advantage of SQL can undoubtedly be considered its simplicity, because, after all, it is included in the school curriculum. Even a novice programmer who doesn’t really understand languages can handle it.
Access DBMS
Microsoft Access is a relational type DBMS, which reasonably balances all the tools and capabilities typical of modern database management systems. 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 for quickly creating database management applications;
Possibility of using OLE technology;
Integration with Microsoft Office suite;
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 a standard relational database language. 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.