How does SQL work. SQL Language Structure and General concepts

SQL is a language specifically oriented towards relational databases (RBDs). It does a lot of work that you would have to do if you had used a universal programming language such as C. To form a RBD in C, you would have to start from scratch.

You would have to define an object called a table that could grow to have any number of rows, and then gradually create procedures to insert and extract values.

If you wanted to find some specific rows, you would have to follow a step by step procedure similar to the next one:

1. Consider the row of the table.
2. Perform a check to see if this row is one of the rows you need.
3 If so, save it somewhere until the whole table is checked.
4. Check to see if there are any other rows in the table.
5. If there are, go back to step 1.
6. If there are no more rows, print out all values saved in step 3.

(Of course, this is not the actual set of C commands, but only the logic of the steps that should have been included in the actual program). SQL will do it all for you. Commands in SQL can work with all groups of tables as a single object and can process any amount of information extracted or obtained from them as a single module.

WHAT’S ANSI DOING?

The SQL standard is defined using ANSI (American National Standards Institute) code. SQL was not invented by ANSI. It is essentially an invention of IBM. But other companies picked up SQL immediately. At least one company (Oracle) regained the right to market sale of SQL products from IBM.

After a number of competing SQL programs appeared in the market, ANSI defined a standard to which they should be brought. (The definition of such standards is a function of ANSI). However, there have been some problems since then. They came about as a result of ANSI standardization, in the form of some limitations. Since ANSI does not always define what is most useful, programs try to conform to the ANSI standard without letting it restrict them too much. This, in turn, leads to random inconsistencies.

Database programs usually give ANSI SQL additional features and often weaken many of the restrictions. Therefore, common types of ANSI will also be considered. Although we will obviously not be able to consider every exception or variety, good ideas tend to be implemented and used in different programs, even when they are not defined by the ANSI standard. ANSI is a type of minimum standard, and you can do more than it allows, although you must follow its instructions when performing the tasks that it defines.

INTERACTIVE AND NESTED SQL

There are two SQL: Interactive and Nested. For the most part, both forms work the same way but are used differently. Interactive SQL is used to function directly in the database to produce output for use by the customer. In this SQL – when you enter a command, it will be executed immediately, and you will be able to see the output immediately (if it succeeds at all).

Nested SQL consists of SQL commands placed inside programs that are usually written in a different language (like COBOL or PASKAL). This makes these programs more powerful and efficient.

However, allowing these languages, you have to deal with the structure of SQL and data management style, which requires some extensions of interactive SQL. Passing SQL commands to nested SQL is “passed off” for variables or parameters used by the program in which they were nested.

In this site we will present SQL in an interactive form. This will give us the opportunity to discuss commands and their actions without having to worry about how they relate to other languages through an interface. Interactive SQL is the form that is most useful for non-programmers. Everything you learn about Interactive SQL is mostly applicable to nested form as well. Changes needed to use the nested form will be covered in the last chapter of this book.

SQL DEFINITIONS

Both interactive and nested SQL forms have numerous parts or subdivisions. Since you are likely to encounter this terminology when reading SQL, we will give some explanations. Unfortunately, these terms are not widely used in all implementations. They are specified by ANSI and are useful on a conceptual level, but most SQL programs practically do not process them separately, so they essentially become functional categories of SQL commands.

DDL (Data Definition Language)

DDL (Data Definition Language) – so called Scheme Description Language in ANSI – consists of commands that create objects (tables, indexes, views and so on) in the database.

DML (Data Manipulation Language)

DML (Data Manipulation Language) is a set of commands that define which values are represented in tables at any given time.

DCD (Data Management Language)

DCD (Data Management Language) consists of tools that determine whether a user is allowed to perform certain actions or not. They are part of the DDL in ANSI.

Do not forget these names. They are not different languages, but SQL command partitions grouped by their functions.

DIFFERENT DATA TYPES

Not all types of values that can be found in the table fields are logically the same. The most obvious difference is between numbers and text. You cannot put numbers in alphabetical order or subtract one name from another.

Since RBD systems are based on the links between information fragments, different data types must be different from each other so that the corresponding processes and comparisons can be performed in them. In SQL, this is done by assigning a data type to each field, indicating the type of value that the field may contain.

All values in this field must be of the same type. In a customer table, for example, cname and city contain lines of text for evaluation, and snum and cnum are numbers. For this reason, you cannot enter a Highest value or a None value in the rating field that has a numeric data type. This limitation is good because it imposes some structure on your data. You will often compare some or all of the values in this field, so you can only perform the action on certain lines, not all of them. You would not be able to do this if the field values had a mixed data type.

Unfortunately, the definition of these data types is the main area in which most commercial database programs and the official SQL standard do not always coincide. ANSI SQL standard recognizes only text and number, while most commercial programs use other special types.

Such as DATA and TIME are in fact almost standard types (although the exact format changes). Some packages also support types such as MONEY and BINARY. (MONEY is a special “currency” calculation system used by computers).

All information in a computer is transmitted in binary numbers and then converted to other systems so that we can easily use and understand them.

ANSI defines several numeric types, the differences between which are quite subtle and sometimes confusing. The permitted ANSI data types are listed in Appendix B. The complexity of ANSI numeric types can be explained, at least in part, by trying to make nested SQL compatible with a number of other languages.

The two types of ANSI numbers, INTEGER (TOTAL NUMBER) and DECIMAL (DECIMAL) (which can be abbreviated as INT and DEC, respectively), will be adequate for our purposes, as well as for most practical business applications. Naturally, the type of OBJECTIVE can be represented as a DECATERIC NUMBER that does not contain any numbers to the right of the decimal point.

The type for text is CHAR (or SIMVOL), referring to a line of text. The CHAR type field has a length determined by the maximum number of characters that can be entered in this field. Most implementations also have a non-standard type called VARCHAR, which is a text string and can be any length up to a certain implementation maximum (usually 254 characters). CHARACTER and VARCHAR values are included in single quotes as ‘text’. The difference between CHAR and VARCHAR is that CHAR must reserve enough memory for the maximum length of the string, and VARCHAR allocates memory as needed.

Character types consist of all printed characters, including numbers. However, number 1 is not the same as character “1”. The symbol “1” is only a printed text fragment and is not defined by the system as numeric value 1. For example, 1 + 1 = 2, but “1” + “1” does not equal “2”. The symbolic values are stored in the computer as binary values, but are shown to the user as printed text.

Conversion is performed according to the format defined by the system you are using. This conversion format will be one of two standard types (possibly with extensions) used in computer systems: ASCII code (used in all personal and small computers) and EBCDIC code (Extended Binary Tensile Information Exchange Code) (used in large computers). Certain operations, such as alphabetically ordering field values, will change with the format. The application of these two formats will be discussed in Chapter 4.

We should be monitoring the market, not ANSI, in using the DATE type. (In a system that does not recognize the DATE type, you can of course declare the date as a character or numeric field, but that will make most operations more time consuming.)

You should review your documentation for the software package that you will be using to find out exactly what types of data it supports.

SQL: INCONSISTENCIES

You can understand from the previous discussion that there are individual inconsistencies between products in the SQL world. SQL emerged from the commercial database world as a tool and was later turned into an ANSI standard. Unfortunately, ANSI does not always define the most useful, so programs try to comply with the ANSI standard, not allowing it to limit them too much. ANSI is a kind of minimum standard – you can do more than it allows, but you must be able to get the same results for the same task.

WHAT IS A “USER”?

SQL is usually found in computer systems that have more than one user and therefore must distinguish between them (your PC family can have any number of users, but it usually has no way to distinguish between one user and another).

Usually in such a system, each user has some kind of authorization code that identifies him or her (terminology changes). At the beginning of a session with a computer, a user logs on (registers), telling the computer who that user is, identifying himself or herself with a certain ID (Identifier).

Any number of people using an Access ID are individual users; and, similarly, one person may represent a large number of users (at different times) using different SQL access IDs. Actions in most SQL environments are led to a special Access Identifier that exactly matches a particular user.

The table or other object belongs to the user, who has full power over it. The user may or may not have the privilege to perform an action on the object. For our purposes, we will agree that any user has the necessary privileges to perform any action until we return specifically to the discussion of privileges in Chapter 22. The special value USER may be used as an argument in a command. It indicates the available ID of the user who issued the command.

SQL SYMBOLS AND TERMINOLOGY

Keywords are words that have special meaning in SQL. They can be commands, but not text or object names. We will select keywords by typing them in header letters. You should be careful not to confuse keywords with terms.

SQL has certain special terms that are used to describe it. These include words such as “query”, “sentence” and “predicate”, which are important in describing and understanding the language, but do not mean anything independent of SQL.

Commands or sentences are instructions by which you refer to the SQL database.

Commands consist of one or more separate logical parts, called sentences.

Suggestions begin with a keyword and consist of keywords and arguments. For example, sentences that you may encounter are “FROM Salespeople” and “WHERE city = “London”. Arguments complete or change the meaning of the sentence. In the examples above, Salespeople is the argument and FROM is the keyword of the FROM sentence. Similarly, “city = “London” is the argument of the WHERE sentence.

Objects are database structures to which names are given and which are stored in memory. This includes base tables, views and indexes.

To show you how commands are formed, we will do this by examples. There is, however, a more formal method of describing commands that uses standardized symbols. We will use it in the following chapters for convenience to understand these notations in case you encounter them in other SQL documents.

Square brackets ( [ ] ) will indicate parts that may not be used, and ellipses ( …) indicate that anything preceding it can be repeated any number of times. Words in angle brackets (< >) are special terms that explain what they represent. We have greatly simplified standard SQL terminology, but without making it any harder to understand.

Now you know something about SQL

Now you know something about SQL: what its structure is, how it’s used, how it represents data and how it’s defined (and some inconsistencies that come up), some of the symbols and terms used to describe it. All of this is too much information for one chapter; we don’t expect you to remember all of these details, but you can go back to them later if you need to.

What is Database & SQL?