Standard Query Language (SQL) is at the base of any relational database, including DB2 from IBM, Oracle, SQL Server from Microsoft and the MySQL open source database.
Although in theory it has been standardized for 20 years, its implementations range from a relational database management system (RDBMS) to another, which is why some of the skills are not easily transferable from one case to another. SQL is used in data manipulation in relational databases. With this structured query language, you can do various things: inserting data, querying, updating, deleting, modifying, and creating schemas. SQL has its origins in the relational model created by Ted Codd for data storage, being developed at IBM in 1970.
An embryonic form of language was the Standard English Query Language, and SQL, as we know it today, which formed the basis of IBM’s SQL-DS and DB2 databases that emerged in the early 1980s.
In 1979, Oracle won the battle in this area through RDBMS, based on SQL principles. SQL provides both enterprise and academic solutions, being used both on individual computers and on corporate servers. With advances in SQL-based technologies, this language has become more and more handy for many users.
To understand what kind of actions you can do in SQL, you need to know that there are three categories of commands:
1. Definitions of data – DDL – allows the definition of objects that model the system.
2. Changes for data manipulation – DML – allow for consultation, updating, suppression or insertion of data.
3. Data Control Command – DCL – ensures data confidentiality and integrity, save information, makes physical changes to the database, resolves competition issues.
Despite the differences in its implementation, SQL provides the basis for exchanging and querying data from different sources. The SQL query has remained as high despite the decline of some of the RDBMS providers, and the skills needed to work with SQL are transferable, though sometimes additional training is required in order to use the new RDBMS solutions properly. SQL allows both access to the contents of databases and their structure.
Any column (or field) in a SQL table is assigned a data type, as in all other programming languages. The data types are as follows:
CHARACTER (or CHAR) – character string
INTEGER (or SMALLINT) – integer
FLOAT, REAL or DOUBLE PRECISION – a real number
NUMERIC (precision, scale) or DECIMAL (precision, scale) – decimal number, where “precision” means the number of digits from the whole, “scales” means the number of decimal places.
DATE – date of the day.
TIME – hour.
The NOW system function returns the current date and time.
Queries
The most commonly used statement in SQL is the SELECT statement.
The INTO clause is used to transfer the query result to a new table; valid in Microsoft Access but not on all SQL platforms.
The WHERE clause is used to specify conditions must meet the columns from which the selection is made.
The GROUP BY clause allows multiple columns to be grouped for processing by aggregate functions: AVG – arithmetic mean; COUNT – number of items; MAX – maximum; MIN – minimum; SUM – Amount.
HAVING clause Unlike the WHERE clause, it acts on the rows resulting from the GROUP BY clause, applying their condition to being further processed by aggregate functions.
Order BY Order the query results in alphabetical order after one or more fields. ASC stands in ascending order, and DESC – descending order. The default order is increasing.
Other Keywords: ALL – All Articles;
DISTINCT – Unique items without duplicates.
Ternary Logic (3VL)
The idea was introduced into SQL to assign the value of the Null video recordings and missing information in a field.
Data Handling
Data Handling Language (DML) is a subset of SQL used to add, update, or delete data.
INSERT – inserts an article into a table:
UPDATE – Updates a set of articles:
DELETE – Deletes a set of items.
Transactions
Transactions are used to control under what conditions a sequence of data handling instructions is performed.
Instructions
START TRANSACTION (or BEGIN WORK, BEGIN TRANSACTION, depending on the SQL dialect) Begin a transaction.
SAVE TRANSACTION (or SAVEPOINT) saves the status of the base at a point in the transaction
COMMIT Operates all transaction transactions as permanent.
ROLLBACK Cancels all transaction transactions from the last COMMIT.
The COMMIT and ROLLBACK instructions terminate the current transaction and unlock the data.
SQL Network User Rights
The Data Control Language (DCL) authorizes users and groups working with a particular database.
Instructions:
GRANT grants one or more rights to a user or group.
REVOKE (reverse operation) removes one or more rights to a user or group.