Introduction to ANSI SQL
We human beings communicate with each other with the help of language. Similiarly, SQL stands for Structured Query Language is the language that a database understands, and we will communicate to the database using SQL. SQL is a 4TH generation database gateway language standardized by ANSI (American National Standards Institute) for managing data held in a RDBMS (Relational Database Management Systems).
Why learning SQL is important
SQL is used to create, manage, manipulate and query the database objects such as tables, views etc. SQL is the building block for those working with data in database management systems. Once you master SQL, you are good to work in any data domain that can be Data Integration, Data Quality, Data warehousing and Business Intelligence etc. A person with strong SQL background can visualize and understand data much better. Now lets start learning this language in a series of articles. Here we will focus on ANSI SQL commands. Although some examples will contain SQL commands that are specific to Oracle or MS SQL server RDBMS.
SQL COMMANDS
The scope of SQL includes schema creation and modification, data access control, data insert, query, update and delete. SQL Commands are broadly classified into the following categories:
Data Definition Language (DDL)
- CREATE, ALTER, TRUNCATE, DROP
Data Manipulation Language (DML)
- INSERT, UPDATE, DELETE, MERGE
- SELECT
Transaction Control Language (TCL)
- COMMIT, ROLLBACK
Data Control Language (DCL)
- GRANT, REVOKE
ANSI Data types
Each column in an RDBMS table specifies/declares the types of data that the columns stores. This enables RDBMS to use storage space more efficiently by internally storing different types of data in different ways. ANSI SQL includes the following basic data types.
CHARACTER
- CHARACTER(n) or CHAR(n): Fixed width n-character string, padded with spaces as needed.
- CHARACTER VARYING(n) or VARCHAR(n): Variable width string with a maximum size of n characters.
- NATIONAL CHARACTER(n) or NCHAR(n): Fixed width n-character string supporting an international character set(Unicode Character).
- NATIONAL CHARACTER VARYING(n) or NVARCHAR(n): Variable width string with a maximum size of n characters supporting an international character set.
NUMERIC
- SMALLINT, INTEGER OR INT
- FLOAT, REAL and DOUBLE PRECISION
- NUMERIC(precision, scale) or DECIMAL(precision, scale) (e.g. 1234.56)
The number 1234.56 has a precision of 6 and a scale of 2. A scale of 0 indicates that the number is an integer.
DATE
- DATE: Date values (e.g. 1999-12-31).
- TIME: Time values (e.g. 23:30:10). The granularity of the time value is usually a tick (100 nanoseconds).
- TIMESTAMP: Date and a Time put together (e.g. 1999-12-31 23:30:10).
BIT
- BIT: bit values (e.g. 0 or 1)
Additional NON-ANSI LARGE OBJECT datatypes - CLOB, BLOB, LONG, RAW, LONG RAW (Oracle specific); VARBINARY(n) (MSSQL Server specific)
Procedural Language - SQL
Apart from SQL being a set-based declarative language (4GL), it also includes procedural elements. There are extensions to Standard SQL which add procedural programming language functionality, such as control-of-flow constructs. For example PL/SQL in Oracle, T-SQL in MS SQL server SPL in Teradata etc.