Introduction to ANSI SQL

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

Data Query Language (DQL)

- 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.