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:

  1. Data Definition Language (DDL)

    - CREATE, ALTER, TRUNCATE, DROP

  2. Data Manipulation Language (DML)

    - INSERT, UPDATE, DELETE, MERGE

  3. Data Query Language (DQL)

    - SELECT

  4. Transaction Control Language (TCL)

    - COMMIT, ROLLBACK

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

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

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

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

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


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • DW Implementation Using EDB Postgres

    PostgreSQL is an advanced open source relational database management system. EDB develops and integrates performance, security, and manageability enhancements into PostgreSQL to support enterprise-class workloads for its database, EDB Postgres...

  • SQL- Data Definition Language

    The Data Definition Language (DDL) manages database table and index structure. The most basic commands of DDL are the CREATE, ALTER, RENAME, DROP and TRUNCATE statements.

  • Top 20 SQL Interview Questions with Answers

    SQL is a language for accessing and manipulating database standardized by ANSI. To be successful with database-centric applications (which includes most of the applications Data Warehousing domain), one must be strong enough in SQL. In this...

  • SQL- Data Manipulation Language

    The Data Manipulation Language (DML) manages data operation like INSERT, UPDATE, DELETE to database tables. The most basic commands of DML are the INSERT, UPDATE, DELETE and MERGE statements.

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

  • Complex Xml Generation from Database

    In this article we will generate complex XML tags/file directly from Database tables using simple SQL SELECT statement. Almost every database vendors provides support for XML read and write functionality. So let us exploit the available database...

  • SQL- Data Query Language

    Structured Query Language (SQL) can be further subdivided in multiple parts based on its functionality. Data Query Language (DQL) mainly deals with SQL SELECT statement for retrieving data from a database