SQL - SQL Introduction
SQL - An Introduction to the Structured Query Language
SQL stands for Structured Query Language (SQL), a standard language used for creating, updating, querying and retrieving relational database management systems (RDBMS). It is used for communicating with the database comprising editing, adding, and updating data in the database and, accessing data from the database.
It is a standard language of two renowned international organizations - International Organization for Standardization (ISO) and the American National Standards Institute (ANSI). It was first developed by Donald D. Chamberlin and Raymond F. Boyce at IBM in the early 1970s.
According to ANSI, SQL is a standard language for RDBMS, and SQL statements are used to establish relations between tables of data of database systems. A database table consists of structured records divided into fields of data. SQL consists of structured statements used in creating tables, and also adding, deleting, and modifying records within the table.
SQL is being used by most of the database systems including Oracle, Sybase, Microsoft SQL Server, Microsoft Access, Ingres, Informix and MySQL etc. with slice modification in the statements and commands. The standard SQL commands like ‘Select’, ‘Insert’, ‘Update’, ‘Delete’, ‘Create’, and ‘Drop’ are common in most of the DBMS that performs almost everything that one needs to do with a database.
History
SQL was derived from its initial version of Structured English Query Language (SEQUEL or SEQL) developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in 1970s to manage data stored in IBM’s original database product system R. Later the acronym SEQUEL was changed to SQL. IBM patented this version of SQL in 1985.
ANSI standardized SQL in 1986 while ISO standardized it in 1987. The earlier standards were not complete, while the later revised versions have advanced the language to include object-relational concepts, integrity management and call level interfaces.
Before this, RDMS, the first relational database management system was developed at MIT, U.C. Berkeley in the early 1970s and Ingres in 1974. Ingres implemented a query language known as QUEL, which was later superseded by SQL in the market.
IBM launched it in the market for business purpose in 1979, 1981, and 1983, respectively after several testing at customer test sites.
Language elements
The SQL language is sub-divided into several language elements, including:
Clauses – It is optional, constituent components of statements and queries.
Expressions - It produces either scalar values or tables consisting of columns and rows of data.
Predicates – It specify conditions that can be evaluated to SQL three-valued logic (3VL) Boolean truth values and which are used to limit the effects of statements and queries, or to change program flow.
Queries - It retrieve data based on specific criteria.
Statements - Statements may have a persistent effect on schemas and data, or which may control transactions, program flow, connections, sessions, or diagnostics.
Semicolon – Semicolon (";") is used for statement terminator, though it is not essential on every platform.
Whitespace: Insignificant whitespace is used for making more readable SQL code format that is generally ignored in SQL statements and queries.
Queries
The SQL queries are the most common and essential SQL operations, which is performed with the declarative SELECT statement. The SELECT statement retrieves data from one or more statements or expressions. An asterisk can specify the query while SQL query can be made more specific with the help of several clauses includes:
FROM – It indicates the table from which the data is to be retrieved. The FROM clause can include optional JOIN subclauses to specify the rules for joining tables
WHERE – It is used to define the rows, in which the search will be carried. It eliminates all rows, for which the WHERE clause is not true.
ORDER BY – This clause is used for sorting the resulting data from the database either ascending or descending order. Without this clause, the order of rows returned in random order.
GROUP BY – This clause is used for grouping rows having common values into a smaller set of rows. It is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. The GROUP BY clause is used after the WHERE clause.
HAVING – With the inclusion of predicate this clause is used for filtering rows resulting from the GROUP BY clause.
Data Definition
The Data Definition Language (DDL) manages table and index structure. The most basic items of DDL are the CREATE, ALTER, RENAME, DROP and TRUNCATE statements:
CREATE – This statement creates an object (for example, a table) in the database.
DROP – This statement deletes an object usually permanently in the database.
ALTER – Alter statement modifies the structure of an existing object in various ways. e.g. adding a column to an existing table. 0
Data Types
The data type refers to the data, which are declared in SQL within the program. Each column in an SQL table declares the type(s) that column may contain. ANSI SQL includes the following datatypes:
Character strings 1
CHARACTER or CHAR — Fixed-length character string. Size is specified in parenthesis; padded with spaces as needed. Max 255 bytes.
CHARACTER VARYING or VARCHAR — variable-length character string with a maximum size of n characters. Max size is specified in parenthesis.
NATIONAL CHARACTER or NCHAR — fixed width string supporting an international character set 2
NATIONAL CHARACTER VARYING or NVARCHAR — variable-width NCHAR string
Bit strings
BIT(n) — an array of n bits
BIT VARYING(n) — an array of up to n bits
3
Numbers
Number value with a max number of column digits specified in parenthesis.
- INTEGER() and SMALLINT()
- FLOAT (), REAL() and DOUBLE PRECISION()
Number value with a maximum number of digits of ‘size’ total, with a maximum number of ‘d’ digits to the right of the decimal. 4
- NUMERIC(precision, scale) or DECIMAL(precision, scale)
Date and Time
This defines the date and time value.
DATE
TIME
TIMESTAMP
INTERVAL
5