The online Merriam-Webster dictionary defines database as big dataset organized specifically for provide fast search and data extraction(for example, using a computer).
Database management system (DBMS), as a rule, is set of libraries, applications and utilities, freeing the application developer from the burden of worrying about details storage and data management. The DBMS also provides facilities for searching and updating records.
Over the years, many DBMSs have been created to solve various kinds of data storage problems.
Database types
In the 1960s and 70s, databases were developed that, in one way or another, solved the problem of repeating groups. These methods have led to the creation of models of database management systems. The basis for such models, which are used to this day, was research conducted at IBM.
One of the fundamental design factors for early DBMSs was efficiency. It is much easier to manipulate database records that have a fixed length, or at least a fixed number of elements per record (columns per row). This avoids the problem of repeating groups. Anyone who has programmed in any procedural language will easily understand that in this case it is possible to read each record of the database into a simple C structure. However, in real life such lucky situations are rare, so programmers have to process data that is not so conveniently structured.
Database with network structure
The network model introduces pointers into databases - records containing links to other records. So, you can store a record for each customer. Each customer has placed many orders with us for some time. The data is arranged so that the customer record contains a pointer to exactly one order record. Each order record contains both the data for that particular order and a pointer to another order record. Then, in the currency converter application that we worked on earlier, we could use a structure that would look something like this (Fig. 1.):
Rice. 1. Structure of currency converter records
The data is loaded and a linked (hence the name of the model is network) list for languages is obtained (Fig. 2):
Rice. 2. Linked List
The two different record types shown in the figure will be stored separately, each in its own table.
Of course, it would be more appropriate if the names of the languages were not repeated in the database over and over again. It is probably better to introduce a third table that would contain the languages and an identifier (often an integer) that would be used to refer to the language table entry from another type of entry. Such an identifier is called a key.
The network database model has several important advantages. If you need to find all records of one type related to a specific record of another type (for example, languages spoken in one of the countries), then you can do this very quickly by following the pointers, starting with the specified record.
There are, however, disadvantages as well. If we want a list of countries where French is spoken, we would have to follow the links of all country records, and for large databases this would be very slow. This can be remedied by creating other linked lists of pointers specifically for the languages, but this solution quickly becomes overly complicated and is certainly not universal, since it is necessary to decide in advance how the links will be organized.
In addition, writing an application that uses the database network model is quite tedious because it is usually the responsibility of the application to create and maintain pointers as records are updated and deleted.
Hierarchical database model
In the late 1960s, IBM used a hierarchical database building model in IMS. In this model, the problem of repeating groups was solved by representing some records as consisting of many others.
This can be thought of as a "BOM" that is used to describe the constituents of a complex product. For example, a car consists of (say) a chassis, a body, an engine, and four wheels. Each of these basic components is in turn made up of several others. The engine includes several cylinders, a cylinder head and a crankshaft. These components again consist of smaller ones; so we get to the nuts and bolts, which are completed with any components of the car.
The hierarchical database model is still used today. A hierarchical DBMS is able to optimize data storage in terms of some specific issues, for example, you can easily determine which car uses a particular part.
Relational database model
A huge leap in the development of the theory of database management systems occurred in 1970, when the report by E. F. Codd (E. F. Codd) "A Relational Model of Data for Large Shared Data Banks ”), see this link. This truly revolutionary work introduced the concept of relationships and showed how to use tables to represent facts that establish relationships with "real world" objects and therefore store data about them.
By this time, it had already become clear that efficiency, which was originally fundamental to the design of the database, was not as important as the integrity of the data. The relational model emphasizes data integrity much more than any other model that has been used before.
A relational database management system is defined by a set of rules. First, a table entry is called a "tuple", which is the term used in some of the PostgreSQL documentation. A tuple is an ordered group of components (or attributes), each of which belongs to a particular type. All tuples are built according to the same template, all have the same number of components of the same types. Here is an example of a set of tuples:
("France", "FRF", 6.56) ("Belgium", "BEF", 40.1)
Each of these tuples consists of three attributes: country name (string type), currency (string type), and exchange rate (float type). In a relational database, all records added to this set (or table) must follow the same form, so the records below cannot be added:
Moreover, no table can have duplicate tuples. That is, duplicate rows or records are not allowed in any relational database table.
Such a measure might seem draconian, as it would seem that for a system that stores orders placed by customers, this means that the same customer cannot order the same product twice.
Each entry attribute must be "atomic", that is, a simple piece of information, not another entry or a list of other arguments. In addition, the types of the corresponding attributes in each entry must match, as shown above. Technically, this means that they must come from the same value set or domain. Almost all of them must be either strings, or integers, or floating-point numbers, or belong to some other type supported by the DBMS.
The attribute by which records are otherwise identical is called a key. In some cases, a combination of several attributes can act as a key.
An attribute (or attributes) intended to distinguish a certain record of a table from all other records of this table (or, in other words, make the record unique) is called the primary key. In a relational database, every relation (table) must have a primary key, that is, something that would make each entry different from all the others in that table.
The final rule that defines the structure of a relational database is referential integrity. This requirement is explained by the fact that at any given time, all records in the database must be meaningful. The developer of an application interacting with the database must be careful, he must make sure that his code does not violate the integrity of the database. Imagine what happens when a client is deleted. If a customer is removed from the CUSTOMER relationship, all of their orders must also be removed from the ORDERS table. Otherwise, there will be records of orders that do not have a customer associated with them.
My next blogs will provide more detailed theoretical and practical information about relational databases. For now, remember that the relational model is built on mathematical concepts such as sets and relationships, and that certain rules must be followed when building systems.
SQL query languages and others
Relational database management systems, of course, provide ways to add and update data, but this is not the main thing, the strength of such systems lies in the fact that they provide the user with the ability to ask questions about stored data in a special query language. Unlike earlier databases, which were specifically designed to answer certain types of questions about the information they contain, relational databases are much more flexible and answer questions that were not yet known when the database was created.
Codd's relational model takes advantage of the fact that relationships define sets, and sets can be processed mathematically. Codd suggested that such a section of theoretical logic as predicate calculus could be applied in queries, and query languages were built on its basis. This approach provides unprecedented performance for searching and retrieving data sets.
The query language QUEL was one of the first to be implemented; it was used in the Ingres database created in the late 1970s. Another query language that used a different method was called QBE (Query By Example). Around the same time, a group at the IBM Research Center developed the Structured Query Language (SQL), the name commonly pronounced "sequel".
SQL- This standard query language, its most common definition is the ISO / IEC 9075:1992 standard, “Information Technology - Database Languages - SQL” (or, more simply, SQL92) and its American counterpart ANSI X3.135-1992, which differs from the first only by a few cover pages. These standards have replaced the pre-existing SQL89. There is actually a later standard, SQL99, but it hasn't caught on yet, and most of the updates don't affect the core SQL language.
There are three levels of SQL92 compliance: Entry SQL, Intermediate SQL, and Full SQL. The most common is the "Entry" level, and PostgreSQL is very close to that, although there are slight differences. The developers are working on fixing minor omissions, and with each new version PostgreSQL is getting closer to the standard.
There are three types of commands in SQL language:
- Data Manipulation Language (DML)- data manipulation language. This is the part of SQL that is used 90% of the time. It consists of commands for adding, deleting, updating, and, most importantly, fetching data from the database.
- Data Definition Language (DDL)- data definition language. These are commands for creating tables and managing other aspects of the database that are structured at a higher level than their data.
- Data Control Language (DCL)- data management language
This is a set of commands that control access rights to data. Many database users never use such commands because they work in large companies where there is a special database administrator (or even several) who manages the database, his functions also include access control.
SQL
SQL is almost universally accepted as the standard query language and, as already mentioned, is described in many international standards. Almost every DBMS these days supports SQL to some degree. This promotes unification because an application written with SQL as the interface to the database can be ported and used in another database without much cost in terms of time and effort.
However, under market pressure, database vendors are forced to create products that differ from each other. This is how several dialects of SQL appeared, which was facilitated by the fact that the standard describing the language does not define commands for many database administration tasks that are a necessary and very important component when using the database in the real world. Therefore, there are differences between the SQL dialects adopted by (for example) Oracle, SQL Server, and PostgreSQL.
SQL will be covered throughout the book, but for now, here are a few examples to show what the language is like. It turns out that in order to start working with SQL, it is not necessary to learn its formal rules.
Let's create a new table in the database using SQL. This example creates a table for the items offered for sale that will be included in the order:
CREATE TABLE item (item_id serial, description char(64) not null, cost_price numeric(7,2), sell_price numeric(7,2));
Here we have determined that the table needs an identifier to act as a primary key, and that it should be automatically generated by the database management system. The identifier is of type serial, which means that each time a new item element is added to the sequence, a new, unique item_id will be created. Description (description) is a text attribute consisting of 64 characters. Cost price (cost_price) and sale price (sell_price) are defined as floating point numbers with two decimal places.
Now we use SQL to populate the table we just created. There is nothing complicated in this:
INSERT INTO item(description, cost_price, sell_price) values("Fan Small", 9.23, 15.75); INSERT INTO item(description, cost_price, sell_price) values("Fan Large", 13.36, 19.95); INSERT INTO item(description, cost_price, sell_price) values("Toothbrush", 0.75, 1.45);
The basis of SQL is the SELECT statement. It is used to create result sets - groups of records (or attributes of records) that match some criteria. These criteria can be quite complex. Result sets can be used as targets for updates by an UPDATE statement or deletions by a DELETE statement.
Here are some examples of using the SELECT statement:
SELECT * FROM customer, orderinfo WHERE orderinfo.customer_id = customer.customer_id GROUP BY customer_id SELECT customer.title, customer.fname, customer.lname, COUNT(orderinfo.orderinfo_id) AS "Number of orders" FROM customer, orderinfo WHERE customer.customer_id = orderinfo.customer_id GROUP BY customer.title, customer.fname, customer.lname
These SELECT statements list all customer orders in the specified order and count the number of orders placed by each customer.
For example, the PostgreSQL database provides several ways to access data, in particular, you can:
- Use a console application to execute SQL statements
- Embed SQL directly into the application
- Use API (Application Programming Interfaces) function calls to prepare and execute SQL statements, view result sets, and update data from many different programming languages
- Use indirect access to PostgreSQL database data using an ODBC (Open Database Connection) or JDBC (Java Database Connectivity) driver or a standard library such as DBI for Perl
Database management systems
DBMS, as mentioned earlier, is a set of programs that make it possible to build databases and use them. The responsibilities of the DBMS include:
- Database creation. Some systems manage one large file and create one or more databases within it, others may use several operating system files or directly implement low-level access to disk partitions. Users and developers do not have to worry about the low-level structure of such files, since the DBMS provides all the necessary access.
- Providing the means to perform queries and updates. The DBMS must provide the ability to query data that satisfies some criteria, such as the ability to select all orders placed by a certain customer but not yet delivered. Before SQL was widely adopted as a standard language, the way such queries were expressed varied from system to system.
- Multitasking. If several applications work with the database or it is simultaneously accessed by several users, then the DBMS must ensure that the processing of each user's request does not affect the work of the others. That is, users only have to wait if someone else is writing data exactly when they need to read (or write) data to some element. Several data reads can occur at the same time. In fact, it turns out that different databases support different levels of multitasking, and that these levels can even be customizable.
- Journaling. The DBMS must keep a log of all data changes over a period of time. It can be used for error tracking and (perhaps even more importantly) for data recovery in the event of a system failure such as an unplanned power outage. Typically, data is backed up and transaction logs are kept, as the backup can be useful for restoring the database in the event of a disk failure.
- Ensuring database security. The DBMS must provide access control so that only registered users can manipulate the data stored in the database and the database structure itself (attributes, tables, and indexes). Usually, a hierarchy of users is defined for each database, at the head of this structure is a “superuser” who can change anything, then there are users who can add and delete data, and at the very bottom are those who have read-only rights. The DBMS must have facilities to allow users to be added and removed, and to specify which database features they can access.
- Maintain referential integrity. Many DBMSs have features that help maintain referential integrity, that is, the correctness of data. Usually, if a query or update violates the rules of the relational model, the DBMS issues an error message.
|
|
In the previous two articles in this series, published in issues 6 and 7 of our journal, we looked at various data access mechanisms, including ADO, BDE and their alternatives. Now we know how to choose a data access technology for a particular pair of "DBMS - development tool".
With data access technology in place, we can finally think about how the data itself and metadata should be manipulated. Manipulation methods can be specific to a given DBMS (for example, using objects of the client part of this DBMS to access database objects) or for a given data access mechanism. Nevertheless, there is a more or less universal way to manipulate data, supported by almost all server-side relational DBMS and most universal data access mechanisms (including when used in conjunction with desktop DBMS). This method is the use of SQL (Structured Query Language - Structured Query Language). Below we will consider the purpose and features of this language, and also learn how to use it to extract and summarize data, add, delete and modify records, protect data from unauthorized access, and create databases. For a more detailed study of SQL, we can recommend the books by Martin Graber "Introduction to SQL" (M., Laurie, 1996) and "SQL. Reference guide” (M., Lori, 1997).
Introduction
Structured Query Language is a non-procedural language used to manage relational DBMS data. The term "non-procedural" means that in a given language it is possible to formulate what is to be done with the data, but it cannot be instructed exactly how to do it. In other words, this language lacks algorithmic constructs such as labels, loop statements, conditional jumps, etc.
The SQL language was created in the early 1970s as a result of an IBM research project whose goal was to create a relational data manipulation language. It was originally called SEQUEL (Structured English Query Language), then SEQUEL/2, and then just SQL. The official SQL standard was published by ANSI (American National Standards Institute - National Standards Institute, USA) in 1986 (this is the most commonly used implementation of SQL today). This standard was extended in 1989 and 1992, so the latest SQL standard is called SQL92. Work is currently underway on the SQL3 standard, which contains some object-oriented extensions.
There are three levels of compliance with the ANSI standard - beginner, intermediate and complete. Many server database vendors, such as IBM, Informix, Microsoft, Oracle, and Sybase, use their own implementations of SQL based on the ANSI standard (meeting at least entry-level compliance) and containing some database-specific extensions.
For more information on conforming to the SQL version used by a particular DBMS, see the documentation that comes with that DBMS.
How SQL Works
Let's take a look at how SQL works. Suppose we have a database managed by some DBMS. To extract data from it, a query formulated in SQL is used. The DBMS processes this request, retrieves the requested data, and returns it. This process is schematically depicted in Fig. one .
As we will see later, SQL allows not only to retrieve data, but also to define the data structure, add and delete data, restrict or grant access to data, and maintain referential integrity.
Note that SQL itself is neither a DBMS nor a separate product. This is the language used to interact with the DBMS and is, in a sense, an integral part of it.
Data Definition Language (DDL)
The Data Definition Language contains operators that allow you to create, modify, and destroy databases and objects within them (tables, views, etc.). These operators are listed in Table. one.
Table 1
|
Operator |
Description |
|---|---|
|
Used to add a new table to a database |
|
|
Used to remove a table from a database |
|
|
Used to change the structure of an existing table |
|
|
Used to add a new view to a database |
|
|
Used to remove a view from a database |
|
|
Used to create an index for a given field |
|
|
Used to remove an existing index |
|
|
Used to create a new schema in a database |
|
|
Used to remove a schema from a database |
|
|
Used to create a new domain |
|
|
Used to override the domain |
|
|
Used to remove a domain from a database |
Data Manipulation Language (DML)
The Data Manipulation Language contains operators that allow you to select, add, delete, and modify data. Note that these statements are not required to complete the transaction within which they are called. DML statements are presented in table. 2.
table 2
Sometimes the SELECT statement falls into a separate category called Data Query Language (DQL).
Cursor Control Language (CCL)
Cursor Control Language statements are used to define a cursor, prepare SQL statements for execution, and some other statements. CCL statements are presented in Table. 5.
Table 5
|
Operator |
Description |
|---|---|
|
Used to determine the cursor for a query |
|
|
Used to describe a query plan. This statement is a SQL extension for Microsoft SQL Server 7.0. It is not required to be executed in other DBMS. For example, in the case of Oracle, you should use the EXPLAIN PLAN statement |
|
|
Used to open a cursor when getting query results |
|
|
Used to get a string from the results of a query |
|
|
Used to close the cursor |
|
|
Used to prepare an SQL statement for execution |
|
|
Used to execute an SQL statement |
|
|
Used to describe a prepared query |
All SQL statements have the form shown in Fig. 2.
Every SQL statement starts with a verb, which is a keyword that defines what the statement does (SELECT, INSERT, DELETE...). The operator also contains clauses containing information about what data operations are performed on. Each clause begins with a keyword such as FROM, WHERE, etc. The structure of the clause depends on its type - some clauses contain table or field names, some may contain additional keywords, constants or expressions.
How to execute SQL statements
All modern server DBMS (as well as many popular desktop DBMS) contain utilities that allow you to execute a SQL statement and get acquainted with its result. In particular, the client part of Oracle contains the SQL Plus utility, and Microsoft SQL Server contains the SQL Query Analyzer utility. It is this utility that we will use to demonstrate the capabilities of SQL, and as the database on which we will “experiment”, we will take the NorthWind database included in the Microsoft SQL Server 7.0 distribution package. In principle, you can use another database and any other utility that can execute SQL statements in this database and display the results (or even write your own using any development tool - Visual Basic, Delphi, C ++ Builder, etc. ). However, it is recommended that you back up this database just in case.
sql is often referred to as the Esperanto language for database management systems (DBMS). Indeed, there is no other language in the world for working with databases (DB), which would be so widely used in programs. The first sol standard appeared in 1986 and has gained universal acceptance by now. It can be used even when working with non-relational DBMS. Unlike other software tools, such as C and Cobol, which are the prerogative of professional programmers, sql is used by specialists from a wide variety of fields. Programmers, DBMS administrators, business analysts - they all successfully process data using sql. Knowledge of this language is useful to everyone who has to deal with the database.
In this article, we will cover the basic concepts of sql. We will tell his background (and dispel a few myths along the way). You will get acquainted with the relational model and will be able to acquire the first skills in working with sql, which will help in further mastering the language.
Is it difficult to learn sql? It depends on how deep you are going to delve into the essence. In order to become a professional, you have to learn a lot. The sql language began in 1974 as the subject of a small 23-page research paper and has come a long way since then. The text of the current standard - the official document "the international standard database language sql" (usually called sql-92) - contains over six hundred pages, but it does not say anything about the specific features of the sol versions implemented in the DBMS of microsoft, oracle, sybase etc. The language is so developed and diverse that just listing its features would require several journal articles, and if you collect everything that is written on the topic of sol, you get a multi-volume library.
However, for an ordinary user it is not at all necessary to know sql in its entirety. Just like a tourist who finds himself in a country where they speak an incomprehensible language, it is enough to learn only a few common expressions and grammar rules, so in sql - knowing a little, you can get a lot of useful results. In this article, we will look at the basic sql commands, the rules for setting criteria for selecting data, and show how to get results. As a result, you will be able to independently create tables and enter information into them, compose queries and work with reports. This knowledge can become the basis for further independent development of sql.
What is sql?
sql is a specialized non-procedural language that allows you to describe data, select and process information from relational DBMS. Specialization means that sol is intended only for working with the database; it is impossible to create a full-fledged application system using only the means of this language - for this you will need to use other languages in which you can embed sql commands. Therefore, sql is also called an auxiliary language tool for data processing. Auxiliary language is used only in combination with other languages.
In a general-purpose application language, there are usually facilities for creating procedures, but in sql they are not. It cannot be used to specify how a certain task should be performed, but only to determine what exactly it is. In other words, when working with sql, we are interested in the results, not the procedures for obtaining them.
The most essential property of sql is the ability to access relational databases. Many people even think that the expressions "sql-processed database" and "relational database" are synonymous. However, you will soon see that there is a difference between them. The sql-92 standard doesn't even have the term relation.
What is a relational DBMS?
Without going into details, a relational DBMS is a system based on a relational data management model.
The concept of a relational model was first proposed in the work of Dr. E. F. Codd, published in 1970. It described the mathematical apparatus for structuring and managing data, and also proposed an abstract model for representing any real information. Prior to this, when using a database, it was necessary to take into account the specific features of storing information in it. If the internal structure of the database was changed (for example, in order to increase performance), application programs had to be reworked, even if there were no changes at the logical level. The relational model made it possible to separate the particular features of data storage from the application program level. Indeed, the model does not describe how information is stored and accessed. Only how this information is perceived by the user is taken into account. Thanks to the advent of the relational model, the approach to data management has changed qualitatively: it has turned from an art into a science, which has led to a revolutionary development of the industry.
Basic concepts of the relational model
According to the relational model, a relation is some kind of table with data. A relation can have one or more attributes (features) corresponding to the columns of this table, and some set (possibly empty) of data representing sets of these attributes (they are called n-ary tuples, or records) and corresponding to the rows of the table.
For any tuple, attribute values must belong to so-called domains. In fact, a domain is some set of data that defines the set of all valid values.
Let's look at an example. Let there be a Days of Week domain containing values from Monday to Sunday. If the relation has a DayWeek attribute that matches this domain, then any tuple in the relation must have one of the listed values in the DayWeek column. The appearance of the values January or Cat is not allowed.
Please note: the attribute must have one of the valid values. Specifying multiple values at once is prohibited. Thus, in addition to the requirement that attribute values belong to a certain domain, the condition of its atomicity must be observed. This means that decomposition is not allowed for these values, that is, they cannot be broken into smaller parts without losing the main meaning. For example, if the attribute value simultaneously contained Monday and Tuesday, then two parts could be distinguished, retaining the original meaning - Day of the Week; therefore, this attribute value is not atomic. However, if you try to break the meaning of "Monday" into parts, you get a set of individual letters - from "P" to "K"; the original meaning is lost, so the value "Monday" is atomic.
Relationships also have other properties. The most significant of them is the mathematical property of closed operations. This means that as a result of performing any operation on a relation, a new relation must appear. This property allows you to get predictable results when performing mathematical operations on relations. In addition, it becomes possible to represent operations as abstract expressions with different levels of nesting.
In his original work, Dr. Codd defined a set of eight operators called relational algebra. Four operators—union, logical multiplication, difference, and Cartesian product—were carried over from traditional set theory; the rest of the operators were created specifically to handle relationships. Subsequent work by Dr. Codd, Chris Date, and others has proposed additional operators. Later in this article, three relational operators will be considered - production (project), restrictions (select, or restrict) and merge (join).
sql and relational model
Now that you are familiar with the relational model, let's forget about it. Of course, not forever, but only to explain the following: although it was the relational model proposed by Dr. Codd that was used in the development of sql, there is no complete or literal correspondence between them (this is one of the reasons why the sql-92 standard does not contain the term relation). For example, the terms sql table and relation are not equivalent, because tables can have several identical rows at once, while identical tuples are not allowed in relations. In addition, sql does not provide for the use of relational domains, although data types play a role to some extent (some influential proponents of the relational model are now trying to get relational domains included in the future sql standard).
Unfortunately, the mismatch between sql and the relational model has created a lot of misunderstanding and controversy over the years. But since the main topic of the article is the study of sql, and not the relational model, these problems are not considered here. Just be aware that there are differences between the terms used in sql and in the relational model. Further in the article, only the terms accepted in sql will be used. Instead of relations, attributes and tuples, we will use their sql counterparts: tables, columns and rows.
static and dynamic sql
You may already be familiar with terms like static and dynamic sql. An sql query is static if it is compiled and optimized at a stage prior to program execution. We already mentioned one form of static sql when we talked about embedding sql commands in C or Cobol programs (there is another name for such expressions - embedded sql). As you can probably guess, a dynamic sql query is compiled and optimized during program execution. As a rule, ordinary users use dynamic sql, which allows them to create queries in accordance with momentary needs. One of the options for using dynamic sql queries is their interactive or direct call (there is even a special term - directsql), when queries sent for processing are entered interactively from the terminal. Between static and dynamic sql there are certain differences in the syntax of the structures used and the features of execution, however, these issues are beyond the scope of the article. We only note that for clarity of understanding, the examples are given in the form of direct sql queries, since this allows not only programmers, but also most end users to learn how to use sql.
how to learn sql
You are now ready to write your first sql queries. If you have access to the database via sql and want to use our examples in practice, then consider the following: you must log in as a user with unlimited privileges and you will need software tools for interactive processing of sql queries (if we are talking about a network database, you should talk to the database administrator about granting you the appropriate rights). If you don’t have access to the database via sql, don’t be upset: all examples are very simple and you can figure them out “dry”, without accessing the machine.
In order to perform any action in sql, you must execute an expression in the sql language. There are several types of expressions, but three main groups can be distinguished among them: ddl-commands (data definition language - data description language), dml-commands (data manipulation language - data manipulation language) and data control tools. Thus, in a sense, three different languages are combined in sql.
Data Description Language Commands
Let's start with one of the main ddl commands - create table (Create a table). There are several types of tables in sql, the main ones are two types: base (base) and selective (views). Base tables are tables related to real-life data; selective - these are "virtual" tables that are created on the basis of information obtained from the base tables; but to users, the forms look like normal tables. The create table command is for creating base tables.
In the create table command, you must specify the name of the table, specify the list of columns and the types of data they contain. Other optional elements can also be present as parameters, but first, let's look at only the main parameters. Let's show the simplest syntactic form for this command:
create table TableName (Column DataType) ;
create and table are sql keywords; TableName, Column, and DataType are formal parameters, instead of which the user enters the actual values each time. The Column and DataType parameters are enclosed in parentheses. In sql, parentheses are commonly used to group individual elements. In this case, they allow you to combine definitions for a column. The semicolon at the end is the command separator. It must complete any expression in the sql language.
Consider an example. Suppose you want to create a table to store data about all appointments (appointments). To do this, enter the command in sql:
create table appointments (appointment_date date) ;
After executing this command, a table called appointments will be created, where there is one appointment_date column, which can store data of type date. Since no data has been entered yet, the number of rows in the table is zero (the create table command only defines the table; the actual values are entered with the insert command, which is discussed later).
The appointments and appointment_date parameters are called identifiers because they specify names for specific database objects, in this case the names for the table and column, respectively. There are two types of identifiers in sql: regular (regular) and dedicated (delimited). Delimited identifiers are enclosed in double quotes and are case-sensitive. Regular identifiers are not distinguished by any limited characters, and they are not case-sensitive. This article uses only regular identifiers.
The symbols used to construct identifiers must follow certain rules. Regular identifiers can only use letters (not necessarily Latin, but also other alphabets), numbers, and the underscore character. The identifier must not contain punctuation, spaces, or special characters (#, @, %, or!); also, it cannot start with a digit or underscore. You can use separate sql keywords for identifiers, but this is not recommended. The identifier is intended to denote some object, so it must have a unique (within a certain context) name: you cannot create a table with a name that is already found in the database; You cannot have columns with the same name in the same table. By the way, keep in mind that appointments and appointments are the same names for sql. It is not possible to create a new identifier by changing the case of letters alone.
Although a table may have only one column, in practice tables with multiple columns are usually required. The command to create such a table in general looks like this:
create table TableName (DataType Column [ ( , DataType Column ) ]) ;
Square brackets are used to indicate optional elements, curly brackets contain elements that can be a list of one-way constructions (when entering a real sql command, neither of these brackets are put). This syntax allows you to specify any number of columns. Note that the second element is preceded by a comma. If there are several parameters in the list, they are separated from each other by commas.
create table appointments2 (appointment_date date , appointment_time time , description varchar (256)) ;
This command creates the appointments2 table (the new table must have a different name, since the appointments table is already present in the database). Like the first table, it has an appointment_date column to record the date of appointments; in addition, there is an appointment_time column to record the times of these appointments. The description parameter is a text string that can contain up to 256 characters. The type specified for this parameter is varchar (short for character varying), since it is not known in advance how much space will be required for the record, but it is clear that the description will take no more than 256 characters. When describing a parameter in the character string type (and some other types), the length of the parameter is specified. Its value is given in parentheses to the right of the type name.
You may have noticed that in the two examples considered, the command entry is formatted differently. If in the first case the command is completely placed on one line, then in the second, after the first open parenthesis, the entry continues from a new line, and the definition of each next column begins from a new line. In sql, there are no special requirements for record formatting. Breaking a record into lines makes it easier to read. The sql language allows, when writing commands, not only to break the command into lines, but also to insert indents at the beginning of lines and spaces between record elements.
Now that you know the basic rules, let's look at a more complex example of creating a table with multiple columns. At the beginning of the article, the employees table was shown. It contains the following columns: last name, first name, date of employment, department, category, and salary for the year. The following sql command is used to define this table:
create table employees (last_name character (13) not null, first_name character (10) not null, hire_date date , branch_office character (15) , grade_level smallint , salary decimal (9 , 2)) ;
There are several new elements in the team. The first is the not null expression at the end of the last_name and first_name column definitions. With the help of such structures, requirements are set that must be observed. In this case, it is indicated that the fields last_name and first_name must be filled in when entering; you cannot leave these columns empty (this is quite logical: how can you identify an employee without knowing his name?).
In addition, there are three new data types in the example: character, smallint, and decimal. So far, we haven't talked much about types. Although there are no relational domains in sql, there is a set of basic data types. This information is used when allocating memory and comparing values; narrows down the list of possible input values to some extent, but sql's type control is less strict than in other languages.
All data types available in sql can be divided into six groups: character strings, exact numeric values, approximate numeric values, bit strings, datetime and intervals. We've listed all the variations, but this article will cover only some of them in detail (bit strings, for example, are not of particular interest to ordinary users).
By the way, if you thought that the datetime is a typo, then you are mistaken. This group (datetime) includes most of the time-related data types used in sql (parameters such as time intervals are in a separate group). In the previous example, two data types from the datetime group have already been encountered - date and time.
The next data type you're already familiar with is character varying (or just varchar); it belongs to the group of character strings. If varchar is used to store strings of variable length, then the char type encountered in the third example is intended for writing strings with a fixed number of characters. For example, the last_name column will contain strings of 13 characters, regardless of the last names actually entered, whether it is poe or penworth-chickering (in the case of poe, the remaining 10 characters will be filled with spaces).
From the user's point of view, varchar and char have the same meaning. Why was it necessary to introduce two types? The fact is that in practice you usually have to find a compromise between speed and disk space savings. As a rule, using fixed-length strings gives some gain in access speed, however, if the string length is too long, disk space is wasted. If in appointments2 for each line of the comment to reserve 256 characters, then this may turn out to be irrational; most often the lines will be much shorter. On the other hand, surnames also have different lengths, but they usually require about 13 characters; in this case, the losses will be minimal. There is a good rule of thumb: if the length of a string is known to change little or is relatively small, then use char; otherwise, varchar.
The next two new data types, smallint and decimal, belong to the group of exact numeric values. smallint is short for small integer. SQL also provides an integer data type. The presence of two similar types in this case is also explained by the consideration of saving space. In our example, grade_level values can be represented as a two-digit number, so the smallint type is used; however, in practice it is not always known what maximum values the parameters can have. If there is no such information, then use integer. The actual storage space for smallint and integer parameters and the corresponding range of values for these parameters are platform specific.
The decimal data type, commonly used for financial accounting, allows you to specify a template with the desired number of decimal places. Because this type is used for exact numeric notation, it guarantees precision when performing mathematical operations on decimal data. If you use data types from the approximate numeric notation group for decimal values, such as float (floating point number), this will lead to rounding errors, so this option is not suitable for financial calculations. The following notation is used to define parameters of the decimal type:
where p is the number of decimal places, d is the number of decimal places. Instead of p, you should write the total number of significant digits in the values used, and instead of d - the number of digits after the decimal point.
The "Creating a Table" sidebar shows the complete summary of the create table command. It contains new elements and shows the format for all the considered data types (In principle, there are other data types, but we do not consider them yet).
At first, it may seem that the syntax of sql commands is too complicated. But you can easily figure it out if you carefully studied the examples above. An additional element appeared on the diagram - a vertical line; it serves to distinguish between alternative designs. In other words, when defining each column, you need to choose the appropriate data type (as you remember, optional parameters are enclosed in square brackets, and constructions that can be repeated many times in curly brackets; these special characters are not written in real sql commands). The first part of the schema shows the full names for data types, the second part shows their abbreviated names; in practice, any of them can be used.
The first part of the article is completed. The second will be devoted to the study of the insert, select, update and delete dml commands. Also, data sampling conditions, comparison and logical operators, the use of null values and ternary logic will be considered.
Creating a table. Syntax of the create table command: optional parameters are indicated in square brackets, repeating constructions are indicated in curly brackets.
create table table (column character (length) [ constraint ] | character varying (length) [ constraint ] | date [ constraint ] | time [ constraint ] | integer [ constraint ] | smallint [ constraint ] | decimal (precision, decimal places) [ constraint ] | float (precision) [ constraint ] [( , column char (length) [ constraint ] | varchar (length) [ constraint ] | date [ constraint ] | time [ constraint ] | int [ constraint ] | smallint [ constraint ] | dec (precision, decimal places) [ constraint ] | float (precision) [ constraint ] )]) ;
sql name secret
In the early 1970s ibm began to put into practice the relational database model proposed by Dr. Codd. Donald Chamberlin and a group of others in the Advanced Research Unit created a prototype language called the structured english query language, or simply sequel. Later it was expanded and improved. The new variant proposed by ibm is called sequel/2. It was used as a programming interface (api) for designing ibm's first relational database system, system/r. For legal reasons, ibm decided to change the name from sequel/2 to sql (structured query language). This abbreviation is often pronounced as "si-ku-el".
SQL 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 fix queries that return incorrect results.
This article is part of the SQL for Access article series. 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 programs such as Microsoft Office Access use SQL to manipulate data. Unlike many programming languages, SQL is easy to read and understand even for beginners. Like many programming languages, SQL is an international standard recognized by standards committees such as ISO and ANSI.
Datasets are described in SQL to help answer questions. When using SQL, the correct syntax must be used. Syntax is a set of rules that allow the elements of a language to be combined correctly. SQL syntax is based on English syntax and shares many elements with Visual Basic for Applications (VBA) language syntax.
For example, a simple SQL statement that retrieves a list of the last names of contacts named Mary might look like this:
SELECT LastName
FROM Contacts
WHERE First_Name = "Mary";
Note: The SQL language is used not only to perform operations on data, but also to create and modify 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 and 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 to be retrieved from the database, including the following:
tables that contain data;
links 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;
need and method of sorting.
SQL statements
An SQL statement consists of several parts called clauses. Each clause in an SQL statement has a purpose. Some offers are mandatory. The table below lists the most commonly used SQL statements.
SQL clause | Description | Mandatory |
|---|---|---|
|
Defines the fields that contain the required data. |
||
|
Defines the tables that contain the fields specified in the SELECT clause. |
||
|
Defines the field selection criteria that all records included in the results must meet. |
||
|
Specifies the sort order for the results. |
||
|
In an SQL statement that contains aggregate functions, specifies the fields for which no summary value is calculated in the SELECT clause. |
Only if there are such fields |
|
|
In an SQL statement that contains aggregate functions, defines the conditions applied to the fields for which the summary value is calculated in the SELECT clause. |
SQL terms
Each SQL sentence is made up of terms that can be compared to parts of speech. The table below lists 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.[PhoneNumber] |
|
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 that evaluates to a single value. |
>= Goods.[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 the SQL statement. 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.
Each SELECT statement ends with a semicolon (;). The semicolon can be either at the end of the last sentence or on a separate line at the end of the SQL statement.
Example in Access
The following example shows what an SQL statement might look like in Access for a simple select query.
1. SELECT clause
2. FROM clause
3. WHERE clause
Let's break the example down by sentences to understand how the SQL syntax works.
SELECT clause
SELECT , Company
This is a SELECT clause. It contains a (SELECT) statement followed by two identifiers ("[Email address]" and "Company").
If the identifier contains spaces or special characters (for example, "E-mail address"), it must be enclosed in square brackets.
In the SELECT clause, you do not need to specify the tables that contain the fields, and you cannot specify the selection conditions that must be met by the data to be included in the results.
In a SELECT statement, the SELECT clause always comes before the FROM clause.
FROM clause
FROM Contacts
This is the FROM clause. It contains a statement (FROM) 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) operator followed by an expression (City="Rostov").
You can do many things with SELECT, FROM, and WHERE clauses. For more information about how to use these offers, see the following articles:
Sort results: ORDER BY
Like Microsoft Excel, Access allows you to sort the results of a query in a table. Using the ORDER BY clause, you can also specify how the results will be sorted when the query is executed. If an ORDER BY clause is used, it must be at the end of the SQL statement.
The ORDER BY clause contains a list of fields to sort on, in the same order in which the sort will be applied.
For example, suppose you want to sort the results first 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 values in descending order instead, you must specify the DESC keyword.
For more information about the ORDER BY clause, see ORDER BY clause.
Working with summary data: GROUP BY and HAVING clauses
Sometimes you need to work with summary data, such as total monthly sales or the most expensive items in stock. To do this, the SELECT clause applies an aggregate function to the field. For example, if the result of a query is to return 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 the available aggregate functions, see the SQL Aggregate Functions article.
Specifying fields 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 is not applied. If aggregate functions apply to all fields in a 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. In the GROUP BY clause, the fields are specified in the same order as in the SELECT clause.
Let's continue the previous example. If the SELECT clause only applies the aggregate function 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 GROUP BY clause.
Constraining aggregated values with grouping conditions: the HAVING clause
If you want to specify conditions to restrict results, but the field you want to apply them to is used in an aggregate function, you can't 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.
Suppose, for example, that the AVG function (which calculates the average value) is applied to the first field in the SELECT clause:
SELECT COUNT(), Company
If you want to restrict query results based on the value of the COUNT function, you cannot apply a filter condition to this field in the WHERE clause. Instead, the condition should be placed in a HAVING clause. For example, if you want the query to return rows only if the 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 the criteria for fields that are not used in the aggregate functions specified in the WHERE clause, and the conditions for fields that are used in the aggregate functions are specified in the HAVING clause.
For more information about the HAVING clause, see the HAVING clause.
Combining Query Results: The 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 combined SELECT statements must have the same number and order of output fields with the same or compatible data types. When a query is run, 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 SELECT statement individually.
Note: In union queries, numeric and text data types 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 join two SELECT statements has the following basic syntax:
SELECT field_1
FROM table_1
UNION
SELECT field_a
FROM table_a
;
Suppose, for example, that there are 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 offered product or service. Although the Products and Services tables have different types of guarantees, the basic information is the same (whether a quality guarantee is provided for individual products or services). To join four fields from two tables, you can use the following join query:
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 the article
SQL database query language appeared in the 70s. Its prototype was developed by IBM and is known as SEQUEL (Structured English QUEry Language). SQL has incorporated the advantages of the relational model, in particular, the fact that it is based on the mathematical apparatus of relational algebra and relational calculus, while using a relatively small number of operators and a simple syntax.
Due to its qualities, the SQL language became first "de facto", and then officially approved as the standard language for working with relational databases, supported by all the world's leading firms operating in the field of database technology. The use of an expressive and efficient standard language has now made it possible to ensure a high degree of independence of the developed application software systems from the specific type of DBMS used, to significantly raise the level and unify the tools for developing applications working with relational databases.
Speaking of the SQL language standard, it should be noted that most of its commercial implementations have more or less deviations from the standard. This, of course, worsens the compatibility of systems using different "dialects" of SQL. On the other hand, useful extensions of language implementations relative to the standard are a means of language development and are included in new editions of the standard over time.
A large number of books, including educational ones, are devoted to the SQL language, some of them are listed in the bibliography of this manual, in particular, the tutorial is specifically devoted to the practical study of the SQL language. In this regard, in this manual we will consider only important general features of this language that are important for the subsequent presentation of the material.
8.1. The difference between SQL and procedural programming languages
The SQL language belongs to the class of non-procedural programming languages. Unlike universal procedural languages, which can also be used to work with databases, the SQL language is not record-oriented, but set-oriented. This means the following. As input information for a database query formulated in SQL, the set record tuples one or more relation tables. The query also results in set of tuples the resulting relation table. In other words, in SQL, the result of any operation on a relation is also a relation. The SQL query does not specify a procedure, i.e. the sequence of actions required to obtain the result, and the conditions that the tuples of the resulting relation must satisfy, formulated in terms of the input (or input) relations.
8.2. Forms and Parts of SQL
Two forms of the SQL language exist and are used: interactive SQL
and embedded SQL.
Interactive SQL used to directly enter and receive the result of SQL queries by the user in interactive mode.
Embedded SQL consists of SQL commands embedded inside programs that are usually written in some other language (Pascal, C, C++, etc.). This makes programs written in such languages more powerful and efficient, allowing them to work with data stored in relational databases, however, requiring the introduction of additional tools that provide an interface to SQL with the language in which it is embedded.
Both interactive and embedded SQL are usually divided into the following parts.
Data Definition Language– DDL (Data Definition Language), makes it possible to create, modify and delete various database objects (tables, indexes, users, privileges, etc.).
Among the additional functions of the DDL data definition language, tools for determining data integrity constraints can also be included,
determining the order in data storage structures, describing the elements of the physical level of data storage.
Data Processing Language– DML (Data Manipulation Language),
provides the ability to select information from the database and transform the data stored in it.
However, these are not two different languages, but components of a single SQL.
8.3. Terms and terminology
Keywords are words used in SQL expressions that have a special meaning. For example, they may refer to specific SQL commands. Keywords cannot be used for other purposes, such as database object names.
SQL statements are statements by which SQL accesses a database. Statements are made up of one or more distinct logical parts called clauses. Sentences begin with the appropriate keyword and consist of keywords and arguments.
It should be noted that the terms used in the SQL language are somewhat different from the terms used to describe the relational model. In particular, instead of the term relation, it uses the term table , instead of the terms tuple and attribute , respectively, row and column .
8.4. Data sampling. Operator SELECT
The simplest SELECT queries
The SQL SELECT statement is the most important and most commonly used statement in SQL. It is designed to retrieve information from database tables. The simplified syntax of the SELECT statement is as follows.
SELECT< attribute list>
FROM< список таблиц>
Items in square brackets indicate items that may be missing from the query.
The SELECT keyword tells the DBMS that the given clause is a request to retrieve information. After the word SELECT, the names of the fields (a list of attributes), the contents of which are requested, are listed, separated by commas.
The required keyword in the SELECT clause is FROM. The FROM keyword is followed by a comma-separated list of table names from which information is retrieved.
For example,
SELECT NAME, SURNAME FROM STUDENT;
The SQL query must end with a semicolon. The above query fetches all values of the fields NAME and
SURNAME from STUDENT table.
Its result is a table of the following form
The order of the columns in this table matches the order of the NAME and SURNAME fields in the query, not their order in the input table
STUDENT.
Let's pay attention to the fact that the tables obtained as a result of the SQL query do not fully meet the definition of a relational relationship. AT
in particular, they may contain duplicate tuples with the same attribute values.
For example, the query: “Get a list of city names in which students live, information about which is in the STUDENT table”, can be written in the following form
SELECT CITY FROM STUDENT ;
The result will be a table
Belgorod
It can be seen that the same rows can occur in this table. They are in bold.
To exclude duplicate records from the result of a SELECT query, use the DISTINCT (excellent) keyword. If the SELECT query retrieves multiple fields, then DISTINCT eliminates duplicate rows in which the values of all selected fields are identical.
Introducing a SELECT statement, a clause defined by the WHERE (where) keyword, into an expression allows you to enter a conditional expression (predicate) that evaluates to true or false for the table row field values accessed by the SELECT statement. The WHERE clause specifies which rows of the specified tables should be selected. The table that is the result of the query includes only those rows for which the condition (predicate) specified in the WHERE clause evaluates to true.
Write a query that selects the names ( NAME ) of all students with the last name ( SURNAME ) Petrov, information about which is in the table
SELECT SURNAME, NAME
FROM STUDENT
WHERE SURNAME = ‘ Petrov ’;
The conditions specified in the WHERE clause can use the comparison operators specified by the following operators: = (equal to), > (greater than),< (меньше), >= (greater than or equal),<= (меньше или равно), <>(not equal), as well as the logical operators AND , OR and NOT .
For example, a query to get the names and surnames of students studying in the third year and receiving a scholarship (the size of the scholarship is greater than zero) will look like this
SELECT NAME, SURNAME FROM STUDENT
WHERE KURS = 3 AND STIPEND > 0 ;
8.5. Implementation of relational algebra operations by means of the SQL language. Relational completeness of SQL
AT In the previous sections devoted to the consideration of relational algebra, it was said that one of the important aspects of the presence of such a mathematical apparatus in the relational model is the possibility of estimating and provingrelational completeness practically used database query languages, in particular the SQL language. In order to show that the SQL language is relationally complete, one must show that any relational algebra operator can be expressed using SQL. In fact, it suffices to show that any of the primitive relational operators can be expressed using SQL. The following are examples of implementing relational operators using the SQL language.
Union operator
Relational algebra: A UNION B SQL statement:
SELECT * FROM A
SELECT * FROM B ;
Intersection operator
Relational Algebra: A INTERSECT B
SQL statement:
SELECT A. FIELD1, A. FIELD2, ...,
FROM A , B
WHERE A. FIELD1=B. FIELD1 AND A. FIELD2=B. FIELD2 AND …;
SELECT A.* FROM A, B
WHERE A.pk =B.pk;
subtraction operator
Relational algebra: A MINUS B SQL statement:
SELECT * FROM A
WHERE A.pk NOT IN (SELECT pk FROM B);
where A.pk and B.pk are the primary keys of tables A and B
Cartesian product operator
Relational algebra: A TIMES B SQL statement:
FROM A , B ;
SELECT A. FIELD1, A. FIELD2, …, B. FIELD1, B. FIELD2, …
FROM A CROSS JOIN B ;
projection operator
Relational algebra: A SQL statement:
SELECT DISTINCT X , Y , …, Z FROM A ;
Fetch Operator
Relational Algebra: A WHERE θ SQL Statement:
SELECT * FROM A
WHERE θ ;
θ-join operator
Relational algebra: (A TIMES B) WHERE θ SQL statement:
SELECT A. FIELD1, A. FIELD2, …, B. FIELD1, B. FIELD2, …
FROM A , B
WHERE θ ;
SELECT A. FIELD1, A. FIELD2, …, B. FIELD1, B. FIELD2, …
FROM A CROSS JOIN B WHERE θ ;
division operator
Relational algebra: A(X,Y) DEVIDE BY B(Y) SQL statement:
SELECT DISTINCT A . X FROM A
(SELECT *
(SELECT * FROM A A1
A1. X=A. X AND A1. Y=B. Y));
Thus, the above expressions prove that the SQL language, like relational algebra, is relationally complete.
You should pay attention to the fact that if in the above queries in the tables there are NULL -values (see section 9.1 below), then all of the above queries may work incorrectly, because. NULL< >NULL and NULL = NULL are false.
This, however, does not refute the conclusion made about the relational completeness of SQL, since NULL values are not supported by the relational model.
